PGCon2012 - Slide release #12

PGCon 2012
The PostgreSQL Conference

Álvaro Herrera
Day Talks - 2 - Friday - 2012-05-18
Room MRT 212
Start time 13:30
Duration 01:00
ID 483
Event type Lecture
Track 9.2 Features
Language used for presentation English

Improving foreign key concurrency

To lock and not to block

Row locking is a mechanism that lets Postgres maintain strict consistency in certain database constraints, such as foreign keys. However, Postgres has historically only provided share and exclusive row locking, which I'll show to have significant drawbacks for concurrency.

To solve the concurrency problem, two new row lock types are being introduced in release 9.2: SELECT FOR KEY SHARE and SELECT FOR KEY UPDATE. In this talk I'll explain how this new locking came to be, how it works, and how it helps significantly improve concurrency in applications.