In the context of transaction processing, Isolation plays a significant role.

Isolation is the property that controls how and when changes are made and when they must be visible to each other, users, and systems.

PostgreSQL achieves Isolation by multi-version concurrency control architecture.

Multi-version concurrency control is a technique to allow multiple sessions to access the same record concurrently, i.e., while session A is updating a record, session B can still access the record.

But,

1. what if both session A and session B wanted to update the same record at the same time?
2. What happens if you try to truncate a table from session B while session A is accessing it?
3. What happens if you decide to vacuum the table if there is an ongoing transaction going on in another session?

Here comes the concept of locking.

The following picture is a summary of what we are going to learn in this humongous post. 6000+ words are waiting for you, grab a cup of coffee and lockdown yourself with PostgreSQL Locks.

PostgreSQL Locks

PostgreSQL Locks is one of the critical topics of PostgreSQL, especially for developers who code with databases.

PostgreSQL Locks help us with concurrent access or modifications of the database objects by issuing a lock as soon as the command is executed.

The lock type is dependent on the type of command executed.

PostgreSQL supports three mechanisms of locking

1. Table Level Locks
2. Row Level Locks
3. Advisory Locks

Table level and Row-level locks can be explicit or implicit, whereas Advisory locks are explicit.

• Implicit locks mean the locks would go off by default when the transaction ends.
• Explicit locks once acquired may be held until explicitly released. We can acquire locks explicitly with the WITH LOCK statement.

PostgreSQL Locks: Table Level Locks

Table-level locks are acquired by built-in SQL commands(implicitly); also, they can be acquired explicitly with LOCK command. Available table-level locks in PostgreSQL are:

1. ACCESS SHARE
2. ROW SHARE
3. ROW EXCLUSIVE
4. SHARE UPDATE EXCLUSIVE
5. SHARE
6. SHARE ROW EXCLUSIVE
7. EXCLUSIVE
8. ACCESS EXCLUSIVE

Throughout the post, we will try to understand each locking mechanism with explicit locking and implicit locking.

PostgreSQL Access Share Lock

PostgreSQL Access Share Lock is acquired by queries that only read from a table but do not modify it. Typically, this is a select query.

Implicit locking example:

1. select something from session 1 which acquires Access Share Lock

2. Try to truncate the table from session 2

Session 1 acquired AccessShareLock to fetch the records.

To truncate a table my session 2 has to acquire a lock with a mode AccessExclusiveLock but is waiting to acquire it because of lock conflict.

ACCESS SHARE LOCK conflicts with the ACCESS EXCLUSIVE lock mode.[you will understand more about lock conflicts later in this post, don’t worry].

When I checked for the locks, I got two locks. You can see AccessShareLock will granted as true and AccessExclusiveLock with granted as false.

To find the locked and locker PID’s

Explicit locking example:

PostgreSQL Row Share Lock

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

Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

As SELECT FOR UPDATE and SELECT FOR SHARE are two of the four available row-level lock modes, we will discuss more Row Share Lock later in this post.

PostgreSQL Row Exclusive Lock

The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table.

Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.[detailed the discussion later in this post]

Implicit locking example:

Example.,

Now, this session 1 acquired a Row Exclusive lock.

From session 2, try to alter the table.

Session 2 will be waiting for session 1 to release the lock, because alter table drop column needs access exclusive which conflicts with row exclusive.

executing the pg_stat_activity to find the PID,

Now, with PID pg_locks output is

We will understand more about virtualxid and transactionid later in a separate post.

Explicit locking example:

PostgreSQL Share Update Exclusive

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

Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent schema changes and VACUUM runs.

Implicit locking example:

Example,

Acquired lock is

You can uselock_timeout  to avoid waiting for the lock.

lock_timeoutWillwillt for the specified time to get access to the object, if the timeout occurs the session waiting for lock disconnects.

Example

SET lock_timeout TO '2s'

Explicit locking example:

and the lock is

PostgreSQL Share Lock

Acquired by CREATE INDEX (without CONCURRENTLY).

The non-concurrent version of CREATE INDEX prevents table updates, e.g., DROP TABLE or INSERT or DELETE, with ShareLock.

Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes.

Implicit locking example:

and the lock is

Explicit locking example:

PostgreSQL Share Row Exclusive

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

Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes and is self-exclusive so that only one session can hold it at a time.

Explicit locking example:

PostgreSQL Exclusive Lock

Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.

Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. 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.

Implicit locking example:

Explicit locking example

PostgreSQL Access Exclusive Locking

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

Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder is the only transaction accessing the table in any way.

Implicit locking example

Explicit locking example:

The following diagram depicts the lock modes’ conflicts.

conclusions can be drawn from the above picture are

• Two transactions cannot hold locks of conflicting modes on the same table at the same time.

Example., If there is an ongoing access share lock, then another session can’t acquire access exclusive.

• Non-conflicting lock modes can be held concurrently by many transactions.

Example., as per the above diagram Row Share lock is non conflicting with Row Exclusive lock so that they can be held at a time by multiple transactions/sessions.

• Some lock modes are self-conflicting

Example, an ACCESS EXCLUSIVE lock cannot be held by more than one transaction at a time

• While some lock modes, not self-conflicting

Example, an ACCESS SHARE lock can be held by multiple transactions.

PostgreSQL Locks: Row Level Locks

Row level locks in PostgreSQL are classified into four types

1. FOR UPDATE
2. FOR NO KEY UPDATE
3. FOR SHARE
4. FOR KEY SHARE

PostgreSQL for update lock

There are certain situations where your PostgreSQL has to obtain a lock to process the data correctly.

For example.,

Here, if my requirement is to fetch the data with my_condition and do some stuff and update the table for the same condition.

Here, as select is an access share lock (as we learned above), there are chances that the other sessions can modify the same records while my session is processing the selects.

To avoid this situation, we use select for update clause

A select ... for update will (as it implies) select certain rows but also lock them as if they have already been updated by the current transaction (or as if the identity update had been performed). This allows you to update them again in the current transaction and then commit, without another transaction being able to modify these rows in any way.

select * from my_table where my_condition;

Since the rows affected by my_condition are locked, no other transaction can modify them in any way, and hence, transaction isolation level makes no difference here.

Select for update blocks the following actions by other sessions

• UPDATE,
• DELETE,
• SELECT FOR UPDATE,
• SELECT FOR NO KEY UPDATE,
• SELECT FOR SHARE or
• SELECT FOR KEY SHARE

PostgreSQL For No Key Update Lock

The select for no key updates behaves similarly to the select for update locking clause but it does not block the SELECT FOR KEY SHARE. It is ideal if you are performing processing on the rows but don’t want to block the creation of child records.

PostgreSQL For Share Lock

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.

select ... for update nowait The statement helps us to get to know that the resource is blocked by some other session.

The Effect of Select For Update on Foreign Keys

Consider below tables

Select for update has an evil impact of all the child tables or foreign key tables if not handled correctly. For example,

When selecting data from the emp table with select for update, dept will be locked as well. This is necessary because otherwise there is a chance of breaking the foreign-key constraint.

While on session 2

Lock query output

We can avoid this situation with For Share Lock

session 1 with For Share Lock

Session 2 with the same query

Lock query result

PostgreSQL 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.

Conflicting Row Level Locks

Requested LockMode	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

Now that we have completed understanding locks at the table level and row level.

The last type of locking mechanism is advisory locks.

PostgreSQL Locks: Advisory Locks

The default locks that we discussed so far are sufficiently enough for your application. But there are certain situations where you need an additional layer of locking mechanism from the application end.

PostgreSQL advisory locks solve such application related problems.

PostgreSQL advisory locks can be usable for the following scenarios:

• When your application needs to talk to multiple services when making an API call, for example, microservices architecture.
• we want to calculate and send a report to some of our users, but we must guarantee that background workers don’t start the calculation concurrently
• a multi-node task scheduler can use advisory locks to coordinate task distribution to workers or in PostgreSQL sharding.

The below code snippet helps you understand how explicit advisory lock is made on PostgreSQL through JAVA

The following table depicts the advisory locks

Each lock type can be 32 bit or 64 bit.

32 bit functions:

Lock Mode	32 Bit
Acquire	pg_advisory_lock(key bigint)
Release	pg_advisory_unlock(key bigint)
Try	pg_try_advisory_lock(key bigint)
Acquire	pg_advisory_lock_shared(key bigint)
Release	pg_advisory_unlock_shared(key bigint)
Try	pg_try_advisory_lock_shared(key bigint)
Acquire	pg_advisory_xact_lock(key bigint)
Release	lock is automatically released at the end of the current transaction
Try	pg_try_advisory_xact_lock(key bigint)
Acquire	pg_advisory_xact_lock_shared(key bigint)
Release	lock is automatically released at the end of the current transaction
Try	pg_try_advisory_xact_lock_shared(key bigint)

64 Bit functions

Lock Mode	64 Bit
Acquire	pg_advisory_lock(key1 int, key2 int)
Release	pg_advisory_unlock(key1 int, key2 int)
Try	pg_try_advisory_lock(key1 int, key2 int)
Acquire	pg_advisory_lock_shared(key1 int, key2 int)
Release	pg_advisory_unlock_shared(key1 int, key2 int)
Try	pg_try_advisory_lock_shared(key1 int, key2 int)
Acquire	pg_advisory_xact_lock(key1 int, key2 int)
Release	lock is automatically released at the end of the current transaction
Try	pg_try_advisory_xact_lock(key1 int, key2 int)
Acquire	pg_advisory_xact_lock_shared(key1 int, key2 int)
Release	lock is automatically released at the end of the current transaction
Try	pg_try_advisory_xact_lock_shared(key1 int, key2 int)

How can I use these advisory locks?

Before we understand how to use these locks, let’s learn some concepts about them.

1. Every lock is associated with an identifier, which can be a 32-bits integer or a 64-bits bigint.
2. The Session-level locks are not bound to any database transaction and, once acquired, they need to be explicitly released by the application developer.
3. Transaction-level advisory locks are bound to the currently executing transaction, and so the locks are released when the transaction ends, either with a commit or a rollback.
4. An exclusive advisory lock will block any exclusive or shared advisory lock on the same lock key.
5. A shared advisory lock will block any exclusive advisory lock on the same lock key while still allowing other shared advisory locks to be acquired for the same lock key.
6. The try_ variants return right away, and you can use the boolean result value to verify if the lock has been successfully acquired.
7. If the same resource is locked three times, it must then be unlocked three times to be released for other sessions’ use.

Examples

Every lock is associated with an identifier, which can be a 32-bits integer or a 64-bits.

For a 64 bit value, you can pass one argument to function pg_advisory_lock(key)

For a 32 bit value, you can pass two arguments to function pg_advisory_lock(key, key)

The Session-level locks are not bound to any database transaction and, once acquired, they need to be explicitly released by the application developer.

The lock is still there even I committed the transaction.

Transaction-level advisory locks are bound to the currently executing transaction, and so the locks are released when the transaction ends

An exclusive advisory lock will block any exclusive or shared advisory lock on the same lock key.

Session 1: Acquired exclusive lock with lock key 1

Session 2: Try to acquire the exclusive lock with lock key 1

Session 2 is hanged and can run only if session 1 releases lock

The error message that I got in alert log file is

A shared advisory lock will block any exclusive advisory lock on the same lock key while still allowing other shared advisory locks to be acquired for the same lock key.

Session 1: Acquired shared lock with lock key 1

Session 2: Still acquired shared lock with lock key 1

The try_ variants return right away, and you can use the boolean result value to verify if the lock has been successfully acquired.

Session 1: get a lock

Session 2 with try variant, it immediately returned false without waiting

If the same resource is locked three times, it must then be unlocked three times to be released for other sessions’ use.

Here, I acquired two locks, so I need to run two unlocks.

Memory for Locks / Lock Space

This memory component is to store all the types of locks used by the PostgreSQL instance. These locks are shared across all the background server and user processes connecting to the database. A non-default larger setting of two database parameters, namely max_locks_per_transaction and max_pred_locks_per_transaction, in a way influences the size of this memory component.

Default values for PostgreSQL Lock space in postgresql.conf file.

• #deadlock_timeout = 1s
• #max_locks_per_transaction = 64
• #max_pred_locks_per_transaction = 64
• #max_pred_locks_per_relation = -2
• #max_pred_locks_per_page = 2

PostgreSQL Locks: Best practices

Let us recap what we understood so far on PostgreSQL locks with the below picture.

We shall now proceed with the best practices.

Lock queues:

Consider below example.,

Now what locks are there on my database

• Session 1 was holding, AccessShareLock while season 2 and session 3 were waiting to get AccessExclusiveLock . Note that the locks are in the queue, i.e., session 2 has to wait for session 1 to release the lock, but session 3 has to wait for both session 1 and session 2 to release the lock.
• pg_dump also holds AccessShareLock

so, if you have any long-running queries or queries which require resource blocked by AccessShareLock, you can implement one of the following for subsequent queries.

1. SET statement_timeout = 40;
2. SET lock_timeout TO ’10s’

Making use of concurrency

1. Indexes: As mentioned in the above picture, create index concurrently will come under the 4th category, which doesn’t block category 3, which means create index concurrently does not block INSERTS, UPDATES, AND DELETES on a table which is very much important in OLTP environment.
2. Materialized views: As mentioned in the above picture, concurrent materialized view creation doesn’t block selects, which is important in the reporting environment.

Rewrite your query whenever required

For example, if you want to replace the contents of a table completely, do it in the test table then rename the test table with the main table with alter commands so that you can avoid long-running import/export on the main table.

Primary keys and Foreign Keys

I need to have primary keys on tables in my environment to have a logical replication to work. If not, I will end up with the following error in my alert log

Here, Instead of creating a primary key with alter table command, which needs to acquire lock 7, create a unique index concurrently which requires 5 and then make it as a primary key constraint.

Finally, keep an eye on point 7 things mentioned in the above picture, and if you think it’s going to take time, try to avoid doing it during business hours, for example, VACUUM FULL.

 

This post would not have been possible without taking references from prominent database administrators and architects. I conclude the post with a small token of gratitude to Vlad Mihalcea, Igor Šarčević, Marco slot, Bruce Momjian.

Help me understand that you liked the post by clicking on the like button on postgreshelp Facebook page.

Want to get trained from PostgreSQL expert? Click here

 

Did I miss anything?

Please let me know in the comments, and I will be happy to share my thoughts.