插入语句中的死锁错误

Question

我们有一个基于Web的应用程序。 应用程序中存在时间限制的数据库操作（INSERT和UPDATE），这些操作需要更长的时间才能完成，因此将此特定流程更改为Java线程，以便它不会等待（阻塞）完整的数据库操作完成。

我的问题是，如果超过1个用户遇到此特定流程，则PostgreSQL会抛出以下错误：

org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 13560 waits for ShareLock on transaction 3147316424; blocked by process 13566.
Process 13566 waits for ShareLock on transaction 3147316408; blocked by process 13560.

上述错误在插入语句中持续出现。

额外信息: 1）在这张表中我定义了主键。 2）这张表中存在外键引用。 3）每个Java线程都传递了单独的数据库连接。

技术信息 Web服务器：Tomcat v6.0.10 Java v1.6.0 Servlet 数据库：PostgreSQL v8.2.3 连接管理：pgpool II

- Gnanam

---

提供更多信息有助于诊断您的问题。您能否提供具体的外键约束、一些关于表模式的基本信息以及导致死锁的实际SQL语句？ - Bob Kaufman

Answer 1

应对死锁的一种方法是使用重试机制，等待随机时间后再尝试运行事务。随机时间是必要的，这样冲突的事务不会不断地相互碰撞，导致所谓的活锁 - 这是更难调试的情况。实际上，大多数复杂的应用程序在需要处理事务串行化失败时，迟早都需要这样的重试机制。

当然，如果您能确定死锁的原因，通常最好消除它，否则它将会让你付出代价。对于几乎所有情况，即使死锁条件很少发生，为了避免偶发的大延迟和在并发缩放时出现的突然性能下降，为获得锁定的确定性顺序或获得更粗粒度的锁定而产生的吞吐量和编码开销都是值得的。

当您持续获得两个INSERT语句发生死锁时，最有可能是唯一索引插入顺序问题。例如在两个psql命令窗口中尝试以下操作:

Thread A           | Thread B
BEGIN;             | BEGIN;
                   | INSERT uniq=1;
INSERT uniq=2;     | 
                   | INSERT uniq=2; 
                   |   block waiting for thread A to commit or rollback, to
                   |   see if this is an unique key error.
INSERT uniq=1;     |
   blocks waiting  |
   for thread B,   |
     DEADLOCK      | 
                   V    

通常解决此问题的最佳方法是找出守卫所有此类交易的父对象。大多数应用程序都有一个或两个主要实体，例如用户或帐户，这些都是很好的候选对象。然后，只需要通过SELECT ... FOR UPDATE获取每个事务触及的主要实体的锁定。如果涉及多个实体，则每次以相同的顺序获取所有实体的锁定（按主键排序是一个不错的选择）。

- Ants Aasma

Answer 2

PostgreSQL在这里所做的事情在显式锁定的文档中有介绍。 "死锁"部分的示例展示了你可能正在做的事情。你可能没有预料到的是，当你更新某个东西时，会获得该行的锁定，该锁定将持续到涉及的事务结束。如果您有多个客户端同时更新多个内容，则除非您特别防止死锁，否则不可避免地会出现死锁。

如果您有多个像UPDATE这样引发隐式锁定的内容，您应该将整个序列包装在BEGIN / COMMIT事务块中，并确保您在每个地方都一致地获取锁定的顺序（即使是UPDATE获取的隐式锁定）。如果您需要更新表A中的某些内容，然后再更新表B中的内容，并且应用程序的一部分执行A然后执行B，而另一部分执行B然后执行A，则总有一天会发生死锁。两个针对同一张表的UPDATE同样注定会失败，除非您可以强制执行客户端之间可重复的两个命令的排序。一旦您获得要更新的记录集，按主键排序并始终首先获取“较低”的记录是常见的策略。

插入操作更少可能导致问题，这些很难陷入死锁状态，除非您像Ants已经描述的那样违反主键。

不要试图在应用程序中复制锁定，这会变成一个巨大的可扩展性和可靠性问题（并且可能仍然导致数据库死锁）。如果你无法在标准数据库锁定方法的限制范围内解决此问题，请考虑使用advisory lock facility或显式LOCK TABLE来强制执行所需内容。这将节省大量痛苦的编码，而不是尝试将所有锁都推到客户端。如果对表进行了多个更新，并且无法强制执行它们发生的顺序，那么您别无选择，只能在执行它们时锁定整个表；这是唯一不会引入死锁潜在性的路线。

- Greg Smith

Answer 3

死锁的解释： 简而言之，正在发生的情况是，一个特定的SQL语句（INSERT或其他）正在等待另一个语句释放对数据库特定部分的锁定，然后才能继续执行。在此锁定被释放之前，第一个SQL语句，称为“语句A”，将不允许自己访问该部分数据库以完成其工作（=常规锁定情况）。但是...语句A还在数据库的另一部分上设置了锁定，以确保数据库的其他用户无法访问（根据锁定类型进行读取、修改/删除）。现在...第二个SQL语句本身需要访问由语句A的锁定标记的数据部分。这是一个死锁：两个语句将相互等待，无限期地。

解决方法： 这需要知道这些各个线程正在运行的特定SQL语句，并查看其中是否有一种方法可以：

a) 删除某些锁定或更改它们的类型。 例如，可能整个表都被锁定，只需要给定行或其中的一页即可。 b) 防止同时提交多个此类查询。 这将通过在多线程逻辑层次上使用信号量/锁（也称为MUTEX）来完成。

请注意，“b)”方法如果实现不正确，可能会将死锁情况从SQL内部转移到程序/线程逻辑内部。关键在于只创建一个互斥体，首先由即将运行这些死锁易发生查询之一的任何线程获取。

- mjv

Answer 4

你的问题可能是插入命令正在尝试锁定一个或两个索引，而这些索引已被其他线程锁定。

一个常见的错误是在每个线程上以不同的顺序锁定资源。检查顺序，并尝试在所有线程中以相同的顺序锁定资源。

- Alexandre Swioklo

---

PostgreSQL在常规语句执行期间不会锁定索引，只会锁定表中的行。如果要锁定索引，则必须明确地修改或维护它。 - Greg Smith