PostgreSQL Lock Management

Lock:

PostgreSQL provides various lock modes to control concurrent access to data in tables. These modes can be used for application-controlled locking in situations where MVCC does not give the desired behavior.

Also, most PostgreSQL commands automatically acquire locks of appropriate modes to ensure that referenced tables are not dropped or modified in incompatible ways while the command executes.

Types of Lock:

1. Table-level Locks
2. Row-level Locks
3. Page-level Locks
4. Deadlocks
5. Advisory Locks

1.Table-level Locks:

ACCESS SHARE:

                        The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode.

ROW SHARE:

                        The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire a lock of this mode on the target table(s) 

(in addition to ACCESS SHARE locks on any other tables that are referenced but not selected FOR UPDATE/FOR SHARE).

ROW EXCLUSIVE:

                        The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables).

In general, this lock mode will be acquired by any command that modifies data in a table.

SHARE UPDATE EXCLUSIVE:

                        Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS and ALTER TABLE VALIDATE and other ALTER TABLE variants.

SHARE:

                        Acquired by CREATE INDEX (without CONCURRENTLY).

SHARE ROW EXCLUSIVE:

                        Acquired by CREATE COLLATION, CREATE TRIGGER, and many forms of ALTER TABLE.

EXCLUSIVE:

                        Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.

ACCESS EXCLUSIVE:

                        Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands.

Many forms of ALTER TABLE also acquire a lock at this level. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly.

2.Row-level Locks:

    Row-level locks do not affect data querying; they block only writers and lockers to the same row. Row-level locks are released at transaction end or during savepoint rollback, just like table-level locks.

FOR UPDATE:

                        The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns.

FOR NO KEY UPDATE:

                        his lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.

FOR SHARE:

                        Behaves similarly to FOR NO KEY UPDATE, except that it acquires a shared lock rather than exclusive lock on each retrieved row. A shared lock blocks other transactions from performing UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE on these rows,

but it does not prevent them from performing SELECT FOR SHARE or SELECT FOR KEY SHARE.

FOR KEY SHARE:

                        Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE.

A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but not other UPDATE, 

and neither does it prevent SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.

3.Page-level Locks:

                        In addition to table and row locks, page-level share/exclusive locks are used to control read/write access to table pages in the shared buffer pool. These locks are released immediately after a row is fetched or updated. Application developers normally need not be concerned with page-level locks, but they are mentioned here for completeness.

4.Deadlocks:

                        The use of explicit locking can increase the likelihood of deadlocks, wherein two (or more) transactions each hold locks that the other wants. For example, if transaction 1 acquires an exclusive lock on table A and then tries to acquire an exclusive lock on table B, while transaction 2 has already exclusive-locked table B and now wants an exclusive lock on table A, then neither one can proceed. 

PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete.

5.Advisory Locks:

                        PostgreSQL provides a means for creating locks that have application-defined meanings. 

These are called advisory locks, because the system does not enforce their use it is up to the application to use them correctly.

             Advisory locks can be useful for locking strategies that are an awkward fit for the MVCC model. There are two ways to acquire an advisory lock in PostgreSQL: at session level or at transaction level.

Example:

postgres=# select locktype,database,relation,pid,mode from pg_locks ;

  locktype  | database | relation |  pid  |      mode       

------------+----------+----------+-------+-----------------

 relation   |    13809 |    24837 | 26567 | AccessShareLock

 virtualxid |          |          | 26567 | ExclusiveLock

 relation   |    13809 |    11577 |  9571 | AccessShareLock

 virtualxid |          |          |  9571 | ExclusiveLock

 relation   |    13809 |    24837 | 27264 | AccessShareLock

 virtualxid |          |          | 27264 | ExclusiveLock

 relation   |    13809 |    24837 | 27265 | AccessShareLock

 virtualxid |          |          | 27265 | ExclusiveLock

(8 rows)

postgres=# select pg_terminate_backend(27265);

 pg_terminate_backend 

----------------------

 t

(1 row)

postgres=# select locktype,database,relation,pid,mode from pg_locks ;

  locktype  | database | relation | pid  |      mode       

------------+----------+----------+------+-----------------

 relation   |    13809 |    11577 | 9571 | AccessShareLock

 virtualxid |          |          | 9571 | ExclusiveLock

(2 rows)