Transaction Isolation

Concurrency Control in PostgresQL

What's a Transaction?

A sequence of database operations (reads, writes) treated as a single logical unit of work.

Think of transferring money:

Debit Account A (-50€)
Credit Account B (+50€)

Both must succeed or fail together.

A transaction guarantees ACID properties:

Atomicity: All or nothing
Consistency: Database remains in a valid state
Isolation: Transactions don't interfere with each other
Durability: Once committed, changes persist

Why do we need isolation?

Databases often serve many users/applications simultaneously.

What happens when multiple transactions try to read/write the same data at the same time?

Quizz time

By default on PostgresQL...

-- 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

The solution: Transaction isolation levels

Database systems provide different Transaction Isolation Levels.

Each level defines:

Which concurrency anomalies are prevented.
Which anomalies are allowed (the trade-off!).

Higher isolation =

More data consistency guarantees
Lower performance/concurrency

Read uncommited (not implemented in PG)
Read commited (default in PG)
Repeatable read
Serializable

ANSI SQL-92 defines four Isolation Levels:

Isolation anomalies

Dirty writes

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;

Dirty writes

Read uncommited
Read committed
Repeatable read
Serializable

Dirty writes

Writes acquires a row-level exclusive lock
- It locks other writes, not reads!

Dirty reads

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;

Dirty reads

~~Read uncommited~~ (except in PG!)
Read committed
Repeatable read
Serializable

Dirty reads

Writes acquires a row-level exclusive lock
Reads see a snapshot of data as it was at the start of the query
- query, not transaction!

Non-Repeatable Read

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;

Non-Repeatable Read

~~Read uncommited~~
~~Read committed~~
Repeatable read
Serializable

Non-Repeatable Read

Writes acquires a row-level exclusive lock
Reads see a snapshot of data as it was at the start of the query
The transaction remembers the set of rows it has read, and subsequent reads see the same data

Lost Update

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;

Lost Update

~~Read uncommited~~
~~Read committed~~
Repeatable read
Serializable

Lost Update

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!

Lost Update

Writes acquires a row-level exclusive lock
Reads see a snapshot of data as it was at the start of the query
The transaction remembers the set of rows it has read, and subsequent reads see the same data
Writes check if any rows previously read have been modified

Phantom Read

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;

Phantom Read

~~Read uncommited~~
~~Read committed~~
~~Repeatable read~~ (except in PG!)
Serializable

Phantom Read

Writes acquires a row-level exclusive lock
Reads see a snapshot of data as it was at the start of the query
The transaction remembers the set of rows it has read, and subsequent reads see the same data
Writes check if any rows previously read have been modified
- this one is still the same

Serialization anomaly

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;

Serialization anomaly

~~Read uncommited~~
~~Read committed~~
~~Repeatable read~~
Serializable

Serialization anomaly

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!

Serialization anomaly

Writes acquires a row-level exclusive lock
Reads see a snapshot of data as it was at the start of the query
The transaction remembers the set of rows it has read, and subsequent reads see the same data
Writes check if any rows previously read have been modified
The database monitors for conflicting read/write patterns between concurrent transactions

Performance

https://lchsk.com/benchmarking-concurrent-operations-in-postgresql.html

Performances are comparable!
The biggest trade-off is serialization failures

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

New quizz!

-- 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!

Transaction Isolation

What's a Transaction?

Why do we need isolation?

Quizz time

By default on PostgresQL...

The solution: Transaction isolation levels

Isolation anomalies

Dirty writes

Dirty writes

Dirty writes

Dirty reads

Dirty reads

Dirty reads

Non-Repeatable Read

Non-Repeatable Read

Non-Repeatable Read

Lost Update

Lost Update

Lost Update

Lost Update

Phantom Read

Phantom Read

Phantom Read

Serialization anomaly

Serialization anomaly

Serialization anomaly

Serialization anomaly

Performance

New quizz!

Thank you

Transaction Isolation: Concurrency Control in PostgresQL (@ Swan)

Transaction Isolation: Concurrency Control in PostgresQL (@ Swan)

antogyn

Transaction Isolation

What's a Transaction?

Why do we need isolation?

Quizz time

By default on PostgresQL...

The solution: Transaction isolation levels

Isolation anomalies

Dirty writes

Dirty writes

Dirty writes

Dirty reads

Dirty reads

Dirty reads

Non-Repeatable Read

Non-Repeatable Read

Non-Repeatable Read

Lost Update

Lost Update

Lost Update

Lost Update

Phantom Read

Phantom Read

Phantom Read

Serialization anomaly

Serialization anomaly

Serialization anomaly

Serialization anomaly

Performance

New quizz!

Thank you

Transaction Isolation: Concurrency Control in PostgresQL (@ Swan)

More from antogyn