Trace PostgreSQL Row-Level Locks with pg_row_lock_tracer

PostgreSQL uses several types of locks to coordinate parallel transactions and manage access to resources like tuples, tables, and in-memory data structures. Heavyweight locks are used to control access to tables. Lightweight locks (LWLocks) manage access to data structures, such as adding data to the write-ahead log (WAL). Row-level locks control access to individual tuples. For example, tuples need to be locked when executing an SQL statement like SELECT * FROM table WHERE i > 10 FOR UPDATE;. The tuples returned by the query are internally locked with an exclusive lock (LOCK_TUPLE_EXCLUSIVE). Another transaction attempting to lock the same tuples must wait until the first transaction releases the locks. In this article, we discuss the tool pg_row_lock_tracer, which uses eBPF and UProbes to trace PostgreSQL’s row-locking behavior. The tool can be downloaded from the pg-lock-tracer project website. This is the third article in a series about tracing PostgreSQL locks. The first article covers the tracing of heavyweight locks, and the second article focuses on LW locks.