Isolation levels, savepoints, and rollbacks

Transactions and Error Handling in PostgreSQL

Jason Myers

Instructor

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
Transactions and Error Handling in PostgreSQL

Repeatable Read

  • SERIALIZABLE

    • Emulates serial transaction execution for all committed transactions.

  • REPEATABLE READ

    • Sees data
      • committed before the transaction began
      • results of previous statements in the transaction
    • Does not sees any changes committed by concurrent transactions.
Transactions and Error Handling in PostgreSQL

Visualizing REPEATABLE READ

REPEATABLE READ diagram

Transactions and Error Handling in PostgreSQL

Visualizing REPEATABLE READ

REPEATABLE READ diagram

Transactions and Error Handling in PostgreSQL

Visualizing REPEATABLE READ

REPEATABLE READ diagram

Transactions and Error Handling in PostgreSQL

REPEATABLE READ

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

UPDATE inventory 
SET quantity = quantity - 4
WHERE name = 'macaron';
SAVEPOINT first;

UPDATE inventory 
SET quantity = quantity - 12
SAVEPOINT second;

COMMIT;
Transactions and Error Handling in PostgreSQL

Let's practice!

Transactions and Error Handling in PostgreSQL

Preparing Video For Download...