Tracking down a deadlock - Mailing list pgsql-general

I need a bit of help understanding what might be causing a deadlock.

To duplicate the problem I'm running a test script that forks two
child processes.  Each child runs the same transaction and thus the
order of execution is exactly the same.  (i.e. not like the typical
deadlock where the order of updates might be reversed between two
sessions.)

The transaction inserts a new document into a document management
system.  The transaction does a number of selects and inserts.  At the
end of the transaction they both try and update the same row in the
"account" table.


It does not happen every time I run my test script -- but if I run it
enough I get a deadlock.  If I fork more child process I can make it
happen more often.  So, it does seem like a timing issue.


No explicit LOCK or SELECT FOR UPDATE is used in the transaction.
I'm running in the default "read committed" isolation level.
The initial problem was reported on PostgreSQL 8.3.5, but
I'm now testing on PostgreSQL 8.2.9.



I've set my deadlock_timeout high so I can review the locks.
I see these entires:


select * from pg_locks where not granted;
   locktype    | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction |  pid
|    mode      | granted  

---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+---------------+---------
 transactionid |          |          |      |       |      18410123 |         |       |          |    18410135 | 13420
|ShareLock     | f 
 tuple         |  2474484 |  2474485 |   30 |    11 |               |         |       |          |    18410123 | 13419
|ExclusiveLock | f 
(2 rows)

select * from pg_locks where locktype='tuple';
 locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction |  pid  |
mode     | granted  

----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+---------------+---------
 tuple    |  2474484 |  2474485 |   30 |    11 |               |         |       |          |    18410135 | 13420 |
ExclusiveLock| t 
 tuple    |  2474484 |  2474485 |   30 |    11 |               |         |       |          |    18410123 | 13419 |
ExclusiveLock| f 
(2 rows)


And pg_stat_activity shows two of the exact same queries in "waiting"
state.  The "current_query" is just:

    UPDATE account set foo = 123 where id = $1

and $1 is indeed the same for both.


If I comment out that update to the "account" table from the
transaction I never get a deadlock.



Maybe I'm missing something, but that by itself doesn't seem like a
deadlock situation.

The "account" table does have a number of constraints, and one looks
like:

    CHECK( ( foo + bar ) <= 0 );

Could those be responsible?  For a test I dropped all the constraints
(except foreign keys) and I'm still getting a deadlock.

In general, do the constraints need to be deferrable and then defer
constraints at the start of the transaction?

What else can I do to debug?


Thanks,


--
Bill Moseley
moseley@hank.org
Sent from my iMutt