INSERT语句中的死锁错误 [英] Deadlock error in INSERT statement

问题描述

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

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.

我的问题是，如果有1个以上的用户遇到此特定流程，我将面临PostgreSQL引发的以下错误：

My problem is, if more than 1 user comes across this particular flow, I'm facing the following error thrown by 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.

上述错误始终在INSERT语句中引发。

The above error is consistently thrown in INSERT statements.

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

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.

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

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

推荐答案

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

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 will come 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.

当您不断使两个INSERT语句陷入僵局时，很可能是唯一的索引插入顺序问题。例如，在两个psql命令窗口中尝试以下操作：

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:

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获得它接触的主要实体的锁。或者，如果触摸多个，则每次都以相同的顺序锁定它们（通过主键排序是一个不错的选择）。

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).

这篇关于INSERT语句中的死锁错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！