PostgreSQL行读取锁 [英] PostgreSQL row read lock

问题描述

比方说，我有一个名为取款的表（id，金额，user_id，状态）。

Let’s say I have a table called Withdrawals (id, amount, user_id, status).

每当我开始取款时，这就是流程：

Whenever I a withdrawal is initiated this is the flow:

• 验证用户是否有足够的余额（根据收到的金额之和-取款金额之和计算）


• 插入金额，user_id和status ='pending'
的行

• 通过gRPC调用第三方软件以发起提款（实际上是汇款），等待响应


• 一旦我们做出肯定的答复，立即更新状态为已完成的行；如果提款失败，则删除条目。

• Verify if user has sufficient balance (which is calculated as sum of amount received - sum of withdrawals amount)
• Insert row with amount, user_id and status=‘pending’
• Call 3rd party software through gRPC to initiate a withdrawal (actually send money), wait for a response
• Update row with status = ‘completed’ as soon we a positive response or delete the entry if the withdrawal failed.

但是，我在此流程中存在并发问题。
假设用户在〜50毫秒内发出了2次全额提款请求：

However, I have a concurrency problem in this flow. Let’s say the user makes 2 full balance withdrawal requests within ~50 ms difference:

请求1

• 用户有足够的余额


• 创建提现（余额= 0）


• 更新提现状态

请求2（约50毫秒后）

Request 2 (after ~50ms)

• 用户有足够的权限余额（这是不正确的，尚未存储其他插入内容）


• 创建提现（余额= negative）


• 更新提现状态

现在，我们正在使用Redis将提款锁定在特定用户（如果它们在x ms内），以避免这种情况，但这是不是最可靠的解决方案。在我们目前使用当前解决方案开发针对企业的API的过程中，我们将阻止可能同时提出的提款请求。
有什么方法可以锁定并确保随后的插入查询根据取款表的user_id等待吗？

Right now, we are using redis to lock withdrawals to specific user if they are within x ms, to avoid this situation, however this is not the most robust solution. As we are developing an API for businesses right now, with our current solution, we would be blocking possible withdrawals that could be requested at the same time. Is there any way to lock and make sure consequent insert queries wait based on the user_id of the Withdrawals table ?

推荐答案

这是事务隔离的属性。关于它的文章很多，我强烈推荐设计数据密集型应用程序。我发现它对增进我的个人理解是最有用的描述。

This is a property of transaction isolation. There is a lot written about it and I would highly recommend the overview in Designing Data-Intensive Applications. I found it to be the most helpful description in bettering my personal understanding.

默认的postgres级别为读取已提交，即使这些并发交易都应该是依存的，它也可以使每个并发交易看到类似的情况（资金可用状态）。

The default postgres level is READ COMMITTED which allows each of these concurrent transactions to see a similiar (funds available state) even though they should be dependent.

一种解决方法是将每笔交易标记为 SERIALIZABLE一致性。

One way to address this would be to mark each of these transactions as "SERIALIZABLE" consistency.

SERIALIZABLE当前事务的所有语句只能看到
行在提交之前在此事务中执行了第一个查询或数据修改语句
。如果在并发的可序列化事务中进行
的读写模式会导致情况
，而这些事务的任何串行（一次）执行
都不会发生，其中的一个会因
serialization_failure错误而回滚。

SERIALIZABLE All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error.

这应该以提高应用程序正确性为代价可用性，即在这种情况下，第二笔交易将不允许修改记录，并且将被拒绝，这将需要重试。对于POC或低流量应用程序，这通常是一个完全可以接受的第一步，因为您可以立即确保正确性。

This should enforce the correctness of your application at a cost to availability, Ie in this case the second transaction will not be allowed to modify the records and would be rejected, which would require a retry. For a POC or a low traffic application this is usually a perfectly acceptable first step as you can ensure correctness for right now.

在上述参考书中，我也认为存在一个有关ATM如何处理可用性的示例。他们考虑到了这种竞争状况，如果用户无法连接到集中式银行，但他们限制了最大提款额以使爆炸半径最小化，则用户会透支！

Also in the book referenced above I think there was an example of how ATM's handle availability. They allow for this race condition and the user to overdraw if they are unable to connect to the centralized bank but bound the maximum withdraw to minimize the blast radius!

另一种解决此问题的体系结构方法是使事务脱机并使它们异步，以便将每个用户调用的事务发布到队列中，然后通过让队列的单个使用者自然避免任何比赛条件。这里的权衡类似，只有一个工人可以提供固定的吞吐量，但这确实有助于解决当前的正确性问题：P

Another architectural way to address this is to take the transactions offline and make them asynchronous, so that each user invoked transaction is published to a queue, and then by having a single consumer of the queue you naturally avoid any race conditions. The tradeoff here is similar there is a fixed throughput available from a single worker, but it does help to address the correctness issue for right now :P

跨机器锁定（例如在postgres / grpc中使用redis）称为分布式锁定，并且对此有很多描述 https://martin.kleppmann.com/2016/02/08/how-to-do-distributed-locking.html

Locking across machines (like using redis across postgres/grpc) called distributed locking and has a good amount written about it https://martin.kleppmann.com/2016/02/08/how-to-do-distributed-locking.html

这篇关于PostgreSQL行读取锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！