Paano gumagana ang pag-lock sa PostgreSQL?

Question

Accepted Answer

PostgreSQL nggunaaken **locks** kanggo koordinaake akses bebarengan lan nyegaake konflik. Merga **MVCC**, maca-read umume ora perlu locks (pembaca ora blok panulis), nanging panulis njupuk row-level locks, lan macem-macem tipe lock koordinaake operasi. Ngerti pag-lock tulung ihindari contention lan deadlocks.

## MVCC teges maca umume ora 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 (saka panulis lan 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` lock baris supaya transaksi bebarengan ora bisa ngowahi iku — nyegaake race conditions ing operasi read-modify-write (conthone aman ngurangi inventory).

## Tipe lock lan 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).
```

## Ihindari masalah lock lan 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)
```

## Kenapa penting

Ngerti pag-lock PostgreSQL penting kanggo bangun **aplikasi bebarengan sing bener, performan**, dadi pancen ilmu senior-level kanggo sistem sing duwe akses database bebarengan abot.

Wisata dhasar iku **MVCC teges maca umume ora lock** (pembaca ora blok panulis — kauntungan concurrency kunci Postgres), dadi lock utamane gugat **panulis lan explicit locking**.

Ngerti **row-level locks** (diambil dening UPDATE/DELETE) lan luwih-luwih **`SELECT ... FOR UPDATE`** (explicit lock baris kanggo nyegaake race conditions ing operasi read-modify-write — kaya aman ngurangi inventory utawa proses balance, tanpa lock transaksi bebarengan bisa nyebabake asil salah) penting kanggo **kebener** ing skenario bebarengan.

Ngerti bilih **DDL/schema changes bisa njupuk table locks abot** (bisa blok query ing tabel sibuk — pertimbangan penting nalika rencana migrasi, sanajan Postgres wis ningkataken macem ALTER ora-blocking) tulung ihindari contention nalika deployment.

Ngerti carane **ihindari masalah lock lan deadlocks** — tenan transaksi ringkes (holding locks sethitik), njupuk locks ing order konsisten (nyegaake deadlocks), nggunaaken `FOR UPDATE` niyat (lan `SKIP LOCKED` kanggo pola queue), lan ndiagnosa contention lock (pg_locks, pg_stat_activity) — pancen ilmu kanggo performance lan reliability ing concurrency.

Merga concurrency melekat ing aplikasi nyata, lan merga pamakean tepat pag-lock (luwih-luwih `FOR UPDATE` kanggo kebener ing operasi read-modify-write, lan ihindari contention/deadlocks) sing nyebabake kode database bebarengan bener lan performan, ngerti pag-lock PostgreSQL — model MVCC-ora-read-locks, explicit row locking kanggo kebener, table locks saka DDL, lan ihindari deadlocks/contention — pancen ilmu senior-level kanggo bangun aplikasi bebarengan robust lan operaake Postgres ing beban abot, keperluan sering relevan sing nuduhake ngerti kontrol concurrency database.