Locking yana aiki ta yaya a PostgreSQL?

Question

IT Interview Practice · Accepted Answer

PostgreSQL yana amfani da **locks** don daidaita shiryayyen dabam da gida kasua daga rikitattun. Godiya ga **MVCC**, karatu galibi ba su bukaci locks (karatattun ba sa dakka marubutan), amma rubutu suna samun row-level locks, da nau'ikan lock daban-daban suna daidaita ayyukan. Fahimtar locking yana taimaka a guje contention da deadlocks.

## MVCC yana nufin karatu galibi ba sa lock

```text
Because of MVCC, plain SELECTs do NOT take row locks — they read a consistent snapshot.
→ Readers don't block writers, writers don't block readers (a key Postgres advantage).
Locking mainly comes into play for WRITES and explicit locking.
```

## Row-level locks (daga rubutu da explicit locking)

```sql
-- UPDATE/DELETE automatically lock the affected ROWS (until the transaction ends)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- locks row 1

-- SELECT ... FOR UPDATE — explicitly lock rows you intend to update (prevent races)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- lock the row
-- ... compute, then UPDATE — no other transaction can modify it meanwhile
UPDATE accounts SET balance = ... WHERE id = 1;
COMMIT;
```

`SELECT ... FOR UPDATE` yana kulle jeri ta daidai don shiryayjen gida ba su iya canja su — takewa jiyya-jiyya a read-modify-write ayyukan (misali karba hankali a inventory).

## Nau'ikan locks da table locks

```text
ROW-level → ACCESS via UPDATE/DELETE/SELECT FOR UPDATE — fine-grained
TABLE-level → some DDL (ALTER TABLE) takes heavier table locks → can block queries
→ Schema changes on busy tables can cause contention; plan migrations carefully
  (Postgres has gotten better — many ALTERs are now non-blocking, but not all).
```

## Guje jiyya-jiyya da deadlocks

```text
✓ Keep transactions SHORT (hold locks briefly → less contention)
✓ Acquire locks in a CONSISTENT ORDER across transactions → avoid DEADLOCKS
✓ Use FOR UPDATE deliberately for read-modify-write; consider FOR UPDATE SKIP LOCKED
  for queue-like patterns (skip already-locked rows)
✓ Watch for long-held locks blocking others (pg_locks, pg_stat_activity to diagnose)
✓ Be cautious with DDL on busy tables (can block)
```

## Dalilin da yake mahalaci

Fahimtar PostgreSQL locking yana da mahimmanci don gida **daidai, mai saurin shiryayjen dabam**, saboda haka ita ce mahimmanci ilimi na babbar mataki don tsarin da ke da shiryayjen dabam sosai na takaita. Fahimta ta asali ita ce **MVCC yana nufin karatu galibi ba sa lock** (karatattun ba sa dakka marubutan — mahimmar nufin amfani na Postgres concurrency), saboda haka locking kallon aiki ne da explicit locking. Fahimtar **row-level locks** (aka daga UPDATE/DELETE) da musamman **`SELECT ... FOR UPDATE`** (kulle jeri ta daidai don takewa jiyya-jiyya a read-modify-write ayyukan — kamar karba hankali a inventory ko sarrafa ma'aji, inda ba tare da locking shiryayjen gida za su iya haifar da sakamakun rashin daidai) yana da mahimmanci don **daidai** a shiryayjen dabam. Sanin cewa **DDL/schema canja zai iya ɗaukar matakin table locks** (yana iya dakka tambayoyi akan miyagun tables — mahimmar la'akari yayin shimedewa, ko da Postgres ya haifi marubutan ALTERs don rashin dakka) yana taimaka a guje contention yayin kasua. Fahimtar ta daidai yadda za a guje lock jiyya-jiyya da deadlocks — jiyya shiryayjen gida (locking gudu), samun locks a jiya daidai (takewa deadlocks), amfani `FOR UPDATE` da hankali (da `SKIP LOCKED` don layin ayyukan), da gida lock contention (pg_locks, pg_stat_activity) — ita ce mahimmaci mo'a da aminci a shiryayjen dabam. Tun da shiryayjen dabam yana da alaƙa a aiki na gaske, da tun da amfani daidai na locking (musamman `FOR UPDATE` don daidai a read-modify-write ayyukan, da guje contention/deadlocks) shine abin da suke zai sa shiryayjen takaita code daidai da mai saurin, fahimtar PostgreSQL locking — MVCC-ba-read-locks tsarin, explicit row locking don daidai, table locks daga DDL, da guje deadlocks/contention — ita ce mahimmaci ilimi na babbar mataki don gida tsarin shiryayjen dabam kuma aiki Postgres a karkashi da iska, mahimmar la'akari mai yada da suka nuna fahimtar control locking takaita.

MVCC yana nufin karatu galibi ba sa lock

Row-level locks (daga rubutu da explicit locking)

Nau'ikan locks da table locks

Guje jiyya-jiyya da deadlocks

Dalilin da yake mahalaci

Tambayoyi masu alaƙa