Deadlocks in PostgreSQL

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

http://www.postgresql.org/docs/9.0/static/sql-lock.html

http://developer.postgresql.org/pgdocs/postgres/explicit-locking.html

Hope you got some idea on PostgreSQL Locks. See you all soon with another good blog.... :)

--Raghav