Re: Deadlock error in INSERT statements

On Wed, Oct 7, 2009 at 10:34 AM, Gnanam <gnanam(at)zoniac(dot)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