© Laurenz Albe 2019
Many people know that explicit table locks with
LOCK TABLE are bad style and usually a consequence of bad design. The main reason is that they hamper concurrency and hence performance.
Through a recent support case I learned that there are even worse effects of explicit table locks.
Before an SQL statement uses a table, it takes the appropriate table lock. This prevents concurrent use that would conflict with its operation. For example, reading from a table will take a
ACCESS SHARE lock which will conflict with the
ACCESS EXCLUSIVE lock that
You can find a description of the individual lock levels in the documentation. There is also the matrix that shows which lock levels conflict with each other.
You don’t have to perform these table locks explicitly, PostgreSQL does it for you automatically.
Explicit table locks with the
LOCK TABLE statement
You can also explicitly request locks on a table with the
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
There are some cases where it is useful and indicated to use such an explicit table lock. One example is a bulk update of a table, where you want to avoid deadlocks with other transactions that modify the table at the same time. In that case you would use a
SHARE lock on the table that prevents concurrent data modifications:
LOCK atable IN SHARE MODE;
Typical mistakes with
Unfortunately, most people don’t think hard enough and just use “
LOCK atable” without thinking that the default lock mode is
ACCESS EXCLUSIVE, which blocks all concurrent access to the table, even read access. This harms performance more than necessary.
But most of the time, tables are locked because developers don’t know that there are less restrictive ways to achieve what they want:
- You don’t want concurrent transactions to modify a row between the time you read it and the time you update it? Use
SELECT ... FOR UPDATE!
If concurrent modifications are unlikely and you are not sure that you are actually going to modify the row, a
REPEATABLE READtransaction may be even better. That means that you have to be ready to retry the operation if the
UPDATEfails due to a serialization error.
- You want to perform several
SELECTs on the table and want to be sure that nobody modifies the table between your statements? Use a transaction with
REPEATABLE READisolation level, so that you see a consistent snapshot of the database!
- You want to get a row from a table, process it and then remove it? Use
DELETE ... RETURNING, then the row will be locked immediately!
- You want to implement a queue where workers should grab different items and process them? Use
SELECT ... LIMIT 1 FOR UPDATE SKIP LOCKED!
- You want to synchronize concurrent processes with database techniques? Use advisory locks!
LOCK TABLE versus autovacuum
It is necessary that autovacuum processes a table from time to time so that
- dead tuples are removed so that the space can be reused
- the free space map and the visibility map of the table are maintained
- table rows get “frozen” (marked as unconditionally all-visible) before the transaction counter wraps around
VACUUM requires a
SHARE UPDATE EXCLUSIVE lock on the table. This conflicts with the lock levels people typically use to explicitly lock tables, namely
ACCESS EXCLUSIVE. (As I said, the latter lock is usually used by mistake.)
Now autovacuum is designed to be non-intrusive. If any transaction that that wants to lock a table is blocked by autovacuum, the deadlock detector will cancel the autovacuum process after a second of waiting. You will see this message in the database log:
ERROR: canceling autovacuum task DETAIL: automatic vacuum of table "xyz"
The autovacuum launcher process will soon start another autovacuum worker for this table, so this is normally no big problem. Note that “normal” table modifications like
DELETE do not require locks that conflict with
How things can go wrong
If you use
LOCK on a table frequently, there is a good chance that autovacuum will never be able to successfully process that table. This is because it is designed to run slowly, again in an attempt not to be intrusive.
Then dead tuples won’t get removed, live tuples won’t get frozen, and the table will grow (“get bloated” in PostgreSQL jargon). The bigger the table grows, the less likely it becomes that autoacuum can finish processing it. This can go undetected for a long time unless you monitor the number of dead tuples for each table.
The ugly end
Eventually, though, the sticky brown substance is going to hit the ventilation device. This will happen when there are non-frozen live rows in the table that are older than
autovacuum_freeze_max_age. Then PostgreSQL knows that something has to be done to prevent data corruption due to transaction counter wrap-around. It will start autovacuum in “anti-wraparound mode” (you can see that in
pg_stat_activity in recent PostgreSQL versions).
Such an anti-wraparound autovacuum will not back down if it blocks other processes. The next
LOCK statement will block until autovacuum is done, and if it is an
ACCESS EXCLUSIVElock, all other transactions will queue behind it. Processing will come to a sudden stop. Since by now the table is probably bloated out of proportion and autovacuum is slow, this will take a long time.
If you cancel the autovacuum process or restart the database, the autovacuum will just start running again. Even if you disable autovacuum (which is a really bad idea), PostgreSQL will launch the anti-wraparound autovacuum. The only way to resume operation for a while is to increase
autovacuum_freeze_max_age, but that will only make things worse eventually: 1 million transactions before the point at which you would suffer data corruption from transaction counter wrap-around, PostgreSQL will shut down and can only be started in single-user mode for a manual
How can I avoid this problem?
First, if you already have the problem, declare downtime, launch an explicit
VACUUM (FULL, FREEZE) on the table and wait until it is done.
To avoid the problem:
- Don’t use
LOCKon a routine basis. Once a day for the nightly bulk load is fine, as long as autovacuum has enough time to finish during the day.
- Tune autovacuum to run more aggressively and hence faster. This can be done by increasing
- Use PostgreSQL 9.6 or later. Anti-wraparound autovacuum has been improved in 9.6; it now skips pages with only frozen rows to speed up processing.