Transaction Isolation Levels

Teori

Forskellige niveauer af isolation mellem concurrent transactions der balancerer consistency mod performance.

Beskrivelse

Transaction isolation levels definerer graden af isolation mellem concurrent transactions - hvor meget én transaktion kan se af en andens uncommitted eller committed changes. SQL standarden definerer fire isolation levels, hver med forskellige trade-offs mellem data consistency og performance. Read Uncommitted er det laveste niveau hvor transactions kan læse uncommitted data fra andre transactions (dirty reads). Read Committed forhindrer dirty reads men tillader non-repeatable reads og phantom reads. Repeatable Read garanterer at gentagende læsninger returnerer samme data, men phantom reads kan stadig ske. Serializable er det højeste niveau der fuldstændigt isolerer transactions som om de kørte sekventielt, hvilket forhindrer alle anomalies men med høj performance cost. De fleste databaser bruger Read Committed som default fordi det giver god balance. PostgreSQL bruger en variant kaldet Snapshot Isolation. Valg af isolation level afhænger af application requirements - financial systems kræver ofte højere isolation, mens read-heavy applications kan acceptere lavere levels for bedre performance.

Problem

Concurrent transactions kan forårsage anomalies: dirty reads (læse uncommitted data), non-repeatable reads (samme query returnerer forskellige resultater), phantom reads (nye rows dukker op i range queries), og lost updates (to transactions overskriver hinandens changes). Uden proper isolation kan data integritet kompromitteres.

Løsning

Database isolation levels giver forskellige garantier mod disse anomalies. Højere isolation levels forhindrer flere anomalies men kræver mere locking og reducerer concurrency. Udviklere vælger isolation level baseret på application needs - hvor vigtig er consistency vs. performance?

Eksempel

-- SQL: Sæt isolation level

-- Read Uncommitted (lavest isolation)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
  SELECT * FROM accounts WHERE id = 123;
  -- Kan se uncommitted changes fra andre transactions (dirty read)
COMMIT;

-- Read Committed (default i de fleste databaser)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
  SELECT balance FROM accounts WHERE id = 123; -- Ser 1000
  -- Anden transaction ændrer balance til 500 og committer
  SELECT balance FROM accounts WHERE id = 123; -- Ser 500 (non-repeatable read)
COMMIT;

-- Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
  SELECT balance FROM accounts WHERE id = 123; -- Ser 1000
  -- Anden transaction ændrer balance til 500 og committer
  SELECT balance FROM accounts WHERE id = 123; -- Ser stadig 1000
  -- Men phantom reads kan stadig ske:
  SELECT * FROM accounts WHERE balance > 100; -- Ser 5 rows
  -- Anden transaction indsætter ny account
  SELECT * FROM accounts WHERE balance > 100; -- Kan se 6 rows
COMMIT;

-- Serializable (højest isolation)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
  SELECT * FROM accounts WHERE balance > 100;
  -- Ingen andre transactions kan ændre data der påvirker dette query
  -- Helt isoleret som om transactions kører sekventielt
COMMIT;

-- Anomaly eksempler

-- 1. Dirty Read (kun muligt i Read Uncommitted)
-- Transaction 1:
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 123;
-- Ikke committed endnu

-- Transaction 2 (READ UNCOMMITTED):
SELECT balance FROM accounts WHERE id = 123; -- Ser 500 (dirty!)

-- Transaction 1:
ROLLBACK; -- Balance er tilbage til original værdi
-- Transaction 2 læste data der aldrig blev committed!

-- 2. Non-Repeatable Read (muligt i Read Committed)
-- Transaction 1:
BEGIN;
SELECT balance FROM accounts WHERE id = 123; -- Ser 1000

-- Transaction 2:
UPDATE accounts SET balance = 500 WHERE id = 123;
COMMIT;

-- Transaction 1:
SELECT balance FROM accounts WHERE id = 123; -- Ser nu 500!
COMMIT;

-- 3. Phantom Read (muligt i Repeatable Read)
-- Transaction 1:
BEGIN;
SELECT COUNT(*) FROM accounts WHERE balance > 100; -- 10 accounts

-- Transaction 2:
INSERT INTO accounts (balance) VALUES (200);
COMMIT;

-- Transaction 1:
SELECT COUNT(*) FROM accounts WHERE balance > 100; -- Nu 11 accounts!
COMMIT;

-- PostgreSQL eksempel
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  -- Query data
  SELECT * FROM products WHERE stock > 0;
  -- Concurrent inserts vil ikke ses (no phantom reads i Postgres)
COMMIT;

-- MySQL/InnoDB eksempel
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
  SELECT SUM(balance) FROM accounts;
  -- Fuldstændig isoleret
COMMIT;

-- Node.js eksempel
const { Pool } = require('pg');
const pool = new Pool();

const client = await pool.connect();

try {
  await client.query('BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE');
  
  const result = await client.query(
    'SELECT stock FROM products WHERE id = $1',
    [productId]
  );
  
  const stock = result.rows[0].stock;
  
  if (stock >= quantity) {
    await client.query(
      'UPDATE products SET stock = stock - $1 WHERE id = $2',
      [quantity, productId]
    );
    
    await client.query('COMMIT');
  } else {
    await client.query('ROLLBACK');
    throw new Error('Insufficient stock');
  }
  
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

-- Isolation Level Comparison Table
-- Level            | Dirty Read | Non-Repeatable | Phantom | Performance
-- Read Uncommitted | Possible   | Possible       | Possible| Højest
-- Read Committed   | Prevented  | Possible       | Possible| Høj
-- Repeatable Read  | Prevented  | Prevented      | Possible| Medium
-- Serializable     | Prevented  | Prevented      | Prevented| Lavest

Fordele

✓Kontrol over consistency vs performance trade-off
✓Forhindrer data anomalies
✓Fleksibilitet til forskellige use cases
✓Standard across databaser (SQL-92)
✓Predictable behavior

Udfordringer

⚠Højere isolation = lavere concurrency
⚠Deadlocks mere sandsynlige ved høj isolation
⚠Performance impact af locking
⚠Forskellige implementations på tværs af databaser
⚠Kompleksitet i valg af rigtigt level

Anvendelsesområder

•Financial transactions (Serializable)
•Reporting og analytics (Read Committed)
•Inventory updates (Repeatable Read)
•Read-heavy dashboards (Read Uncommitted)
•Booking systems (Serializable)

Eksempler fra den virkelige verden

•Bank transfers bruger Serializable eller Repeatable Read
•E-commerce stock updates bruger Repeatable Read
•Analytics dashboards kan bruge Read Committed
•Booking systems (flights) bruger Serializable
•Social media feeds kan bruge Read Committed