Lock dan Error Busy

Kunci tulis tunggal, busy_timeout, dan pola menangani SQLITE_BUSY.

Daftar isi

Satu basis data SQLite hanya memperbolehkan satu penulis pada satu waktu, sehingga akses tulis yang bertabrakan menghasilkan error busy. Topik ini menjelaskan model lock SQLite, membedakan SQLITE_BUSY dengan SQLITE_LOCKED, memperkenalkan PRAGMA busy_timeout beserta parameter timeout pada Python, dan menutup dengan pola praktis menghadapi error tersebut. Seluruh demonstrasi memakai dua koneksi dalam satu skrip yang urutannya tetap, sehingga hasilnya selalu dapat direproduksi.

Satu penulis pada satu waktu#

SQLite mengatur akses berkas basis data dengan lock, yaitu tanda kepemilikan yang dipatuhi seluruh koneksi sebelum membaca atau menulis. Pada mode journal rollback, kunci itu bertingkat. Kunci SHARED boleh dipegang banyak pembaca sekaligus, kunci RESERVED menandakan niat menulis dan hanya boleh dipegang satu koneksi, kunci PENDING menandakan penulis yang menunggu seluruh pembaca bubar, dan kunci EXCLUSIVE yang hanya satu itu diperlukan untuk benar-benar menulis ke berkas.

Bentuknya menjadi lebih longgar pada mode WAL, karena pembaca tidak lagi menahan penulis dan sebaliknya. Meskipun demikian, sifat satu penulis tidak berubah, yaitu dua koneksi tetap tidak dapat mengomit bersamaan pada basis data yang sama. Perbedaannya letaknya pada siapa yang menunggu siapa, sehingga pola satu penulis dengan banyak pembaca nyaman dijalankan pada WAL dan tersendat pada mode rollback.

Ketika koneksi yang membutuhkan kunci menemukannya sedang dipegang pihak lain, hasilnya ditentukan oleh pengaturan penunggu. Tanpa penunggu, koneksi langsung menerima error dan pernyataannya gagal. Dengan penunggu, koneksi tidur sebentar lalu mencoba lagi sampai kunci diperoleh atau batas waktu habis, dan perilaku inilah yang diatur oleh busy_timeout.

SQLITE_BUSY dan SQLITE_LOCKED#

SQLITE_BUSY adalah kode hasil ketika kunci yang dibutuhkan sedang dipegang koneksi lain, biasanya pada proses berbeda, misalnya proses A sedang menulis besar sementara proses B mencoba membuka transaksi tulis. SQLITE_LOCKED menunjukkan konflik yang terjadi di dalam koneksi yang sama, misalnya pernyataan DROP TABLE yang ditolak karena masih ada kursor pada koneksi itu yang sedang membaca tabelnya. Keduanya sampai ke aplikasi Python sebagai sqlite3.OperationalError, dengan pesan database is locked untuk kasus BUSY.

Pengaturan penunggunya terlihat langsung lewat pragma. Nilai bawaan busy_timeout adalah nol, yang berarti tanpa penungguan sama sekali, dan nilainya diatur per koneksi.

bash

# baca nilai bawaan busy_timeout pada koneksi baru
sqlite3 :memory: "PRAGMA busy_timeout;"
# pasang penunggu 2000 milidetik, lalu baca lagi
sqlite3 :memory: "PRAGMA busy_timeout = 2000; PRAGMA busy_timeout;"

text Keluaran

0
2000
2000

Tiga baris keluaran itu berurutan menunjukkan nilai bawaan nol, nilai kembalian pragma penugasan yang langsung melaporkan nilai barunya, dan hasil pembacaan ulang pada koneksi yang sama. Nilai 2000 berarti setiap kegagalan memperoleh kunci akan dicoba ulang selama dua detik sebelum menyerah. Pengaturan yang berlaku hanya pada koneksi yang mengaturnya, bukan pada berkas basis datanya.

PRAGMA busy_timeout dan parameter timeout#

Modul sqlite3 pada Python menyediakan jalur yang sama lewat parameter timeout pada fungsi sqlite3.connect, yang menyatakan batas tunggu dalam detik dan secara bawaan bernilai 5.0. Nilai tersebut dijalankan sebagai busy handler, yakni rutinitas yang tidur sebentar lalu mencoba ulang setiap kali kunci belum diperoleh. Mengatur PRAGMA busy_timeout atau parameter timeout menghasilkan perilaku yang sama, sehingga pilihannya hanya soal tempat paling nyaman menuliskannya.

Skrip berikut memeragakan kegagalan dan pemulihannya secara deterministik. Koneksi a membuka transaksi tulis dengan BEGIN IMMEDIATE lalu memegangnya, sementara koneksi b yang diberi penunggu sepuluh milidetik mencoba memperbarui baris lain. Setelah a menutup transaksinya dengan COMMIT, percobaan ulang b berhasil.

python demo-busy.py

import os
import sqlite3

DB = "demo-busy.db"
if os.path.exists(DB):
    os.remove(DB)

def siapkan():
    k = sqlite3.connect(DB, isolation_level=None)
    k.execute("CREATE TABLE anggota (id INTEGER PRIMARY KEY, nama TEXT NOT NULL, saldo_denda INTEGER NOT NULL DEFAULT 0)")
    k.executemany("INSERT INTO anggota (nama, saldo_denda) VALUES (?, ?)",
                  [("Budi Santoso", 3000), ("Citra Kirana", 1500)])
    k.close()

siapkan()

a = sqlite3.connect(DB, isolation_level=None)
a.execute("PRAGMA busy_timeout = 10")
a.execute("BEGIN IMMEDIATE")
a.execute("UPDATE anggota SET saldo_denda = saldo_denda - 500 WHERE nama = 'Budi Santoso'")

b = sqlite3.connect(DB, isolation_level=None)
b.execute("PRAGMA busy_timeout = 10")
try:
    b.execute("UPDATE anggota SET saldo_denda = saldo_denda + 500 WHERE nama = 'Citra Kirana'")
    print("update dari b berhasil")
except sqlite3.OperationalError as e:
    print("error dari b:", e)

a.execute("COMMIT")
print("transaksi a selesai")

b.execute("UPDATE anggota SET saldo_denda = saldo_denda + 500 WHERE nama = 'Citra Kirana'")
print("ulangan dari b berhasil")
b.close()
a.close()

cek = sqlite3.connect(DB)
for nama, denda in cek.execute("SELECT nama, saldo_denda FROM anggota ORDER BY id"):
    print(nama, denda)
cek.close()

text Keluaran

error dari b: database is locked
transaksi a selesai
ulangan dari b berhasil
Budi Santoso 2500
Citra Kirana 2000

Pesan error dari b muncul karena penunggu sepuluh milidetiknya habis sebelum a melepaskan kuncinya, sedangkan percobaan ulang setelah COMMIT a berjalan mulus dan akhirnya mengubah saldo Citra Kirana menjadi 2000. Nilai sepuluh milidetik dipilih agar contoh cepat dan hasilnya pasti, sedangkan aplikasi nyata biasanya memasang ratusan milidetik sampai beberapa detik. Keluaran akhir menegaskan bahwa kedua pembaruan, milik a dan milik b, sama-sama tersimpan tanpa saling merusak.

Pola menghadapi error busy#

Error busy bukan kerusakan, melainkan sinyal antre yang sebaiknya dijawab dengan disiplin tertentu. Pola-pola berikut mengurangi frekuensinya sekaligus memperbaiki cara meresponsnya.

Pasang busy_timeout pada setiap koneksi sejak awal, karena banyak error busy sederhana hilang begitu ada penunggu yang layak.
Jaga transaksi tulis tetap singkat. Kelompokkan perubahan dan tutup transaksi segera, karena durasi kunci dipatuhi selama transaksi terbuka, bukan selama pernyataan dijalankan.
Gunakan BEGIN IMMEDIATE untuk pola baca lalu tulis dalam satu transaksi, sehingga hak tulis diamankan sebelum pekerjaan dimulai.
Antrekan tulisan melalui satu penulis tunggal apabila jumlah penulis bertambah, misalnya satu proses khusus yang melayani seluruh permintaan tulis pada mode WAL.

Pilihan BEGIN IMMEDIATE memindahkan titik kegagalan ke awal transaksi. Transaksi DEFERRED yang membaca dahulu lalu menulis baru mencari hak tulis ketika pernyataan tulis pertamanya datang, sehingga kegagalan bisa terjadi setelah sebagian pekerjaan sudah dijalankan. Pada BEGIN IMMEDIATE, konflik yang sama dilaporkan seketika ketika transaksi dibuka, dan penangannya cukup dicoba ulang tanpa membuka pekerjaan apa pun.

Kombinasi penunggu bawaan dan ulangan di tingkat aplikasi menutup dua sisi masalah yang sama. Penunggu menangani tabrakan pendek yang selesai dalam milidetik, sedangkan ulangan menangani antrean panjang yang butuh keputusan, misalnya memberi tahu pengguna bahwa permintaannya tertunda. Keduanya lebih baik daripada membiarkan error mentah naik sampai pengguna.

Ringkasan#

SQLite hanya mengizinkan satu penulis pada satu waktu, dan kunci tulisnya diperoleh melalui tingkatan lock pada mode rollback maupun mekanisme tulis WAL.
SQLITE_BUSY berarti kunci dipegang koneksi lain, sedangkan SQLITE_LOCKED berarti konflik terjadi pada koneksi yang sama.
PRAGMA busy_timeout dan parameter timeout pada Python memasang penunggu yang mencoba ulang sampai batas waktu tertentu.
Pola penangannya mencakup penunggu yang layak, transaksi tulis yang singkat, BEGIN IMMEDIATE untuk baca lalu tulis, dan antrean penulis tunggal.

Referensi#

File Locking And Concurrency In SQLite Version 3 — SQLite — Dokumentasi tingkatan lock SHARED, RESERVED, PENDING, dan EXCLUSIVE.
Transaction — SQLite — Perilaku BEGIN DEFERRED dan BEGIN IMMEDIATE ketika penulis lain sedang aktif.
PRAGMA Statements — SQLite — Rincian PRAGMA busy_timeout dan semantik penunggunya.