Deadlock: hai transaction giữ khóa theo thứ tự ngược nhau

Câu hỏi bài này trả lời: một query đang chờ khóa có phải deadlock không, transaction nào bị hủy và retry ở đâu để không xử lý một yêu cầu hai lần?

Cần biết trước: BEGIN/COMMIT/ROLLBACK và lab database. Bài đã chạy MySQL 26.7.0/InnoDB trên macOS arm64, Python 3.14.4, dữ liệu giả. Không có phép thử PostgreSQL, Docker/Linux hay Podman ở bài này.

Lịch làm việc tạo vòng chờ

Hai hàng có khóa chính 1 và 2. Mỗi yêu cầu tăng cả hai balance lên 1, ghi mã yêu cầu vào bảng riêng trong cùng transaction. Hai yêu cầu hợp lệ A/B cần đưa mỗi balance từ 100 lên 102, với đúng hai mã đã commit.

Bước	Session A	Session B	Khóa và trạng thái
A1	BEGIN, ghi mã A, UPDATE hàng 1	Chưa bắt đầu	A giữ khóa hàng 1, chưa commit
B1	Dừng tại đây	BEGIN, ghi mã B, UPDATE hàng 2	B giữ khóa hàng 2, chưa commit
A2	UPDATE hàng 2, câu lệnh chưa trả về	Dừng tại đây	A chờ B; mới có một cạnh chờ
B2	Vẫn chờ	UPDATE hàng 1	B cần khóa của A: vòng A → B → A

Ở A2, B vẫn có thể commit để nhả khóa; đó là chờ bình thường. B2 mới đóng vòng. Khi phát hiện vòng, InnoDB hủy một transaction để transaction còn lại đi tiếp. Không gán trước victim là A hoặc B; lựa chọn đó không phải hợp đồng của ứng dụng.

Tạo thư mục trống và chép bốn file của cách A trong bài lab: lab-common.sh, lab-local.sh, seed-pg.sql, seed-mysql.sql. Server dùng socket riêng, không lấy địa chỉ database từ cấu hình máy.

. ./lab-local.sh
lab_up
lab_seed
lab_whoami
lab_mysql -N -e 'SELECT @@version, @@innodb_deadlock_detect, @@innodb_rollback_on_timeout'

Lab dưới đòi deadlock detection bật và rollback-on-timeout tắt, là cấu hình được kiểm ở lượt chạy này. Nếu khác, dừng để hiểu cấu hình thay vì tự đổi biến global trên server.

CREATE TABLE IF NOT EXISTS wiki_lab.accounts (
  id INT PRIMARY KEY,
  balance INT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS wiki_lab.requests (
  id CHAR(1) PRIMARY KEY
) ENGINE=InnoDB;
DELETE FROM wiki_lab.requests;
DELETE FROM wiki_lab.accounts;
INSERT INTO wiki_lab.accounts VALUES (1, 100), (2, 100);

Nếu làm bằng hai terminal, mở cùng thư mục, . ./lab-local.sh, rồi lab_mysql wiki_lab ở mỗi terminal. Sau khi nạp setup, thực hiện từng ô của lịch, dừng đúng A1/B1 và chỉ gõ B2 sau khi A2 đang chờ:

. ./lab-local.sh
lab_whoami
lab_mysql < setup.sql

-- A1, terminal A
BEGIN;
INSERT INTO wiki_lab.requests VALUES ('A');
UPDATE wiki_lab.accounts SET balance = balance + 1 WHERE id = 1;
-- B1, terminal B
BEGIN;
INSERT INTO wiki_lab.requests VALUES ('B');
UPDATE wiki_lab.accounts SET balance = balance + 1 WHERE id = 2;
-- A2, terminal A: chờ ở đây
UPDATE wiki_lab.accounts SET balance = balance + 1 WHERE id = 2;
-- B2, terminal B
UPDATE wiki_lab.accounts SET balance = balance + 1 WHERE id = 1;

Chỉ COMMIT ở session có UPDATE thứ hai thành công. Session nhận 1213 đã mất toàn transaction; đừng tiếp tục các bước còn lại như thể thay đổi đầu tiên còn tồn tại. Sau khi kiểm xong, ROLLBACK cả hai session và thoát trước khi reset.

Chạy lịch có điểm đồng bộ và assertion

Để không phụ thuộc tốc độ gõ, controller sau mở hai MySQL client thật. SELECT marker xác nhận A1/B1 đã xong; bảng performance_schema.data_lock_waits xác nhận A2 đang chờ B trước khi gửi B2. Thời hạn 10 giây chỉ để phát hiện lab treo, không dùng sleep làm bằng chứng đã lấy khóa.

Lưu thành deadlock.py. File chỉ gọi client socket của lab, dùng mã A/B cố định và cất báo cáo InnoDB trong thư mục thực hành. Không dùng controller này làm thư viện retry của ứng dụng.

import os
import queue
import signal
import subprocess
import threading
import time
from pathlib import Path

CLIENT = '. ./lab-local.sh; lab_mysql --batch --raw --skip-column-names --unbuffered'

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

class Session:
    def __init__(self, inspect_error=False):
        client = CLIENT + (' --force' if inspect_error else '')
        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()
        self.send('SELECT CONNECTION_ID();')
        self.id = int(self.line())

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

    def line(self):
        value = self.lines.get(timeout=10)
        assert value is not None, 'client kết thúc trước marker'
        return value

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

    def mark(self, sql, label):
        self.send(sql + f" SELECT '{label}';")
        assert self.line() == label

    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 wait_edge(waiter, owner):
    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
    JOIN performance_schema.data_locks l
      ON l.ENGINE = w.ENGINE AND l.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
    WHERE r.PROCESSLIST_ID = {waiter.id} AND b.PROCESSLIST_ID = {owner.id}
      AND l.OBJECT_SCHEMA = 'wiki_lab' AND l.OBJECT_NAME = 'accounts';
    '''
    deadline = time.monotonic() + 10
    while time.monotonic() < deadline:
        if int(query(sql)) > 0:
            return
        time.sleep(0.02)
    raise AssertionError('không thấy cạnh chờ của hai session lab')

def reset():
    query(Path('setup.sql').read_text())

def balances(value, requests):
    assert query('SELECT balance FROM wiki_lab.accounts ORDER BY id;') == f'{value}\n{value}'
    assert int(query('SELECT COUNT(*) FROM wiki_lab.requests;')) == requests

def retry(request):
    assert request in ('A', 'B')
    sql = f'''BEGIN;
    INSERT INTO wiki_lab.requests VALUES ('{request}');
    UPDATE wiki_lab.accounts SET balance = balance + 1 WHERE id = 1;
    UPDATE wiki_lab.accounts SET balance = balance + 1 WHERE id = 2;
    COMMIT;'''
    for attempt in range(3):
        result = subprocess.run(['bash', '-c', CLIENT], input=sql, text=True,
                                capture_output=True, timeout=15)
        if result.returncode == 0:
            return True
        if 'ERROR 1062 ' in result.stderr:
            # Chỉ INSERT mã request có thể trùng trong transaction này.
            return False
        if 'ERROR 1213 (40001)' not in result.stderr or attempt == 2:
            raise RuntimeError(result.stderr)
        time.sleep(0.02 * (attempt + 1))
    raise AssertionError('retry vượt giới hạn')

def cycle(round_number):
    reset()
    a, b = Session(inspect_error=True), Session(inspect_error=True)
    try:
        a.mark("BEGIN; INSERT INTO wiki_lab.requests VALUES ('A'); "
               'UPDATE wiki_lab.accounts SET balance=balance+1 WHERE id=1;', 'A1')
        b.mark("BEGIN; INSERT INTO wiki_lab.requests VALUES ('B'); "
               'UPDATE wiki_lab.accounts SET balance=balance+1 WHERE id=2;', 'B1')
        a.send("UPDATE wiki_lab.accounts SET balance=balance+1 WHERE id=2; SELECT 'A2';")
        wait_edge(a, b)
        b.send("UPDATE wiki_lab.accounts SET balance=balance+1 WHERE id=1; SELECT 'B2';")
        assert a.line() == 'A2' and b.line() == 'B2'
        active = {}
        for name, session in (('A', a), ('B', b)):
            session.send(f"SELECT COUNT(*) FROM wiki_lab.requests WHERE id='{name}';")
            active[name] = int(session.line())
        assert sorted(active.values()) == [0, 1]
        rolled_back = a if active['A'] == 0 else b
        rolled_back.send('SELECT GROUP_CONCAT(balance ORDER BY id) FROM wiki_lab.accounts;')
        assert rolled_back.line() == '100,100'
        for name, session in (('A', a), ('B', b)):
            session.mark('COMMIT;' if active[name] == 1 else 'ROLLBACK;', 'closed')
        results = {'A': a.finish(), 'B': b.finish()}
        victims = [name for name, (code, error) in results.items()
                   if 'ERROR 1213 (40001)' in error]
        assert len(victims) == 1
        victim = victims[0]
        winner = 'B' if victim == 'A' else 'A'
        assert active[victim] == 0 and active[winner] == 1
        assert results[winner] == (0, '')
        assert all('ERROR 1205 ' not in error for _, error in results.values())
        balances(101, 1)
        assert query('SELECT id FROM wiki_lab.requests;') == winner
        status = query('SHOW ENGINE INNODB STATUS;')
        Path(f'innodb-status-{round_number}.txt').write_text(status)
        assert 'LATEST DETECTED DEADLOCK' in status
        assert 'accounts' in status and 'PRIMARY' in status
        assert 'WE ROLL BACK TRANSACTION' in status
        print(f'vòng {round_number}: ERROR 1213 (40001), đúng một victim; balance=101, requests=1')
        print('báo cáo InnoDB có vòng chờ trên accounts/PRIMARY và transaction bị rollback')
        assert retry(victim)
        assert not retry(winner)
        assert not retry(victim)
        balances(102, 2)
        print('retry toàn transaction và gửi trùng: balance=102, requests=2')
    finally:
        a.close()
        b.close()

def ordered():
    reset()
    a, b = Session(), Session()
    try:
        a.mark("BEGIN; INSERT INTO wiki_lab.requests VALUES ('A'); "
               'UPDATE wiki_lab.accounts SET balance=balance+1 WHERE id=1;', 'A1')
        b.send("BEGIN; INSERT INTO wiki_lab.requests VALUES ('B'); "
               'UPDATE wiki_lab.accounts SET balance=balance+1 WHERE id=1;')
        wait_edge(b, a)
        a.send('UPDATE wiki_lab.accounts SET balance=balance+1 WHERE id=2; COMMIT;')
        assert a.finish() == (0, '')
        b.send('UPDATE wiki_lab.accounts SET balance=balance+1 WHERE id=2; COMMIT;')
        assert b.finish() == (0, '')
        balances(102, 2)
        print('cùng thứ tự 1 rồi 2: có chờ, hai commit, không deadlock')
    finally:
        a.close()
        b.close()

def timeout():
    reset()
    owner, waiter = Session(), Session(inspect_error=True)
    try:
        owner.mark('BEGIN; UPDATE wiki_lab.accounts SET balance=balance+1 WHERE id=1;', 'held')
        waiter.mark('SET SESSION innodb_lock_wait_timeout=1; BEGIN; '
                    'UPDATE wiki_lab.accounts SET balance=balance+7 WHERE id=2;', 'W1')
        waiter.send("UPDATE wiki_lab.accounts SET balance=balance+1 WHERE id=1; "
                    "SELECT 'timeout_finished';")
        assert waiter.line() == 'timeout_finished'
        waiter.send('SELECT balance FROM wiki_lab.accounts WHERE id=2;')
        assert waiter.line() == '107'
        assert query('SELECT balance FROM wiki_lab.accounts WHERE id=2;') == '100'
        waiter.mark('ROLLBACK;', 'waiter_rolled_back')
        code, error = waiter.finish()
        assert 'ERROR 1205 ' in error and 'ERROR 1213 ' not in error
        owner.mark('ROLLBACK;', 'rolled_back')
        assert owner.finish() == (0, '')
        balances(100, 0)
        print('timeout ERROR 1205: waiter còn thấy 107; explicit rollback trả về 100')
    finally:
        owner.close()
        waiter.close()

assert query('SELECT @@innodb_deadlock_detect, @@innodb_rollback_on_timeout;') == '1\t0'
cycle(1)
cycle(2)
ordered()
timeout()
reset()
balances(100, 0)
print('reset và kiểm dữ liệu đạt')

Controller dùng --force ở phép thử lỗi để giữ connection và đọc trạng thái ngay sau lỗi: victim không còn mã yêu cầu hay balance đã tăng; waiter timeout vẫn thấy thay đổi trước đó. Chỉ winner được COMMIT; các connection lỗi có ROLLBACK tường minh. Đây là cách quan sát trong lab, không phải cách tiếp tục script nghiệp vụ sau lỗi.

Hàm retry chạy client batch không có --force: lỗi làm dừng script và đóng connection, nên không chạy tiếp COMMIT; connection đóng cũng giải phóng transaction còn mở. Nó chỉ retry 1213, tối đa ba lần, bắt đầu lại từ BEGIN bằng cùng mã yêu cầu. Mã trùng 1062 được coi là đã xử lý chỉ vì ở fixture này duy nhất INSERT mã yêu cầu có thể sinh lỗi đó; ứng dụng nhiều unique constraint phải nhận diện đúng constraint, không bắt mọi 1062 như thành công.

python3 deadlock.py

vòng 1: ERROR 1213 (40001), đúng một victim; balance=101, requests=1
báo cáo InnoDB có vòng chờ trên accounts/PRIMARY và transaction bị rollback
retry toàn transaction và gửi trùng: balance=102, requests=2
vòng 2: ERROR 1213 (40001), đúng một victim; balance=101, requests=1
báo cáo InnoDB có vòng chờ trên accounts/PRIMARY và transaction bị rollback
retry toàn transaction và gửi trùng: balance=102, requests=2
cùng thứ tự 1 rồi 2: có chờ, hai commit, không deadlock
timeout ERROR 1205: waiter còn thấy 107; explicit rollback trả về 100
reset và kiểm dữ liệu đạt

Đọc lỗi, báo cáo và trạng thái transaction

1213/40001 là deadlock ở phép thử này. Trước khi đóng connection, victim đọc được hai balance 100 và không thấy mã yêu cầu của mình: thay đổi đầu tiên cũng đã rollback. Sau COMMIT của winner, hai balance bằng 101 và chỉ mã winner tồn tại. Retry victim đưa balance lên 102; gửi lại hai mã đều không tăng balance nữa.

Mở innodb-status-1.txt trong thư mục lab để tìm LATEST DETECTED DEADLOCK, các transaction, khóa đang giữ/đang chờ và dòng victim. SHOW ENGINE INNODB STATUS chỉ chứa deadlock gần nhất; không thay thế lịch sử mọi deadlock. Trên hệ thống thật, báo cáo có thể chứa SQL và định danh vận hành: chọn đoạn cần thiết và khử định danh trước khi chia sẻ.

Tránh cố chụp cả hai cạnh trong data_lock_waits sau B2: detector có thể giải vòng trước khi query quan sát chạy. Controller xác nhận cạnh A → B trước B2, rồi dùng lỗi và báo cáo để chứng minh vòng đã hình thành.

Chờ khóa, deadlock và timeout có kết quả khác nhau

Trường hợp	Bằng chứng lab	Trạng thái cần xử lý
Chờ có thể giải	B đợi A ở bản sửa, A commit rồi B tiếp tục	Chưa phải lỗi; giới hạn thời gian chờ theo yêu cầu
Deadlock	`1213`, báo cáo vòng, một victim	InnoDB rollback toàn transaction; retry toàn đơn vị công việc
Lock timeout	`1205` khi owner vẫn giữ hàng 1	Với rollback-on-timeout tắt, chỉ statement lỗi được rollback; transaction trước đó có thể còn

Phép thử timeout cho waiter sửa hàng 2 thành 107 trước khi chờ hàng 1. Sau 1205, chính connection đó vẫn đọc được 107, trong khi connection ngoài thấy 100. ROLLBACK waiter rồi owner đưa mọi hàng về 100. Đây là bằng chứng thay đổi trước lỗi còn trong transaction; chủ động ROLLBACK trước khi trả connection vào pool hoặc retry toàn transaction. InnoDB Error Handling mô tả khác biệt rollback này.

Sửa thứ tự và đặt retry ở biên nghiệp vụ

Cho cả A/B UPDATE hàng 1 rồi hàng 2. Trong lịch đã kiểm, B chờ hàng 1, không giữ hàng 2 để chặn A; A hoàn thành rồi B tiếp tục. Phép thử này loại vòng hai hàng, không chứng minh ứng dụng không thể deadlock ở unique index, foreign key hay một đường code khác. Rà thứ tự nhất quán trên toàn đơn vị nghiệp vụ; giữ transaction ngắn.

Với ứng dụng dùng driver, đặt vòng retry bao ngoài BEGIN → ghi mã yêu cầu → thay đổi → COMMIT. Khi gặp 1213, rollback/loại connection lỗi theo hợp đồng driver, backoff có jitter với số lần và deadline hữu hạn, rồi chạy lại toàn transaction. Đọc lại dữ liệu trong lượt mới. Khi hết giới hạn, trả lỗi có thể điều tra; không lặp vô hạn hoặc retry mọi lỗi SQL.

Khóa mã yêu cầu phải được ghi atomically cùng thay đổi. Nếu commit thành công nhưng phản hồi mất, gửi lại cùng mã sẽ không tăng balance lần nữa. Ví dụ chỉ bảo vệ hai UPDATE trong database; gọi API thanh toán/gửi email bên ngoài transaction cần cơ chế riêng như outbox và khóa idempotency ở nơi nhận.

Dọn và thử biến thể

. ./lab-local.sh
lab_clean
test ! -e lab.env

Đổi cả hai session sang cùng thứ tự và xác nhận có cạnh chờ nhưng không có 1213.
Cho một đường code vẫn lấy khóa theo thứ tự cũ: kiểm vòng trở lại, đừng chỉ sửa một caller.
Đổi số hàng và thứ tự thao tác, vẽ lại cạnh chờ rồi kiểm báo cáo. Không suy rằng chỉ có hai transaction mới tạo được vòng.

Không thấy deadlock: thường đã COMMIT quá sớm, chạy hai câu trong hai connection khác nhau hoặc detector đang tắt. Không thấy cạnh chờ: xác nhận đúng session/socket lab, transaction còn mở và quyền đọc Performance Schema. Lỗi 1205 trước B2: lịch chưa được phối hợp kịp; tăng thời gian hợp lý trong lab, không gọi đó là deadlock.

Học tiếp và nguồn

Lab database: namespace, socket, reset và cleanup.
Đọc EXPLAIN ANALYZE: query tìm nhiều hàng có thể làm phạm vi công việc lớn; kết quả PostgreSQL không thay phép đo lock MySQL.
MySQL 26.7, How to Minimize and Handle Deadlocks: báo cáo, thứ tự thao tác và retry.
MySQL 26.7, InnoDB Error Handling: phạm vi rollback theo loại lỗi.
MySQL 26.7, data_lock_waits: quan hệ bên chờ và bên giữ khóa.

Nguồn đọc ngày 2026-10-03. Lab kiểm cơ chế và số lần xử lý trên dữ liệu giả, không đo tải production hay hứa retry sẽ luôn thành công.

Keyboard shortcuts

Hải Nghĩa's Wiki