postgresql 将SELECT FOR UPDATE与WHERE IN子句一起使用时出现死锁

dohp0rv5  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(181)

请考虑以下示例:

  1. -- Transaction 1 -> T1
  2. BEGIN;
  3. SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
  4. SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
  5. UPDATE table1 set col1 = 'abcd' where id = 1;
  6. COMMIT;
  7. -- Transaction 2 -> T2
  8. BEGIN;
  9. SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
  10. SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
  11. UPDATE table1 set col1 = 'defg' where id = 2;
  12. COMMIT;

在这个例子中,很明显,如果两个事务并发执行,就会发生死锁,因为如果T1锁定id=1的行,然后T2锁定id=2的行,T1和T2都不能执行第二个SELECT FOR UPDATE查询,我们就会发生死锁。
现在,为了解决这个问题,我们可以按照相同的顺序执行SELECT FOR UPDATE查询:

  1. -- Transaction 1 -> T1
  2. BEGIN;
  3. SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
  4. SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
  5. UPDATE table1 set col1 = 'abcd' where id = 1;
  6. COMMIT;
  7. -- Transaction 2 -> T2
  8. BEGIN;
  9. SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
  10. SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
  11. UPDATE table1 set col1 = 'defg' where id = 2;
  12. COMMIT;

我们解决了这个例子中的死锁问题。
现在,我的问题是,如果你考虑以下类似的例子:

  1. -- Transaction 1 -> T1
  2. BEGIN;
  3. SELECT * FROM table1 WHERE id IN (1, 2) FOR UPDATE;
  4. UPDATE table1 set col1 = 'abcd' where id = 1;
  5. COMMIT;
  6. -- Transaction 2 -> T2
  7. BEGIN;
  8. SELECT * FROM table1 WHERE id IN (1, 2) FOR UPDATE;
  9. UPDATE table1 set col1 = 'defg' where id = 2;
  10. COMMIT;

我的问题:

在最后一个例子中有可能出现死锁吗?
换句话说:Postgres会同时自动锁定所有匹配WHERE条件的行吗?
如果是的话,我们还可以说WHERE子句的顺序不算数吗?所以在T1中我们可以使用:用途:

  1. SELECT * FROM table1 WHERE id IN (1, 2) FOR UPDATE;

在T2中,我们可以使用:用途:

  1. SELECT * FROM table1 WHERE id IN (2, 1) FOR UPDATE;

又不冒着造成僵局的风险?

elcex8rz

elcex8rz1#

最后一个例子容易出现死锁。
锁不是“原子地”获取的,也就是说,它们实际上会在同一时间为同一事务(甚至是同一语句)发生。锁被沿着一个接一个地进行,并在事务结束时被释放。
关键是IN子句中的项目列表不一定要求锁定行的顺序。你需要一个ORDER BY子句来实现。或者像你已经成功尝试过的那样分开陈述。
单独的语句是冗长和更昂贵的。所以:

  1. BEGIN;
  2. SELECT FROM table1
  3. WHERE id IN (1,2)
  4. ORDER BY id -- !
  5. FOR UPDATE;
  6. UPDATE table1 set col1 = 'abcd' WHERE id = 1;
  7. COMMIT;

只要对同一个表的所有写访问都可靠地遵循相同的顺序,就不会有死锁(来自这种交互)。
The manual:
防止死锁的最佳方法通常是确保所有使用数据库的应用程序都以一致的顺序获取多个对象上的锁,从而避免死锁。
相关信息:

  • 尽管ON CONFLICT DO NOTHING,但多行INSERT仍出现死锁

对于磁盘上或多或少按id物理排序的数据,这甚至可以提高性能。

展开查看全部

相关问题