Re: Tracking down a deadlock - Mailing list pgsql-general

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@hank.org
Sent from my iMutt