In the pg_locks system view there are 'pid', and 'relation' columns
(there's also a 'transaction' column but the pid will work just fine).
Do a look up on that table using the pids from the error messages. The
values for relation can be used to look up the table in pg_class.
Something like this would do the trick all at once for both processes
SELECT pl.pid as process_id, pc.relname as relation_name, pl.granted as
lock_status, pl.locktype
FROM pg_class pc, pg_locks pl
WHERE pc.oid=pl.relation AND pl.pid IN (6469, 28763);
Note that I've included the (attempted) lock type and the status of each
which should help in diagnosing the problem as different queries use
different lock types.
See this: http://www.postgresql.org/docs/8.1/interactive/mvcc.html for more.
Terry Fielder wrote:
I am getting this in my log file:
2006-10-05 16:06:23 [6469] ERROR: deadlock detected
DETAIL: Process 6469 waits for ShareLock on transaction 668582701;
blocked by process 28763.
Process 28763 waits for ShareLock on transaction 668586325;
blocked by process 6469.
I believe the scenario is likely like:
user 1 does something which causes a lock on element a
user 2 does something which causes a lock on element b
user 1 tries to do something and blocks waiting for a lock on b
user 2 tries to do something and blocks waiting for a lock on a
deadlock
I believe that the elements "a" and "b" are different tables.
The solution is to have both apps lock "a" first, then "b", hence no
deadlock could occur. Problem is, I don't know what the underlying
entities are.
Is there somewhere I can gather more information about which tables or
entities are behind the transaction number?
Thanks in advance
--
erik jones <erik@xxxxxxxxxx>
software development
emma(r)
