PostgreSQL LOCKS: Comprehensive Guide 101

I. What is PostgreSQL Locks?

PostgreSQL locks are used to control access to data in a database. They are used to prevent multiple transactions from accessing the same data at the same time, which can lead to data corruption and inconsistency. There are several types of locks in PostgreSQL, each with its own purpose and use case. In this article, we will discuss the different types of locks in PostgreSQL, how to use them, and when to use them.

II. Why do we need PostgreSQL Locks?

PostgreSQL locks are essential for maintaining data consistency and integrity in a database. They ensure that only one transaction can access a particular piece of data at a time, preventing conflicts and data corruption. Without locks, multiple transactions could read and write to the same data simultaneously, leading to inconsistent and incorrect results.

III. Types of PostgreSQL Locks

There are several types of locks in PostgreSQL, each with its own purpose and use case. The most common types of locks in PostgreSQL are:

• Table Level Locks: These locks are used to control access to entire tables in a database.
• Row Level Locks: These locks are used to control access to individual rows in a table.
• Page Level Locks: These locks are used to control access to individual pages in a table.
• Advisory Locks: These locks are used to create application-defined locks with specific meanings.

IV. PostgreSQL Locks: Table Level Locks

In PostgreSQL, locks are used to control access to data in a database. They are used to prevent multiple transactions from accessing the same data at the same time, which can lead to data corruption and inconsistency. There are several types of locks in PostgreSQL, each with its own purpose and use case. In this article, we will discuss the different types of locks in PostgreSQL, how to use them, and when to use them.

1. Access Share Locks

These locks are acquired on a specific table via the PostgreSQL SELECT command. After acquiring these locks on the table, we are only able to read data from it and not able to edit it. Access exclusive is the only locking mode that conflicts with this PostgreSQL lock mechanism.

Example:

LOCK TABLE employees IN ACCESS SHARE MODE;

2. Row Share Locks

The SELECT will acquire this PostgreSQL Lock on the table FOR SHARE & SELECT FOR UPDATE statements. This lock conflicts with Exclusive & Access Exclusive modes of Locking.

Example:

LOCK TABLE employees IN ROW SHARE MODE;

3. Row Exclusive Locks

The share row exclusive, share, access exclusive, and exclusive modes of PostgreSQL conflict with this lock. The locks on the table will be acquired by UPDATE, DELETE & INSERT statements.

Example:

LOCK TABLE employees IN ROW EXCLUSIVE MODE;

4. Share Update Exclusive Locks

The share row exclusive, share, access exclusive, share update exclusive, and exclusive lock modes in PostgreSQL are X with this lock. It will get control over PostgreSQL’s vacuum, index construction, edit table, and validate commands.

Example:

LOCK TABLE employees IN SHARE UPDATE EXCLUSIVE MODE;

5. Share Locks

In PostgreSQL, this lock is X with the share row exclusive, share, access exclusive, share update exclusive, share, and exclusive modes. This lock mode will obtain locks from PostgreSQL’s create index command.

Example:

LOCK TABLE employees IN SHARE MODE;

6. Share Row Exclusive Locks

In PostgreSQL, this lock conflicts with the share row exclusive, share, access, share update, and share row exclusive, share, and exclusive modes.

Example:

LOCK TABLE employees IN SHARE ROW EXCLUSIVE MODE;

7. Exclusive Locks

The share row exclusive, share, access exclusive, share update exclusive, share row exclusive, share, and exclusive modes of PostgreSQL clash with this lock. By using the refresh materialized view, one can obtain this lock.

Example:

LOCK TABLE employees IN EXCLUSIVE MODE;

8. Access Exclusive Locks

The share row exclusive, share, access exclusive, share update exclusive, share row exclusive, share, access exclusive, and exclusive modes of PostgreSQL clash with this lock. Only the person who applied the lock to the table can access it when utilizing it.

Example:

LOCK TABLE employees IN ACCESS EXCLUSIVE MODE;

9. Lock Compatibility Matrix

The following table shows the compatibility of different lock modes in PostgreSQL:

10. Conlusion

The following conclusions can be made from the image above:

• On the same table, two transactions cannot hold locks with conflicting modes at once. For instance, a continuing access share lock prevents one session from acquiring access exclusivity.

• Numerous transactions may hold concurrently non-conflicting lock modes. For instance, the Row Share lock and the Row Exclusive lock in the preceding diagram do not clash, allowing many transactions or sessions to hold both simultaneously.

• Some lock modes contradict one another. For instance, only one transaction may have an ACCESS EXCLUSIVE lock at a time.

• While specific lock modes don’t interfere with one another. For instance, different transactions may each hold an ACCESS SHARE lock.

Note: PostgreSQL employs multi-version concurrency control (MVCC) to ensure that data is available and consistent in high-concurrency contexts when a query requires modifying or deleting data. Since each transaction uses its copy of the database, neither write nor read operations will obstruct the other.

V. PostgreSQL Locks: Row Level Locks

In PostgreSQL, locks can also be applied at the row level. This allows you to control access to individual rows in a table, rather than the entire table. There are several types of row-level locks in PostgreSQL, each with its own purpose and use case. In this section, we will discuss the different types of row-level locks in PostgreSQL, how to use them, and when to use them. Shared or exclusive locks are the two fundamental types that can cause this.

• Shared Locks: Shared locks are used to prevent other transactions from modifying a specific row while a transaction is reading data from it. This lock mode is O with other shared locks, but conflicts with exclusive locks.

• Exclusive Locks: Exclusive locks are used to prevent other transactions from modifying a specific row while a transaction is reading or modifying data from it. This lock mode conflicts with all other lock modes.

1. FOR UPDATE Locks

FOR UPDATE locks are used to prevent other transactions from modifying a specific row while a transaction is reading or modifying data from it. This lock mode conflicts with all other lock modes.

Example:

SELECT * FROM employees WHERE id = 1 FOR UPDATE;

2. FOR NO KEY UPDATE Locks

FOR NO KEY UPDATE locks are used to prevent other transactions from modifying a specific row while a transaction is reading or modifying data from it. This lock mode is O with other FOR NO KEY UPDATE locks, but conflicts with all other lock modes.

Example:

SELECT * FROM employees WHERE id = 1 FOR NO KEY UPDATE;

3. FOR SHARE Locks

FOR SHARE locks are used to prevent other transactions from modifying a specific row while a transaction is reading data from it. This lock mode is O with other FOR SHARE locks, but conflicts with all other lock modes.

Example:

SELECT * FROM employees WHERE id = 1 FOR SHARE;

4. FOR KEY SHARE Locks

FOR KEY SHARE locks are used to prevent other transactions from modifying a specific row while a transaction is reading data from it. This lock mode is O with other FOR KEY SHARE locks, but conflicts with all other lock modes.

Example:

SELECT * FROM employees WHERE id = 1 FOR KEY SHARE;

5. Lock Compatibility Matrix

VI. PostgreSQL Locks: Page Level Locks

In PostgreSQL, locks can also be applied at the page level. This allows you to control access to individual pages in a table, rather than the entire table or individual rows. There are several types of page-level locks in PostgreSQL, each with its own purpose and use case. In this section, we will discuss the different types of page-level locks in PostgreSQL, how to use them, and when to use them.

VII. PostgreSQL Locks: Advisory Locks

Locks can be created in PostgreSQL with application-defined meanings. They are referred to as advisory locks. The program must correctly use them because the system does not enforce their use. Advisory locks can aid in locking techniques that match the MVCC model poorly.

1. Use PostgreSQL advisory locks in the following situations:

• In a microservices architecture, for instance, making an API call requires your application to communicate with several different services.
• To calculate and send a report to some of our users. However, we must ensure that no background workers begin the calculation simultaneously.
• Advisory locks can be used in PostgreSQL sharding or to coordinate task distribution to workers by a multi-node task scheduler.

For example, advisory locks frequently mimic the pessimistic locking techniques used by so-called “flat file” data management systems. Although the same thing might be accomplished with a flag kept in a table, advisory locks are quicker, prevent table bloat, and are immediately cleaned up by the server after the session.

The manual contains a comprehensive list of all operations used to manipulate advisory locks.

2. How can I use these advisory locks?

Let’s study basic principles about these locks before figuring out how to use them.

• Each lock has a unique identifier: a 64-bit big int or a 32-bit integer.
• The application developer must explicitly release the session-level locks after they have been acquired because they are not tied to any database transaction.
• Locks associated with the presently running transaction, known as transaction-level advisory locks, are released when that transaction completes, either with a commit or rollback.
• An exclusive advisory lock will block any exclusive or shared advisory lock on the same lock key.
• A shared advisory lock prevents any exclusive advisory lock from being obtained for the same lock key while permitting the purchase of other shared advisory locks.
• The boolean result value of the try_variations can be used to determine whether the lock has been successfully acquired.
• The resource cannot be made available for usage by subsequent sessions if it has been locked three times in a row without being unlocked.

3. How to use PostgreSQL advisory locks

The following example demonstrates how to use PostgreSQL advisory locks:

SELECT pg_advisory_lock(1234567890);

The above command will acquire an exclusive advisory lock with the key 1234567890. The lock will be held until it is explicitly released by the application developer.

SELECT pg_advisory_unlock(1234567890);

The above command will release the exclusive advisory lock with the key 1234567890.

VIII. PostgreSQL Locks: Deadlocks

A deadlock occurs when two or more transactions are waiting for each other to release locks. This can happen when two transactions are trying to acquire locks on the same resources in a different order. PostgreSQL has a deadlock detection mechanism that can detect and resolve deadlocks automatically. When a deadlock is detected, PostgreSQL will automatically roll back one of the transactions to resolve the deadlock.

1. Example of Deadlocks

Here is an example of a deadlock in PostgreSQL:

• Transaction 1 acquires a lock on row 1 in table A.
• Transaction 2 acquires a lock on row 2 in table B.
• Transaction 1 tries to acquire a lock on row 2 in table B, but it is blocked by Transaction 2.
• Transaction 2 tries to acquire a lock on row 1 in table A, but it is blocked by Transaction 1.
• Both transactions are now waiting for each other to release locks, resulting in a deadlock.

Example:

BEGIN;
SELECT * FROM employees WHERE id = 1 FOR UPDATE;
SELECT * FROM departments WHERE id = 1 FOR UPDATE;
COMMIT;

BEGIN;
SELECT * FROM departments WHERE id = 1 FOR UPDATE;
SELECT * FROM employees WHERE id = 1 FOR UPDATE;
COMMIT;

2. How to Avoid Deadlocks

To avoid deadlocks in PostgreSQL, you should follow these best practices:

• Always acquire locks in the same order to prevent deadlocks.
• Use the NOWAIT option when acquiring locks to avoid waiting for locks to be released.
• Use the SKIP LOCKED option when acquiring locks to skip rows that are already locked.
• Use the LOCK_TIMEOUT option to set a timeout for acquiring locks.

Example:

SELECT * FROM employees WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM departments WHERE id = 1 FOR UPDATE SKIP LOCKED;
SET LOCK_TIMEOUT TO '5s';

IX. Memory for Locks

PostgreSQL uses a fixed amount of memory for locks, which is determined by the deadlock_timeout, max_locks_per_transaction, max_pred_locks_per_transaction, max_pred_locks_per_transaction, and max_pred_locks_per_page parameters. If your application requires more locks than the default memory allocation, you can increase the memory for locks in PostgreSQL.

1. How to Increase Memory for Locks

To increase the memory for locks in PostgreSQL, you should follow these steps:

• Increase the max_locks_per_transaction, max_pred_locks_per_transaction, max_pred_locks_per_transaction, and max_pred_locks_per_page parameters in the postgresql.conf file.

Default values:

#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

Example:

deadlock_timeout = 5s
max_locks_per_transaction = 128
max_pred_locks_per_transaction = 128
max_pred_locks_per_transaction = 128
max_pred_locks_per_page = 128

• Restart the PostgreSQL server to apply the changes.

2. Memory for Locks Best Practices

To optimize memory usage for locks in PostgreSQL, you should follow these best practices:

• Use the deadlock_timeout parameter to set a timeout for detecting deadlocks.
• Use the max_locks_per_transaction, max_pred_locks_per_transaction, max_pred_locks_per_transaction, and max_pred_locks_per_page parameters to increase the memory for locks.
• Monitor the memory usage for locks in PostgreSQL using the pg_locks view.

Example:

SELECT * FROM pg_locks;

X. Conclusion

In this article, we discussed the different types of locks in PostgreSQL, how to use them, and when to use them. We also covered how to avoid deadlocks, increase memory for locks, and monitor memory usage for locks in PostgreSQL. By following these best practices, you can ensure that your application is using locks efficiently and effectively to maintain data consistency and integrity in a database.

References:

• PostgreSQL LOCKS: Comprehensive Guide 101
• PostgreSQL Documentation