我有一个列为sourceId、destinationId、amount、status的Operations表。每当用户进行转账时，API都会在该表中插入一个新行，在之后，通过计算信用操作的总和减去借记操作的总和来检查用户的余额。只有当余额大于或等于转账金额时，操作才会插入成功状态。
问题是并发性，因为用户同时执行多个传输可能会导致负平衡。
有多种方法可以处理PostgreSQL的并发问题：
- 可序列化事务隔离级别
- 工作台锁定
- 行版本控制
- 行锁定
等
我们的预期行为是，数据库应该等待上一个事务完成，在不将事务隔离级别设置为SERIALIZABLE的情况下获得最新插入的版本，而不是在(sourceId, version)上因唯一冲突而失败。
然而，我不完全确定最佳方法。以下是我尝试过的：
1.可序列化事务隔离级别
这是最简单的方法，但问题是锁定升级，因为如果数据库引擎负载过重，一个事务可以锁定整个表，这就是记录的行为。
伪代码：
newId = INSERT INTO "Operations" ("SourceId", "DestinationId", "Amount", "Status", "OccuredAt") values (null, 2, 3, 100, 'PENDING', null);
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM "Operations" WHERE ("SourceId" = 2 or "DestinationId"=2) and "Status" = 'SUCCESSFUL';
'''API: check if balance > transfer amount'''
UPDATE "Operations" SET "Status" = 'SUCCESSFUL' where id = newId
COMMIT;
2.表锁定
这是我们希望通过不使用可串行化事务级别来避免的
3.行版本控制
这种方法似乎是迄今为止性能最好的方法。我们在(sourceId, version)上添加了一个列version int和一个唯一索引，当插入事务时，它将与下一个版本一起插入。如果两个事务是并发的，数据库会抛出一个错误：
重复键值违反唯一约束"IX_Transactions_SourceWalletId_Version；
伪代码：
newId = INSERT INTO "Operations" ("SourceId", "DestinationId", "Amount", "Status", "OccuredAt") values (null, 2, 3, 100, 'PENDING', null);
BEGIN;
lastVersion = SELECT o."Version"
FROM "Operations"
WHERE ("SourceId" = 2) AND ("Version" IS NOT NULL)
ORDER BY o."Version" DESC
LIMIT 1
SELECT * FROM "Operations" WHERE ("SourceId" = 2 or "DestinationId"=2)
and "Status" = 'SUCCESSFUL';
'''API: check if balance > transfer amount'''
UPDATE "Operations" SET "Status" = 'SUCCESSFUL', "Version" = lastVersion + 1 where id = newId;
COMMIT;
4.行锁定
在计算用户余额之前，使用sourceWalletId = x锁定所有交易行(其中x是进行转账的用户)。但我在PostgreSQL中找不到这样做的方法，使用for update可以做到这一点，但在并发事务等待第一个事务后，结果不会返回新插入的行，这是PostgreSQL的记录行为。
使用
for update可以做到这一点，但在并发事务等待第一个事务后，结果不会返回新插入的行，这是PostgreSQL的记录行为。
这是真的，但也不是一个挡箭牌。是的，在默认的READ COMMITTED事务隔离中，每条语句只看到在查询开始前提交的行请注意，查询，而不是事务。参见：
- 在PostgreSQL 9.1中，并发值修改会影响单选吗
获取锁后，只需在同一事务中启动下一个查询。
假设一个表每个(相关的)用户正好包含一行(就像您应该拥有的那样)。我称之为"your_wallet_table"，基于引用的"sourceWalletId":
BEGIN;
SELECT FROM "your_wallet_table" WHERE "sourceWalletId" = x FOR UPDATE;
-- x is the user making the transfer
-- check the user's balance (separate query!)
INSERT INTO "Operations" ... -- 'SUCCESSFUL' or 'PENDING'
COMMIT;
只有在没有其他事务对同一用户进行操作时才会获取锁，并且只有在事务结束时才会释放锁。
下一个事务将在其下一个语句中看到所有提交的行。如果所有交易都坚持这种做法，一切都会好起来的。当然，不能允许事务更改影响其他用户余额的行。
相关：
- 如何在PostgreSQL中使用带有ON CONFLICT的RETURNING
示例中的第一个变量，可序列化变量，是唯一一个可以保证正确行为的变量，如果更新计数为零或事务已回滚，则代码不必重试事务
示例中的数字3看起来有点像乐观锁定，可能看起来更具性能，但这取决于负载的类型。更新索引(在您的情况下是唯一索引)也可能会影响性能。通常，您对索引上的锁的控制较少，这会使情况的确定性降低或更难推理。此外，在低于REPEATABLE read的任何事务隔离级别中，仍然可能出现不同的读取值。
另一件事是，您的实现在以下场景中表现不正确：
- 进程1启动并读取版本1
- 进程2启动并读取版本1
- 进程2成功并写入版本2
- 进程3启动并读取版本2
- 进程3成功并写入版本3
- 进程1成功并写入版本2//NO VIOLATION
有效的是乐观锁定，它看起来有点像你的数字3。伪代码/SQL:
BEGIN
SELECT "version", "amount" FROM "operations" WHERE "id" = identifier
// set variable oldVersion to version
// set variable newVersion to version + 1
// set variable newAmount to amount + amountOfOperation
IF newAmount < 0
ROLLBACK
ELSE
UPDATE "operations" SET "version" = newVersion, "amount" = newAmount WHERE "id" = identifier AND "version" = oldVersion
COMMIT
ENDIF
这不需要包含版本的唯一索引。通常，即使具有READ COMMITTED事务隔离级别，UPDATE的WHERE条件中的查询和更新本身也是正确的。我不确定PostGreSQL：请在文档中验证这一点！
一般来说，我会从可序列化的数字1示例开始，直到实际用例中的测量结果表明这是一个问题。然后尝试乐观锁定，看看它是否有所改进，还有实际的用例。
请记住，如果UPDATE报告没有更新行，或者事务失败，则代码必须始终能够从BEGIN到END回放事务。
祝你好运，玩得开心！
您将不得不承受性能打击。CCD_ 20隔离是最简单的方法。您可以增加max_predicate_locks_per_relation、max_predicate_locks_per_xact或max_predicate_locks_per_page(取决于您达到的限制)以稍后升级锁定。
或者，您可以有一个存储每个用户余额的表，该表由延迟触发器更新。然后可以在该表上设置一个检查约束。这将只序列化每个用户的操作。