PostgreSQL Locks(Table-level Locks & Row-level Locks)

To understand query locking we will take an example to know more about how locking works and how to see what’s going on within our database.
Start with our own example
Logging in PostgreSQL Database with pgAdmin or other tools and create a table, it will use in our example.
CREATE TABLE student (
                      id serial NOT NULL,
                      name     character varying(36),
                      CONSTRAINT student_pkey PRIMARY KEY (id)
                    );
INSERT INTO student (name) VALUES ('ram'), ('mohan'), ('sohan');
Open a SQL Editor (First SQL Editor) and run
Begin;
Run below query:
SELECT  locktype,     relation::regclass,     mode,     transactionid AS tid,
    virtualtransaction AS vtid,         pid,     granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE  db.datname = 'postgres'
and NOT pid = pg_backend_pid();
Out put
 
Run below select statement
SELECT * FROM student;
After that again run below query
SELECT  locktype,     relation::regclass,     mode,     transactionid AS tid,
    virtualtransaction AS vtid,         pid,     granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE  db.datname = 'postgres'
and NOT pid = pg_backend_pid();
Output :
 
Open a new SQL Editor (Second SQL Editor) and only write
Begin;
And then run
SELECT  locktype,     relation::regclass,     mode,     transactionid AS tid,
    virtualtransaction AS vtid,         pid,     granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE  db.datname = 'postgres'
and NOT pid = pg_backend_pid(); 
Output:
 
Suppose in this SQL Editor we run
SELECT * FROM student;
That is the two SELECT statements do not interfere and can safely run concurrent without blocking each other in two different sessions. We expect from a reliable database.
Postgres Locks (pg_lock)
Open a new SQL Editor (Third SQL Editor) and Run below query 
To see what kind of locks are acquired.
SELECT  locktype,     relation::regclass,     mode,     transactionid AS tid,
    virtualtransaction AS vtid,         pid,     granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE  db.datname = 'postgres'
and NOT pid = pg_backend_pid();
Output:
 
Each active lock is stored in pg_catalogs.pg_lock view. To make sure we only see locks on the postgres database we join pg_catalog.pg_database and filter by datname=’postgres’ OR datname IS NULL.
The relation column is typecast into regclass to make it human readable.
The relation student is locked with AccessShareLock by virtual transaction 7/218 from pid 52 and the lock is granted. That is the transaction got the lock. Both session(user) concurrently see what student’s name are.
Note that each transaction also hold an `ExclusiveLock on its own virtualxid that is their virtual transaction ID.
By mistake in student table name of roll 1 is incorrect then first User decided to correct the name
And he run the query
UPDATE student SET name = 'ramanna' WHERE id = 1;
As you can see First user got it successfully. Let’s take a look at what happens to the locks:
SELECT  locktype,     relation::regclass,     mode,     transactionid AS tid,
        virtualtransaction AS vtid,         pid,     granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE  db.datname = 'postgres'
and NOT pid = pg_backend_pid();
 
Two more locks are added. First User(Session)  got a RowExclusiveLock on student that is the name  is now to here. There is also a real transactionid added for this user(Session) on which this user holds Exclusive Lock.
For every transaction that potentially changes the state of the database a transactionid is added.
Multiversion concurrency control(MVCC)
The transaction is not committed that sway Second User (Session) still sees the old data.
SELECT * FROM student;

As it is not clear whether First user will commit or rollback his transaction, Second User sees the state of the table as it was before First User started his transaction. PostgreSQL uses  Multi Version Concurrency Control (MVCC) to make sure that each transaction always sees a consistent state of the database.
Locking situation is detected or (Blocking Queries)
Suppose Second User also wants to update the name whose roll is 1.
He also run the update statement in his session
UPDATE student SET name = 'ramanna' WHERE id = 1;
Now, transaction goes in lock state. Let’s see about locks
 
We realize nothing really happens because Second session has to wait until First session finishes his transaction.
Now a transactionid for Second User (Session) is added and Second User (Session) is asking to get a ShareLock on First User (Session) transactionid. As both locks conflict with each other Second User’s (Session) request is not granted and he needs to wait until First User (Session) releases his ExclusiveLock on his transactionid by finishing his transaction.
Find kind of queries (pg_stats_activity)
There is another interesting pg_catalog view called pg_stat_activity which shows you what kind of queries are going on:
SELECT query,state,wait_event_type,wait_event, pid
FROM pg_stat_activity
WHERE datname='postgres' AND NOT (state='idle' OR pid=pg_backend_pid());
Here we see that First User (Session) query is waiting for the transaction to commit while Second User (Session)  query is active and waiting.
Now let’s combine the two to see which query is waiting for whom.
We can run any query to find out who is blocking whom:
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;
 
SELECT blockeda.pid AS blocked_pid,
    blockeda.query as blocked_query,
    blockinga.pid AS blocking_pid,
    blockinga.query as blocking_query
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.transactionid=blockedl.transactionid
  AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted AND blockinga.datname='postgres';
Here we can clearly see which query is blocked by which statement and which USER is blocked by which USER.
Now if First User (Session) decided to rollback (or commit) his transaction his ExclusiveLock would release and Second User (Session) would get the ShareLock on his transactionid. If Second User (Session) now commits his transaction, the row would get updated depending on whether First User (Session) commit or rolled back.
Suppose First User (Session) commit his transaction and when we run above Query it looks like below:
 
Other scenario supposes we run two different queries on same Table it will work properly without any block.
e.g.
In First User (First Session)  we run
UPDATE student SET name = 'prakash' WHERE id = 1;
And Second User (Second Session)  we run
UPDATE student SET name = 'prakash' WHERE id = 2;
Above queries will run without any block.
 
And when we run query to find who is blocking whom we can’t find any row
SELECT blockeda.pid AS blocked_pid,
    blockeda.query as blocked_query,
    blockinga.pid AS blocking_pid,
    blockinga.query as blocking_query
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.transactionid=blockedl.transactionid
  AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted AND blockinga.datname='postgres';
Output
Nothing……
 
Table-level Locks
The list below shows the available lock modes and the contexts in which they are used automatically by PostgreSQL. You can also acquire any of these locks explicitly with the command LOCK. Remember that all of these lock modes are table-level locks, even if the name contains the word "row";
Table-level Lock Modes:
ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE
Conflicting Lock Modes

Requested Lock Mode	Current Lock Mode
	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

  
ACCESS EXCLUSIVE
This mode guarantees that the holder is the only transaction accessing the table in any way. 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.
Tip: Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement.
Let’s say First User (First Session) decides to take the access exclusive.
BEGIN;
LOCK TABLE student IN ACCESS EXCLUSIVE MODE;
 
Go to second User (second session) and run
BEGIN;
UPDATE student SET name = 'prakashtest' WHERE id = 2;
Now, we get output like below which has already explained in above example, second User (second session) are waiting to release the lock held by first user (first session).
 
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.
BEGIN;
LOCK TABLE student IN ACCESS SHARE 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).
BEGIN;
LOCK TABLE student IN ROW SHARE  MODE;
 
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.

BEGIN;
LOCK TABLE student IN ROW EXCLUSIVE  MODE;
 
SHARE UPDATE EXCLUSIVE
Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, and ALTER TABLE VALIDATE and other ALTER TABLE variants.
BEGIN;
LOCK TABLE student IN SHARE UPDATE EXCLUSIVE  MODE;
 
SHARE 
This mode protects a table against concurrent data changes. Acquired by CREATE INDEX (without CONCURRENTLY).
SHARE ROW EXCLUSIVE:
This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time. Acquired by CREATE TRIGGER and many forms of ALTER TABLE
BEGIN;
LOCK TABLE student IN SHARE ROW EXCLUSIVE  MODE;
 
If we acquire this lock no any other user or other session can do any insert, update and delete on locked table.  

For e.g. When we run DML query on student table on other session
BEGIN;
UPDATE student SET name = 'prakashtest' WHERE id = 2;
DELETE FROM student  WHERE id = 2;
INSERT INTO student (name) VALUES('amit')

We are not able to do any DML.
Output looks like below
 
EXCLUSIVE
This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode. Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.Once acquired, a lock is normally held till end of transaction. But if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to. This is consistent with the principle that ROLLBACK cancels all effects of the commands since the savepoint. The same holds for locks acquired within a PL/pgSQL exception block: an error escape from the block releases locks acquired within it.
BEGIN;
LOCK TABLE student IN EXCLUSIVE MODE;
 

Row-level Locks
A transaction can hold conflicting locks on the same row, even in different subtransactions; but other than that, two transactions can never hold conflicting locks on the same row. Row-level locks do not affect data querying; they block only writers and lockers to the same row.

Conflicting Row-level Locks

Requested Lock Mode	Current Lock Mode
	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				X
FOR SHARE			X	X
FOR NO KEY UPDATE		X	X	X
FOR UPDATE	X	X	X	X

  FOR UPDATE
FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends.
When we run below statement it lock that TABLE and other session can’t do any UPDATE or DELETE.

BEGIN;
SELECT * FROM student FOR UPDATE;

When we run below statement it only lock that row which will comes in where clause, on other rows we can do UPDATE or DELETE.

BEGIN;
SELECT * FROM student WHERE id = 1 FOR UPDATE;

FOR NO KEY UPDATE
Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.
BEGIN;
SELECT * FROM student WHERE id = 1 FOR NO KEY UPDATE;

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.
BEGIN;
SELECT * FROM student WHERE id = 1 FOR 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.
BEGIN;
SELECT * FROM student WHERE id = 1 FOR KEY SHARE;

*************************************************************
pg_locks Columns
 

Name	Type	References	Description
locktype	text		Type of the lockable object: relation, extend, page, tuple, transactionid, virtualxid, object, userlock, or advisory
database	oid	pg_database.oid	OID of the database in which the lock target exists, or zero if the target is a shared object, or null if the target is a transaction ID
relation	oid	pg_class.oid	OID of the relation targeted by the lock, or null if the target is not a relation or part of a relation
page	integer		Page number targeted by the lock within the relation, or null if the target is not a relation page or tuple
tuple	smallint		Tuple number targeted by the lock within the page, or null if the target is not a tuple
virtualxid	text		Virtual ID of the transaction targeted by the lock, or null if the target is not a virtual transaction ID
transactionid	xid		ID of the transaction targeted by the lock, or null if the target is not a transaction ID
classid	oid	pg_class.oid	OID of the system catalog containing the lock target, or null if the target is not a general database object
objid	oid	any OID column	OID of the lock target within its system catalog, or null if the target is not a general database object
objsubid	smallint		Column number targeted by the lock (the classid and objid refer to the table itself), or zero if the target is some other general database object, or null if the target is not a general database object
virtualtransaction	text		Virtual ID of the transaction that is holding or awaiting this lock
pid	integer		Process ID of the server process holding or awaiting this lock, or null if the lock is held by a prepared transaction
mode	text		Name of the lock mode held or desired by this process (see Section 13.3.1 and Section 13.2.3)
granted	boolean		True if lock is held, false if lock is awaited
fastpath	boolean		True if lock was taken via fast path, false if taken via main lock table

*************************************************************