Concurrency Control in PostgresQL
A sequence of database operations (reads, writes) treated as a single logical unit of work.
Think of transferring money:
Both must succeed or fail together.
A transaction guarantees ACID properties:
Databases often serve many users/applications simultaneously.
What happens when multiple transactions try to read/write the same data at the same time?
-- Transaction A
BEGIN;
-- Give 100€ to empty accounts for free
UPDATE accounts SET balance = 100 WHERE balance = 0;
COMMIT;
-- Transaction B
BEGIN;
-- 10% tax
UPDATE accounts SET balance = balance * 0.9 WHERE balance > 0;
COMMIT;
What's the new balance of an empty account?
a) 100€
b) 90€
c) 0€
-- Transaction A
BEGIN;
-- Give 100€ to empty accounts for free
UPDATE accounts SET balance = 100 WHERE balance = 0;
COMMIT;
-- Transaction B
BEGIN;
-- 10% tax
UPDATE accounts SET balance = balance * 0.9 WHERE balance > 0;
COMMIT;
Do non-empty accounts get taxed?
a) Yes
b) No
-- Transaction A
BEGIN;
-- Give 100€ to empty accounts for free
UPDATE accounts SET balance = 100 WHERE balance = 0;
COMMIT;
-- Transaction B
BEGIN;
-- Check empty accounts
SELECT * from accounts WHERE balance = 0;
-- 10% tax
UPDATE accounts SET balance = balance * 0.9 WHERE balance > 0;
COMMIT;
What's the new balance of an empty account?
a) 100€
b) 90€
c) 0€
d) this is impossible
-- Transaction A
BEGIN;
-- Expire all started consents
UPDATE consent SET status = 'expired' WHERE status = 'started';
COMMIT;
-- Transaction B
BEGIN;
-- Grant consent 1234
UPDATE consent SET status = 'granted' WHERE id = '1234';
COMMIT;
a) granted
b) granted at first, then expired
c) expired (never granted)
d) this is impossible
Assuming consent '1234' was started, what's its status?
-- Transaction A
BEGIN;
-- Expire all started consents
UPDATE consent SET status = 'expired' WHERE status = 'started';
COMMIT;
-- Transaction B
BEGIN;
SELECT * from consent where id = '1234';
-- Grant consent 1234
UPDATE consent SET status = 'granted' WHERE id = '1234';
COMMIT;
Assuming consent '1234' was started, what's its status?
a) expired, then granted
b) granted (never expired)
c) expired
d) this is impossible
Database systems provide different Transaction Isolation Levels.
Each level defines:
Higher isolation =
ANSI SQL-92 defines four Isolation Levels:
Overwriting data that hasn't been committed yet (and might be rolled back).
-- Transaction A
BEGIN;
-- Expire all started consents
UPDATE consent SET status = 'expired' WHERE status = 'started';
-- What happens here??
ROLLBACK;
-- Transaction B
BEGIN;
-- Grant started consent 1234 - DIRTY WRITE!
UPDATE consent SET status = 'granted' WHERE id = '1234';
COMMIT;
Reading data that hasn't been committed yet (and might be rolled back).
-- Transaction A
BEGIN;
-- Shows 100
SELECT balance FROM accounts WHERE id = 1;
-- Shows 50 - DIRTY READ!
SELECT balance FROM accounts WHERE id = 1;
COMMIT;
-- Transaction B
BEGIN;
-- Update to 50, but don't commit
UPDATE accounts SET balance = 50 WHERE id = 1;
-- Nevermind, rollback
ROLLBACK;
Reading the same data twice within a transaction yields different results because another transaction modified it and committed.
-- Transaction A
BEGIN;
-- Shows 100
SELECT balance FROM accounts WHERE id = 1;
-- Shows 50 - NON-REPEATABLE READ!
SELECT balance FROM accounts WHERE id = 1;
COMMIT;
-- Transaction B
BEGIN;
-- Update to 50, and commit
UPDATE accounts SET balance = 50 WHERE id = 1;
COMMIT;
Two transactions read the same row to update it but the first committed update is overwritten by the second committed update
-- Transaction A
BEGIN;
-- Expire all started consents
UPDATE consent SET status = 'expired' WHERE status = 'started';
COMMIT;
-- Transaction B
BEGIN;
-- Returns consent.status = 'started'
SELECT * from consent WHERE id = '1234';
-- Grant consent 1234 - LOST UPDATE!
UPDATE consent SET status = 'granted' WHERE id = '1234';
COMMIT;
ERROR: could not serialize access due to concurrent update
STATEMENT: UPDATE consent SET status = 'granted' WHERE id = '1234'
The second transaction that tries to commit will be rolled back!
Re-running a query within a transaction finds new rows that weren't there before (inserted by another committed transaction).
-- Transaction A
BEGIN;
-- Returns 5 accounts
SELECT * FROM accounts WHERE balance > 50;
-- Returns 6 accounts - PHANTOM READ!
SELECT * FROM accounts WHERE balance > 50;
COMMIT;
-- Transaction B
BEGIN;
INSERT INTO accounts VALUES (200);
COMMIT;
The result of concurrent transactions is inconsistent with any possible serial (one-after-another) execution.
-- Transaction A
BEGIN;
-- Give 100€ to empty accounts for free
UPDATE accounts SET balance = 100 WHERE balance = 0;
COMMIT;
-- Transaction B
BEGIN;
-- Tax 10% to all accounts with some money
UPDATE accounts SET balance = balance * 0.9 WHERE balance > 0;
COMMIT;
ERROR: could not serialize access due to read/write dependencies among transactions
STATEMENT: UPDATE accounts SET balance = balance * 0.9 WHERE balance > 0
The second transaction that tries to commit will be rolled back!
https://lchsk.com/benchmarking-concurrent-operations-in-postgresql.html
https://mbukowicz.github.io/databases/2020/05/01/snapshot-isolation-in-postgresql.html
PostgresQL uses a technique known as Snapshot Isolation
... and couples it with an other technique called MultiVersion Concurrency Control (MVCC) to make it performant
-- Transaction A
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Give 100€ to new empty accounts for free
UPDATE accounts SET balance = 100 WHERE balance = 0;
COMMIT;
-- Transaction B
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 10% tax
UPDATE accounts SET balance = balance * 0.9 WHERE balance > 0;
COMMIT;
What's the new balance of an empty account?
a) 100€
b) 90€
c) 0€
Transaction A gets rolled back!
-- Transaction A
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Give 100€ to new empty accounts for free
UPDATE accounts SET balance = 100 WHERE balance = 0;
COMMIT;
-- Transaction B
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 10% tax
UPDATE accounts SET balance = balance * 0.9 WHERE balance > 0;
COMMIT;
Will old accounts get taxed?
a) Yes
b) No
Transaction B gets rolled back!
-- Transaction A
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- Expire all started consents
UPDATE consent SET status = 'expired' WHERE status = 'started';
COMMIT;
-- Transaction B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * from consent where id = '1234';
-- Grant consent 1234
UPDATE consent SET status = 'granted' WHERE id = '1234';
COMMIT;
Assuming consent '1234' was started, what's its status?
a) expired, then granted
b) granted (never expired)
c) expired
d) this is impossible
Transaction B gets rolled back!