SQL 7 — Transaction, ACID, Isolation & Khoá

Khi nhiều người dùng cùng đọc và ghi vào một cơ sở dữ liệu, mọi thứ trở nên phức tạp hơn nhiều so với việc chạy một câu lệnh đơn lẻ. Điều gì xảy ra nếu hệ thống sập ngay giữa lúc chuyển tiền? Điều gì xảy ra nếu hai người cùng rút tiền từ một tài khoản tại cùng một thời điểm? Transaction (giao dịch) và ACID chính là nền tảng lý thuyết giúp cơ sở dữ liệu trả lời những câu hỏi này một cách đáng tin cậy.

Bài này đi từ khái niệm cơ bản đến cơ chế bên trong: transaction, từng chữ trong ACID, các hiện tượng đồng thời, bốn mức cô lập, cơ chế khoá, deadlock và cuối cùng là MVCC của PostgreSQL.

Transaction là gì?

Một transaction là một nhóm các câu lệnh SQL được xem như một đơn vị công việc nguyên tử (không thể chia nhỏ). Hoặc tất cả các câu lệnh đều thành công và được lưu vĩnh viễn, hoặc không câu lệnh nào có hiệu lực — không có trạng thái lưng chừng.

Ba lệnh điều khiển transaction cốt lõi:

• BEGIN (hoặc START TRANSACTION): bắt đầu một giao dịch mới.
• COMMIT: xác nhận, ghi vĩnh viễn tất cả thay đổi trong giao dịch xuống đĩa.
• ROLLBACK: huỷ bỏ, hoàn tác toàn bộ thay đổi kể từ BEGIN.

BEGIN;
  -- nhiều câu lệnh ...
COMMIT;   -- hoặc ROLLBACK; nếu có lỗi

SAVEPOINT — điểm lưu trung gian

Đôi khi ta muốn hoàn tác chỉ một phần của giao dịch chứ không huỷ toàn bộ. SAVEPOINT cho phép đặt một mốc, sau đó có thể ROLLBACK TO mốc đó mà vẫn giữ phần việc trước nó.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  SAVEPOINT s1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 99;  -- giả sử id 99 sai
  ROLLBACK TO s1;   -- huỷ riêng phần UPDATE id = 99
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;   -- làm lại đúng
COMMIT;

Sau ROLLBACK TO s1, giao dịch vẫn đang mở; chỉ những thay đổi sau s1 bị huỷ. Đây là công cụ hữu ích trong các thủ tục dài hoặc khi xử lý lỗi cục bộ.

Auto-commit

Mặc định nhiều client (psql, JDBC, ORM) chạy ở chế độ auto-commit: mỗi câu lệnh đơn lẻ tự động là một transaction và được commit ngay. Khi bạn gõ BEGIN, auto-commit bị tạm tắt cho đến khi COMMIT/ROLLBACK.

ACID — bốn thuộc tính bảo đảm

ACID là viết tắt của bốn thuộc tính mà một hệ quản trị giao dịch tin cậy phải bảo đảm. Ta dùng ví dụ kinh điển: chuyển 100 từ tài khoản A sang tài khoản B.

A — Atomicity (Tính nguyên tử)

Giao dịch là "tất cả hoặc không gì cả". Chuyển khoản gồm hai bước: trừ 100 ở A và cộng 100 ở B. Nếu hệ thống sập sau khi trừ A nhưng trước khi cộng B, Atomicity bảo đảm bước trừ A sẽ bị hoàn tác. Tiền không bao giờ "bốc hơi". ROLLBACK và cơ chế khôi phục sau sự cố (crash recovery) là hiện thực của thuộc tính này.

C — Consistency (Tính nhất quán)

Giao dịch đưa cơ sở dữ liệu từ một trạng thái hợp lệ sang một trạng thái hợp lệ khác, tôn trọng mọi ràng buộc (NOT NULL, CHECK, FOREIGN KEY, UNIQUE) và các quy tắc nghiệp vụ. Trong ví dụ, một quy tắc nghiệp vụ có thể là "tổng tiền hệ thống không đổi". Nếu một câu lệnh vi phạm ràng buộc (ví dụ CHECK (balance >= 0)), giao dịch bị từ chối và rollback, giữ dữ liệu nhất quán.

I — Isolation (Tính cô lập)

Các giao dịch chạy đồng thời không được nhìn thấy trạng thái dở dang của nhau. Đứng từ góc nhìn của mỗi giao dịch, mọi thứ trông như thể nó đang chạy một mình. Mức độ cô lập có thể điều chỉnh (xem phần Isolation Levels) — đây là thuộc tính phức tạp và tốn kém nhất, nên thường được nới lỏng để đổi lấy hiệu năng.

D — Durability (Tính bền vững)

Một khi giao dịch đã COMMIT, thay đổi là vĩnh viễn, kể cả khi mất điện ngay sau đó. PostgreSQL hiện thực điều này bằng WAL (Write-Ahead Log): trước khi báo commit thành công, dữ liệu thay đổi được ghi tuần tự xuống file log trên đĩa. Sau sự cố, hệ thống phát lại WAL để khôi phục các commit đã xác nhận.

Tóm gọn: A lo việc rollback, C lo ràng buộc, I lo đồng thời, D lo bền vững sau commit.

Các hiện tượng đồng thời (Concurrency Phenomena)

Khi nhiều giao dịch chạy song song mà cô lập không hoàn hảo, có thể xuất hiện các hiện tượng "lạ". Hiểu chúng là chìa khoá để chọn đúng mức isolation.

Dirty read (Đọc bẩn)

Giao dịch T1 đọc được dữ liệu mà T2 đã sửa nhưng chưa commit. Nếu T2 sau đó rollback, T1 đã đọc một giá trị "ma" chưa từng tồn tại chính thức.

Non-repeatable read (Đọc không lặp lại)

Trong cùng một giao dịch T1, đọc cùng một hàng hai lần lại cho hai kết quả khác nhau, vì giữa hai lần đọc, T2 đã update/delete hàng đó và commit.

Phantom read (Đọc bóng ma)

T1 chạy cùng một câu truy vấn theo điều kiện (ví dụ WHERE balance > 1000) hai lần và lần sau xuất hiện thêm/bớt hàng mới — vì T2 đã INSERT/DELETE các hàng khớp điều kiện và commit. Khác với non-repeatable: phantom liên quan đến tập hợp hàng, không phải giá trị của một hàng cụ thể.

Lost update (Mất cập nhật)

Hai giao dịch cùng đọc một giá trị, mỗi bên tính toán dựa trên giá trị cũ rồi ghi lại; bản ghi của giao dịch sau đè mất bản ghi của giao dịch trước. Ví dụ kinh điển: hai người cùng đọc balance = 100, cả hai cộng 50 và ghi 150, kết quả đúng phải là 200.

Bốn mức cô lập (Isolation Levels)

Chuẩn SQL định nghĩa bốn mức cô lập, đánh đổi giữa tính đúng đắn và hiệu năng. Mức càng cao càng an toàn nhưng càng tốn khoá/chi phí và dễ xung đột.

1. READ UNCOMMITTED: cho phép đọc dữ liệu chưa commit. Lỏng nhất.
2. READ COMMITTED: chỉ đọc dữ liệu đã commit. Mỗi câu lệnh thấy snapshot mới nhất tại thời điểm nó chạy.
3. REPEATABLE READ: trong suốt giao dịch, các hàng đã đọc giữ nguyên giá trị.
4. SERIALIZABLE: kết quả tương đương như thể các giao dịch chạy tuần tự lần lượt. Chặt nhất.

Bảng: mức isolation vs hiện tượng

"Có thể xảy ra" nghĩa là mức đó không chặn hiện tượng; "Bị chặn" nghĩa là mức đó ngăn chặn.

Ghi chú quan trọng về PostgreSQL: chuẩn SQL cho phép phantom ở REPEATABLE READ, nhưng nhờ MVCC, PostgreSQL chặn luôn cả phantom ở mức REPEATABLE READ. Ngoài ra PostgreSQL không có READ UNCOMMITTED thực sự — nếu bạn yêu cầu mức này, nó sẽ chạy như READ COMMITTED, nên dirty read không bao giờ xảy ra trên PostgreSQL.

Lost update không nằm trong bảng chuẩn nhưng đáng lưu ý: ở READ COMMITTED nó vẫn có thể xảy ra; ở REPEATABLE READ trở lên, PostgreSQL sẽ phát hiện và báo lỗi serialization, buộc ứng dụng thử lại giao dịch.

-- Đặt mức cô lập cho giao dịch hiện tại (minh hoạ, không chạy trong sandbox)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... các câu lệnh ...
COMMIT;

Ví dụ: chuyển khoản trong một transaction

Đây là DML (thay đổi dữ liệu), nên (minh hoạ, không chạy trong sandbox):

-- Chuyển 100 từ tài khoản 1 sang tài khoản 2 (minh hoạ, không chạy trong sandbox)
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

-- Kiểm tra ràng buộc trước khi xác nhận
-- Nếu mọi thứ ổn:
COMMIT;

-- Nếu phát hiện lỗi (ví dụ balance âm) thì thay COMMIT bằng:
-- ROLLBACK;

Atomicity bảo đảm rằng nếu hệ thống sập giữa hai lệnh UPDATE, sau khi khởi động lại không có bên nào bị trừ tiền oan. Một cách phòng thủ thường thấy là thêm ràng buộc CHECK (balance >= 0) hoặc cập nhật có điều kiện:

-- (minh hoạ, không chạy trong sandbox)
UPDATE accounts
SET balance = balance - 100
WHERE id = 1 AND balance >= 100;
-- Nếu không có hàng nào bị ảnh hưởng, ứng dụng biết số dư không đủ và ROLLBACK.

Hai giao dịch đồng thời — sơ đồ

Sơ đồ dưới minh hoạ hai giao dịch cùng thao tác. T1 chuyển tiền, T2 đọc số dư; nhờ cô lập, T2 không thấy trạng thái dở dang của T1.

Ở mức READ COMMITTED, lần SELECT thứ hai của T2 (sau khi T1 commit) sẽ thấy giá trị mới — đây chính là non-repeatable read. Ở REPEATABLE READ, T2 vẫn thấy giá trị cũ vì nó làm việc trên một snapshot cố định.

Khoá (Lock)

Để hiện thực cô lập, cơ sở dữ liệu dùng khoá. Hai loại cơ bản:

• Shared lock (S, khoá đọc): nhiều giao dịch có thể cùng giữ khoá S trên một tài nguyên để đọc. S tương thích với S nhưng xung đột với X.
• Exclusive lock (X, khoá ghi): chỉ một giao dịch giữ được, dùng khi UPDATE/DELETE. X xung đột với cả S và X.

Khoá cũng có mức granularity khác nhau:

• Row lock (khoá hàng): chỉ khoá những hàng bị ảnh hưởng. Cho phép đồng thời cao, là cách PostgreSQL khoá khi UPDATE.
• Table lock (khoá bảng): khoá toàn bảng. Dùng cho DDL (ALTER TABLE) hoặc khi nâng cấp khoá. Đồng thời thấp nhưng ít overhead theo dõi.

Ta có thể chủ động khoá hàng để đọc:

-- Khoá hàng để chuẩn bị cập nhật (minh hoạ, không chạy trong sandbox)
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- giữ khoá X trên hàng
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

SELECT ... FOR UPDATE ngăn lost update: giao dịch khác muốn FOR UPDATE cùng hàng phải chờ.

Deadlock (Bế tắc) và cách tránh

Deadlock xảy ra khi hai (hoặc nhiều) giao dịch chờ khoá của nhau theo vòng tròn, không bên nào nhường. Ví dụ: T1 khoá hàng A rồi xin hàng B; T2 khoá hàng B rồi xin hàng A. Cả hai chờ vĩnh viễn.

Cơ sở dữ liệu (như PostgreSQL) có bộ phát hiện deadlock: khi phát hiện vòng chờ, nó chọn một giao dịch làm "nạn nhân", tự động rollback giao dịch đó và báo lỗi (deadlock detected). Ứng dụng nên bắt lỗi này và thử lại.

Cách giảm deadlock:

1. Truy cập tài nguyên theo cùng một thứ tự nhất quán trong mọi giao dịch (ví dụ luôn khoá hàng theo id tăng dần). Đây là biện pháp hiệu quả nhất.
2. Giữ giao dịch ngắn, commit sớm, tránh tương tác người dùng bên trong giao dịch.
3. Giảm mức isolation nếu nghiệp vụ cho phép.
4. Dùng SELECT ... FOR UPDATE để lấy khoá cần thiết sớm và dứt khoát.

MVCC trong PostgreSQL (mức khái niệm)

PostgreSQL chủ yếu dùng MVCC — Multi-Version Concurrency Control thay vì khoá đọc. Ý tưởng cốt lõi: người đọc không chặn người ghi, người ghi không chặn người đọc.

Cơ chế khái niệm:

• Khi một hàng bị UPDATE, PostgreSQL không ghi đè tại chỗ. Nó tạo một phiên bản mới (tuple version) của hàng và đánh dấu phiên bản cũ là hết hạn. Mỗi phiên bản mang thông tin "được tạo bởi giao dịch nào" (xmin) và "bị xoá/thay bởi giao dịch nào" (xmax).
• Mỗi giao dịch làm việc với một snapshot: tập hợp các giao dịch đã commit mà nó được phép nhìn thấy. Khi đọc một hàng, PostgreSQL chọn đúng phiên bản hợp lệ với snapshot của giao dịch đó.
• Vì vậy, T2 đọc số dư của A trong khi T1 đang sửa A: T2 thấy phiên bản cũ (đã commit), không cần chờ khoá, và không bao giờ thấy dữ liệu bẩn của T1.

Hệ quả thực tế:

• Các phiên bản cũ không còn ai nhìn thấy trở thành "rác" và được dọn bởi tiến trình VACUUM (sẽ bàn ở bài DBA).
• READ COMMITTED lấy snapshot mới ở mỗi câu lệnh; REPEATABLE READ và SERIALIZABLE lấy snapshot cố định ở câu lệnh đầu tiên và giữ suốt giao dịch — điều này giải thích vì sao REPEATABLE READ chặn được non-repeatable read và (trong PostgreSQL) cả phantom.
• SERIALIZABLE trong PostgreSQL dùng SSI (Serializable Snapshot Isolation): vẫn dựa trên snapshot nhưng theo dõi các phụ thuộc nguy hiểm giữa giao dịch; nếu phát hiện khả năng vi phạm tuần tự, nó huỷ một giao dịch với lỗi serialization.

Tóm tắt

• Transaction là đơn vị công việc nguyên tử, điều khiển bằng BEGIN/COMMIT/ROLLBACK; SAVEPOINT cho phép hoàn tác một phần.
• ACID = Atomicity (tất-cả-hoặc-không), Consistency (giữ ràng buộc), Isolation (cô lập đồng thời), Durability (bền vững sau commit nhờ WAL).
• Các hiện tượng đồng thời: dirty read, non-repeatable read, phantom read và lost update.
• Bốn mức cô lập đánh đổi an toàn lấy hiệu năng; bảng cho thấy mức nào chặn hiện tượng nào. PostgreSQL không có dirty read thật và chặn phantom ngay ở REPEATABLE READ.
• Khoá (shared/exclusive, row/table) hiện thực cô lập; deadlock tránh bằng cách truy cập tài nguyên theo thứ tự nhất quán và giữ giao dịch ngắn.
• MVCC của PostgreSQL dùng nhiều phiên bản và snapshot để người đọc và người ghi không chặn nhau.

Tự kiểm tra

1. Bốn chữ trong ACID là gì, và mỗi chữ bảo đảm điều gì trong ví dụ chuyển khoản?
2. Phân biệt non-repeatable read và phantom read — điểm khác nhau cốt lõi nằm ở đâu?
3. Ở mức READ COMMITTED, hiện tượng nào còn có thể xảy ra và hiện tượng nào bị chặn?
4. Vì sao PostgreSQL không bao giờ cho dirty read, dù bạn yêu cầu READ UNCOMMITTED?
5. Mô tả một tình huống deadlock với hai hàng và nêu cách phòng tránh hiệu quả nhất.
6. Trong MVCC, vì sao "người đọc không chặn người ghi"? Snapshot đóng vai trò gì?

Đọc tiếp

SQL 8 — Quản trị CSDL (DBA)