ACID และ MVCC ฉบับเข้าใจง่าย: อธิบายผ่านการโอนเงินใน PostgreSQL

ACID และ MVCC ตอบคำถามคนละข้อ ACID คือสิ่งที่ database แบบ transactional สัญญาไว้ ได้แก่ transaction ต้อง Atomic, ทำให้ข้อมูล Consistent, ทำงานแบบ Isolated จาก transaction อื่น และ Durable เมื่อ commit แล้ว ส่วน MVCC (multi-version concurrency control) คือเทคนิคหลักที่ database ใช้รักษาคำสัญญาเรื่อง isolation โดยไม่ต้องให้คนอ่านรอคนเขียนทุกครั้ง PostgreSQL ใช้ MVCC กับทุก table

วิธีที่เข้าใจทั้งสองเรื่องได้ง่ายที่สุดคือตัวอย่างคลาสสิก: การโอนเงินระหว่างบัญชีธนาคารสองบัญชี บทความนี้ไล่ ACID ทีละตัวอักษรด้วยตัวอย่างนี้ บอกว่า PostgreSQL ทำอะไรเบื้องหลังเพื่อรับประกันแต่ละข้อ แล้วอธิบายว่า MVCC ทำให้รายงานอ่านยอดบัญชีได้อย่างไรในขณะที่มีการโอนเงินกำลังแก้บัญชีนั้นอยู่ ตัวอย่างทั้งหมดใช้ PostgreSQL 16

ตัวอย่างการโอนเงิน

มีสองบัญชี และกฎว่ายอดเงินห้ามติดลบ:

CREATE TABLE accounts (
    id      text PRIMARY KEY,
    balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts VALUES ('A', 1000.00), ('B', 200.00);

การโอน 300 จาก A ไป B ประกอบด้วยสอง statement ที่ต้องทำงานเหมือนเป็นอันเดียว:

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 'A';
UPDATE accounts SET balance = balance + 300 WHERE id = 'B';
COMMIT;

ทุกอย่างที่อาจผิดพลาดกับการโอนนี้ จับคู่ได้กับตัวอักษรตัวใดตัวหนึ่งของ ACID

Atomicity: ทำทั้งหมดหรือไม่ทำเลย

ถ้า server ล่ม connection หลุด หรือ UPDATE ตัวที่สองล้มเหลว A ต้องไม่เสียเงิน 300 ไปในขณะที่ B ไม่ได้อะไรเลย การเปลี่ยนแปลงต้องเกิดครบทั้งคู่หรือไม่เกิดเลย

ลองโอนเงินแบบที่ผิดกฎ:

BEGIN;
UPDATE accounts SET balance = balance - 1500 WHERE id = 'A';
-- ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
UPDATE accounts SET balance = balance + 1500 WHERE id = 'B';
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;

หลังเกิด error แรก PostgreSQL จะไม่ยอมรันคำสั่งอื่นใน transaction นั้นอีก ทำได้อย่างเดียวคือ rollback ซึ่งตั้งใจออกแบบไว้แบบนี้ เพราะการทำต่อหลังจากล้มเหลวไปครึ่งทางคือต้นเหตุของการโอนเงินที่ค้างครึ่ง ๆ กลาง ๆ ถ้าต้องการรับมือกับ error ที่คาดไว้แล้วภายใน transaction ให้ใช้ SAVEPOINT และ ROLLBACK TO SAVEPOINT

PostgreSQL ทำอย่างไร PostgreSQL ไม่ได้ย้อนการเปลี่ยนแปลงด้วยการเขียน row กลับ แต่ละ transaction มี ID และสถานะสุดท้ายของมัน (กำลังทำ, commit แล้ว หรือ abort) ถูกบันทึกใน commit log ใต้ pg_xact/ row ที่ transaction ซึ่ง abort ไปแล้วเขียนไว้ยังอยู่ใน table แต่ทุกคนที่อ่านจะเช็กสถานะแล้วถือว่า row นั้นไม่เคยมีอยู่ vacuum จะมาลบทิ้งทีหลัง การ rollback จึงถูกมาก ไม่ว่า transaction จะแก้ row ไปหนึ่งแถวหรือหนึ่งล้านแถว

Consistency: กฎยังต้องเป็นจริงหลังจบ transaction

Consistency หมายถึง transaction พาข้อมูลจากสถานะที่ถูกต้องหนึ่งไปสู่อีกสถานะที่ถูกต้อง คำว่า "ถูกต้อง" ถูกนิยามด้วยกฎ บางกฎเราประกาศไว้ใน database บางกฎมีอยู่แค่ในแอปพลิเคชัน

Database บังคับใช้สิ่งที่เราประกาศไว้:

• CHECK (balance >= 0) ปฏิเสธการถอนเกินยอด อย่างที่เห็นข้างบน
• PRIMARY KEY และ UNIQUE ปฏิเสธข้อมูลซ้ำ
• FOREIGN KEY ปฏิเสธรายการโอนที่อ้างถึงบัญชีที่ไม่มีอยู่จริง
• NOT NULL ปฏิเสธค่าที่หายไป

กฎอื่น ๆ เป็นความรับผิดชอบของเรา database ไม่รู้ว่ายอดเงินรวมของ A และ B ต้องเท่าเดิมทั้งก่อนและหลังการโอน ถ้ามี bug ที่หัก A ไป 300 แต่เพิ่มให้ B แค่ 30 ทุก constraint จะผ่านหมด แต่ข้อมูลผิด ยิ่งเราเขียน business rule เป็น constraint ได้มากเท่าไร bug แบบนี้ก็หลุดไปถึงข้อมูลได้น้อยลงเท่านั้น นี่คือเหตุผลที่ตัว C ใน ACID ขึ้นอยู่กับการออกแบบ schema พอ ๆ กับตัว database engine

Isolation: transaction ที่รันพร้อมกันต้องไม่รบกวนกัน

ระบบจริงมีการโอนเงินพร้อมกันหลายรายการ isolation หมายถึงแต่ละ transaction ทำงานเหมือนมี database ไว้ใช้คนเดียว ตามระดับที่เราเลือก มาตรฐาน SQL กำหนดไว้สี่ระดับ และ PostgreSQL แยกการทำงานจริงไว้สามแบบ:

Isolation คือจุดที่ MVCC ทำงาน ซึ่งจะอธิบายต่อด้านล่าง ส่วนแต่ละระดับยอมให้เกิด anomaly อะไรบ้าง รวมถึง lost update และ write skew อยู่ในtransaction isolation levels และ write skew มี bug ฝั่งแอปที่พบบ่อยที่ควรพูดถึงตรงนี้ด้วย คือการอ่านยอดเงินขึ้นมาในโค้ด ลบในโค้ด แล้วเขียนผลกลับลงไป request สองตัวที่มาพร้อมกันอาจอ่านได้ 1000 ทั้งคู่และเขียน 700 ทั้งคู่ การคำนวณใน SQL (balance = balance - 300) หรือการ lock row ช่วยป้องกันได้ ดูรายละเอียดในการป้องกัน race condition ในระบบชำระเงิน

Durability: commit แล้วต้องอยู่จริง

เมื่อ COMMIT คืนค่ากลับมาแล้ว การโอนนั้นต้องรอดทั้งไฟดับ kernel panic หรือการ restart

PostgreSQL ทำอย่างไร ก่อนที่ COMMIT จะคืนค่า การเปลี่ยนแปลงของ transaction จะถูกเขียนลง write-ahead log (WAL) และ flush ลง disk ด้วย fsync ส่วน data page ที่ถูกแก้ยังอยู่ใน memory แล้วค่อยเขียนทีหลังได้ ถ้าเครื่องล่ม PostgreSQL จะ replay WAL จาก checkpoint ล่าสุดเพื่อสร้างการเปลี่ยนแปลงที่ยังไม่ลง data file ขึ้นมาใหม่ process ที่เกี่ยวข้องอย่าง WAL writer และ checkpointer อธิบายไว้ในPostgreSQL architecture

มีสอง setting ที่ควบคุมเรื่องนี้:

• fsync = on: อย่าปิดบน database ที่ข้อมูลมีค่า ถ้าปิดแล้วเครื่องล่ม ทั้ง cluster อาจเสียหาย ไม่ใช่แค่ transaction ล่าสุดหาย
• synchronous_commit = on (ค่าเริ่มต้น): commit รอจน WAL flush เสร็จ ถ้าตั้งเป็น off commit จะเร็วขึ้น และเมื่อเครื่องล่มอาจเสีย commit ช่วงไม่กี่ร้อยมิลลิวินาทีสุดท้าย (สูงสุดสามเท่าของ wal_writer_delay) แต่ข้อมูลไม่เสียหาย เหมาะกับข้อมูลอย่าง page view ไม่เหมาะกับเงิน:

BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO page_views (path, viewed_at) VALUES ('/pricing', now());
COMMIT;

Durability บนเครื่องเดียวไม่รอดถ้า disk พัง กรณีนั้นต้องมี replica และต้องใช้ synchronous replication ถ้าต้องการให้ commit อยู่บนสองเครื่องก่อนคืนค่า

MVCC ใน PostgreSQL ทำงานอย่างไร

ถ้าใช้ lock แบบธรรมดา transaction ที่ update row จะ lock row นั้น และใครที่อยากอ่านต้องรอ MVCC คิดต่างออกไป: เก็บ row ไว้หลายเวอร์ชัน แล้วให้แต่ละ transaction เห็นเวอร์ชันที่ตัวเองมีสิทธิ์เห็น

ใน PostgreSQL ทุกเวอร์ชันของ row (tuple) มีฟิลด์ซ่อนสองตัว:

• xmin: ID ของ transaction ที่สร้างเวอร์ชันนี้
• xmax: ID ของ transaction ที่ลบหรือแทนที่เวอร์ชันนี้ หรือ 0 ถ้ายังไม่มี

UPDATE ไม่เคยแก้ row ในที่เดิม มันจะใส่ xmax ให้เวอร์ชันปัจจุบัน แล้ว insert เวอร์ชันใหม่ที่มี xmin ใหม่ ส่วน DELETE แค่ใส่ xmax เวอร์ชันเหล่านี้ไปอยู่ตรงไหนใน page ขนาด 8KB อธิบายไว้ในPostgreSQL storage internals

ทุก query ทำงานพร้อมกับ snapshot ซึ่งเป็นบันทึกว่าตอนที่ถ่าย snapshot มี transaction ไหน commit ไปแล้วบ้าง:

SELECT pg_current_snapshot();
-- 1049:1052:1051

อ่านได้ว่า ทุก transaction ที่ต่ำกว่า 1049 จบไปแล้ว transaction ตั้งแต่ 1052 ขึ้นไปยังไม่เริ่ม และ 1051 ยังทำงานอยู่ row เวอร์ชันหนึ่งจะมองเห็นได้เมื่อ xmin ของมัน commit ก่อน snapshot และ xmax ว่าง, abort ไปแล้ว หรือยังไม่ commit ในมุมของ snapshot นั้น ใน Read Committed แต่ละ statement จะถ่าย snapshot ใหม่ ส่วนใน Repeatable Read และ Serializable transaction จะใช้ snapshot แรกไปตลอด

ทำไมคนอ่านไม่บล็อกคนเขียน

เริ่มใหม่จากยอดเดิม (A = 1000, B = 200) รันการโอนใน session A โดยยังไม่ commit แล้วอ่านจาก session B:

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 'A';
SELECT pg_current_xact_id();   -- 1051

-- Session B
SELECT ctid, xmin, xmax, balance FROM accounts WHERE id = 'A';
--  ctid  | xmin | xmax | balance
-- (0,1)  | 1049 | 1051 | 1000.00

Session B ได้ผลทันที มันเห็นเวอร์ชันเก่า xmax เป็น 1051 แต่ 1051 ยังไม่ commit เวอร์ชันนี้จึงยังมีชีวิตในมุมของ B ตัว B ไม่ต้องรอ A และ A ก็ไม่ต้องรอ B พอ commit ฝั่ง A แล้วรัน query เดิมใน B อีกครั้ง:

--  ctid  | xmin | xmax | balance
-- (0,3)  | 1051 |    0 |  700.00

ตอนนี้ B เห็นเวอร์ชันใหม่ซึ่งอยู่คนละตำแหน่งใน page ส่วนเวอร์ชันเก่าที่ (0,1) กลายเป็น dead tuple รอ vacuum มาเก็บ

แต่คนเขียนยังบล็อกคนเขียน บน row เดียวกัน ถ้า B รัน UPDATE accounts SET balance = balance + 50 WHERE id = 'A' ขณะที่ A ยังไม่ commit ตัว B จะต้องรอ เมื่อ A commit แล้ว B (ใน Read Committed) จะอ่านเวอร์ชันล่าสุดใหม่แล้วบวกเพิ่มจาก 700 ได้ 750 ถ้าเป็น Repeatable Read ตัว B จะ error ว่า "could not serialize access due to concurrent update" และควร retry

เทียบกับการออกแบบที่ใช้ lock ล้วน ๆ ซึ่งรายงานที่รันนาน ๆ จะถือ shared lock ไว้ และบล็อกการโอนเงินทุกรายการที่แตะ row ที่มันอ่าน

ราคาของ MVCC: dead tuple และ vacuum

Row เวอร์ชันเก่าไม่หายไปเอง PostgreSQL ใช้ VACUUM ซึ่งปกติ autovacuum เป็นคนรัน มาทำเครื่องหมายว่าพื้นที่ของเวอร์ชันที่ตายแล้วนำกลับมาใช้ได้ เมื่อไม่มี transaction ไหนที่ยังต้องเห็นมันอีก

ผลในทางปฏิบัติสามข้อ:

1. Table bloat การ update บ่อยสร้าง dead tuple จำนวนมาก ถ้า autovacuum ตามไม่ทัน table และ index จะบวม และการ scan จะช้าลง
2. Transaction ที่เปิดค้างนานขวางการเก็บกวาด transaction ที่เปิดค้างไว้หนึ่งชั่วโมงถือ snapshot ที่อาจต้องใช้เวอร์ชันเมื่อชั่วโมงก่อน vacuum จึงลบอะไรที่ใหม่กว่านั้นไม่ได้เลยทั้ง database สาเหตุที่พบบ่อยคือ session ที่ค้างอยู่ในสถานะ idle in transaction เพราะ bug ในแอป
3. Transaction ID wraparound transaction ID เป็นตัวนับขนาด 32-bit vacuum จะ "freeze" row เก่าเพื่อให้นำ ID กลับมาใช้ได้อย่างปลอดภัย ถ้า vacuum ถูกขวางนานเกินไป สุดท้าย PostgreSQL จะหยุดรับการเขียนเพื่อปกป้องข้อมูล

ตรวจทั้งสองปัญหาได้ด้วย:

-- tables with the most dead tuples
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- oldest open transactions
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

ตาข่ายกันพลาดสำหรับ transaction ที่ถูกลืม:

ALTER ROLE app SET idle_in_transaction_session_timeout = '5min';

PostgreSQL ยังลดต้นทุนของ update ด้วย HOT (heap-only tuple) update ถ้าไม่มีคอลัมน์ที่มี index ถูกเปลี่ยน และเวอร์ชันใหม่ใส่ลง page เดิมได้ index ก็ไม่ต้องเพิ่ม entry ใหม่ การเว้นที่ว่างใน page ด้วย fillfactor ที่ต่ำลงทำให้ table ที่ update หนัก ๆ ได้ HOT update บ่อยขึ้น

Database อื่นทำ MVCC ต่างออกไป InnoDB ของ MySQL และ Oracle แก้ row ในที่เดิมแล้วเก็บเวอร์ชันเก่าไว้ใน undo log ซึ่งย้ายต้นทุนการเก็บกวาดไปอยู่ที่อื่น แต่ไม่ได้ทำให้มันหายไป

คำถามที่พบบ่อย

ACID ใน database ย่อมาจากอะไร?

Atomicity, Consistency, Isolation และ Durability คือ transaction ต้องทำครบหรือไม่ทำเลย, กฎต้องเป็นจริงทั้งก่อนและหลัง, transaction ที่รันพร้อมกันต้องไม่รบกวนกัน และข้อมูลที่ commit แล้วต้องรอดจากเครื่องล่ม

MVCC แปลว่า PostgreSQL ไม่มี lock เลยหรือเปล่า?

ไม่ใช่ การอ่านไม่ได้ถือ lock ที่บล็อกการเขียน แต่สอง transaction ที่ update row เดียวกันยังชนกัน และตัวที่สองต้องรอ คำสั่ง DDL อย่าง ALTER TABLE ก็ถือ lock เช่นกัน

PostgreSQL เป็น ACID เต็มรูปแบบไหม?

ใช่ เมื่อใช้ค่าเริ่มต้น การปิด fsync ทำลาย durability และอาจทำให้ข้อมูลเสียหาย การปิด synchronous_commit อาจทำให้ commit ล่าสุดหายหลังเครื่องล่ม แต่ข้อมูลยังถูกต้อง

ทำไมต้องมี VACUUM ในเมื่อ PostgreSQL ลบ row ไปแล้ว?

DELETE หรือ UPDATE แค่ทำเครื่องหมายว่าเวอร์ชันเก่าตายแล้ว เพราะ transaction อื่นอาจยังต้องเห็นมันอยู่ vacuum จะมาคืนพื้นที่ทีหลัง เมื่อไม่มี snapshot ไหนมองเห็นเวอร์ชันนั้นอีก

Isolation level เริ่มต้นของ PostgreSQL คืออะไร?

Read Committed แต่ละ statement เห็นข้อมูลที่ commit ก่อน statement นั้นเริ่ม เพิ่มระดับราย transaction ได้ด้วย BEGIN ISOLATION LEVEL REPEATABLE READ หรือ SERIALIZABLE

Checklist เรื่อง ACID และ MVCC

• ใส่การเปลี่ยนแปลงหลายขั้นตอนที่ต้องสำเร็จหรือล้มเหลวไปด้วยกันไว้ใน transaction เดียว
• เขียน business rule เป็น constraint ให้มากที่สุด: CHECK, UNIQUE, FOREIGN KEY, NOT NULL
• คำนวณใน SQL หรือ lock row อย่าอ่านขึ้นมาแก้ในโค้ดแล้วเขียนกลับ
• เปิด fsync ไว้เสมอ และผ่อน synchronous_commit เฉพาะข้อมูลที่ยอมเสียได้
• ทำ transaction ให้สั้น ตั้ง idle_in_transaction_session_timeout และเฝ้าดู dead tuple

ถ้ากำลังออกแบบระบบที่การรับประกันเหล่านี้เกี่ยวกับเงินจริง Vectorkub รับสร้างและรีวิว backend ลักษณะนี้