Re: Tracking down a deadlock

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(at)hank(dot)org
Sent from my iMutt