# 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 ACCESS EXCLUSIVE.

If another transaction comes along and wants to INSERT into the table, it will try to acquire a lock of type ROW EXCLUSIVE.

However, 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:

ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS 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^[1]. 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 command^[2]:

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^[3]. When looking at the docs for SELECT, note the optional row-level locking syntax. Other statements such as UPDATE and DELETE also acquire row-level locks.

SELECT * FROM users WHERE email = 'rolf@example.com' FOR UPDATE;

The 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 UPDATE, DELETE, 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^[4]. 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 pg_locks view^[5].

PostgreSQL also supports advisory locks, which have application-defined meaning and don't actually prevent queries from running at all^[6].

← Connections vs. transactions Asynchronous psycopg2 →