PostgreSQL – Locks

Prerequisites: What is PostgreSQL – Introduction

In PostgreSQL, a lock is a mechanism that is used to control concurrent access to database objects such as tables, rows, or blocks of a database file. Locks are used to preventing conflicts between transactions, ensuring that each transaction can access and modify the database without interfering with other transactions.

Types of Locks:

There are several types of locks in PostgreSQL, including:

• Access Exclusive (AEX) locks: These locks are used to prevent any other transactions from accessing or modifying the locked object. They are typically used when a transaction is performing a destructive operation, such as dropping a table or truncating a table.
• Exclusive (EX) locks: These locks are used to prevent other transactions from modifying the locked object, but they allow other transactions to read the object. They are typically used when a transaction is performing an update operation that modifies the structure of the object, such as adding or removing columns from a table.
• Share (SH) locks: These locks are used to allow multiple transactions to read the same object concurrently, but prevent them from modifying the object. They are typically used when a transaction is performing a read-only operation, such as a SELECT query.
• Row Share (RS) and Row Exclusive (RX) locks: These locks are used to control concurrent access to individual rows of a table. Row share locks allow multiple transactions to read the same row concurrently, but prevent them from modifying the row. Row-exclusive locks allow a transaction to modify a row but prevent other transactions from reading or modifying the row.

In PostgreSQL, locks are acquired automatically by the database system whenever a transaction accesses or modifies a database object. The database system uses a lock manager to keep track of which locks are held by which transactions, and it uses a lock escalation mechanism to avoid having too many locks in memory.

When a transaction attempts to access an object that is already locked by another transaction, it will either wait for the lock to be released (if the lock mode is compatible), or it will be aborted with an error (if the lock mode is incompatible). This ensures that transactions are executed in a consistent and serializable manner.

In PostgreSQL, a deadlock occurs when two or more transactions are waiting for each other to release a lock, causing them to be stuck in an indefinite wait state. Deadlocks can be caused by a variety of factors, including the use of conflicting lock modes, the order in which locks are acquired, and the presence of circular dependencies between transactions.

Here is an example of a deadlock that can occur in PostgreSQL when two transactions attempt to update the same table concurrently:

Transaction 1:

BEGIN;
UPDATE accounts SET balance 
= balance + 100 WHERE account_id = 1;

Transaction 2:

BEGIN;
UPDATE accounts SET balance 
= balance - 100 WHERE account_id = 2;

In this example, Transaction 1 acquires an exclusive (EX) lock on the accounts table, and Transaction 2 acquires an exclusive (EX) lock on the same table. Because both transactions are trying to modify the same table concurrently, they are waiting for each other to release their locks, causing a deadlock.

To avoid this type of deadlock, it is important to ensure that transactions acquire locks on database objects in the same order, and to use lock modes that are compatible with each other. For example, in the above example, if both transactions use a shared (SH) lock instead of an exclusive (EX) lock, they would be able to access the table concurrently without waiting for each other.