Isolation levels

Transactions and Error Handling in PostgreSQL

Jason Myers

Instructor

Concurrency

Handling the coordination of multiple operations at the same time.
Has a unique set of issues in a database.
Uses rules known as isolation levels to make it easier to reason about outcomes.

Dirty Reads

Dirty Read

Nonrepeatable Read

Phantom Read

Serialization Anomaly

Isolation levels

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read Uncommitted	Protected (PostgreSQL)	vulnerable	vulnerable	vulnerable
Read Committed	Protected	vulnerable	vulnerable	vulnerable
Repeatable Read	Protected	Protected	Protected (PostgreSQL)	vulnerable
Serializable	Protected	Protected	Protected	Protected

Affects of isolation levels

START TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
SELECT COUNT(*) FROM cookies WHERE name = 'lemon drop';

-- Cookie 6 has been added in an external transaction.

SELECT COUNT(*) FROM cookies WHERE name = 'lemon drop';
COMMIT;

Statement results

First SELECT result: 5
Second SELECT result: 6

Affects of isolation levels

START TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
SELECT COUNT(*) FROM cookies WHERE name = 'lemon drop';

-- Cookie 6 has been added in an external transaction.

SELECT COUNT(*) FROM cookies WHERE name = 'lemon drop';
COMMIT;

Statement results

First SELECT result: 5
Second SELECT result: 5

Let's practice!

Transactions and Error Handling in PostgreSQL