Обсуждение: Deadlock error in INSERT statements
Hi, We've a web-based application. We are trying to do concurrency testing, Three person doing same operation on different data. I'm facing the following deadlock 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. Above error is consistently thrown in INSERT statement. Scenario of the Operation: 1) The following operation is done in parallela) User A: Doing a Send Mail for 200 email idsb) User B: Doing a Send Mailfor 200 email idsc) User C: Doing a Send Mail for 200 email ids 2) For each email id an activity is recorded in table named "ACTIVITY". 3) Then mail is sent through smtp server. 4) For User A, User B ACTIVITY is created and Mail is also sent successfully. 5) For User C deadlock error is thrown in "INSERT INTO ACTIVITY". 6) Our code rolls back, activity is not created and mail is not sent for User C Additional Information: 1) I have PRIMARY KEY defined in ACTIVITY table. 2) There are FOREIGN KEY references in ACTIVITY table. 3) There are INDEXes in ACTIVITY table Technologies Web Server: Tomcat v6.0.10 Java v1.6.0 Servlet Database: PostgreSQL v8.2.3 Connection Management: pgpool II NOTE: I've seen "deadlock" errors in UPDATE statement but why it is throwing in INSERT statements. Regards, Gnanam -- View this message in context: http://www.nabble.com/Deadlock-error-in-INSERT-statements-tp25787834p25787834.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Wed, Oct 7, 2009 at 10:34 AM, Gnanam <gnanam@zoniac.com> wrote: > > Hi, > > We've a web-based application. > We are trying to do concurrency testing, Three person doing same operation > on different data. > > I'm facing the following deadlock 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. > > Above error is consistently thrown in INSERT statement. > > Scenario of the Operation: > > 1) The following operation is done in parallel > a) User A: Doing a Send Mail for 200 email ids > b) User B: Doing a Send Mail for 200 email ids > c) User C: Doing a Send Mail for 200 email ids > 2) For each email id an activity is recorded in table named "ACTIVITY". > 3) Then mail is sent through smtp server. > 4) For User A, User B ACTIVITY is created and Mail is also sent > successfully. > 5) For User C deadlock error is thrown in "INSERT INTO ACTIVITY". > 6) Our code rolls back, activity is not created and mail is not sent for > User C > > Additional Information: > 1) I have PRIMARY KEY defined in ACTIVITY table. > 2) There are FOREIGN KEY references in ACTIVITY table. > 3) There are INDEXes in ACTIVITY table > > Technologies > Web Server: Tomcat v6.0.10 Java v1.6.0 > Servlet Database: PostgreSQL v8.2.3 > Connection Management: pgpool II > > NOTE: I've seen "deadlock" errors in UPDATE statement but why it is throwing > in INSERT statements. > > Regards, > Gnanam Can you provide the actual queries that you are executing here? Ideally with EXPLAIN ANALYZE output? ...Robert
On Wed, 2009-10-07 at 16:34 +0200, Gnanam wrote: > NOTE: I've seen "deadlock" errors in UPDATE statement but why it is throwing > in INSERT statements. It is because of the foreign key. Inserting a child row will lock the corresponding parent row, and if you insert multiple rows with different parents in the same transaction, and do that in different concurrent transactions but in different order of the parent rows, you can get a deadlock. If you keep in mind that the parent row is locked on the insert of a child row, you will figure out what's happening... BTW, I don't think the "hackers" list is the right one for this kind of question, better use the "general" list... Cheers, Csaba.