MVCC và isolation: cùng lịch hai session, khác dữ liệu nhìn thấy

Câu hỏi bài này trả lời: session B đã cập nhật hoặc commit, vì sao A vẫn đọc giá trị cũ, đọc giá trị mới, bị chờ hoặc nhận lỗi khi lấy khóa?

Cần biết trước: SQL transaction, COMMIT/ROLLBACK và lab database. Bài chạy PostgreSQL 18.6 và MySQL 26.7.0/InnoDB bằng socket riêng trên macOS arm64, Python 3.14.4 stdlib. Docker/Linux của fixture chưa kiểm. Không suy kết quả giữa engine chỉ từ tên isolation.

MVCC giữ thông tin phiên bản để một phép đọc chọn dữ liệu phù hợp với snapshot. Isolation quy định snapshot và xung đột được xử lý thế nào; FOR UPDATE không phải một plain SELECT. PostgreSQL MVCC mô tả mô hình đa phiên bản, còn InnoDB multi-versioning dùng undo để dựng phiên bản cũ. Lab quan sát visibility/khóa, không đo heap/undo bytes hoặc khả năng ghi nhanh hơn của engine.

Một hàng giả, lịch có điểm dừng

Tạo thư mục trống, chép bốn file cách A của bài lab: lab-common.sh, lab-local.sh, seed-pg.sql, seed-mysql.sql. Bài thêm bảng wiki_lab.stock có (id=1, qty=10). Mỗi case reset về 10 khi các transaction trước đã kết thúc.

. ./lab-local.sh
lab_up
lab_seed
lab_whoami
lab_psql -c 'CREATE TABLE wiki_lab.stock (id integer PRIMARY KEY, qty integer NOT NULL); INSERT INTO wiki_lab.stock VALUES (1,10);'
lab_mysql -e 'CREATE TABLE wiki_lab.stock (id INT PRIMARY KEY, qty INT NOT NULL) ENGINE=InnoDB; INSERT INTO wiki_lab.stock VALUES (1,10);'

Lịch cho đọc thường:

Riêng MySQL Serializable, plain SELECT trong transaction tường minh lấy shared lock; bước B UPDATE sẽ chờ A. Controller xác nhận cạnh chờ qua data_lock_waits, A đọc lại rồi COMMIT để B tiếp tục. Không giả rằng B đã commit trong khi nó còn bị khóa.

Controller hai connection thật

Lưu isolation.py. Các query chỉ dùng ID cố định/bảng lab. Marker đi sau SQL, nên nhận được marker nghĩa là câu trước đã hoàn thành. Poll bảng lock-wait xác nhận chờ; timeout là giới hạn chống treo, không là bằng chứng SQL đã chạy.

import os
import queue
import signal
import subprocess
import threading
import time

CLIENT = {
    "pg": ". ./lab-local.sh; lab_psql -At",
    "mysql": ". ./lab-local.sh; lab_mysql --batch --raw --skip-column-names --unbuffered",
}


def query(engine, sql):
    result = subprocess.run(["bash", "-c", CLIENT[engine]], input=sql, text=True,
                            capture_output=True, timeout=15, check=True)
    return result.stdout.strip()


class Session:
    def __init__(self, engine, inspect_error=False):
        self.engine = engine
        client = CLIENT[engine]
        if engine == "pg":
            client += " -v VERBOSITY=verbose"
            if inspect_error:
                client += " -v ON_ERROR_STOP=0"
        self.process = subprocess.Popen(
            ["bash", "-c", client], stdin=subprocess.PIPE, stdout=subprocess.PIPE,
            stderr=subprocess.PIPE, text=True, bufsize=1, start_new_session=True)
        self.lines = queue.Queue()
        self.reader = threading.Thread(target=self.read, daemon=True)
        self.reader.start()
        identity = "SELECT pg_backend_pid();" if engine == "pg" else "SELECT CONNECTION_ID();"
        self.id = int(self.scalar(identity))
        if engine == "pg":
            self.mark("SET statement_timeout='10s';")
        else:
            self.mark("SET SESSION innodb_lock_wait_timeout=10;")

    def read(self):
        for line in self.process.stdout:
            self.lines.put(line.strip())
        self.lines.put(None)

    def send(self, sql):
        self.process.stdin.write(sql + "\n")
        self.process.stdin.flush()

    def collect(self):
        values = []
        while True:
            value = self.lines.get(timeout=10)
            assert value is not None, "client kết thúc trước marker"
            if value == "barrier":
                return values
            values.append(value)

    def ask(self, sql):
        self.send(sql + " SELECT 'barrier';")
        return self.collect()

    def mark(self, sql):
        assert self.ask(sql) == []

    def scalar(self, sql):
        values = self.ask(sql)
        assert len(values) == 1, values
        return values[0]

    def begin(self, level):
        if self.engine == "pg":
            self.mark(f"BEGIN ISOLATION LEVEL {level};")
            assert self.scalar("SHOW transaction_isolation;") == level.lower()
        else:
            self.mark(f"SET SESSION TRANSACTION ISOLATION LEVEL {level}; START TRANSACTION;")
            assert self.scalar("SELECT @@transaction_isolation;") == level.replace(" ", "-")

    def finish(self):
        self.process.stdin.close()
        code = self.process.wait(timeout=10)
        self.reader.join(timeout=2)
        return code, self.process.stderr.read()

    def close(self):
        if self.process.poll() is None:
            os.killpg(self.process.pid, signal.SIGTERM)
            try:
                self.process.wait(timeout=3)
            except subprocess.TimeoutExpired:
                os.killpg(self.process.pid, signal.SIGKILL)
                self.process.wait(timeout=3)


def reset(engine):
    query(engine, "UPDATE wiki_lab.stock SET qty=10 WHERE id=1;")


def value(session, lock=False):
    suffix = " FOR UPDATE" if lock else ""
    return int(session.scalar("SELECT qty FROM wiki_lab.stock WHERE id=1" + suffix + ";"))


def finish_ok(*sessions):
    for session in sessions:
        code, error = session.finish()
        assert code == 0 and not error, "SQL không được có lỗi ngoài dự kiến"


def wait_edge(writer, reader):
    sql = f"""SELECT count(*) FROM performance_schema.data_lock_waits w
JOIN performance_schema.threads r ON r.THREAD_ID=w.REQUESTING_THREAD_ID
JOIN performance_schema.threads b ON b.THREAD_ID=w.BLOCKING_THREAD_ID
WHERE r.PROCESSLIST_ID={writer.id} AND b.PROCESSLIST_ID={reader.id};"""
    deadline = time.monotonic() + 8
    while time.monotonic() < deadline:
        if int(query("mysql", sql)) > 0:
            return
        time.sleep(0.02)
    raise AssertionError("Không thấy cạnh writer chờ reader")


def ordinary(engine, level):
    reset(engine)
    a, b = Session(engine), Session(engine)
    try:
        a.begin(level)
        b.begin("READ COMMITTED")
        first = value(a)
        assert first == 10
        if engine == "mysql" and level == "SERIALIZABLE":
            b.send("UPDATE wiki_lab.stock SET qty=20 WHERE id=1; SELECT 'barrier';")
            wait_edge(b, a)
            again = value(a)
            assert again == 10
            a.mark("COMMIT;")
            assert b.collect() == []
            b.mark("COMMIT;")
            a.begin(level)
            fresh = value(a)
            assert fresh == 20
            a.mark("ROLLBACK;")
            print("mysql SERIALIZABLE first=10 writer_wait=yes second=10 new_tx=20")
        else:
            b.mark("UPDATE wiki_lab.stock SET qty=20 WHERE id=1;")
            during = value(a)
            b.mark("COMMIT;")
            after = value(a)
            expected_during = 20 if engine == "mysql" and level == "READ UNCOMMITTED" else 10
            expected_after = 20 if level in ("READ UNCOMMITTED", "READ COMMITTED") else 10
            assert (during, after) == (expected_during, expected_after)
            a.mark("ROLLBACK;")
            a.begin(level)
            fresh = value(a)
            assert fresh == 20
            a.mark("ROLLBACK;")
            print(f"{engine} {level} first={first} uncommitted={during} committed={after} new_tx={fresh}")
        finish_ok(a, b)
    finally:
        a.close()
        b.close()


def locking(engine, level):
    reset(engine)
    expect_error = engine == "pg" and level in ("REPEATABLE READ", "SERIALIZABLE")
    a, b = Session(engine, inspect_error=expect_error), Session(engine)
    try:
        a.begin(level)
        b.begin("READ COMMITTED")
        assert value(a) == 10
        b.mark("UPDATE wiki_lab.stock SET qty=20 WHERE id=1; COMMIT;")
        if expect_error:
            a.send("SELECT qty FROM wiki_lab.stock WHERE id=1 FOR UPDATE; ROLLBACK; SELECT 'barrier';")
            assert a.collect() == []
            code, error = a.finish()
            assert code == 0 and error.count("40001") == 1
            assert "could not serialize access due to concurrent update" in error
            print(f"pg {level} locking=40001 rollback=yes")
        else:
            current = value(a, lock=True)
            plain = value(a)
            assert current == 20
            assert plain == (10 if level == "REPEATABLE READ" else 20)
            a.mark("ROLLBACK;")
            finish_ok(a)
            print(f"{engine} {level} locking={current} plain_after={plain} rollback=yes")
        finish_ok(b)
        assert query(engine, "SELECT qty FROM wiki_lab.stock WHERE id=1;") == "20"
    finally:
        a.close()
        b.close()


for engine in ("pg", "mysql"):
    for level in ("READ UNCOMMITTED", "READ COMMITTED", "REPEATABLE READ", "SERIALIZABLE"):
        ordinary(engine, level)
for level in ("READ COMMITTED", "REPEATABLE READ", "SERIALIZABLE"):
    locking("pg", level)
locking("mysql", "REPEATABLE READ")
for engine in ("pg", "mysql"):
    reset(engine)
    assert query(engine, "SELECT qty FROM wiki_lab.stock WHERE id=1;") == "10"
print("reset qty=10; all cases passed")

Chạy từ thư mục chứa file lab. lab_target kiểm socket/server riêng trước controller; khi có lỗi vẫn chạy khối cleanup ở cuối bài.

. ./lab-local.sh
lab_target
python3 isolation.py

Expected theo lịch trên; cần đếm và so lại khi chạy trên môi trường khác:

pg READ UNCOMMITTED first=10 uncommitted=10 committed=20 new_tx=20
pg READ COMMITTED first=10 uncommitted=10 committed=20 new_tx=20
pg REPEATABLE READ first=10 uncommitted=10 committed=10 new_tx=20
pg SERIALIZABLE first=10 uncommitted=10 committed=10 new_tx=20
mysql READ UNCOMMITTED first=10 uncommitted=20 committed=20 new_tx=20
mysql READ COMMITTED first=10 uncommitted=10 committed=20 new_tx=20
mysql REPEATABLE READ first=10 uncommitted=10 committed=10 new_tx=20
mysql SERIALIZABLE first=10 writer_wait=yes second=10 new_tx=20
pg READ COMMITTED locking=20 plain_after=20 rollback=yes
pg REPEATABLE READ locking=40001 rollback=yes
pg SERIALIZABLE locking=40001 rollback=yes
mysql REPEATABLE READ locking=20 plain_after=10 rollback=yes
reset qty=10; all cases passed

Đọc output theo engine

PostgreSQL: RU có hành vi như RC, nên không thấy B chưa commit. RC đọc snapshot theo statement; RR giữ snapshot nên sau B commit vẫn thấy 10. Serializable đọc thường cũng thấy 10 ở lịch này. Nó thêm kiểm dependency để các transaction commit có thể tương đương một thứ tự tuần tự; ổn định một hàng chưa chứng minh ứng dụng giữ được mọi invariant. Xung đột có thể trả SQLSTATE 40001, cần retry toàn transaction. Manual isolation PG18 mô tả snapshot, SSI và retry.

InnoDB: RU đã đọc 20 trước COMMIT (dirty read); nếu B rollback thì ứng dụng từng dùng dữ liệu không tồn tại ở kết quả commit. RC đọc lại thấy 20 sau commit; RR giữ read view từ consistent read đầu tiên nên vẫn 10. Serializable trong transaction tường minh khác: SELECT của A giữ shared lock, writer B chưa UPDATE xong. writer_wait=yes đến từ bảng lock-wait, không phải thời gian ngủ. Manual isolation MySQL26.7 nêu khác biệt này.

Ở locking case, A đọc thường 10, B đã commit 20 rồi A mới FOR UPDATE. PG RC khóa/đọc 20; PG RR/Serializable không thể khóa phiên bản đã bị thay sau snapshot, nhận 40001 rồi rollback. Controller chỉ tắt ON_ERROR_STOP cho hai client quan sát lỗi này, để gửi ROLLBACK và marker; nó kiểm stderr đúng một 40001, không bỏ lỗi rồi báo query thành công. Ứng dụng thật phải dừng luồng và retry theo chính sách, không dùng chế độ tiếp tục SQL sau lỗi của lab.

MySQL RR locking read thấy 20, nhưng plain SELECT sau đó vẫn đọc snapshot 10 vì A chưa tự ghi gì. Trộn hai kiểu đọc có thể khiến cùng transaction thấy hai trạng thái khó hiểu. Consistent reads và locking reads phân biệt read view với đọc lấy khóa; đây là quan sát cơ chế, không khuyến nghị trộn chúng cho nghiệp vụ.

Transaction dài, reset và giới hạn

• BEGIN và “đã tạo snapshot dữ liệu” không luôn cùng thời điểm; lịch này chủ động đọc bảng trước khi B cập nhật. PG RR lấy snapshot ở câu cần snapshot đầu tiên; InnoDB RR ở consistent read đầu tiên. Không dùng thời điểm click BEGIN để suy visibility khi chưa đọc.
• Một transaction thấy thay đổi của chính nó; bảng output không có A UPDATE nên chưa thử trường hợp đó. Autocommit làm mỗi statement thành transaction riêng; không thể so hai SELECT cách nhau với lịch RR tường minh ở đây.
• “Reader không chặn writer” chỉ đúng phạm vi đọc MVCC thích hợp. Locking read, InnoDB Serializable và khóa DDL có thể chặn. Snapshot lâu còn trì hoãn dọn phiên bản cũ; bài chưa đo bloat/VACUUM/purge.
• Lab chưa thử phantom/range lock, write skew, SSI dependency cycle hoặc distributed transaction. Không đổi một bảng minh họa thành bảng bảo đảm chung cho mọi DB. Bài tập mở: thêm một insert giữa hai lần đếm để thử phantom, hoặc hai hàng invariant để thử write skew; cần thiết kế lịch và expected riêng trước khi chạy.
• Timeout chỉ chống treo; nó không thay cho quan sát lock. Lỗi schema/socket/isolation phải xử lý trước, không sửa địa chỉ client sang database thật.

Controller kết thúc mỗi case bằng COMMIT/ROLLBACK, kiểm trạng thái cuối và reset qty=10. Chạy lại toàn controller từ reset:

. ./lab-local.sh
lab_target
python3 isolation.py

. ./lab-local.sh
lab_clean
echo cleaned

Học tiếp deadlock/retry, N+1 và split query, EXPLAIN ANALYZE. Nguồn manual đúng phiên bản đọc ngày 2026-10-03; dữ liệu và controller do bài tự dựng.