PostgreSQL LOCKS: Comprehensive Guide 101

Curious to learn about PostgreSQL Locks? While PostgreSQL is excellent at running multiple operations simultaneously, there are a few cases in which PostgreSQL needs to block an operation using a lock. You, therefore, have to be careful about which locks your transactions take.

But with the high-level abstractions that PostgreSQL locks provide, it can be challenging to know precisely what will happen. This article aims to elaborate on the locking behaviors in PostgreSQL and advise on how to avoid common problems. Let us begin!

Types of PostgreSQL Locks

• PostgreSQL Locks: Table-Level Locks
• PostgreSQL Locks: Row-Level Locks
• PostgreSQL Locks: Page Level Locks
• PostgreSQL Locks: Advisory Locks
• PostgreSQL Locks: Deadlocks

While Advisory locks are explicit, table and row level locks might be either explicit or implicit.

• Implicit locks signify locks that would automatically be released when a transaction is completed.
• Once acquired, explicit locks can be kept in place until they are released. When using the WITH LOCK statement, we can explicitly acquire locks.

PostgreSQL Locks: Table Level Locks

• Mode of Locks
• Conflict Modes in Table Level Locks

The lock table is defined as a command used to lock the table specifying the mode of locking and the table name. The table is not usable for read or write operations after applying a lock.

The syntax for LOCK command:

Lock table name_of_table IN [Mode of locking] [NOWAIT]

• name − The name of the existing table to lock, possibly qualified by its schema. Only that table is locked if ONLY is entered before the table name. The table and any possible descendant tables are locked if ONLY is not specified.
• lock_mode − Which locks this lock conflicts with is specified by the lock mode. The most restrictive lock mode, ACCESS EXCLUSIVE, is utilized if no lock mode is specified.
•  [NOWAIT] simply tells not to wait for any lock to be released from the table, according to the current definition. If the lock is not gained, PostgreSQL will abort the transaction immediately.

Note: The lock is held for the duration of the current transaction after it has been achieved. Locks are always released after a transaction; there is no UNLOCK TABLE command.

Mode of Locks

• PostgreSQL Access Share Lock
• PostgreSQL Row Share Lock
• PostgreSQL Row Exclusive Lock
• PostgreSQL Share Update Exclusive
• PostgreSQL Share Lock
• PostgreSQL Share Row Exclusive
• PostgreSQL Exclusive Lock
• PostgreSQL Access Exclusive Lock

These lock modes will contain their specified function to lock the table in PostgreSQL.

Access Share

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.
Syntax:

begin;
lock table Email IN ACCESS SHARE MODE;
select * from Email;

Row Share

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.
Syntax:

begin;
lock table Email IN ROW SHARE MODE;

Row Exclusive

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. 
Syntax:

begin;
lock table Email IN ROW EXCLUSIVE MODE;

Share Update Exclusive

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

begin;
lock table Email IN SHARE UPDATE EXCLUSIVE MODE;

Share

In PostgreSQL, this lock is incompatible 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.
Syntax:

begin;
lock table Email IN SHARE MODE;

Share Row Exclusive

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

begin;
lock table Email IN SHARE ROW EXCLUSIVEMODE;

Exclusive

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.
Syntax:

begin;
lock table Email IN EXCLUSIVE MODE;

Access Exclusive

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.
Syntax:

begin;
lock table Email IN ACCESS EXCLUSIVE MODE;

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.

Conflict Modes in Table Level Locks

The following diagram depicts the lock modes’ conflicts.

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.

PostgreSQL Locks: Row Level Locks

• Types of Row Level Locks
• Conflict Modes in Row Level Locks

Types of Row Level Locks

PostgreSQL uses locks in every aspect of its functioning to serialize or distribute access to crucial data. Shared or exclusive locks are the two fundamental types that can cause this.

• Shared locks allow multiple backends or sessions to access the same resource simultaneously.
• Exclusive locks prevent more than one backend or session from using the specific resource at once.

In Postgres 9.3 and 9.4, there are four types of row-level locks:

• FOR UPDATE – SELECT-fetched rows are locked for updates while in the FOR UPDATE mode. As a result, other transactions cannot lock, modify, or destroy them. These rows will be stopped for any subsequent transactions that try to UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE them. DELETE of a row and UPDATE of some columns both acquire this mode (currently, the set of columns considered are those that have a unique index on them that can be used as a foreign key – but this may change in the future)
  Syntax:

select * from Email where id > 10 and id < 15 FOR UPDATE;

Note: This type of lock cannot be held concurrently by another transaction on the rows whose id is between 10 and 15. However, it can read.

• FOR SHARE – Similar to FOR NO KEY UPDATE, FOR SHARE gains share lock instead (not exclusive). A shared lock prevents other transactions from doing SELECT FOR SHARE or SELECT FOR KEY SHARE on these rows but not from performing UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE.
  Syntax:

select * from Email where id > 10 and id < 15 FOR SHARE;

Note: A subsequent transaction may acquire the same type of lock but is not permitted to change or remove the selected rows or obtain any exclusive locks.

• FOR NO KEY UPDATE – While comparable to FOR UPDATE, FOR NO KEY UPDATE is less effective and does not interfere with SELECT FOR KEY SHARE lock mode. It is obtained by the UPDATE command, which does not obtain the FOR UPDATE lock.
• FOR KEY SHARE – Similar to FOR SHARE in behavior, FOR KEY SHARE has a weaker lock: SELECT FOR UPDATE is prevented, but not SELECT FOR NO KEY UPDATE. A key-shared lock prevents other transactions from performing DELETE or any UPDATE that modifies the key values, but it does not stop other UPDATEs or selects for SHARE or KEY SHARE.

Conflict Modes in Row Level Locks

PostgreSQL Locks: Page Level Locks

Page-level Locks are native to two types. Share & Exclusive locks limit read/write access to table pages in the shared buffer pool and table and row locks. After a row is fetched or updated, these locks are immediately released. Page-level locks are typically not an issue for application developers, although they are listed here for completeness.

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.

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.

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.

PostgreSQL Locks: Deadlocks

Deadlocks might happen when two transactions wait for one another to complete their operations. Deadlocks can be annoying even though PostgreSQL can recognize them and break them with a ROLLBACK. Design your applications such that they will lock items in the same order to avoid encountering this issue.
Example:

The first transaction includes updates such as:

UPDATE users set name='Pratibha' where id = 2;

And the second transaction concurrently updates like:

UPDATE users set name='Pratibha' where id = 8;
UPDATE users set name='Pratibha' where id = 2;

And the first transaction wants to update like:

UPDATE users set name='Pratibha' where id = 8;

Thus, the first transaction in this instance is holding a row-level lock on row id=2, while the second transaction is holding a row-level lock on row(id=8) and is waiting for the lock’s release on row(id=2). But the initial transaction also holds off until the lock on row(id=8) is released. As a result, the two transactions are in waiting mode.

Memory for Locks

All the locks that the PostgreSQL instance uses will be stored in this memory component. All background servers and user processes connecting to the database share these locks. The size of this RAM component is somewhat influenced by the non-default, more significant settings of the two database options, namely max locks per transaction and max pred locks per transaction.

Postgresql.conf file default values for PostgreSQL lock space are as follows:

• #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

Before wrapping up, let’s cover some basics as well.

What is PostgreSQL Locks?

PostgreSQL locks often called “write locks” or “exclusive locks,” restrict users from modifying a row or a PostgreSQL table’s contents. Rows that have undergone a DELETE or UPDATE operation will be locked solely until the transaction is finished. Other users won’t be able to change the same rows until the transaction is rolled back or committed. Users won’t have to wait to alter various rows because this locking mechanism only engages when they attempt to modify the same rows.

PostgreSQL serializes changes to vital portions of the database using many layers of locks to ensure that complex transactions can run safely in parallel. Until they attempt to acquire a conflicting lock, such as when they update the same row, transactions are carried out concurrently. The first transaction to obtain the lock may then continue, while the second transaction must wait until the first transaction commits or aborts before proceeding.

Although some locking in databases happens automatically, there are some circumstances when locking must be done manually. The PostgreSQL LOCK command enables manual locking.

Note: There is no equivalent command for unlocking a PostgreSQL table; locks are automatically released at the end of a transaction.

What is the need for PostgreSQL Locks?

Isolation is essential in the context of transaction processing. The attribute known as isolation governs how and when changes are done and when they must be made accessible to other users, systems, and users themselves. By using a multi-version concurrency control framework, PostgreSQL accomplishes isolation.

Multiple sessions can access the same record simultaneously using the multi-version concurrency control technique. For example, while session A updates a record, session B can continue to access the record.

But,

• What would happen if session A and session B wished to change the same record simultaneously?
• What happens if session B tries to truncate a table while session A utilizes it?
• What will happen if you decide to vacuum the table while a transaction is still being processed in another session?

You can also take a look how you can work PostgreSQL VACUUM Command and JSON Functions & Operators to get a better understanding of PostgreSQL Functions.

Conclusion

Maintaining data integrity necessitates managing concurrent access to rows and tables. One can easily exercise this control and guarantee that only one person is making changes to a row or table at once by using PostgreSQL locking. In this article, we described the operation of PostgreSQL locks and examined an illustration showing how to use the LOCK TABLE command in practice.

You will be prepared to enable table locking in your own PostgreSQL database setup with the help of this tutorial.

Copying data into a warehouse using ETL for Data Analysis may be time-consuming if you frequently utilize PostgreSQL.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions