postgresql INSERT 语句中的死锁错误

Deadlock error in INSERT statement

提问by Gnanam

We've got a web-based application. There are time-bound database operations (INSERTs and UPDATEs) in the application which take more time to complete, hence this particular flow has been changed into a Java Thread so it will not wait (block) for the complete database operation to be completed.

我们有一个基于 Web 的应用程序。应用程序中存在有时限的数据库操作（插入和更新）需要更多时间才能完成，因此此特定流程已更改为 Java 线程，因此它不会等待（阻塞）完成完整的数据库操作。

My problem is, if more than 1 user comes across this particular flow, I'm facing the following error thrown by PostgreSQL:

我的问题是，如果超过 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.

The above error is consistently thrown in INSERT statements.

在 INSERT 语句中始终抛出上述错误。

Additional Information:1) I have PRIMARY KEY defined in this table. 2) There are FOREIGN KEY references in this table. 3) Separate database connection is passed to each Java Thread.

附加信息：1) 我在此表中定义了 PRIMARY KEY。2) 此表中有 FOREIGN KEY 引用。3) 单独的数据库连接传递给每个 Java 线程。

TechnologiesWeb Server: Tomcat v6.0.10 Java v1.6.0 Servlet Database: PostgreSQL v8.2.3 Connection Management: pgpool II

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

回答by Ants Aasma

One way to cope with deadlocks is to have a retry mechanism that waits for a random interval and tries to run the transaction again. The random interval is necessary so that the colliding transactions don't continuously keep bumping into each other, causing what is called a live lock - something even nastier to debug. Actually most complex applications will need such a retry mechanism sooner or later when they need to handle transaction serialization failures.

处理死锁的一种方法是使用重试机制，等待随机间隔并尝试再次运行事务。随机间隔是必要的，这样冲突的事务就不会持续不断地相互碰撞，从而导致所谓的活锁——调试起来更麻烦。实际上，大多数复杂的应用程序在需要处理事务序列化失败时，迟早会需要这种重试机制。

Of course if you are able to determine the cause of the deadlock it's usually much better to eliminate it or it willcome back to bite you. For almost all cases, even when the deadlock condition is rare, the little bit of throughput and coding overhead to get the locks in deterministic order or get more coarse-grained locks is worth it to avoid the occasional large latency hit and the sudden performance cliff when scaling concurrency.

当然，如果您能够确定僵局的原因，通常最好将其消除，否则它会回来咬您。对于几乎所有情况，即使死锁情况很少见，以确定性顺序获取锁或获取更粗粒度锁的少量吞吐量和编码开销是值得的，以避免偶尔的大延迟命中和突然的性能悬崖在扩展并发时。

When you are consistently getting two INSERT statements deadlocking it's most likely an unique index insert order issue. Try for example the following in two psql command windows:

当您始终遇到两个 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    

Usually the best course of action to resolve this is to figure out the parent objects that guard all such transactions. Most applications have one or two of primary entities, such as users or accounts, that are good candidates for this. Then all you need is for every transaction to get the locks on the primary entity it touches via SELECT ... FOR UPDATE. Or if touches several, get locks on all of them but in the same order every time (order by primary key is a good choice).

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

回答by Greg Smith

What PostgreSQL does here is covered in the documentation on Explicit Locking. The example in the "Deadlocks" section shows what you're probably doing. The part you may not have expected is that when you UPDATE something, that acquires a lock on that row that continues until the transaction involved ends. If you have multiple clients all doing updates of more than one thing at once, you'll inevitably end up with deadlocks unless you go out of your way to prevent them.

PostgreSQL 在此处所做的工作在有关显式锁定的文档中有所介绍。“死锁”部分中的示例显示了您可能正在执行的操作。您可能没有预料到的部分是，当您更新某些内容时，它会获取对该行的锁，该锁会一直持续到所涉及的事务结束。如果您有多个客户端同时进行不止一件事情的更新，那么您将不可避免地以死锁告终，除非您不遗余力地阻止它们。

If you have multiple things that take out implicit locks like UPDATE, you should wrap the whole sequence in BEGIN/COMMIT transaction blocks, and make sure you're consistent about the order they acquire locks (even the implicit ones like what UPDATE grabs) at everywhere. If you need to update something in table A then table B, and one part of the app does A then B while the other does B then A, you're going to deadlock one day. Two UPDATEs against the same table are similarly destined to fail unless you can enforce some ordering of the two that's repeatable among clients. Sorting by primary key once you have the set of records to update and always grabbing the "lower" one first is a common strategy.

如果你有多个东西可以取出像 UPDATE 这样的隐式锁，你应该将整个序列包装在 BEGIN/COMMIT 事务块中，并确保它们获取锁的顺序（即使是像 UPDATE 抓取的隐式锁）的顺序一致到处。如果您需要先更新表 A 和表 B 中的某些内容，并且应用程序的一部分先执行 A 然后执行 B，而另一部分执行 B 然后执行 A，那么总有一天你会陷入僵局。对同一个表的两个更新同样注定会失败，除非您可以强制执行在客户端之间可重复的两个更新的某些排序。一旦您拥有要更新的记录集并始终首先获取“较低”的记录，则按主键排序是一种常见策略。

It's less likely your INSERTs are to blame here, those are much harder to get into a deadlocked situation, unless you violate a primary key as Ants already described.

您的 INSERT 不太可能在这里受到指责，除非您违反了 Ants 已经描述的主键，否则它们更难陷入僵局。

What you don't want to do is try and duplicate locking in your app, which is going to turn into a giant scalability and reliability mess (and will likely still result in database deadlocks). If you can't work around this within the confines of the standard database locking methods, consider using either the advisory lock facility or explicit LOCK TABLEto enforce what you need instead. That will save you a world of painful coding over trying to push all the locks onto the client side. If you have multiple updates against a table and can't enforce the order they happen in, you have no choice but to lock the whole table while you execute them; that's the only route that doesn't introduce a potential for deadlock.

您不想做的是尝试在您的应用程序中重复锁定，这将变成一个巨大的可扩展性和可靠性混乱（并且可能仍会导致数据库死锁）。如果您无法在标准数据库锁定方法的范围内解决此问题，请考虑使用建议锁定工具或显式LOCK TABLE来强制执行您需要的操作。这将为您节省一个痛苦的编码世界，而不是试图将所有锁推送到客户端。如果你对一个表有多个更新并且不能强制执行它们发生的顺序，你别无选择，只能在执行它们时锁定整个表；这是唯一不会导致死锁的途径。

回答by mjv

Deadlock explained:
In a nutshell, what is happening is that a particular SQL statement (INSERT or other) is waiting on another statement to release a lock on a particular part of the database, before it can proceed. Until this lock is released, the first SQL statement, call it "statement A" will not allow itself to access this part of the database to do its job (= regular lock situation). But... statement A has also put a lock on anotherpart of the database to ensure that no other users of the database access (for reading, or modifiying/deleting, depending on the type of lock). Now... the second SQL statement, is itself in need of accessing the data section marked by the lock of Statement A. That is a DEAD LOCK : both Statement will wait, ad infinitum, on one another.

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

The remedy...

补救措施...

This would require to know the specific SQL statement these various threads are running, and looking in there if there is a way to either:

这需要知道这些不同线程正在运行的特定 SQL 语句，并在那里查看是否有办法：

a) removing some of the locks, or changing their types.
   For example, maybe the whole table is locked, whereby only a given row, or
   a page thereof would be necessary.
b) preventing multiple of these queries to be submitted at a given time.
   This would be done by way of semaphores/locks (aka MUTEX) at the level of the
   multi-threading logic.

Beware that the "b)" approach, if not correctly implemented may just move the deadlock situation from within SQL to within the program/threads logic. The key would be to only create one mutex to be obtained first by any thread which is about to run one of these deadlock-prone queries.

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

回答by Alexandre Swioklo

Your problem, probably, is the insert command is trying to lock one or both index and the indexes is locked for the other tread.

您的问题可能是插入命令试图锁定一个或两个索引，而索引已锁定另一个踏板。

One common mistake is lock resources in different order on each thread. Check the orders and try to lock the resources in the same order in all threads.

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