Kunci baris dari chapter sebelumnya menyelesaikan duel dua penulis pada baris yang sama. Namun sebagian operasi bekerja pada cakupan yang jauh lebih luas: ALTER TABLE mengubah struktur, CREATE INDEX membangun indeks baru, VACUUM membersihkan versi baris mati, dan semuanya butuh jaminan bahwa tabel tidak berubah bentuk di tengah pekerjaan. Untuk itulah PostgreSQL memakai kunci level tabel — delapan mode dengan aturan benturan yang ketat. Bagian yang sering mengejutkan pemula: Anda sudah memegang kunci tabel setiap hari tanpa sadar, karena SELECT sekalipun mengambil salah satu mode itu secara otomatis. Chapter ini memetakan kedelapan mode, membuktikan jejaknya lewat catalog pg_locks, lalu mendemokan fenomena yang paling sering menggigit di produksi: ALTER TABLE yang membuat tabel terbaca mati total.

Menyiapkan Tabel Latihan #

Demo memakai tabel rekening yang sama dengan chapter-chapter sebelumnya, dibuat ulang supaya chapter ini mandiri:

sql buat-tabel.sql 
CREATE TABLE rekening (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    jenis text NOT NULL,
    saldo numeric(12, 2) NOT NULL CHECK (saldo >= 0),
    CONSTRAINT saldo_ewallet_maks
        CHECK (jenis <> 'e-wallet' OR saldo <= 1000000)
);

INSERT INTO rekening (id, nama, jenis, saldo) VALUES
    (1, 'Budi Santoso', 'tabungan', 500000),
    (2, 'Siti Aminah', 'tabungan', 300000),
    (3, 'Dewi Lestari', 'e-wallet', 950000);

SELECT id, nama, jenis, saldo FROM rekening ORDER BY id;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 3
 id |     nama     |  jenis   |   saldo   
----+--------------+----------+-----------
  1 | Budi Santoso | tabungan | 500000.00
  2 | Siti Aminah  | tabungan | 300000.00
  3 | Dewi Lestari | e-wallet | 950000.00
(3 rows)

Kunci Tabel yang Diam-diam Anda Pegang #

Setiap perintah yang menyentuh tabel mengambil kunci tabel level tertentu secara otomatis — UPDATE dan DELETE mengambil mode ROW EXCLUSIVE, SELECT mengambil mode ACCESS SHARE. Anda tidak pernah menuliskannya, tapi kunci itu tetap dipegang sampai transaksi berakhir. Catalog pg_locks mencatat semua kunci yang sedang aktif, dan query berikut memburu jejak kunci yang diambil UPDATE biasa dari dalam transaksi yang sama:

sql kunci-implisit.sql 
-- Setiap UPDATE diam-diam mengambil kunci tabel ROW EXCLUSIVE.
-- Query pg_locks berikut membuktikannya dari dalam transaksi yang sama.

BEGIN;

UPDATE rekening SET saldo = saldo - 1000 WHERE id = 1;

SELECT l.locktype, c.relname, l.mode, l.granted
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE c.relname = 'rekening'
ORDER BY l.locktype, c.relname, l.mode;

COMMIT;

SELECT id, nama, saldo FROM rekening ORDER BY id;

Output yang Dihasilkan:

text 
BEGIN
UPDATE 1
 locktype | relname  |       mode       | granted 
----------+----------+------------------+---------
 relation | rekening | RowExclusiveLock | t
(1 row)

COMMIT
 id |     nama     |   saldo   
----+--------------+-----------
  1 | Budi Santoso | 499000.00
  2 | Siti Aminah  | 300000.00
  3 | Dewi Lestari | 950000.00
(3 rows)

Satu baris hasil menunjukkan kunci relation pada rekening dengan mode RowExclusiveLock dan status granted = t — kunci itu dipegang sendiri oleh transaksi yang sedang berjalan. Query pemburu memfilter c.relname = 'rekening' dengan sengaja: tanpa filter, hasilnya berisikan kunci pada catalog itu sendiri (pg_locks, pg_class) yang dipakai untuk menjalankan query penelusuran. Kunci tabel tidak menghalangi transaksi lain membaca baris — MVCC tetap bekerja — yang dihalangi hanyalah operasi yang butuh mode kunci bertentangan, seperti yang dipetakan di bagian berikutnya.

Delapan Mode Kunci Tabel #

Kedelapan mode tersusun dari yang paling ringan hingga paling brutal. Tabel berikut merangkum siapa pengambil otomatisnya dan mode apa saja yang benturan dengannya — dua transaksi tidak bisa memegang mode yang saling benturan pada tabel yang sama secara bersamaan; yang datang belakangan menunggu:

ModeDiambil otomatis olehBertentangan dengan
ACCESS SHARESELECT dan query baca lainnyaACCESS EXCLUSIVE
ROW SHARESELECT ... FOR UPDATE / FOR SHAREEXCLUSIVE, ACCESS EXCLUSIVE
ROW EXCLUSIVEUPDATE, DELETE, INSERT, MERGESHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE UPDATE EXCLUSIVEVACUUM (tanpa FULL), ANALYZE, CREATE INDEX CONCURRENTLY, COMMENT ONdirinya sendiri, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARECREATE INDEX (tanpa CONCURRENTLY)ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE ROW EXCLUSIVECREATE TRIGGER, sebagian bentuk ALTER TABLEROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, dirinya sendiri, EXCLUSIVE, ACCESS EXCLUSIVE
EXCLUSIVEREFRESH MATERIALIZED VIEW CONCURRENTLYROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, dirinya sendiri, ACCESS EXCLUSIVE
ACCESS EXCLUSIVEDROP TABLE, TRUNCATE, CLUSTER, VACUUM FULL, REINDEX, banyak bentuk ALTER TABLEsemua mode, termasuk dirinya sendiri

Tiga pola mudah diingat dari tabel itu. Pertama, keluarga SHARE memblokir penulisan (ROW EXCLUSIVE benturan dengan SHARE) tetapi tidak pernah memblokir pembacaan — ACCESS SHARE hanya benturan dengan ACCESS EXCLUSIVE. Kedua, EXCLUSIVE masih menyisakan satu teman: pembaca ACCESS SHARE boleh lewat. Ketiga, ACCESS EXCLUSIVE sendirian di puncak — pemegangnya menjadi satu-satunya yang boleh menyentuh tabel dalam bentuk apa pun, dan mode inilah yang diambil sebagian besar perintah DDL.

LOCK TABLE Eksplisit #

Perintah LOCK TABLE memungkinkan Anda mengambil mode apa pun secara eksplisit, lebih kuat dari yang biasanya dibutuhkan perintah Anda sendiri. Kasus pakai klasiknya: aplikasi maintenance ingin membekukan penulisan sambil tetap membaca dengan tenang. Mode SHARE pas untuk itu — penulis lain menunggu, pembaca tetap bebas:

sql kunci-share.sql 
-- LOCK TABLE eksplisit dengan mode SHARE:
-- sesi lain masih boleh membaca, tetapi tulisan mereka harus menunggu.
-- Transaksi sendiri tetap boleh menulis (benturan dengan diri sendiri diizinkan).

BEGIN;

LOCK TABLE rekening IN SHARE MODE;

SELECT l.locktype, c.relname, l.mode, l.granted
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE c.relname = 'rekening'
ORDER BY l.locktype, c.relname, l.mode;

SELECT count(*) AS jumlah_rekening FROM rekening;

UPDATE rekening SET saldo = saldo - 25000 WHERE id = 2;

COMMIT;

SELECT id, nama, saldo FROM rekening ORDER BY id;

Output yang Dihasilkan:

text 
BEGIN
LOCK TABLE
 locktype | relname  |   mode    | granted 
----------+----------+-----------+---------
 relation | rekening | ShareLock | t
(1 row)

 jumlah_rekening 
-----------------
               3
(1 row)

UPDATE 1
COMMIT
 id |     nama     |   saldo   
----+--------------+-----------
  1 | Budi Santoso | 499000.00
  2 | Siti Aminah  | 275000.00
  3 | Dewi Lestari | 950000.00
(3 rows)

pg_locks kini menunjukkan ShareLock terpasang di tempat RowExclusiveLock sebelumnya. SELECT count(*) berjalan normal — pembacaan tidak pernah benturan dengan SHARE. UPDATE di akhir file juga berhasil, meski secara teori ROW EXCLUSIVE yang dibutuhkan UPDATE benturan dengan SHARE: satu transaksi tidak pernah menunggu kunci yang dipegangnya sendiri, jadi benturan dengan diri sendiri selalu diizinkan. Penulis dari sesi lain tentu saja tetap menunggu sampai transaksi ini COMMIT. LOCK TABLE tanpa klausa IN ... MODE mengambil ACCESS EXCLUSIVE — mode paling keras — jadi biasakan selalu menuliskan mode yang Anda maksud.

DDL Memblokir Pembaca #

Inilah bagian yang paling sering mengejutkan: ALTER TABLE mengambil ACCESS EXCLUSIVE — mode yang bertentangan dengan semua mode lain, termasuk ACCESS SHARE milik SELECT polos. Selama ALTER TABLE berjalan, bahkan sekadar membaca tabel harus menunggu. Jika ALTER TABLE berjalan di dalam transaksi terbuka, seluruh tabel tertutup untuk semua orang sampai transaksi itu ditutup.

Demo berikut mensimulasikan dua terminal. Terminal A menjalankan ALTER TABLE di dalam transaksi yang sengaja dibiarkan terbuka; terminal B mencoba SELECT biasa dengan batas waktu tunggu lock_timeout = '2s' supaya kegagalan terlihat cepat:

bash ddl-blokir-baca.sh 
#!/bin/bash
# Simulasi dua terminal: terminal A menjalankan DDL (ALTER TABLE) di dalam
# transaksi terbuka, terminal B mencoba membaca tabel yang sama dengan
# batas waktu tunggu lock_timeout.

: "${PGDATABASE:=latihan}"
export PGDATABASE

TMPD=$(mktemp -d)
mkfifo "$TMPD/komando.fifo"

# Terminal A: sesi psql yang tetap hidup, perintah dikirim lewat fifo
PGAPPNAME=terminal-a psql -X < "$TMPD/komando.fifo" > "$TMPD/a.log" 2>&1 &
SESIA=$!
exec 3>"$TMPD/komando.fifo"

printf 'BEGIN;\nALTER TABLE rekening ADD COLUMN catatan text;\n' >&3

# Tunggu sampai ALTER TABLE di A benar-benar berjalan (status idle in transaction)
for i in $(seq 1 100); do
  n=$(psql -X -tA -c "SELECT count(*) FROM pg_stat_activity
                      WHERE datname = current_database()
                        AND application_name = 'terminal-a'
                        AND state = 'idle in transaction'")
  [ "$n" = "1" ] && break
  sleep 0.05
done

# Terminal B: SELECT sederhana diblokir oleh ACCESS EXCLUSIVE milik A,
# lalu dibatalkan setelah lock_timeout terlampaui
PGAPPNAME=terminal-b psql -X \
  -c "SET lock_timeout = '2s'" \
  -c "SELECT count(*) FROM rekening;" \
  > "$TMPD/b.log" 2>&1

# Terminal A membatalkan transaksinya - kolom catatan ikut lenyap
printf 'ROLLBACK;\n' >&3
exec 3>&-
wait $SESIA

echo "== Terminal A: ALTER TABLE dalam transaksi terbuka =="
cat "$TMPD/a.log"
echo
echo "== Terminal B: SELECT diblokir, dibatalkan lock_timeout =="
cat "$TMPD/b.log"
echo
echo "== Struktur tabel setelah A rollback =="
psql -X -c "SELECT column_name, data_type FROM information_schema.columns
            WHERE table_name = 'rekening' ORDER BY ordinal_position;"

rm -rf "$TMPD"

Output yang Dihasilkan:

text 
== Terminal A: ALTER TABLE dalam transaksi terbuka ==
BEGIN
ALTER TABLE
ROLLBACK

== Terminal B: SELECT diblokir, dibatalkan lock_timeout ==
SET
ERROR:  canceling statement due to lock timeout
LINE 1: SELECT count(*) FROM rekening;
                             ^

== Struktur tabel setelah A rollback ==
 column_name | data_type 
-------------+-----------
 id          | integer
 nama        | text
 jenis       | text
 saldo       | numeric
(4 rows)

Ceritanya tiga babak. Terminal A membuka transaksi dan menjalankan ALTER TABLE rekening ADD COLUMN catatan text — perintahnya sendiri selesai seketika (tag ALTER TABLE muncul), tetapi kunci ACCESS EXCLUSIVE yang diambilnya terus dipegang selama transaksi masih terbuka. Terminal B kemudian mencoba menghitung isi tabel dan langsung terhenti: tanpa lock_timeout, SELECT itu akan menggantung selamanya menunggu A. Dengan lock_timeout = '2s', PostgreSQL membatalkan perintah B dengan error canceling statement due to lock timeout. Terminal A akhirnya melakukan ROLLBACK — dan pemeriksaan struktur di akhir demo membuktikan kolom catatan ikut lenyap, karena DDL yang belum di-commit juga bisa dibatalkan seperti data biasa.

Kunci tabel memang berkuasa, tetapi kekuatan itu punya harga tersembunyi: dua transaksi yang masing-masing menunggu kunci yang dipegang lawannya akan mengunci satu sama lain selamanya — kecuali PostgreSQL turun tangan. Keadaan itu bernama deadlock, dan mendeteksi serta mencegahnya adalah topik chapter berikutnya.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

buat-tabel.sql

Unduh berkas
sql 
CREATE TABLE rekening (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    jenis text NOT NULL,
    saldo numeric(12, 2) NOT NULL CHECK (saldo >= 0),
    CONSTRAINT saldo_ewallet_maks
        CHECK (jenis <> 'e-wallet' OR saldo <= 1000000)
);

INSERT INTO rekening (id, nama, jenis, saldo) VALUES
    (1, 'Budi Santoso', 'tabungan', 500000),
    (2, 'Siti Aminah', 'tabungan', 300000),
    (3, 'Dewi Lestari', 'e-wallet', 950000);

SELECT id, nama, jenis, saldo FROM rekening ORDER BY id;

ddl-blokir-baca.sh

Unduh berkas
bash 
#!/bin/bash
# Simulasi dua terminal: terminal A menjalankan DDL (ALTER TABLE) di dalam
# transaksi terbuka, terminal B mencoba membaca tabel yang sama dengan
# batas waktu tunggu lock_timeout.

: "${PGDATABASE:=latihan}"
export PGDATABASE

TMPD=$(mktemp -d)
mkfifo "$TMPD/komando.fifo"

# Terminal A: sesi psql yang tetap hidup, perintah dikirim lewat fifo
PGAPPNAME=terminal-a psql -X < "$TMPD/komando.fifo" > "$TMPD/a.log" 2>&1 &
SESIA=$!
exec 3>"$TMPD/komando.fifo"

printf 'BEGIN;\nALTER TABLE rekening ADD COLUMN catatan text;\n' >&3

# Tunggu sampai ALTER TABLE di A benar-benar berjalan (status idle in transaction)
for i in $(seq 1 100); do
  n=$(psql -X -tA -c "SELECT count(*) FROM pg_stat_activity
                      WHERE datname = current_database()
                        AND application_name = 'terminal-a'
                        AND state = 'idle in transaction'")
  [ "$n" = "1" ] && break
  sleep 0.05
done

# Terminal B: SELECT sederhana diblokir oleh ACCESS EXCLUSIVE milik A,
# lalu dibatalkan setelah lock_timeout terlampaui
PGAPPNAME=terminal-b psql -X \
  -c "SET lock_timeout = '2s'" \
  -c "SELECT count(*) FROM rekening;" \
  > "$TMPD/b.log" 2>&1

# Terminal A membatalkan transaksinya - kolom catatan ikut lenyap
printf 'ROLLBACK;\n' >&3
exec 3>&-
wait $SESIA

echo "== Terminal A: ALTER TABLE dalam transaksi terbuka =="
cat "$TMPD/a.log"
echo
echo "== Terminal B: SELECT diblokir, dibatalkan lock_timeout =="
cat "$TMPD/b.log"
echo
echo "== Struktur tabel setelah A rollback =="
psql -X -c "SELECT column_name, data_type FROM information_schema.columns
            WHERE table_name = 'rekening' ORDER BY ordinal_position;"

rm -rf "$TMPD"

kunci-implisit.sql

Unduh berkas
sql 
-- Setiap UPDATE diam-diam mengambil kunci tabel ROW EXCLUSIVE.
-- Query pg_locks berikut membuktikannya dari dalam transaksi yang sama.

BEGIN;

UPDATE rekening SET saldo = saldo - 1000 WHERE id = 1;

SELECT l.locktype, c.relname, l.mode, l.granted
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE c.relname = 'rekening'
ORDER BY l.locktype, c.relname, l.mode;

COMMIT;

SELECT id, nama, saldo FROM rekening ORDER BY id;

kunci-share.sql

Unduh berkas
sql 
-- LOCK TABLE eksplisit dengan mode SHARE:
-- sesi lain masih boleh membaca, tetapi tulisan mereka harus menunggu.
-- Transaksi sendiri tetap boleh menulis (benturan dengan diri sendiri diizinkan).

BEGIN;

LOCK TABLE rekening IN SHARE MODE;

SELECT l.locktype, c.relname, l.mode, l.granted
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE c.relname = 'rekening'
ORDER BY l.locktype, c.relname, l.mode;

SELECT count(*) AS jumlah_rekening FROM rekening;

UPDATE rekening SET saldo = saldo - 25000 WHERE id = 2;

COMMIT;

SELECT id, nama, saldo FROM rekening ORDER BY id;