# Locking in PostgreSQL
Locking allows us to control concurrent access to data in tables. For example, we can use locking to prevent two transactions from accessing the same table at the same time.
- Table-level locking
- Row-level locking
- Advisory locking
# Table-level locking
A table-level lock is a type of lock that a transaction can acquire while working on a table.
There are different types of table-level locks. Depending on what the transaction is doing, it may acquire a different type. When a transaction acquires a lock of a certain type, this prevents other transactions from acquiring locks that would conflict with it.
For example, let's say you start a transaction and it's trying to run an
ALTER TABLE query. While the
ALTER TABLE query runs, it usually acquires a lock of type
If another transaction comes along and wants to
INSERT into the table, it will try to acquire a lock of type
ACCESS EXCLUSIVE conflicts with
ROW EXCLUSIVE, so that second transaction would have to wait until the
ALTER TABLE transaction has either committed or rolled back.
There are various types of lock:
SHARE UPDATE EXCLUSIVE
SHARE ROW EXCLUSIVE
These conflict with each other in different ways, so if a transaction comes along and it acquires one lock, other transactions that try to acquire conflicting locks will have to wait.
Here's a table of which locks conflict with which: https://www.postgresql.org/docs/12/explicit-locking.html#TABLE-LOCK-COMPATIBILITY (opens new window)
Most of the time, you won't be acquiring locks yourself (although you can do). The PostgreSQL commands such as
ALTER TABLE or
INSERT will acquire relevant locks so that they can run without issues. It's important to understand that this is happening, because sometimes commands can take a long time to run, as they're waiting for another command to release their locks.
# Manual table-level locking
You can manually lock a table with the
LOCK TABLE users IN SHARE UPDATE EXCLUSIVE MODE;
At the end of the transaction, the lock is automatically released.
Note that manual locking can be quite dangerous because it can produce deadlocks (more on that in a little bit!).
# Row-level locking
Similar to table-level locking, each row can have its own lock. Row-level locking prevents two different transactions from affecting the same row, while still potentially affecting the same table. Row-level locking doesn't affect querying, it only affects writing and locking the row.
Here's a table of which row-level locks conflict: https://www.postgresql.org/docs/12/explicit-locking.html#ROW-LOCK-COMPATIBILITY (opens new window)
To acquire a row-level lock manually, we use the
SELECT statement. When looking at the docs for
SELECT, note the optional row-level locking syntax. Other statements such as
DELETE also acquire row-level locks.
SELECT * FROM users WHERE email = '[email protected]' FOR UPDATE;
FOR UPDATE clause at the end tells PostgreSQL to use the
FOR UPDATE row-level lock on the selected row(s). That means that other transactions that attempt
SELECT FOR UPDATE,
SELECT FOR NO KEY UPDATE,
SELECT FOR SHARE or
SELECT FOR KEY SHARE, will be blocked until the current transaction ends.
If this is done in a transaction for example, now the transaction could update the row multiple times without fear of another transaction acquiring a conflicting lock on the row.
When working with row-level locking (particularly if doing so manually), we can end up with a deadlock. A deadlock occurs when two separate transactions are both waiting on each other to release a lock before they can acquire a lock.
# Wrapping Up
PostgreSQL locking can be a rather confusing topic, both to understand initially and to debug when you encounter slow-running transactions later on!
You can monitor outstanding locks in a database using the
PostgreSQL also supports advisory locks, which have application-defined meaning and don't actually prevent queries from running at all.