Database Transactions 'ej Isolation Levels yaj

"transaction Daq wrap" common advice 'ach transaction alone cha' concurrent operations step on each other pagh guarantee - isolation level depend, 'ej most databases default weaker people assume. wrong vaj bug real production load Daq neH tu' (single-user dev environment Daq pagh).

transaction actually guarantee

transaction atomicity (writes Hoch happen pagh none) 'ej durability (committed vaj crash survive) nob. cha' rarely question. vary: isolation - transaction wa' in-progress work other transaction (same time Qu') Daq visible chay' law'.

Isolation spectrum. weaker isolation: more concurrency better throughput 'ach more interference ways. stronger isolation: fewer surprises 'ach more locking contention.

wej classic anomalies

isolation levels sense qaSpa' problems Sov poQ:

Dirty read - A B written (not yet committed) row read. B rollback vaj A data never existed read.

Non-repeatable read - A row cha'logh read, different values (B change commit Dung).

Phantom read - A same range query cha'logh (WHERE amount > 100), second time different set of rows (B matching row insert pagh delete).

standard isolation levels

SQL loS levels define, Hoch more prevent:

• Read Uncommitted - nothing prevent. Dirty reads possible. PostgreSQL distinct implement pagh (Read Committed rap); MySQL InnoDB ghaj.
• Read Committed - dirty reads prevent. committed data neH legh 'ach value cha' reads Dung same transaction Daq choH laH. PostgreSQL, Oracle, SQL Server default.
• Repeatable Read - dirty reads 'ej non-repeatable reads prevent. row read qaSDI' transaction rest same value legh (other transaction change commit 'ach). MySQL InnoDB default. Phantom reads SQL standard technically possible 'ach PostgreSQL (snapshot isolation) prevent je.
• Serializable - wej Hoch prevent. transactions one at a time ran rap behave (concurrent Qu' 'ach).

Read Committed bite

cha' transactions account balance check debit qaSpa':

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- reads 100
-- ...application logic decides it's safe to withdraw 80...
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

-- Transaction B, running concurrently
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- also reads 100
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

cha' transactions balance read commit qaSpa' vaj cha' enough money believe. Read Committed Daq second UPDATE first transaction commit Dung block (row-level locking), current balance 20 Daq apply - raw SQL alone negative balance pagh. 'ach application balance one query Daq read "80 <= balance" application code Daq validate separate update qaSpa' vaj check-then-act sequence race live, Read Committed protect pagh.

fix: explicit locking pagh stronger isolation

Option 1: SELECT ... FOR UPDATE - row read explicitly lock, concurrent transaction same row read commit Dung block.

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- application logic
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

Option 2: isolation level raise 'ej conflict retry.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

Serializable proactively lock pagh - commit time conflicts detect, conflicting transactions wa' serialization failure abort. application catch 'ej retry:

async function withdrawSerializable(db, accountId, amount) {
  for (let attempt = 0; attempt < 3; attempt++) {
    try {
      await db.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const { rows } = await db.query('SELECT balance FROM accounts WHERE id = $1', [accountId]);
      if (rows[0].balance < amount) throw new Error('insufficient funds');
      await db.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, accountId]);
      await db.query('COMMIT');
      return;
    } catch (err) {
      await db.query('ROLLBACK');
      if (err.code !== '40001') throw err; // not a serialization failure — don't retry
    }
  }
  throw new Error('could not complete withdrawal after retries');
}

Option 3: single atomic statement, read-then-write race sidestep:

UPDATE accounts SET balance = balance - 80
WHERE id = 1 AND balance >= 80;
-- check affected row count; 0 means the guard failed

fits vaj best option usually - explicit locking pagh, retry logic pagh, isolation-level tuning pagh. always applicable pagh (read, non-trivial logic, write poQ) 'ach single UPDATE ... WHERE whole operation express vaj simpler faster.

practice level choose

Serializable everywhere Qo' - retry complexity chel, contention Daq throughput reduce. reasonable default:

• Read Committed (database default) operations law' vaD.
• Explicit row locking (FOR UPDATE) read-then-write sequences specific row Daq (balance example).
• Serializable correctness transaction consistent snapshot multiple statements depend vaj: financial ledgers, inventory counts, phantom pagh non-repeatable read wrong business outcome.
• atomic single statement operation one express vaj, isolation level Hoch - race window entirely remove.

Isolation level trade-off knob ("safety on" switch pagh) - right value specific race worry code piece Daq depend (whole application blanket setting pagh).