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

~/.psqlrc file for DBA's

In our regular DBA monitoring, we will be using so many combination of pg_catalog queries to reteive information like <IDLE> in transaction , waiting queries,  No. of connections, etc. Most of the DBA's, create views to cut short big combination queries and keep handy for later use per requirement.

PostgreSQL, provides a startup file(.psqlrc) which executes before connecting to the database when using with psql utility. Using .psqlrc file you can place all your important queries with one word alias by '\set' command and execute it in psql terminal instead of typing big queries. If you wont see .psqlrc file in 'postgres' user home directory, you can create it explicitly. I tried it and found very helpful.

Points on .psqlrc:

• .psqlrc is a startup file, executes when connecting to the cluster.
• .psqlrc file will reside in 'postgres' user home directory.
•  psql options -X or -c, do not read the .psqlrc file.
• .psqlrc file is for complete session-level not database level.

My terminal Screenshot:

Lets see how to implement this.

Syntax:

\set <alias-variable-name>  'query'

Note: if your query has single or double quotes then use \' or \" in the query.

Sample Queries to put in .psqlrc file with alias:

vi ~/.psqlrc

\set PAGER OFF

\set waits 'SELECT pg_stat_activity.procpid, pg_stat_activity.current_query, pg_stat_activity.waiting, now() - pg_stat_activity.query_start  as "totaltime", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.current_query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;;'

\set locks 'select pid,mode,current_query from pg_locks,pg_stat_activity where granted=false and locktype=\'transactionid\' and pid=procpid order by pid,granted;;'

:wq!

Usage:

postgres=# :waits
 procpid |         current_query         | waiting |    totaltime    |          backend_start
---------+-------------------------------+---------+-----------------+----------------------------------
    9223 | insert into locks VALUES (1); | t       | 00:00:18.901773 | 2011-10-08 00:29:10.065186+05:30
(1 row)

postgres=# :locks
 pid  |   mode    |         current_query
------+-----------+-------------------------------
 9223 | ShareLock | insert into locks VALUES (1);
(1 row)

Was it not helpful. Enjoy... :). Will be back with some more stuff.

--Raghav