Thiếu một cái index, cả hệ thống tắc và đường đi tới deadlock

01Một câu DELETE vô hại

Ticket ban đầu chỉ ghi checkout chậm. Rồi nó đổi thành checkout chết. Khi mở database (một instance Oracle) lên xem, thủ phạm là một dòng mà script back-office đã chạy một phút trước: DELETE FROM orders WHERE id = 42. Một dòng, một primary key, câu lệnh vô hại nhất mà database biết. Vậy mà hàng chục session chẳng liên quan gì đang xếp hàng sau nó, không session nào đụng tới đơn 42, và hàng vẫn dài thêm.

Câu chuyện này là bản ghép: đó là mẫu hình mình gặp lần đầu trong ghi chú của một anh DBA mình từng làm cùng (nơi dùng Oracle), rồi gặp lại khi đọc chương 7 của Designing Data-Intensive Applications (Kleppmann, bản 1). Hai nguồn mô tả cùng một cơ chế từ hai đầu. Ghi chú của anh bắt đầu từ triệu chứng và đi tới nguyên nhân; cuốn sách bắt đầu từ lý thuyết về lock. Đặt cạnh nhau, câu rơi ra là thế này: lock của transaction và lock sinh ra do thiếu index là cùng một cơ chế. Bài này nói về câu đó, và về chuyện gì xảy ra khi những cái lock như vậy bắt đầu thắt vào nhau thành một vòng.

thứ câu DELETE định đụng tới

1 row

cột khóa ngoại Postgres tự index giùm

0

pha trong two-phase locking

2

SQLSTATE của deadlock ở Postgres

40P01

02Kho hàng có và không có catalogue

Để thấy vì sao lock có thể rộng hơn nhiều so với dòng bạn định khóa, hãy rời database một lát và hình dung một nhà kho. Một khách nhờ bạn niêm phong tất cả thùng của họ. Nếu kho có catalogue, bạn tra tên khách, đọc ra dãy và kệ, đi thẳng tới đó và khóa ba cái thùng ấy. Phần còn lại của kho vẫn chạy bình thường. Nếu không có catalogue, bạn phải đi hết mọi dãy và đọc từng nhãn để tìm thùng của khách, và để chắc không thùng nào bị dời đi trong lúc mình tìm, bạn xích cổng chính cho tới khi xong. Mọi người khuân vác đẩy xe đều phải đứng ngoài, kể cả những người chẳng liên quan gì tới vị khách của bạn.

Index trong database chính là cuốn catalogue đó. Nó làm hai việc, và người ta thường chỉ nhớ việc thứ nhất: làm tìm kiếm nhanh, và thu hẹp tập dòng mà engine phải đi qua, nên thu hẹp luôn tập dòng nó khóa. Database phải chắc rằng thứ nó vừa đọc vẫn còn đúng lúc nó ghi, nên nó khóa những gì nó đã nhìn. Nhìn càng nhiều, khóa càng nhiều.

Đây không phải chuyện truyền miệng. Ở chương về transaction, Kleppmann mô tả index-range lock: vì predicate lock chính xác quá đắt, database xấp xỉ nó bằng cách gắn lock vào một khoảng của index; và theo cách mình đọc đoạn đó, nếu không có index phù hợp để treo khoảng ấy lên, database có thể lùi về shared lock trên cả bảng. Tài liệu của InnoDB nói điều tương tự bằng lời của nó: ở mức REPEATABLE READ mặc định, câu lệnh không có index dùng được và phải quét bảng sẽ khóa mọi dòng nó đã quét. Index chính là đơn vị quyết định độ mịn của lock.

03Ca điển hình: orders và order_items

Hết chuyện nhà kho. Đây là schema đứng sau câu chuyện. Bảng cha là đơn hàng, bảng con là các dòng hàng, nối với nhau bằng khóa ngoại. Khóa ngoại đã khai báo; index trên nó thì chưa. Toàn bộ lỗi nằm ở đó.

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id bigint      NOT NULL,
  status      text        NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id       bigint PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders(id),   -- đã khai báo khóa ngoại...
  sku      text   NOT NULL,
  qty      int    NOT NULL
);
-- ...nhưng không có gì index order_items(order_id). Ràng buộc FK
-- đảm bảo toàn vẹn dữ liệu; nó không hứa có đường nhanh để tìm các dòng con.

-- cách sửa, một dòng (CONCURRENTLY để không chặn ghi trên Postgres):
CREATE INDEX CONCURRENTLY order_items_order_id_idx ON order_items (order_id);

Giờ chạy DELETE FROM orders WHERE id = 42. Trước khi được xóa dòng cha, database phải chứng minh không còn dòng con nào trỏ tới nó. Chứng minh đó là một query: có dòng nào trong order_items có order_id = 42 không? Có index trên order_id, câu trả lời đến từ một lần dò. Không có, engine phải đi hết bảng con mới nói được không, và việc nó làm trong lúc đi hết còn tùy engine.

Mỗi engine thật sự làm gì

Chỗ này cần chính xác, vì ba engine ta hay dùng cư xử khá khác nhau. Nguyên lý (index ít hơn thì quét nhiều hơn, khóa nhiều hơn hoặc giữ lâu hơn) thì chung. Nhưng triệu chứng thì tùy engine.

Oracle là bức tranh rõ nhất: khi khóa ngoại không có index, DELETE hoặc update khóa chính ở bảng cha lấy một share lock trên cả bảng con, và mọi writer trên order_items (mỗi checkout thêm một dòng hàng) đều phải chờ. Đó là “TM contention” trong ghi chú của anh DBA. Ở các phiên bản Oracle hiện đại, lock được nhả ngay khi câu lệnh kết thúc, nên nó ẩn mình khi test vì rất ngắn. Nhưng dưới tải, “ngắn” thành một hàng đợi, và hàng đợi checkout là một sự cố.

Ở PostgreSQL không có chuyện leo thang lên table lock, nên lỗi trông khác và tới muộn hơn: DELETE ở bảng cha chạy bước quét bảng con ngay trong transaction của bạn, nên với bảng con lớn, một transaction đáng ra kéo dài một mili giây có thể kéo dài vài giây, và mọi thứ nó đang giữ (dòng cha, những dòng khác nó đã update) bị giữ theo. Dẫn tới nguy cơ thật sự: lock giữ càng lâu thì càng dễ gặp nhau thành một vòng.

04Tìm những khóa ngoại chưa ai index

Hố này đào rất dễ. Cách sửa chỉ một dòng; phần khó là biết áp dụng ở đâu, vì không có gì hỏng cho tới khi bảng lớn và traffic thật. Trên PostgreSQL, catalogue cho bạn biết: liệt kê mọi ràng buộc khóa ngoại và giữ lại những cái không có index nào bắt đầu bằng đúng các cột đó.

-- khóa ngoại mà các cột của nó không phải là cột đứng đầu của bất kỳ index nào
SELECT c.conrelid::regclass  AS child_table,
       c.conname             AS fk_name,
       pg_get_constraintdef(c.oid) AS definition
FROM   pg_constraint c
WHERE  c.contype = 'f'
  AND  NOT EXISTS (
         SELECT 1
         FROM   pg_index i
         WHERE  i.indrelid = c.conrelid
           AND  (string_to_array(i.indkey::text, ' ')::int2[])[1:cardinality(c.conkey)]
                  @> c.conkey
           AND  (string_to_array(i.indkey::text, ' ')::int2[])[1:cardinality(c.conkey)]
                  <@ c.conkey
       )
ORDER  BY 1, 2;

Chạy nó trên một database bạn thừa kế rồi chuẩn bị tinh thần khó chịu. Một dòng kết quả là nghi phạm, chưa phải bản án: một bảng lookup nhỏ không bao giờ bị xóa thì không cần index, và mỗi index bạn thêm đều đánh thuế mọi lệnh ghi. Cần sửa trước là con của những bảng có parent delete hoặc update khóa, và những bảng con lớn, ghi nhiều, đúng như order_items.

05Two-phase locking không phải khóa hai lần

Lock thì chồng chất; nửa còn lại của câu chuyện là luật quyết định khi nào chúng được nhả. Mình phải thú nhận một chỗ hiểu sai ở đây. Rất lâu mình đọc “two-phase locking” là khóa, rồi khóa thêm lần nữa, kiểu ổ khóa kiểm tra hai lần. Hoàn toàn không phải. Hai pha là hai đoạn trong đời của một transaction: đầu tiên là pha growing, transaction chỉ xin thêm lock khi chạm vào các dòng; sau đó là pha shrinking, chỉ nhả lock và không xin thêm cái nào.

Vì sao cần luật này? Vì nó là cách đổi lock lấy serializability: nếu một transaction không nhả lock nào trước khi đã xin xong cái cuối cùng, không transaction khác lọt được vào giữa các lần đọc ghi của nó. Cái giá, như Kleppmann nhấn mạnh, là khả năng chạy đồng thời: dưới 2PL, reader chặn writer và writer chặn reader, còn thời gian phản hồi ở các percentile cao có thể thất thường (bài tail latency nói vì sao cái đuôi đó đau). PostgreSQL làm nhẹ chuyện này nhờ MVCC, nơi lệnh đọc thường không lấy row lock, nhưng writer vẫn giữ row lock tới cuối. Engine thật còn chặt hơn nữa và giữ mọi lock tới lúc commit, đó chính là lý do một transaction chậm là một transaction đắt: suốt thời gian đó nó giữ cả pha growing của mình làm con tin.

06Deadlock từ đâu ra

Luật đó có một tác dụng phụ làm mình bất ngờ. Mình tưởng lock sinh ra để ngăn rắc rối, nên một giao thức lock đúng thì không thể tự làm kẹt mình. Nhưng two-phase locking không ngăn deadlock, nó khiến deadlock có thể xảy ra. Giữ cái đang có trong khi xin thêm chính là định nghĩa của pha growing. Nếu transaction 1 giữ A và muốn B, còn transaction 2 giữ B và muốn A, hai bên chờ nhau mãi.

Bạn có thể tái hiện nó bằng hai session psql trong một phút. Chạy các câu lệnh theo thứ tự số.

-- chuẩn bị (session nào cũng được)
INSERT INTO orders (id, customer_id, status) VALUES (1, 7, 'paid'), (2, 8, 'paid');

-- 1) session 1
BEGIN;
UPDATE orders SET status = 'packed' WHERE id = 1;     -- giữ dòng 1

-- 2) session 2
BEGIN;
UPDATE orders SET status = 'packed' WHERE id = 2;     -- giữ dòng 2

-- 3) session 1
UPDATE orders SET status = 'shipped' WHERE id = 2;    -- bị chặn: dòng 2 đang là của session 2

-- 4) session 2 (đợi hơn 1 giây sau bước 3, tức quá deadlock_timeout)
UPDATE orders SET status = 'shipped' WHERE id = 1;    -- khép vòng lại
-- ... im lặng khoảng một giây, rồi ở session 2:
-- ERROR:  deadlock detected
-- DETAIL:  Process 8412 waits for ShareLock on transaction 9030; blocked by process 8398.
--          Process 8398 waits for ShareLock on transaction 9031; blocked by process 8412.
-- HINT:  See server log for query details.
-- SQLSTATE 40P01. Session 2 bị rollback; UPDATE ở bước 3 của session 1 giờ chạy xong.
-- (Session nào hết deadlock_timeout khi vòng đã tồn tại thì session đó bị hủy.)

PID và id transaction trên máy bạn sẽ khác; hình dạng thì không. Để ý rằng không ai làm sai gì: mỗi session cập nhật hai dòng, mỗi update đều hợp lệ. Lỗi duy nhất là chúng chạm vào cùng hai dòng theo thứ tự ngược nhau. Trong code thật, chuyện đó xảy ra mỗi khi một batch job xóa WHERE id IN (...) theo thứ tự planner chọn, còn một API call chạm vào cùng các đơn đó theo thứ tự khác.

Phát hiện nó: wait-for graph

Database không ngăn được cái vòng, nhưng nó nhìn thấy được. Nó duy trì một wait-for graph: mỗi transaction là một đỉnh, có mũi tên từ T1 sang T2 khi T1 đang chờ một lock mà T2 giữ. Deadlock đơn giản là một chu trình trong đồ thị đó. Engine chạy việc kiểm tra liên tục hoặc sau khi một lần chờ kéo dài đủ lâu (PostgreSQL đợi deadlock_timeout, mặc định một giây, rồi mới đi tìm). Khi thấy chu trình, nó chọn một nạn nhân, rollback transaction đó để lock của nó biến mất, và cho các bên còn lại đi tiếp. Kleppmann mô tả đúng như vậy: database tự phát hiện deadlock, hủy một transaction, và ứng dụng được kỳ vọng sẽ thử lại.

Có hai lớp phòng thủ, và bạn muốn có cả hai. Một là thiết kế: chạm vào các dòng theo một thứ tự toàn cục. Nếu mọi đường code đều lock đơn hàng theo id tăng dần (SELECT ... WHERE id IN (...) ORDER BY id FOR UPDATE trước khi ghi, cùng loại row lock như trong bài vé concert), vòng không thể hình thành. Hai là khiêm tốn: cứ giả định nó vẫn sẽ xảy ra, vì một ngày nào đó sẽ có người viết một đường code quên mất luật.

07Vòng retry mà ORM sẽ không viết giùm bạn

Khi database chọn bạn làm nạn nhân, nó không lặng lẽ làm lại việc của bạn. Nó trả về lỗi: SQLSTATE 40P01 ở PostgreSQL, 40001 cho serialization failure, lỗi 1213 ở MySQL, và ở Prisma là P2034 (“write conflict or a deadlock”). ORM sẽ vui vẻ ném nó thành exception và người dùng thấy 500. Vòng retry phải do bạn viết, bao quanh cả transaction, không phải một câu lệnh lẻ.

const RETRYABLE_SQLSTATE = new Set(["40P01", "40001"]); // deadlock_detected, serialization_failure

function isRetryable(e: unknown): boolean {
  const err = e as { code?: string; errno?: number; meta?: { code?: string }; cause?: { code?: string } };
  return (
    err?.code === "P2034" ||                       // Prisma: write conflict hoặc deadlock
    RETRYABLE_SQLSTATE.has(err?.code ?? "") ||     // node-postgres
    RETRYABLE_SQLSTATE.has(err?.meta?.code ?? "") ||
    RETRYABLE_SQLSTATE.has(err?.cause?.code ?? "") ||
    err?.errno === 1213                            // MySQL ER_LOCK_DEADLOCK
  );
}

export async function withRetry<T>(
  fn: () => Promise<T>,                            // phải chạy TOÀN BỘ transaction
  { attempts = 5, baseMs = 50, capMs = 2000 } = {},
): Promise<T> {
  for (let attempt = 0; ; attempt++) {
    try {
      return await fn();
    } catch (e) {
      if (!isRetryable(e) || attempt + 1 >= attempts) throw e;
      // exponential backoff + full jitter, để hai nạn nhân không đâm vào nhau lần nữa
      const ceiling = Math.min(capMs, baseMs * 2 ** attempt);
      await new Promise((r) => setTimeout(r, Math.random() * ceiling));
    }
  }
}

// cách dùng
await withRetry(() =>
  prisma.$transaction(async (tx) => {
    await tx.order.update({ where: { id: 1 }, data: { status: "shipped" } });
    await tx.order.update({ where: { id: 2 }, data: { status: "shipped" } });
  }),
);

• Retry cả đơn vị. Nạn nhân đã bị rollback hoàn toàn; chạy lại mỗi câu bị lỗi sẽ để các câu trước đó mất tác dụng.
• Jitter rất quan trọng. Hai nạn nhân cùng ngủ 100 ms sẽ cùng thức dậy và có thể đâm nhau lần nữa. Sự ngẫu nhiên phá thế hòa.
• Không để side effect lọt vào. Không gửi email, không gọi thanh toán trong callback, nếu không retry sẽ gửi hai lần. Kết hợp với idempotency key, như trong bài đặt lịch khám.
• Giới hạn số lần. Vòng retry không giới hạn biến deadlock thành livelock khi tải cao.

08Checklist, và một cơ chế duy nhất

• Chạy query khóa ngoại ở trên trên mọi database bạn sở hữu. Mỗi nghi phạm phải có một quyết định: thêm index hoặc có lý do ghi rõ.
• Đặt index trên cột bảng con trước khi bảng cha có DELETE hoặc update khóa, đừng đợi sau sự cố đầu tiên.
• Giữ transaction ngắn và không có lời gọi mạng; lock bị giữ bao lâu thì transaction sống bấy lâu.
• Chạm vào các dòng theo một thứ tự toàn cục (primary key tăng dần là lựa chọn thường gặp).
• Bọc mọi transaction nhiều câu lệnh trong retry có giới hạn, có backoff và jitter, và log từng lần retry để deadlock hiện ra thay vì vô hình.

Cái lock chỉ hẹp bằng cái index đã tìm ra dòng đó. Thu hẹp việc tìm kiếm là thu hẹp cái lock.

Câu thần chú của mình cho chương này

Vậy câu trả lời cho câu hỏi ban đầu mình chưa trả lời được, quan hệ giữa lock của transaction và lock do thiếu index khóa ngoại là gì?, là không có quan hệ nào cần đi tìm. Chúng là một cơ chế nhìn từ hai góc: transaction quyết định khi nào lock được giữ, index quyết định bao nhiêu, và deadlock là thứ bạn nhận được khi cả hai đều rộng rãi. Quay lại nhà kho, thuốc chữa chưa bao giờ là sợi xích chắc hơn. Nó là cuốn catalogue, để người gác khỏi phải khóa cổng chính chỉ để tìm ba cái thùng.

09Kỳ sau

Ở bài này lock rộng vì thiếu index. Ở bài kế tiếp, lock hẹp hoàn hảo, đúng một dòng, mà vẫn là vấn đề, vì một trăm nghìn writer cùng muốn đúng dòng đó. Ghi chú của anh DBA còn ba ca nữa (bitmap index, hint APPEND, hot block), mình sẽ viết sau khi đọc xong chứ không đoán trước.

Bài viết của Vũ Duy Anh, kỹ sư backend ở Hà Nội. Câu hỏi hay góp ý sửa sai xin gửi qua email.