Not getting any nibbles, so allow me to try a short question: If I have a deadlock situation (that will be reported as such by Postgresql once the deadlock_timeout passes), does pg_stat_activity show the queries that are blocking each other? I'm wondering if I'm misinterpreting what I'm seeing below. On Thu, Apr 30, 2009 at 10:30:26AM -0700, Bill Moseley wrote: > > 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? > -- Bill Moseley. moseley@xxxxxxxx Sent from my iMutt -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
