Optimistic vs Pessimistic Locking databases Daq

cha' requests same row laD, cha' new value compute, cha' write back - second write first silently overwrite (qaS pagh rap). lost update ghaH. concurrent database access locking poQ. cha' standard strategies, optimistic 'ej pessimistic, conflicts chay' law' bet pIm.

lost update concrete

checkout Daq stock decrement.

-- request A reads stock = 5
SELECT stock FROM products WHERE id = 42;

-- request B reads stock = 5, at nearly the same time
SELECT stock FROM products WHERE id = 42;

-- both compute 5 - 1 = 4 and write it back
UPDATE products SET stock = 4 WHERE id = 42;
UPDATE products SET stock = 4 WHERE id = 42;

cha' units sold 'ach stock wa' neH drop. query fail pagh, transaction rollback pagh - data wrong. locking cha' requests wa' wait pagh fail.

pessimistic locking: conflict happen assume

pessimistic locking row lock grab (touch qaSpa'), transaction end hold, other transaction same lock want block.

BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- stock is now locked until this transaction commits or rolls back
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;

FOR UPDATE (Postgres, MySQL/InnoDB, relational engines law') database jatlh: other transaction row FOR UPDATE read pagh update wait. request B request A qaSDI' queue (race pagh).

reason Dun - lost update possibility pagh - 'ach cost: locked row contention point. checkout flow slow payment API call lock hold vaj others same product buy wait.

optimistic locking: conflict rare assume

optimistic locking lock up front pagh. row version marker tag, write vaj version choH pagh check.

-- add a version column
ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 0;

-- read the row along with its current version
SELECT stock, version FROM products WHERE id = 42;
-- suppose stock = 5, version = 7

-- write back conditioned on the version being unchanged
UPDATE products
SET stock = 4, version = 8
WHERE id = 42 AND version = 7;

UPDATE affected-row count legh. 1 vaj write Qapla', vay' touch pagh. 0 vaj vay' else row update (version bump) read write Dung - data stale, application decide: re-read retry pagh conflict user show.

async function decrementStock(productId) {
  const { stock, version } = await db.one(
    'SELECT stock, version FROM products WHERE id = $1',
    [productId],
  );

  const result = await db.result(
    'UPDATE products SET stock = $1, version = $2 WHERE id = $3 AND version = $4',
    [stock - 1, version + 1, productId, version],
  );

  if (result.rowCount === 0) {
    throw new ConflictError('Product was modified concurrently, retry');
  }
}

lock application logic, network calls, user think-time Daq hold pagh. contention write moment Daq neH visible, conflict qaS vaj.

choose

• Pessimistic conflicts frequent 'ej cheap serialize: hot rows few (popular product stock, shared counter, booking seat) - requests wait (retry pagh).
• Optimistic conflicts rare 'ej read-write Dung work expensive pagh long: document edit, user profile update, human form fill. database lock "user thinking" duration hold viable pagh.

heuristic: network call, UI interaction, tight database transaction pagh Dochmey lock vaj optimistic locking neH.

conflict handle

optimistic locking 0-row update error pagh - information. wej ways:

• Retry: row re-read, change reapply, write try. simple numeric updates (stock decrement) Dun.
• Merge: complex update (document multiple fields) re-read, non-conflicting parts merge, retry.
• Surface: user-facing edits "vay' else choH" user jatlh, decide (silently overwrite pagh discard pagh).

zero-row-count case catch 'ej move on Qo' - lost update again.

yaj

Pessimistic locking conflicts prevent (concurrent access wait turn); optimistic locking conflicts detect after fact, application respond. wa' universally better pagh - collisions chay' law' expect, request wait vs retry cost.