Đến nội dung chính
notes.vinhnq.com

Tìm kiếm

Database & ORMSenior21 phút đọc

Transaction từ bên trong: WAL, MVCC và undo log

Ôn 13 thẻ
Trong bài này

Cụm bài Transaction và locking chuyên sâu: 1. Transaction từ bên trong · 2. Transaction gây hại · 3. Lock trong InnoDB · 4. Optimistic, pessimistic và bài đặt vé

Bài này đi xuống bên dưới isolation level: database làm gì để giữ lời hứa của transaction, và vì sao một transaction mở quá lâu làm chậm cả hệ thống. Đọc xong, bạn giải thích được redo log, undo log, MVCC của InnoDB và PostgreSQL, và biết đo cái giá của snapshot cũ. Nên đọc trước bài transaction, isolation level và locking.

1. Chạy lại bốn isolation level

Ý chính: bảng “level nào chặn anomaly nào” trong sách là chuẩn SQL, còn mỗi database cài đặt khác nhau. Chạy thử thì thấy khác ở ba chỗ: READ UNCOMMITTED, lost update ở REPEATABLE READ, và cách SERIALIZABLE từ chối.

Năm anomaly (hiện tượng sai khi hai transaction chạy chen nhau):

  • Dirty read: đọc được dữ liệu người khác chưa commit.
  • Non-repeatable read: đọc một dòng hai lần, ra hai giá trị.
  • Phantom: đếm hai lần, lần sau có thêm dòng người khác vừa insert.
  • Lost update: hai bên cùng đọc, tính trong app rồi ghi, bên sau đè mất phần bên trước.
  • Write skew: hai bên đọc cùng dữ liệu rồi ghi vào hai dòng khác nhau, gộp lại phá một quy tắc (ví dụ “luôn có ít nhất một bác sĩ trực”).

Mọi số liệu trong bài là từ thí nghiệm trên Docker (MySQL 8.4.11, PostgreSQL 16.14) bằng JDK 21. Chương trình mở hai connection, đẩy từng câu theo đúng thứ tự thời gian; câu nào quá 400 ms chưa xong thì ghi nhận là “bị chặn” (đang chờ lock).

Chỗ khác lý thuyết MySQL 8.4 (InnoDB) PostgreSQL 16
READ UNCOMMITTED dirty read thật: T2 thấy 0 chạy như READ COMMITTED: T2 thấy 100
Lost update ở RR không chặn: mất +50 T1 lỗi could not serialize access due to concurrent update
Write skew ở RU, RC, RR không chặn: còn 0 người trực không chặn: còn 0 người trực
SERIALIZABLE đọc thành khoá, kết thúc bằng deadlock SSI: không ai chờ, một bên bị huỷ
Bảng đo đầy đủ: 5 anomaly × 4 level trên MySQL 8.4 và PostgreSQL 16

Dữ liệu: bảng acct có 3 dòng, mỗi dòng balance = 100, hai dòng có on_call = TRUE. Code ở Bài tập 1.

Kịch bản Level MySQL 8.4 PostgreSQL 16
Dirty read: T1 sửa chưa commit, T2 đọc RU T2 thấy 0 T2 thấy 100
RC, RR thấy 100 thấy 100
SERIALIZABLE T2 bị chặn tới khi T1 xong thấy 100
Non-repeatable read: T1 đọc hai lần, giữa chừng T2 sửa và commit RU, RC 100 rồi 50 100 rồi 50
RR, SER 100 rồi 100 100 rồi 100
Phantom: T1 đếm hai lần, giữa chừng T2 insert và commit RU, RC 3 rồi 4 3 rồi 4
RR 3 rồi 3 3 rồi 3
SER 3 rồi 3, insert của T2 bị chặn tới khi T1 commit 3 rồi 3, không ai bị chặn
Lost update: cả hai đọc 100, tính trong app rồi ghi RU, RC còn 110 (mất +50) còn 110
RR còn 110 T1 lỗi could not serialize access due to concurrent update
SER T2 bị chặn, T1 bị chọn làm nạn nhân deadlock T1 lỗi serialization
Write skew: hai bác sĩ cùng thấy 2 người trực, mỗi người tự nghỉ RU, RC, RR còn 0 người trực còn 0 người trực
SER T2 bị deadlock, còn 1 người T2 lỗi khi commit (read/write dependencies), còn 1 người

Lost update ở REPEATABLE READ của InnoDB là câu phỏng vấn senior kinh điển. Nó cũng cho thấy “dùng READ COMMITTED cho hệ thống tài chính” chỉ đúng một nửa: tiền vẫn mất nếu code đọc-tính-ghi trong ứng dụng. Tính đúng của tiền đến từ câu UPDATE nguyên tử, khoá dòng hoặc version (phần 4 đo chuyện này trên bài đặt vé).

2. Đường đi của một lần ghi: write-ahead logging

Ý chính: commit chỉ cần ghi một bản ghi thay đổi vào log tuần tự và fsync; page dữ liệu được ghi xuống đĩa sau. Đó là write-ahead logging (WAL).

Ghi ngay page dữ liệu 16 KB vào đúng vị trí mỗi lần commit là ghi ngẫu nhiên, rất chậm; ghi nối vào cuối log thì nhanh hơn nhiều. Giống người bán hàng ghi sổ mỗi đơn trước, cuối ngày mới sắp lại kho: mất điện thì đọc sổ là biết cần làm lại gì.

flowchart LR
  A[UPDATE] --> B[Sửa page trong buffer pool]
  B --> C[Ghi bản ghi redo vào log buffer]
  C --> D[COMMIT: ghi và fsync redo log]
  D --> E[Trả OK cho client]
  B -.-> F[Page bẩn được flush xuống file dữ liệu sau]
  • Buffer pool: vùng nhớ chứa page dữ liệu và index. UPDATE sửa page ở đây, page thành “bẩn” (dirty, chưa xuống đĩa).
  • Redo log: log tuần tự. Khi commit, InnoDB ghi redo log xuống đĩa và fsync (với innodb_flush_log_at_trx_commit = 1).
  • Khi crash: InnoDB đọc redo log và làm lại thay đổi chưa kịp xuống file dữ liệu, rồi dùng undo log huỷ transaction chưa commit. PostgreSQL làm tương tự với WAL của nó (pg_wal).

Cần nhớ: redo log cho durability (làm lại cái đã commit), undo log cho atomicity và MVCC (huỷ cái chưa commit, dựng bản cũ). Hai thứ hay bị nói lẫn.

Bên trong InnoDB: LSN, checkpoint, doublewrite và group commit
  • LSN (log sequence number): số tăng dần gắn với mỗi bản ghi redo. Từ MySQL 8.0.30, kích thước redo log chỉnh bằng innodb_redo_log_capacity.
  • Checkpoint: tiến trình nền flush dần page bẩn xuống file dữ liệu. Page có LSN nhỏ hơn checkpoint đã an toàn trên đĩa, nên phần redo log tương ứng được tái sử dụng. Khi crash, InnoDB đọc redo log từ checkpoint cuối.
  • Doublewrite buffer: page 16 KB có thể bị ghi dở khi mất điện (torn page). InnoDB ghi page vào vùng doublewrite trước, rồi mới ghi vào vị trí thật, để lúc khôi phục luôn có một bản nguyên vẹn. PostgreSQL dùng full_page_writes thay cho doublewrite.

3. MVCC trong InnoDB: chuỗi phiên bản trong undo log

Ý chính: dòng trong bảng luôn là bản mới nhất; bản cũ được dựng lại bằng cách áp ngược chuỗi bản ghi undo. Read view quyết định mỗi câu đọc được thấy bản nào.

MVCC (multi-version concurrency control): database giữ nhiều phiên bản của một dòng, để người đọc thấy một ảnh chụp nhất quán mà không phải chờ người ghi.

Mỗi dòng có cột ẩn. DB_TRX_ID là id transaction sửa dòng gần nhất. DB_ROLL_PTR trỏ tới bản ghi undo chứa phiên bản trước. Bảng không có primary key thì có thêm DB_ROW_ID.

Mỗi lần sửa tạo một bản ghi undo. Phiên bản cũ không được lưu nguyên dòng. Nó được dựng lại bằng cách đi theo chuỗi DB_ROLL_PTR và áp ngược từng bản ghi undo. Slide nói “một phiên bản của dòng gọi là undo log” là chưa chính xác: undo log là công thức để quay lại phiên bản trước, giống lịch sử Ctrl+Z.

flowchart LR
  R["Clustered index: v4 (trx 104)"] -->|roll ptr| U3["undo: v4 về v3 (trx 103)"]
  U3 -->|roll ptr| U2["undo: v3 về v2 (trx 102)"]
  U2 -->|roll ptr| U1["undo: v2 về v1 (trx 101)"]

Read view là ảnh chụp dùng để đọc: danh sách transaction đang chạy, id nhỏ nhất trong đó và id sẽ cấp tiếp theo. Một phiên bản thấy được nếu DB_TRX_ID của nó đã commit trước khi read view được tạo (hoặc là của chính mình); nếu không, đi tiếp về bản cũ hơn.

Read view được tạo lúc nào (slide nói gọn là “snapshot tạo khi SELECT”):

  • REPEATABLE READ: ở câu consistent read đầu tiên của transaction, không phải lúc START TRANSACTION. Muốn tạo ngay thì dùng START TRANSACTION WITH CONSISTENT SNAPSHOT.
  • READ COMMITTED: tạo mới cho mỗi câu lệnh.
  • FOR UPDATE, FOR SHARE, UPDATE, DELETE: không dùng read view. Chúng là current read: đọc bản mới nhất đã commit và khoá nó.

Purge dọn phiên bản cũ. Khi không còn read view nào cần một bản cũ, luồng purge nền xoá bản ghi undo đó, và xoá hẳn các dòng đã DELETE (vốn chỉ được đánh dấu). Page undo nằm trong buffer pool, nên undo phình to thì chiếm chỗ của dữ liệu thật.

3.1. History list length: đo cái giá của snapshot cũ

Ý chính: history list length (HLL) là số bản ghi undo đã commit nhưng chưa purge được. HLL tăng đều không giảm gần như luôn nghĩa là có một transaction mở quá lâu, và snapshot cũ đọc chậm hẳn.

Xem HLL bằng SHOW ENGINE INNODB STATUS (dòng History list length) hoặc metric trx_rseg_history_len trong information_schema.innodb_metrics.

Thí nghiệm: session A mở transaction và đọc dòng hot một lần, rồi ngồi chờ. Session B chạy 20.000 câu UPDATE tự commit trên dòng đó.

Thời điểm HLL Một câu đọc dòng hot của A
Trước thí nghiệm 46 0,43 ms
Sau 20.000 update, A vẫn mở 20.001 26,8 ms (lần sau 10,5 ms)
Cùng lúc, A đọc FOR SHARE (bản mới nhất) 0,2 ms
A commit, vài giây sau 0

Một câu đọc theo khoá chính trên một dòng chậm đi khoảng 60 lần. Lý do: InnoDB phải đi ngược 20.000 bản ghi undo để dựng lại phiên bản mà read view của A được thấy.

Các bước thí nghiệm HLL và số đo đối chiếu từ sách
  1. Session A mở transaction, đọc dòng hot một lần (tạo read view), rồi ngồi chờ.
  2. Session B chạy 20.000 câu UPDATE hot SET v = v + 1, mỗi câu tự commit.
  3. Session A đọc lại dòng đó, theo snapshot và theo FOR SHARE.

Sách Efficient MySQL Performance (Daniel Nichter) có số đo cùng xu hướng: thời gian phản hồi của một truy vấn tăng từ khoảng 0,2 ms khi HLL bằng 0 lên khoảng 8,5 ms khi HLL khoảng 11.500.

Không có ngưỡng HLL chung cho mọi hệ thống. Thứ nên báo động là HLL tăng đều không giảm.

4. MVCC trong PostgreSQL: phiên bản nằm ngay trong bảng

Ý chính: PostgreSQL không có undo log. Mỗi UPDATE ghi một bản sao mới của dòng ngay trong bảng, bản cũ thành “tuple chết” chờ VACUUM dọn; snapshot cũ cũng chặn VACUUM y như chặn purge.

  • Tuple: một phiên bản của dòng nằm trong bảng.
  • xmin: transaction tạo ra tuple. xmax: transaction xoá hoặc thay thế nó (0 nếu còn sống).
  • Một transaction thấy tuple nếu xmin đã commit trước snapshot của nó và xmax thì chưa.
  • Dead tuple (tuple chết): bản cũ không còn ai cần. VACUUM dọn chúng, thường do autovacuum chạy nền.

Cùng kiểu sự cố như InnoDB: một transaction REPEATABLE READ mở và đứng yên, session khác update 5.000 lần. VACUUM VERBOSE báo 5.000 tuple chết “but not yet removable”. Tìm thủ phạm qua cột backend_xmin của pg_stat_activity (snapshot cũ nhất mà backend đang giữ):

SELECT pid, state, now() - xact_start AS xact_age, backend_xmin, left(query, 40)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start;
Thí nghiệm pageinspect và output VACUUM VERBOSE

Xem trực tiếp hai tuple của cùng một dòng bằng extension pageinspect:

SELECT xmin, xmax, ctid, v FROM hot;      -- 832 | 0 | (0,1) | 0
BEGIN; UPDATE hot SET v = 1 WHERE id = 1; COMMIT;   -- transaction 833
SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('hot', 0));
--  lp | t_xmin | t_xmax | t_ctid
--   1 |    832 |    833 | (0,2)    <- bản cũ, bị 833 thay thế, trỏ sang bản mới
--   2 |    833 |      0 | (0,2)    <- bản hiện tại

Output VACUUM VERBOSE khi transaction RR cũ còn mở, sau 5.000 update:

tuples: 0 removed, 5001 remain, 5000 are dead but not yet removable

Bài tập

Bài 1: Chạy lại kịch bản isolation level

Đề (diễn đạt lại): dựng lại các kịch bản dirty read, non-repeatable read, phantom read cho từng isolation level và quan sát kết quả, thay vì học thuộc bảng.

Lời giải Bài 1: điều phối hai transaction bằng Java

Mình mở rộng thêm lost update và write skew, chạy trên cả MySQL lẫn PostgreSQL. Điểm khó là điều phối hai transaction đúng thứ tự thời gian, và nhận ra khi một câu bị chặn mà không treo cả chương trình. Mỗi session có một executor một thread; mỗi bước gửi câu lệnh vào executor rồi chờ tối đa 400 ms.

static final class Session implements AutoCloseable {
    final Connection c;
    final ExecutorService ex = Executors.newSingleThreadExecutor();
    Future<?> last = CompletableFuture.completedFuture(null);

    Session(Connection c) { this.c = c; }

    /** Chạy một bước; quá 400 ms chưa xong thì coi như đang chờ lock. */
    String run(String sql) {
        last = ex.submit(() -> exec(sql));
        try { return String.valueOf(last.get(400, TimeUnit.MILLISECONDS)); }
        catch (TimeoutException e) { return "(blocked)"; }
        catch (ExecutionException e) { return "ERROR: " + e.getCause().getMessage(); }
        catch (InterruptedException e) { Thread.currentThread().interrupt(); return "interrupted"; }
    }

    /** Chờ bước bị chặn trước đó chạy xong (sau khi transaction kia commit/rollback). */
    String awaitLast() throws Exception { return String.valueOf(last.get(10, TimeUnit.SECONDS)); }

    Object exec(String sql) throws SQLException {
        try (Statement st = c.createStatement()) {
            if (!st.execute(sql)) return "ok(" + st.getUpdateCount() + ")";
            try (ResultSet rs = st.getResultSet()) {
                List<String> rows = new ArrayList<>();
                while (rs.next()) rows.add(rs.getString(1));
                return rows;
            }
        }
    }

    @Override public void close() throws Exception { c.rollback(); c.close(); ex.shutdownNow(); }
}

// Kịch bản lost update: cả hai đọc, mỗi bên tính trong ứng dụng rồi ghi
static String lostUpdate(Session t1, Session t2) throws Exception {
    int b1 = firstInt(t1.run("SELECT balance FROM acct WHERE id = 1"));
    int b2 = firstInt(t2.run("SELECT balance FROM acct WHERE id = 1"));
    String w2 = t2.run("UPDATE acct SET balance = " + (b2 + 50) + " WHERE id = 1");
    String c2 = t2.run("COMMIT");
    String w1 = t1.run("UPDATE acct SET balance = " + (b1 + 10) + " WHERE id = 1");
    String c1 = t1.run("COMMIT");
    if (w2.equals("(blocked)")) w2 = t2.awaitLast();
    return "T2 " + w2 + "/" + c2 + ", T1 " + w1 + "/" + c1;
}

Mỗi kịch bản chạy bốn lần, mỗi lần với hai connection mới đặt setTransactionIsolation(...) và setAutoCommit(false). Đặt innodb_lock_wait_timeout = 2 (MySQL) hoặc lock_timeout = '2s' (PostgreSQL) để câu bị chặn không chờ 50 giây. Kết quả là bảng ở mục 1.

Điều đáng học nhất nằm ở hai dòng cuối: lost update và write skew vượt qua RR của MySQL. Ở SERIALIZABLE, hai database “từ chối” theo hai cách khác nhau (deadlock và lỗi serialization), nên code phải retry cả transaction.

Bài 2: Cảnh báo HLL

Đề (tự đặt thêm): khi nào nên báo động về history list length?

Lời giải: thu trx_rseg_history_len từ information_schema.innodb_metrics mỗi 15 giây (mysqld_exporter của Prometheus có sẵn). Đặt hai cảnh báo:

  1. HLL vượt mức nền bình thường của hệ thống nhiều lần, trong 10 phút liền.
  2. Tuổi transaction già nhất trong innodb_trx vượt ngưỡng (ví dụ 5 phút với OLTP).

Cảnh báo 2 thường đến trước và chỉ thẳng thủ phạm. Cảnh báo 1 bắt cả trường hợp nhiều transaction vừa vừa chồng lên nhau.

Ghi nhớ

  • Giải thích được vì sao REPEATABLE READ của InnoDB để mất update còn của PostgreSQL thì báo lỗi.
  • Phân biệt được redo log và undo log, mỗi cái phục vụ chữ nào trong ACID.
  • Biết read view được tạo lúc nào ở RR và RC, và khi nào một câu đọc bỏ qua read view.
  • Giải thích được vì sao một transaction chỉ đọc mở lâu làm chậm cả database, và đo nó bằng gì.
  • So sánh được undo log của InnoDB với tuple trong bảng của PostgreSQL: rollback, bloat, VACUUM.

Câu hỏi phỏng vấn

Junior

Redo log và undo log khác nhau thế nào?

Redo log để làm lại cái đã commit (durability); undo log để quay lại phiên bản trước (atomicity và MVCC).

Redo log giúp sau crash làm lại những gì đã commit nhưng chưa kịp ghi vào file dữ liệu. Undo log dùng để rollback transaction chưa commit, và để dựng phiên bản cũ cho các câu đọc theo snapshot.

Vì sao database ghi log trước rồi mới ghi file dữ liệu?

Vì ghi tuần tự vào cuối log nhanh hơn nhiều so với ghi ngẫu nhiên từng page, mà vẫn đủ để không mất dữ liệu.

Commit chỉ cần log đã nằm an toàn trên đĩa; page dữ liệu được ghi sau theo lô. Khi crash, database đọc log để làm lại phần còn thiếu. Đó là write-ahead logging.

Mid

Read view của InnoDB được tạo khi nào ở REPEATABLE READ và READ COMMITTED?

RR: ở câu consistent read đầu tiên, dùng tới hết transaction. RC: một read view mới cho mỗi câu lệnh.

Ở RR, read view không tạo lúc BEGIN; muốn tạo ngay thì dùng START TRANSACTION WITH CONSISTENT SNAPSHOT. Đọc có khoá và UPDATE/DELETE không dùng read view mà đọc bản mới nhất (current read).

Vì sao ở MySQL REPEATABLE READ, hai câu đọc cùng một dòng trong một transaction có thể ra hai kết quả?

Vì một câu là SELECT thường (đọc snapshot), câu kia là SELECT ... FOR UPDATE/FOR SHARE (current read, đọc bản mới nhất đã commit).

RR chỉ hứa lặp lại được với các consistent read. Vì thế quyết định ghi phải dựa trên current read, hoặc nằm ngay trong điều kiện WHERE của câu UPDATE.

Senior

History list length tăng liên tục. Bạn điều tra thế nào?

Tìm read view cũ đang chặn purge: transaction già nhất trong information_schema.innodb_trx, rồi xử lý nó và sửa gốc ở ứng dụng.

  • Sắp innodb_trx theo trx_started, nối với processlist và performance_schema.events_statements_current để biết connection, host và câu cuối.
  • Connection leak hay phiên bỏ quên: kill (chấp nhận thời gian rollback), rồi sửa ứng dụng.
  • Không có transaction cũ: kiểm tra purge có theo kịp tải ghi không (innodb_purge_threads).
  • Sau đó thêm cảnh báo theo HLL và tuổi transaction.
So sánh MVCC của InnoDB và PostgreSQL, và hệ quả vận hành của mỗi cách.

InnoDB giữ bản cũ trong undo log, PostgreSQL giữ bản cũ ngay trong bảng; cả hai đều bị transaction mở lâu làm hại.

  • InnoDB: bản mới nhất trong clustered index, bản cũ dựng lại từ undo. Bảng không phình, nhưng snapshot cũ đọc chậm khi chuỗi undo dài, và rollback transaction lớn rất lâu.
  • PostgreSQL: tuple mới cho mỗi update, đánh dấu bằng xmin/xmax. Rollback tức thì, nhưng bảng và index phình, phụ thuộc VACUUM, cần freeze để tránh wraparound, nên tận dụng HOT update.
Bạn chọn SERIALIZABLE trên PostgreSQL cho một nghiệp vụ. Ứng dụng phải chuẩn bị gì?

Một vòng retry bọc ngoài cả transaction, vì SSI không chặn mà huỷ transaction với SQLState 40001, có khi ngay ở COMMIT.

  • Retry có giới hạn số lần và backoff ngẫu nhiên.
  • Thân transaction phải chạy lại được an toàn: không gửi email, không gọi API bên trong.
  • Giữ transaction ngắn để giảm xung đột. Khai báo READ ONLY cho transaction chỉ đọc (hoặc DEFERRABLE cho báo cáo dài) để giảm báo động giả.

Từ vựng tiếng Anh

Term Phát âm Nghĩa Ví dụ câu tiếng Anh dùng trong phỏng vấn
write-ahead logging /raɪt əˈhed ˈlɑː.ɡɪŋ/ ghi log trước khi ghi dữ liệu “Write-ahead logging turns random writes into sequential ones at commit time.”
redo log /ˈriː.duː lɑːɡ/ log làm lại “After a crash, InnoDB replays the redo log from the last checkpoint.”
undo log /ˈʌn.duː lɑːɡ/ log quay lại “Old row versions are rebuilt from the undo log.”
read view /riːd vjuː/ ảnh chụp dùng để đọc (InnoDB) “In repeatable read, the read view is created at the first consistent read.”
current read /ˈkɝː.ənt riːd/ đọc bản mới nhất, có khoá “SELECT FOR UPDATE is a current read, so it ignores the snapshot.”
purge /pɝːdʒ/ dọn phiên bản cũ “Purge can’t remove undo records that an old read view still needs.”
history list length /ˈhɪs.tər.i lɪst leŋθ/ số undo chưa dọn “A growing history list length usually means a transaction was left open.”
dead tuple /ded ˈtuː.pəl/ dòng chết (PostgreSQL) “VACUUM reported five thousand dead tuples that were not yet removable.”
bloat /bloʊt/ phình to “Frequent updates cause table bloat if autovacuum can’t keep up.”
serialization failure /ˌsɪr.i.ə.ləˈzeɪ.ʃən ˈfeɪl.jɚ/ lỗi tuần tự hoá “Under SSI, a serialization failure means you retry the whole transaction.”

Ghi chép dựa trên khoá Advanced Backend (Ronin Engineer), viết lại theo cách hiểu của mình.