Isolation Levels — Read Committed, Repeatable Read, Serializable

~25 daq · PostgreSQL · Transaction va Concurrency

Ikkita tranzaksiya bir vaqtda ishlayapti. Biri ma'lumotni o'qiyapti, ikkinchisi o'zgartirmoqda. O'quvchi nima ko'rishi kerak — yangi qiymatmi, eski qiymatmi? Bu savol Isolation Level bilan hal qilinadi.

Muammolar

Dirty Read — nopok o'qish

Tranzaksiya 2 ning COMMIT qilinmagan o'zgarishini o'qish:

T1: BEGIN; UPDATE balans = 0;          (hali COMMIT emas)
T2:                          SELECT balans; → 0 (noto'g'ri!)
T1: ROLLBACK; (aslida o'zgarmadi)
T2: 0 ni ishlatdi — noto'g'ri ma'lumot

Non-repeatable Read

Bir tranzaksiya ichida bir xil so'rov turli natija:

T1: SELECT balans; → 1000
T2:           UPDATE balans = 500; COMMIT;
T1: SELECT balans; → 500  (o'zgardi!)

Phantom Read — yangi qatorlar paydo bo'lishi

T1: SELECT COUNT(*) → 10
T2:           INSERT ... COMMIT;
T1: SELECT COUNT(*) → 11  (yangi qator paydo bo'ldi!)

Isolation Levels

PostgreSQL to'rtta darajani qo'llab-quvvatlaydi:

Read Uncommitted  — PostgreSQL da Read Committed kabi ishlaydi
Read Committed    — standart, COMMIT qilinganlar ko'rinadi
Repeatable Read   — tranzaksiya boshidagi holat ko'rinadi
Serializable      — to'liq izolyatsiya, ketma-ket ishlagan kabi

Read Committed (standart)

Faqat COMMIT qilingan o'zgarishlar ko'rinadi. Dirty read yo'q.

-- Standart, alohida belgilash shart emas
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

T1: BEGIN; UPDATE balans = 0; (hali COMMIT emas)
T2: SELECT balans; → 1000  (T1 ning o'zgarishi ko'rinmaydi)
T1: COMMIT;
T2: SELECT balans; → 0     (endi ko'rinadi)

Non-repeatable read bo'lishi mumkin.

Repeatable Read

Tranzaksiya boshlangandagi holat "muzlab qoladi" — o'sha vaqtdagi ma'lumot ko'rinadi:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

T1: BEGIN (Repeatable Read);
T1: SELECT balans; → 1000
T2: UPDATE balans = 500; COMMIT;
T1: SELECT balans; → 1000  (o'zgarmadi — muzlab qolgan!)
T1: COMMIT;

Non-repeatable read yo'q. PostgreSQL da phantom read ham yo'q (MVCC tufayli).

Serializable — eng qattiq

Tranzaksiyalar ketma-ket (parallel emas) ishlagan kabi natija beradi. Eng xavfsiz, lekin eng sekin:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Agar parallel tranzaksiyalar bir-biriga ta'sir qilsa — biri serialization failure xatosi bilan bekor qilinadi:

ERROR: could not serialize access due to concurrent update

Bunday holda tranzaksiyani qayta urinish kerak.

Qaysi darajani tanlash?

Read Committed    — ko'pchilik holat uchun yetarli (standart)
Repeatable Read   — hisobot, eksport — bir vaqtdagi holat kerak
Serializable      — moliyaviy operatsiyalar, hisob-kitob

Tez-tez uchraydigan xatolar

Serializable da qayta urinishsiz:

// ❌ — serialization failure da dastur to'xtaydi
await client.query('BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE');
// ...

// ✅ — qayta urinish bilan
for (let i = 0; i < 3; i++) {
  try {
    await client.query('BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE');
    // ...
    await client.query('COMMIT');
    break;
  } catch (e) {
    await client.query('ROLLBACK');
    if (e.code !== '40001') throw e; // 40001 = serialization_failure
  }
}

Xulosa

Daraja              Dirty Read  Non-rep.  Phantom
──────────────────  ──────────  ────────  ───────
Read Committed      ❌           bo'ladi   bo'ladi
Repeatable Read     ❌           ❌         ❌ (PG da)
Serializable        ❌           ❌         ❌

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Keyingi dars

46-dars: Locking — Row lock, Advisory lock, Deadlock