An understanding of PostgreSQL locking is important to build scalable applications and avoid downtime. Modern computers and servers have many CPU cores and it’s possible to execute multiple queries in parallel. Databases containing many consistent structures with changes made by queries or background processes running in parallel could crash a database or even corrupt data. Thus we need the ability to prevent access from concurrent processes, while changing shared memory structures or rows. One thread updates the structure while all others wait (exclusive lock), or multiple threads read the structure and all writes wait. The side effect of waits is a locking contention and server resources waste. Thus it’s important to understand why waits happen and what locks are involved. In this article, I review PostgreSQL row level locking.
In follow up posts, I will investigate table-level locks and latches protecting internal database structures.
Row locks – an overview
PostgreSQL has many locks at different abstraction levels. The most important locks for applications are related to MVCC implementation – row level locking. In second place – locks appearing during maintenance tasks (during backups/database migrations schema changes) – table level locking. It’s also possible—but rare—to see waits on low level PostgreSQL locks. More often there is a high CPU usage, with many concurrent queries running, but overall server performance reduced in comparison with normal number of queries running in parallel.
Example environment
To follow along, you need a PostgreSQL server with a single-column table containing several rows:
1 2 3 4 | postgres=# CREATE TABLE locktest (c INT); CREATE TABLE postgres=# INSERT INTO locktest VALUES (1), (2); INSERT 0 2 |
Row locks
Scenario: two concurrent transactions are trying to select a row for update.
PostgreSQL uses row-level locking in this case. Row level locking is tightly integrated with MVCC implementation, and uses hidden xmin and xmax fields. xmin and xmax store the transaction id. All statements requiring row-level locks modify the xmax field (even SELECT FOR UPDATE). The modification happens after the query returns its results, so in order to see xmax change we need to run SELECT FOR UPDATE twice. Usually, the xmax field is used to mark a row as expired—either removed by some transaction completely or in favor of updated row version—but it also used for row-level locking infrastructure.
If you need more details about the xmin and xmax hidden fields and MVCC implementation, please check our “Basic Understanding of Bloat and VACUUM in PostgreSQL” blog post.
1 2 3 4 5 6 7 8 9 10 11 12 | postgres=# BEGIN; postgres=# SELECT xmin,xmax, txid_current(), c FROM locktest WHERE c=1 FOR UPDATE; BEGIN xmin | xmax | txid_current | c ------+------+--------------+--- 579 | 581 | 583 | 1 (1 row) postgres=# SELECT xmin,xmax, txid_current(), c FROM locktest WHERE c=1 FOR UPDATE; xmin | xmax | txid_current | c ------+------+--------------+--- 579 | 583 | 583 | 1 (1 row) |
If a statement is trying to to modify the same row, it checks the list of unfinished transactions. The statement has to wait for modification until the transaction with id=xmax is finished.
There is no infrastructure for waiting on a specific row, but a transaction can wait on transaction id.
1 2 | -- second connection SELECT xmin,xmax,txid_current() FROM locktest WHERE c=1 FOR UPDATE; |
The SELECT FOR UPDATE query running in the second connection is unfinished, and waiting for the first transaction to complete.
pg_locks
Such waits and locks can be seen by querying pg_locks:
1 2 3 4 5 6 | postgres=# SELECT locktype,transactionid,virtualtransaction,pid,mode,granted,fastpath postgres-# FROM pg_locks WHERE transactionid=583; locktype | transactionid | virtualtransaction | pid | mode | granted | fastpath ---------------+---------------+--------------------+-------+---------------+---------+---------- transactionid | 583 | 4/107 | 31369 | ShareLock | f | f transactionid | 583 | 3/11 | 21144 | ExclusiveLock | t | f |
You can see the writer transaction id for locktype=transactionid == 583. Let’s get the pid and backend id for the holding lock:
1 2 3 4 5 | postgres=# SELECT id,pg_backend_pid() FROM pg_stat_get_backend_idset() AS t(id) postgres-# WHERE pg_stat_get_backend_pid(id) = pg_backend_pid(); id | pg_backend_pid ----+---------------- 3 | 21144 |
This backend has its lock granted (t). Each backend has an OS process identifier (PID) and internal PostgreSQL identifier (backend id). PostgreSQL can process many transactions, but locking can happen only between backends, and each backend executes a single transaction. Internal bookkeeping requires just a virtual transaction identifier: a pair of backend ids and a sequence number inside the backend.
Regardless of the number of rows locked, PostgreSQL will have only a single related lock in the pg_locks table. Queries might modify billions of rows but PostgreSQL does not waste memory for redundant locking structures.
A writer thread sets ExclusiveLock on its transactionid. All row level lock waiters set ShareLock. The lock manager resumes all previously locked backend locks as soon as the writer releases the lock.
Lock release for transactionid occurs on commit or rollback.
pg_stat_activity
Another great method to get locking-related details is to select from the pg_stat_activity table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | postgres=# SELECT pid,backend_xid,wait_event_type,wait_event,state,query FROM pg_stat_activity WHERE pid IN (31369,21144); -[ RECORD 1 ]---+--------------------------------------------------------------------------------------------------------------------------- pid | 21144 backend_xid | 583 wait_event_type | Client wait_event | ClientRead state | idle in transaction query | SELECT id,pg_backend_pid() FROM pg_stat_get_backend_idset() AS t(id) WHERE pg_stat_get_backend_pid(id) = pg_backend_pid(); -[ RECORD 2 ]---+--------------------------------------------------------------------------------------------------------------------------- pid | 31369 backend_xid | 585 wait_event_type | Lock wait_event | transactionid state | active query | SELECT xmin,xmax,txid_current() FROM locktest WHERE c=1 FOR UPDATE; |
Source code-level investigation
Let’s check the stack trace for the waiter with gdb and the pt-pmp tool:
1 2 3 4 | # pt-pmp -p 31369 Sat Jul 28 10:10:25 UTC 2018 30 ../sysdeps/unix/sysv/linux/epoll_wait.c: No such file or directory. 1 epoll_wait,WaitEventSetWaitBlock,WaitEventSetWait,WaitLatchOrSocket,WaitLatch,ProcSleep,WaitOnLock,LockAcquireExtended,LockAcquire,XactLockTableWait,heap_lock_tuple,ExecLockRows,ExecProcNode,ExecutePlan,standard_ExecutorRun,PortalRunSelect,PortalRun,exec_simple_query,PostgresMain,BackendRun,BackendStartup,ServerLoop,PostmasterMain,main |
The WaitOnLock function is causing the wait. The function is located in lock.c file (POSTGRES primary lock mechanism).
A lock table is a shared memory hash table. The conflicting process sleeps for the lock in storage/lmgr/proc.c. For the most part, this code should be invoked via lmgr.c or another lock-management module, not directly.
Next, locks listed in pg_stat_activity as “Lock” are also called heavyweight locks, and controlled by Lock Manager. HWLocks are also used for many high level actions.
By the way, a full description can be found here: https://www.postgresql.org/docs/current/static/explicit-locking.html
Summary
- Avoid long running transactions modifying frequently updated rows or too many rows
- Next, do not use hotspots (single row or multiple rows updated in parallel by many application client connections) with MVCC databases. This kind of workload is more suitable for in-memory databases and can usually be separated from the main business logic.
