ภาวะพร้อมกัน: ล็อก ระดับการแยกกัน และการอ่านภาพหลอน

ทำความเข้าใจโมเดลการล็อกของ SQLite ทรานแซกชันของมัน และความผิดปกติด้านการแยกกันที่มาตรฐาน SQL อธิบายไว้

12 นาที · 3 คำถาม

SQLite ล็อกที่ระดับ ไฟล์ฐานข้อมูลทั้งไฟล์ ไม่ใช่ที่ระดับแถว ในโหมดเริ่มต้น (rollback journal) ผู้อ่านจะใช้ล็อก SHARED ร่วมกัน แต่ผู้เขียนได้เพียงรายเดียวในแต่ละครั้งที่จะได้ล็อก EXCLUSIVE ผลที่ตามมาคือ: การเขียนถูก ทำให้เป็นลำดับ (serialized) มาสร้างตารางเพื่อทดลองใช้ทรานแซกชันกัน

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  balance INTEGER NOT NULL
);

INSERT INTO accounts (id, balance)
VALUES (1, 100), (2, 50);

-- Read: deferred transaction (default)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

ล็อกวิวัฒน์ไปเป็นขั้น ๆ: UNLOCKED จากนั้น SHARED (อ่าน), RESERVED (ตั้งใจจะเขียน), PENDING และสุดท้าย EXCLUSIVE (เขียน) หากทรานแซกชันหนึ่งถือล็อกสำหรับเขียนอยู่แล้ว อีกทรานแซกชันจะได้รับข้อผิดพลาด SQLITE_BUSY แทนที่จะล้มเหลวทันที ให้ตั้งค่าหน่วงเวลารอด้วย busy_timeout และเปิดใช้งานโหมด WAL เพื่อให้ผู้อ่านและผู้เขียนดำเนินการไปพร้อมกันได้

-- A single writer, concurrent readers
PRAGMA journal_mode = WAL;

-- Wait up to 5 s if the database is busy
PRAGMA busy_timeout = 5000;

มาตรฐาน SQL อธิบายความผิดปกติไว้สามอย่าง: การอ่านข้อมูลสกปรก (dirty read) (การอ่านข้อมูลที่ยังไม่ได้คอมมิต), การอ่านที่ทำซ้ำไม่ได้ (non-repeatable read) (การอ่านแถวเดิมซ้ำแล้วเห็นค่าที่ต่างออกไป) และ การอ่านภาพหลอน (phantom read) (การรันการสืบค้นแบบช่วงซ้ำแล้วเห็นแถวใหม่ ๆ ปรากฏขึ้น) เนื่องจาก SQLite ทำให้ผู้เขียนเป็นลำดับ มันจึงมีพฤติกรรมเสมือนเป็น SERIALIZABLE: ความผิดปกติเหล่านี้จะไม่ปรากฏ (ยกเว้นในโหมด shared-cache ที่ใช้ read_uncommitted) ในที่อื่น ๆ คุณเลือกระดับได้อย่างชัดแจ้ง

-- Atomic transfer: immediate write lock
BEGIN IMMEDIATE;
UPDATE accounts
SET balance = balance - 10
WHERE id = 1;
UPDATE accounts
SET balance = balance + 10
WHERE id = 2;
COMMIT;

ทดสอบความรู้

ตรวจสอบว่าคุณจำประเด็นสำคัญของบทเรียนนี้ได้ครบถ้วน

1. ในทางปฏิบัติ SQLite ให้ระดับการแยกกันแบบใด?
   • READ UNCOMMITTED การอ่านข้อมูลสกปรกเป็นไปได้โดยค่าเริ่มต้น
   • READ COMMITTED เหมือน PostgreSQL
   • SERIALIZABLE เพราะมีผู้เขียนเพียงรายเดียวทำงานในแต่ละครั้ง
   • ไม่มีเลย เพราะไม่มีทรานแซกชัน
2. อะไรคือความแตกต่างระหว่าง BEGIN และ BEGIN IMMEDIATE?
   • BEGIN IMMEDIATE เอาล็อกสำหรับเขียนทันที ส่วน BEGIN รอจนกว่าจะมีการเข้าถึงเพื่อเขียนครั้งแรก
   • BEGIN IMMEDIATE ปิดใช้งาน rollback journal
   • BEGIN IMMEDIATE สร้างฐานข้อมูลใหม่
   • ไม่มีความแตกต่างในพฤติกรรม
3. การอ่านภาพหลอนคืออะไร?
   • การอ่านค่าที่ทรานแซกชันอื่นยังไม่ได้คอมมิต
   • การอ่านแถวเดิมซ้ำแล้วได้ค่าที่ต่างออกไป
   • การรันการสืบค้นแบบช่วงซ้ำแล้วเห็นแถวใหม่ที่ถูกแทรกในระหว่างนั้นปรากฏขึ้น
   • การอ่านแถวที่เพิ่งถูกลบไป