Deadlock error in INSERT statements

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.