Обсуждение: share lock error

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


--
Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085

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@myemma.com>
software development
emma(r)

Terry Fielder <terry@ashtonwoodshomes.com> writes:
> 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 that the elements "a" and "b" are different tables.

Actually, what you're looking at there is a conflict on row-level locks
being obtained in opposite orders.  What PG version is this?  If it's
8.1 you can identify the row in question from other entries in pg_locks,
but if it's older then there's no easy way to find out.

            regards, tom lane

Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085

Terry Fielder <terry@ashtonwoodshomes.com> writes: 

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 that the elements "a" and "b" are different tables.   

Actually, what you're looking at there is a conflict on row-level locks
being obtained in opposite orders.  What PG version is this?  If it's
8.1 you can identify the row in question from other entries in pg_locks,
but if it's older then there's no easy way to find out.
		regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
 

Terry Fielder <terry@ashtonwoodshomes.com> writes:
> 7.4.3

Yoi.  You need to think about an update.

> And the records are gone from pg_locks, how much time after the deadlock
> do I have before they are purged?

Approximately none --- pg_locks is a live view.

            regards, tom lane