Deadlocks in PostgreSQL

Posted on by Raghav
Before discussing on deadlocks, lets see type of locks and their acquiring methodolgy in PostgreSQL.
Types of Locks:

  1. Table-Level Locks and
  2. Row-Level Locks

Table-Level Locks:
  1. AcessShareLock : It acquired automatically by a SELECT statement on the table or tables it retrieves from. This mode blocks ALTER TABLE, DROP TABLE, and  VACUUM (AccessExclusiveLock) on the same table
  2. RowShareLock : It acquired automatically by a SELECT…FOR  UPDATE clause. It blocks concurrent ExclusiveLock and AccessExclusiveLock on the same table.
  3. RowExclusiveLock: It acquired automatically by an UPDATE, INSERT, or DELETE command. It blocks ALTER TABLE, DROP TABLE, VACUUM, and CREATE INDEX commands (ShareLock, ShareRowExclusiveLock, ExclusiveLock, and AccessExclusiveLock) on the same table.
  4. ShareLock: It acquired automatically by a CREATE INDEX command. It blocks INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE, and VACUUM commands. (RowExclusiveLock, ShareRowExclusiveLock, ExclusiveLock, and AccessExclusiveLock) on the same table.
  5. ShareRowExclusiveLock: This lock mode nearly identical to the ExclusiveLock, but which allows concurrent RowShareLock to be acquired.
  6. ExclusiveLock: “Every transaction holds an exclusive lock on its transaction ID for its entire duration. If one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID. That will succeed only when the other transaction terminates and releases its locks.” (regards, tom lane). Best definition by Tom Lane, I Believe every email from him is a lesson, he is Dr. PostgreSQL 🙂 .  ExclusiveLock blocks INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, DROP TABLE, SELECT…FOR UPDATE and VACUUM commands on the table.(RowShareLock,RowExclusiveLock, ShareLock, ShareRowExclusiveLock, ExclusiveLock, and AccessExclusiveLock)
  7. AccessExclusiveLock: It acquired automatically by a ALTER TABLE, DROP TABLE, or VACUUM command on the table it modifies.This blocks any concurrent command or other lock mode from being acquired on the locked table.
Row-Level Locks:
Two types of row-level locking share and exclusive locks. Don’t fall into confusion of LOCK naming, you can differentiate row-lock and table-lock by the column ‘lock_type’ in pg_locks. 
  1. Exclusive lock: It is aquired automatically when a row hit by an update or delete. Lock is held until a transaction commits or rollbacks. To manually acquiring exclusive-lock use SELECT FOR UPDATE. 
  2. Share-Lock: It is acquired when a row hit by an SELECT…FOR SHARE.
Note: In either cases of row-level locks, data retreival is not at all effectied. Row-level lock block Writers (ie., Writer will block the Writer)
DeadLocks:

Now Deadlocks, you have seen the lock modes and their lock aquiring methodology, there are situations some of the transactions fall under deadlock. I believe application designing is the culprit forcing transactions to deadlocks. Deadlock mostly caused by ExclusiveLock’s  i.e., UPDATE or DELETE. 
What is deadlock ?

Process A holding lock on object X and waiting for lock on Object Y. Process B holding lock on Object Y and waiting for lock on Object X. At this point the two processes are now in what’s called ‘deadlock’ each is trying to obtain a lock on something owned by the other. They both will wait on each other forever if left in this state. One of them has to give up and release the locks they already have. Now, deadlock detector comes into picture and allow one process to success and another to rollback. 
To over come deadlock, design application in such a way that any transaction UPDATE or DELETE should succeed with complete ownership on the table.  Lock the table with ‘SHARE UPDATE EXCLUSIVE MODE’  or  ‘SELECT…FOR UPDATE’ or ‘ACCESS EXCLUSIVE MODE’ and complete the transaction. In this model, deadlock detector never throw that it has hit by a EXCLUSIVE LOCK’s.
You can test the scenario given in the pic above with the resolution, you see that deadlock detector never throws error. 
Locking Query:

set locks 'SELECT w.locktype AS waiting_locktype,w.relation::regclass AS waiting_table,w.transactionid, substr(w_stm.current_query,1,20) AS waiting_query,w.mode AS waiting_mode,w.pid AS waiting_pid,other.locktype AS other_locktype,other.relation::regclass AS other_table,other_stm.current_query AS other_query,other.mode AS other_mode,other.pid AS other_pid,other.granted AS other_granted FROM pg_catalog.pg_locks AS w JOIN pg_catalog.pg_stat_activity AS w_stm ON (w_stm.procpid = w.pid) JOIN pg_catalog.pg_locks AS other ON ((w."database" = other."database" AND w.relation  = other.relation) OR w.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.procpid = other.pid) WHERE NOT w.granted AND w.pid <> other.pid;;'

Locking information Links
Hope you got some idea on PostgreSQL Locks. See you all soon with another good blog…. 🙂
–Raghav

24 Replies to “Deadlocks in PostgreSQL”

  1. Yes I agree with you, it should be processed in well-defined order to avoid deadlocks. However, my saying was, DEADLOCK occur if you are trying to take for two EXCLUSIVE LOCKs, in case of SELECT FOR UPDATE the transaction allows others to READ those rows and it completes with full ownership. As a mater of fact, SELECT FOR UPDATE in two session in any RDBMS is complete lock.

  4. My application is running with Postgresql database. Whenever I am running any process , then it take too long to execute. later I run this statement SELECT relation::regclass, * FROM pg_locks WHERE NOT granted; on my database. This returns some 50 rows. Maximum of the locks are exclusive locks. How can I solve this and get a good performance from my application ? Kindly help me out

  6. If you are not getting required lock for any of the activity in the database then there might be an exclusive lock already acquired by some process. You need to see why your process is not granted any lock. Try executing below query and see what is blocking:-select bl.pid as blocked_pid, a.usename as blocked_user,kl.pid as blocking_pid, ka.usename as blocking_user, a.query as blocked_statement FROM pg_locks bl JOIN pg_stat_activity a on bl.pid = a.pid JOIN pg_locks kl JOIN pg_stat_activity ka on kl.pid = ka.pid on bl.transactionid = kl.transactionid and bl.pid != kl.pid where not bl.granted;Note: This query for PG 9.2 and above, tweak it according to your PG version.After looking to the result you will know what is being blocked. Accordingly, release the exclusive locks to continue with other locks. For faster replies, you can take your queries to Pgsql-general forum.

  7. Hahah.. Thanks Vibhor. Keep some envy, lot more queued up, no time to present :). Agreed. That is the point for the deadlock concept.–Raghav

  8. As I said, deadlock will be detected in the given time in deadlock_timeout parameter, after that one transaction will be succeeded and next will be rolled back.

  9. Well, the primary way to avoid deadlocks is not to lock everything as early as possible, but to process things in a well-defined order. At least at this point you are wrong, I think – it is easy to produce a deadlock by doing two SELECT … FOR UPDATE statements in a different order.

  10. Thanks very informative.. Can u explain how postgres handles deadlocks. Is there any configuration parameter which terminate queries which are in deadlock.. deadlock_timeout is suppose to log and detect deadlock i hope.

  11. will select query creates any locks? I am trying create table where I am adding FK constraint for district table. Its OLTP application and district is reference table which is being used by many users online. My create table statement fails with deadlock error. What type of lock select statement creates? If the same table I create without FK no error. How can we use select to avoid any locking?

  12. I always envy you for presenting Concept in pictorial way. Good one. However, one point for you. Deadlock happens when there is lock request cycle forms and none process in Cycle is ready for giving lock to other process.

  14. Yes I agree with you, it should be processed in well-defined order to avoid deadlocks. However, my saying was, DEADLOCK occur if you are trying to take for two EXCLUSIVE LOCKs, in case of SELECT FOR UPDATE the transaction allows others to READ those rows and it completes with full ownership. As a mater of fact, SELECT FOR UPDATE in two session in any RDBMS is complete lock.

  15. Well, the primary way to avoid deadlocks is not to lock everything as early as possible, but to process things in a well-defined order. At least at this point you are wrong, I think – it is easy to produce a deadlock by doing two SELECT … FOR UPDATE statements in a different order.

  16. will select query creates any locks? I am trying create table where I am adding FK constraint for district table. Its OLTP application and district is reference table which is being used by many users online. My create table statement fails with deadlock error. What type of lock select statement creates? If the same table I create without FK no error. How can we use select to avoid any locking?

  17. If you are not getting required lock for any of the activity in the database then there might be an exclusive lock already acquired by some process. You need to see why your process is not granted any lock. Try executing below query and see what is blocking:-

    select bl.pid as blocked_pid,
    a.usename as blocked_user,
    kl.pid as blocking_pid,
    ka.usename as blocking_user,
    a.query as blocked_statement
    FROM pg_locks bl
    JOIN pg_stat_activity a on bl.pid = a.pid
    JOIN pg_locks kl
    JOIN pg_stat_activity ka on kl.pid = ka.pid on bl.transactionid = kl.transactionid and bl.pid != kl.pid where not bl.granted;

    Note: This query for PG 9.2 and above, tweak it according to your PG version.

    After looking to the result you will know what is being blocked. Accordingly, release the exclusive locks to continue with other locks. For faster replies, you can take your queries to Pgsql-general forum.

  18. My application is running with Postgresql database. Whenever I am running any process , then it take too long to execute. later I run this statement
    SELECT relation::regclass, * FROM pg_locks WHERE NOT granted; on my database. This returns some 50 rows. Maximum of the locks are exclusive locks. How can I solve this and get a good performance from my application ? Kindly help me out

  19. As I said, deadlock will be detected in the given time in deadlock_timeout parameter, after that one transaction will be succeeded and next will be rolled back.

  20. Thanks very informative.. Can u explain how postgres handles deadlocks. Is there any configuration parameter which terminate queries which are in deadlock.. deadlock_timeout is suppose to log and detect deadlock i hope.

  24. I always envy you for presenting Concept in pictorial way. Good one.

    However, one point for you. Deadlock happens when there is lock request cycle forms and none process in Cycle is ready for giving lock to other process.

Comments are closed.