How PostgreSQL Locking works with FOR UPDATE MODE queries?

How PostgreSQL Locking works with FOR UPDATE MODE queries?


In PostgreSQL, when a query is executed with the FOR UPDATE mode, it places a lock on the selected rows. This lock prevents any other queries from modifying or updating the locked rows until the current transaction is committed or rolled back.

The locking mechanism in PostgreSQL works based on the isolation level of the transaction. The default isolation level is READ COMMITTED, which means that when a query is executed with FOR UPDATE mode, it acquires a shared lock on the selected rows. Other queries can read the locked rows, but they cannot update or delete them until the lock is released.

If the transaction isolation level is set to SERIALIZABLE, then the FOR UPDATE query acquires a exclusive lock on the selected rows, which means no other query can access the locked rows until the current transaction is committed or rolled back.

In addition to FOR UPDATE mode, PostgreSQL also supports other types of locks such as FOR SHARE, which allows multiple transactions to read the locked rows but not modify them, and FOR NO KEY UPDATE, which allows multiple transactions to read and update the locked rows but not insert new rows.

SQL Query for monitoring FOR UPDATE MODE queries in PostgreSQL

To monitor FOR UPDATE MODE queries in PostgreSQL, you can use the following SQL query:

This query will return the currently running queries that are using the FOR UPDATE MODE, along with their start time, lock status, process ID, and the user that executed the query.

About Shiv Iyer 485 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.