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:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT pg_stat_activity.query, pg_stat_activity.query_start, pg_locks.granted, pg_stat_activity.pid, pg_stat_activity.usename FROM pg_stat_activity JOIN pg_locks ON (pg_stat_activity.pid = pg_locks.pid) WHERE pg_stat_activity.query LIKE '%FOR UPDATE%' ORDER BY pg_stat_activity.query_start DESC; |
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.
