PostgreSQL Locks: Deadlocks, Object-Level Locks, and Predicate Locks
By
fanf2
If you only eat one bagel today, this is the bagel.
Summary
This technical article is part of a series on PostgreSQL locks, focusing on three main topics: deadlocks, remaining object-level locks, and predicate locks. It explains how deadlocks occur when transactions wait for each other's resources, discusses various object-level locks beyond relation locks, and introduces predicate locks used for serializable isolation levels. The content is technical and educational, aimed at database administrators and developers working with PostgreSQL.
Key quotes
· 3 pulledWhen using locks, we can confront a deadlock. It occurs when one transaction tries to acquire a resource that is already in use by another transaction, while that second transaction tries to acquire a resource held by the first one.
We'll start with deadlocks (actually, I planned to discuss them last time, but that article was excessively long in itself), then briefly review object-level locks left and finally discuss predicate locks.
We've already discussed some object-level locks (specifically, relation-level locks), as well as row-level locks with their connection to object-level locks and also explored wait queues, which are not always fair.
You might also wanna read
PostgreSQL work_mem Configuration Pitfalls: How Low Memory Settings Can Cause Catastrophic Outages
The article discusses a critical PostgreSQL database performance issue where a production cluster with 2 TB of RAM was killed by the OOM (Ou
mydbanotebook.org·2mo ago
PostgreSQL VACUUM Limitations: Why Indexes Become Bloated and How to Fix Them
This article debunks common misconceptions about PostgreSQL's VACUUM command, explaining that while VACUUM cleans dead tuples from tables, i
boringsql.com·5mo ago
PostgreSQL Double Buffering: Why OS RAM and Buffer Cache Compete and How the 25% Rule Helps
This article explains the problem of double buffering in PostgreSQL, where the OS filesystem cache and PostgreSQL's shared_buffers both cach
PostgreSQL Transaction ID Wraparound Incident: A Production Database Outage Case Study
This article details a real-world PostgreSQL production incident caused by transaction ID wraparound, a critical database failure mode. The
sqlservercentral.com·1mo ago
Performance Implications of UUID Version 4 as Primary Keys in PostgreSQL
The article discusses performance issues with using UUID Version 4 as primary keys in PostgreSQL databases, explaining that random UUIDs cau
andyatkinson.com·5mo ago
PostgreSQL Data Modeling: Comparing Lookup Tables vs Enum Types for Limited Value Columns
This technical article compares three approaches for implementing string columns with limited distinct values in PostgreSQL: simple strings
cybertec-postgresql.com·5mo ago