PostgreSQL ShareLock Deadlocks

PostgreSQL ShareLock Deadlocks

Home → Postgresql → PostgreSQL ShareLock Deadlocks 

We recently encountered the following error which warranted further investigation:

ERROR:  deadlock detected
DETAIL:  Process 4312 waits for ShareLock on transaction 1426407; blocked by process 2583.
Process 2583 waits for ShareLock on transaction 1426408; blocked by process 4312.
HINT:  See server log for query details.

The transactions involved were each trying to obtain a lock that the other transaction had already obtained.  Since neither transaction was able to complete and release their locks this resulting in the above deadlock condition.

PostgreSQL’s deadlock detector then kicked in and sacrificed (rolled back) one of the transactions to prevent them both waiting forever for the locks to be released.

This situation may not be too much of a problem if your code is sufficiently robust to recover from the transaction being rolled back, but is in no way ideal.

The following example reveals a scenario that can lead to deadlock conditions and suggests one way to solve this problem.

Example

create table table_a (row_id integer, value integer);
insert into table_a values (1,1),(2,2);

The queries responsible for producing a deadlock condition can be determined through inspection of PostgreSQL’s main.log:

2012-06-13 14:17:48 BST ERROR: deadlock detected
2012-06-13 14:17:48 BST DETAIL: Process 4312 waits for ShareLock on transaction 1426407; blocked by process 2583.
Process 2583 waits for ShareLock on transaction 1426408; blocked by process 4312.
Process 4312: update table_a set row_id=1 where value=1;
Process 2583: update table_a set row_id=2 where value=2;

To prevent this deadlock occurring in the future one solution would be to either lock specific rows or the entire table in advance of performing updates/deletes:

Transaction A locks rows 1 and 2 in advance so when Transaction B attempts to lock rows 1 and 2 it has to wait for Transaction A to either be committed or rolled back before it can obtain these locks.