Fork me on GitHub

Project Notes

PostgreSQL Locks

Notes on managing PostgreSQL locks

Notes

Lock Conflicts

The following table summarises the locks that will prevent other locks from being granted, with examples of the types of commands that will request such locks.

Lock Requested \ Held AS RS RE SUE S SRE E AE Example SQL Commands
AS: Access Share               x SELECT
RS: Row Share             x x SELECT FOR UPDATE/SHARE
RS: Row Exclusive           x x x INSERT, UPDATE, DELETE
SUE: Share Update Exclusive         x x x x VACUUM, ALTER TABLE*, СREATE INDEX CONCURRENTLY
S: Share       x x x x x CREATE INDEX
SRE: Share Row Exclusive     x x x x x x CREATE TRIGGER, ALTER TABLE
E: Exclusive   x x x x x x x REFRESH MAT. VIEW CONCURRENTLY
AE: Access Exclusive x x x x x x x x DROP, TRUNCATE, VACUUM FULL, LOCK TABLE, ALTER TABLE

Query Current Locks

Using the pg_locks mixed with some other views to find which users and queries are holding a lock:

select
  l.pid,
  r.relname,
  locktype,
  l.granted,
  virtualtransaction,
  mode,
  a.usename,
  substr(query,1,50) as current_query
from pg_locks l
join pg_stat_activity a on a.pid=l.pid
join pg_class r on r.oid=l.relation
where l.pid <> pg_backend_pid()
order by virtualtransaction,l.pid;

Credits and References

About LCK#214 databasespostgresql
Project Source on GitHub Return to the Project Catalog

This page is a web-friendly rendering of my project notes shared in the LittleCodingKata GitHub repository.

LittleCodingKata is my collection of programming exercises, research and code toys broadly spanning things that relate to programming and software development (languages, frameworks and tools).

These range from the trivial to the complex and serious. Many are inspired by existing work and I'll note credits and references where applicable. The focus is quite scattered, as I variously work on things new and important in the moment, or go back to revisit things from the past.

This is primarily a personal collection for my own edification and learning, but anyone who stumbles by is welcome to borrow, steal or reference the work here. And if you spot errors or issues I'd really appreciate some feedback - create an issue, send me an email or even send a pull-request.