database - 数据库如何锁定行并释放它们？

Question

最近，我们遇到了一个死锁问题。我们系统的两个部分将在相似的时间更新同一个表，一个更新(命名为“UP1”)事务中的几行，另一个(命名为“UP2”)使用类似“update ... where id在(...)”。

似乎是因为 sql“update ... where id in (...)”中的 ids 是乱序的，例如“5,6,2,3,4,1”；而在事务中，update action会按照“1,2,3,4,5,6”的顺序进行。当“UP1”更新id“1,2,3,4”和“UP2”更新id“5,6”时，“UP1”想要被“UP2”锁定的id 5，而“UP2”想要被“UP1”锁定的id 2，所以死锁就出来了。

我的问题是，数据库是逐行锁定，只有在sql或事务完成时才释放它们吗？如果不是，为什么不能在事务开始或"where id in (...)"sql 中锁定所有行，为什么不能将更新的行一一释放？

期待任何有帮助的回复，谢谢。

Answer 1

您几乎是在正确的轨道上。使用默认 Read Committed Isolation Level PostgreSQL 一路获取锁，并在事务提交或回滚时释放它们。

如果您可以确保所有 UPDATE 操作都以相同的顺序更新行，您将永远不会遇到死锁。您是否尝试过使 UP1 以升序更新行并以相同的方式对 UP2 的 IN 列表进行排序？

如果您不能保证同步更新，您可能会对 Serializable Isolation Level 感兴趣.这是最严格的隔离级别，可能会稍微减慢您的查询速度，尤其是在大量并发的情况下。但它应该可以防止您看到的错误。此外，请准备好因序列化失败而重试事务。

为此，start your transaction与:

BEGIN ISOLATION LEVEL SERIALIZABLE;