Transaction trong SQLite: giải thích BEGIN, COMMIT, ROLLBACK

Giao dịch là một gói "tất cả hoặc không gì cả"

Giao dịch (transaction) gom nhiều câu lệnh lại để chúng hoặc cùng có hiệu lực, hoặc không câu nào có hiệu lực. Nếu có gì trục trặc giữa chừng, bạn có thể rollback và cơ sở dữ liệu trở về đúng trạng thái ban đầu.

Ví dụ kinh điển là chuyển tiền:

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

Hai câu UPDATE này đi liền với nhau. Nếu cơ sở dữ liệu bị sập giữa hai câu, Ada sẽ mất 2000 cent còn Boris chẳng nhận thêm gì. Bọc chúng trong BEGIN ... COMMIT khiến cặp lệnh này trở nên nguyên tử: cả hai cùng xảy ra, hoặc không câu nào xảy ra.

Autocommit: mặc định bạn vẫn đang dùng

Mọi câu lệnh SQL bạn chạy từ trước đến giờ đều là một giao dịch. Theo mặc định, SQLite ở chế độ autocommit: mỗi câu lệnh được bọc bởi một BEGIN và COMMIT ngầm của riêng nó.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

INSERT INTO notes (body) VALUES ('first');
INSERT INTO notes (body) VALUES ('second');
INSERT INTO notes (body) VALUES ('third');

SELECT * FROM notes;

Ba câu thêm, ba giao dịch riêng biệt, ba lần ghi xuống đĩa để fsync thay đổi. Như vậy là ổn với các lần ghi lẻ, nhưng chậm khi nạp dữ liệu hàng loạt, và nó có nghĩa là bạn không thể hoàn tác một nhóm câu lệnh như một khối. BEGIN tắt autocommit cho đến lần COMMIT hoặc ROLLBACK tiếp theo.

ROLLBACK: coi như chưa từng xảy ra

ROLLBACK hủy bỏ mọi thứ đã làm kể từ BEGIN tương ứng. Cơ sở dữ liệu trở về trạng thái trước giao dịch.

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

Cả UPDATE lẫn DELETE đều biến mất: bảng trông y như trước BEGIN. Đây là tấm lưới an toàn giúp code ứng dụng dừng lại gọn gàng khi gặp lỗi giữa một thao tác gồm nhiều câu lệnh.

Nhân tiện, vi phạm ràng buộc bên trong một giao dịch không tự động rollback toàn bộ giao dịch. Nó chỉ rollback câu lệnh gây lỗi và để giao dịch tiếp tục mở, chờ bạn quyết định. Nếu bạn muốn "tất cả hoặc không gì cả", ứng dụng phải tự gửi ROLLBACK khi gặp lỗi.

Tăng tốc thêm dữ liệu hàng loạt

Vì mỗi câu lệnh autocommit tự fsync riêng, bọc cả lô trong một giao dịch thường nhanh hơn gấp 100 lần:

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

Chỉ một lần đồng bộ đĩa ở COMMIT thay vì một lần cho mỗi hàng. Nếu bạn đang nhập hàng nghìn hàng và thắc mắc sao nó chậm như rùa, đây gần như luôn là câu trả lời.

DEFERRED, IMMEDIATE, EXCLUSIVE

BEGIN nhận một chế độ quyết định khi nào SQLite lấy khóa:

• BEGIN DEFERRED (mặc định): không lấy khóa nào cho đến khi bạn đọc hoặc ghi. Khóa ghi được lấy muộn, ở câu lệnh ghi đầu tiên.
• BEGIN IMMEDIATE: lấy khóa ghi ngay lập tức. Các kết nối khác vẫn đọc được, nhưng không kết nối nào khác có thể bắt đầu ghi.
• BEGIN EXCLUSIVE: như IMMEDIATE, thêm vào đó không kết nối nào khác đọc được. Ở chế độ WAL, nó hoạt động giống IMMEDIATE; khác biệt chỉ có ý nghĩa ở chế độ rollback journal cũ hơn.

BEGIN DEFERRED;     -- same as plain BEGIN
BEGIN IMMEDIATE;    -- reserve the write lock now
BEGIN EXCLUSIVE;    -- reserve everything (rollback-journal mode)

Lựa chọn này quan trọng với xử lý đồng thời. Với BEGIN thông thường, hai kết nối có thể cùng bắt đầu giao dịch, cùng đọc thoải mái, rồi tranh nhau khi muốn ghi: kết nối thứ hai xin khóa ghi sẽ nhận SQLITE_BUSY, và tệ hơn, nó đã đọc một số dữ liệu mà giờ phải bỏ đi.

BEGIN IMMEDIATE giải quyết điều đó: nếu bạn biết mình sẽ ghi, hãy xin khóa ghi trước. Kết nối thứ hai sẽ bị chặn (hoặc thất bại nhanh) ngay lập tức, trước khi làm bất kỳ việc gì phải bỏ đi.

Quy tắc kinh nghiệm: nếu giao dịch của bạn sẽ ghi, hãy dùng BEGIN IMMEDIATE.

Đọc bên trong giao dịch sẽ thấy một bản chụp

Khi một giao dịch đang mở, các lần đọc của bạn thấy một bản chụp nhất quán của cơ sở dữ liệu tại thời điểm giao dịch bắt đầu (ở chế độ WAL) hoặc tại lần đọc đầu tiên (ở chế độ rollback journal). Thay đổi mà các kết nối khác commit sẽ không đột ngột xuất hiện trong truy vấn của bạn.

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99, your own write is visible
COMMIT;

SELECT n FROM counter;

Bạn thấy những gì mình đã ghi dù chưa commit; các kết nối khác thì không. Khi bạn COMMIT, giá trị mới hiện ra với mọi người. Đây là điều người ta muốn nói khi bảo SQLite là serializable: không có tùy chọn READ COMMITTED nào để chỉnh, vì mặc định đã là mức mạnh nhất.

Giao dịch trong code ứng dụng

Trong một chương trình thực tế, mẫu thường gặp là try/except (hoặc try/catch) bao quanh phần thân, kèm ROLLBACK ở nhánh xử lý lỗi:

-- Pseudocode for any client library
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

Hầu hết thư viện máy khách (sqlite3 của Python, better-sqlite3, v.v.) đã bọc sẵn việc này cho bạn bằng một khối with hoặc một hàm hỗ trợ transaction(). Nên đọc tài liệu của thư viện bạn dùng: giá trị mặc định không phải lúc nào cũng như bạn nghĩ. Riêng sqlite3 của Python từ trước đến nay có hành vi autocommit khá kỳ quặc; các phiên bản gần đây đã thêm tham số autocommit đúng nghĩa để khắc phục.

Những điểm hay khiến người ta vấp

• DDL bên trong giao dịch vẫn hoạt động. CREATE TABLE, ALTER TABLE, thậm chí DROP TABLE đều có thể rollback. SQLite khác thường ở điểm này: nhiều cơ sở dữ liệu tự động commit DDL.
• VACUUM không thể chạy bên trong giao dịch. Một số lệnh bảo trì khác cũng vậy. Hãy chạy chúng ở chế độ autocommit.
• COMMIT thất bại là thất bại thật. Nếu COMMIT trả về SQLITE_BUSY (hiếm nhưng có thể xảy ra), giao dịch chưa được commit. Code của bạn cần xử lý trường hợp đó, thường là bằng cách thử lại.
• Giao dịch kéo dài sẽ chặn các tiến trình ghi. Một giao dịch mở suốt vài phút sẽ chặn các tiến trình ghi khác suốt vài phút. Hãy mở giao dịch muộn và commit thật nhanh.

Tiếp theo: savepoint

BEGIN và COMMIT là "tất cả hoặc không gì cả". Đôi khi bạn chỉ muốn rollback một phần của giao dịch, chẳng hạn bỏ một bước rủi ro nhưng giữ lại phần còn lại. Đó là việc của savepoint, và đó là nội dung tiếp theo.