SQLite WAL trong production: Checkpoint, starvation và backup an toàn

SQLite ở chế độ WAL cho phép reader và writer hoạt động đồng thời, nhưng không có nghĩa “bật WAL là xong”. Reader giữ transaction quá lâu có thể chặn checkpoint, file -wal tiếp tục lớn, độ trễ đọc tăng và một số checkpoint trả SQLITE_BUSY. Production cần quan sát cả transaction lẫn vòng đời WAL.

Bài viết này giải thích end mark, checkpoint starvation, bốn chế độ checkpoint, busy_timeout, durability, backup và shutdown. Nội dung được đối chiếu với tài liệu WAL chính thức, PRAGMA wal_checkpoint, sqlite3_wal_checkpoint_v2() và SQLite Backup API.

1. Kiểm tra phiên bản trước tiên

Nếu nhiều connection cùng ghi hoặc checkpoint một database WAL, hãy kiểm tra thư viện SQLite thực sự được process nạp.

SELECT sqlite_version();

Tài liệu SQLite công bố WAL-reset bug hiếm có thể gây corruption trong các bản từ 3.7.0 đến 3.51.2. Bản sửa có trong 3.51.3 và mới hơn, cùng các backport 3.44.6 và 3.50.7. Đừng chỉ kiểm tra CLI sqlite3: runtime của Python, PHP, mobile app hoặc binary static có thể link phiên bản khác.

Đây không phải lý do để hoảng loạn hay xóa WAL thủ công; hãy inventory phiên bản, nâng cấp theo change-management và chạy integrity/backup checks bình thường.

2. WAL thay đổi đường đi của commit

Ở rollback journal, thay đổi cuối cùng được ghi vào database chính. Với WAL, transaction append page mới vào file -wal; commit hoàn tất khi WAL nhận commit record. Checkpoint sau đó copy page đã commit về file database.

Ba hoạt động tồn tại độc lập:

• Read: đọc database chính và version page phù hợp trong WAL.
• Write: append frame vào WAL; tại một thời điểm vẫn chỉ có một writer.
• Checkpoint: merge frame trở lại database và tạo điều kiện recycle WAL.

Reader không chặn writer theo cách rollback mode thường làm, nhưng writer–writer vẫn serialize. Giữ write transaction ngắn; không gọi API chậm hoặc network request giữa BEGIN và COMMIT.

3. End mark tạo snapshot cho reader

Khi read transaction bắt đầu, SQLite ghi nhớ commit record hợp lệ cuối trong WAL—“end mark”. Reader đó chỉ thấy state đến end mark dù writer tiếp tục append commit mới. Đây là snapshot consistency của read transaction.

Checkpoint có thể copy page đến khi gặp page mà một reader cũ vẫn cần. Nó dừng, ghi nhớ tiến độ rồi thử tiếp ở lần sau. Vì vậy reader và checkpoint có thể chạy đồng thời, nhưng reader sống lâu đặt một rào cản cho tiến độ.

4. Checkpoint starvation hình thành thế nào?

Nếu luôn tồn tại ít nhất một read transaction cũ, checkpoint không thể hoàn tất và reset/recycle WAL. Dấu hiệu thường gặp:

• file database.db-wal tăng liên tục;
• số frame checkpointed luôn thấp hơn số frame log;
• read latency tăng dần theo kích thước WAL;
• RESTART/TRUNCATE checkpoint chờ lâu hoặc báo busy;
• connection pool giữ cursor/result iterator chưa đóng.

Nguyên nhân thường không phải câu SQL chậm duy nhất mà là transaction boundary: UI pagination giữ cursor, stream response đọc từng dòng, ORM mở transaction quanh toàn request hoặc exception path không rollback.

5. Bật WAL và xác nhận kết quả

PRAGMA journal_mode=WAL;

Pragma trả về journal mode thực tế. Chỉ tiếp tục nếu kết quả là wal; VFS thiếu shared-memory primitive có thể giữ mode cũ. WAL mode là thuộc tính persistent của database, không phải setting tạm cho một connection.

Tất cả process truy cập WAL thông thường phải ở cùng host vì wal-index dùng shared memory. Không đặt database WAL trên NFS/SMB rồi cho nhiều máy cùng mở. Network block storage chỉ được dùng khi vendor chứng minh semantics locking/shared memory phù hợp và workload đã được test.

6. Autocheckpoint mặc định và latency tail

SQLite mặc định chạy automatic checkpoint khi commit làm WAL đạt khoảng 1.000 page. Với page 4 KiB, đó thường là khoảng 4 MiB, nhưng phải đọc page size thực tế thay vì hard-code.

PRAGMA page_size;
PRAGMA wal_autocheckpoint;

Automatic checkpoint là PASSIVE và thường chạy trong thread thực hiện commit vượt ngưỡng. Hệ quả: đa số commit nhanh, một số commit nhận chi phí checkpoint và có tail latency cao hơn.

Nếu SLA nhạy với tail latency, có thể tắt autocheckpoint trên từng connection và chạy checkpoint bằng worker riêng. Khi đó ứng dụng phải sở hữu scheduler, backpressure, monitoring và recovery; tắt mà không thay thế sẽ làm WAL lớn không giới hạn.

7. Bốn chế độ checkpoint

PRAGMA wal_checkpoint(PASSIVE);
PRAGMA wal_checkpoint(RESTART);
PRAGMA wal_checkpoint(TRUNCATE);

Không chạy TRUNCATE liên tục chỉ để dashboard thấy file nhỏ; recycle file hiện có thường nhanh hơn truncate và grow lại.

8. Đọc kết quả PRAGMA wal_checkpoint

Pragma trả một row ba số:

1. 0 nếu checkpoint không bị chặn theo mode, 1 nếu gặp busy;
2. tổng số frame hiện có trong WAL;
3. số frame đã được checkpoint.

Ghi metric log_frames, checkpointed_frames, chênh lệch và thời lượng. Một PASSIVE checkpoint trả busy không nhất thiết là incident; xu hướng chênh lệch tăng qua nhiều chu kỳ mới là tín hiệu starvation.

Đừng parse text output của CLI trong service. Dùng driver API hoặc sqlite3_wal_checkpoint_v2() và xử lý return code.

9. SQLITE_BUSY vẫn có thể xảy ra

WAL giảm contention reader–writer nhưng không loại bỏ busy:

• hai writer tranh write lock;
• FULL/RESTART/TRUNCATE checkpoint chờ connection khác;
• connection dùng exclusive locking mode;
• connection cuối đang cleanup WAL hoặc connection đầu đang recovery sau crash;
• transaction nâng từ read lên write gặp snapshot đã cũ.

PRAGMA busy_timeout=5000;

busy_timeout là giới hạn chờ, không phải giải pháp throughput. Mỗi connection chỉ có một busy handler; framework có thể ghi đè setting. Retry phải có deadline, jitter và idempotency ở tầng ứng dụng. Không retry vô hạn trong request thread.

10. synchronous và durability

PRAGMA synchronous=FULL sync WAL ở mỗi commit, tăng độ bền trước power loss nhưng tốn latency. Ở NORMAL, commit thường không sync WAL ở mỗi transaction; checkpoint thực hiện sync quan trọng, nên process crash vẫn an toàn theo thiết kế nhưng mất điện/hard reset có thể rollback transaction gần nhất.

Chọn dựa trên RPO, loại storage và nguồn điện:

• ledger hoặc dữ liệu không thể tái tạo: nghiêng về FULL và storage có flush semantics đáng tin;
• cache/telemetry có thể tái tạo: NORMAL có thể phù hợp;
• không dùng OFF trong production nếu chưa chấp nhận corruption khi OS/power failure.

Benchmark phải gồm checkpoint, fsync tail và power-failure model, không chỉ transactions/second.

11. Đừng xóa hoặc copy riêng file WAL

File -wal là một phần trạng thái persistent. Tách database khỏi WAL có thể làm mất transaction đã commit hoặc gây hỏng. Không dùng cron rm *.db-wal.

Để backup online, ưu tiên SQLite Backup API hoặc VACUUM INTO theo contract phiên bản và workload. Để copy offline:

1. quiesce writer và đóng mọi connection;
2. để SQLite checkpoint/cleanup bình thường;
3. xác nhận không còn process giữ database;
4. copy artifact đã nhất quán;
5. restore vào staging và chạy PRAGMA integrity_check.

Nếu buộc copy live ở filesystem level, phải snapshot đồng thời database, WAL và SHM theo cơ chế được SQLite/storage hỗ trợ; copy ba file tuần tự không tạo snapshot nguyên tử.

12. Thiết kế connection pool

• Đặt read transaction ngắn và đóng cursor/result iterator ngay.
• Không giữ transaction qua network call, sleep hoặc render response.
• Giới hạn writer concurrency thay vì để hàng trăm request cùng tranh lock.
• Dùng BEGIN IMMEDIATE khi workflow chắc chắn sẽ ghi để fail/wait sớm, không nâng cấp bất ngờ.
• Rollback trong mọi exception path trước khi trả connection về pool.
• Thiết lập journal/synchronous/busy policy nhất quán cho từng connection mới.
• Không giả định PRAGMA connection-scoped tự lan sang pool khác.

WAL mode persistent, nhưng nhiều PRAGMA vận hành là connection-scoped. Hook khởi tạo connection là nơi thích hợp để enforce chúng.

13. Runbook checkpoint production

-- Quan sát nhẹ, không ép reader rời WAL
PRAGMA wal_checkpoint(PASSIVE);

-- Trong maintenance window sau khi giảm traffic
PRAGMA wal_checkpoint(RESTART);

-- Chỉ khi thật sự cần file WAL về 0 byte
PRAGMA wal_checkpoint(TRUNCATE);

Nếu RESTART/TRUNCATE bị busy:

1. không kill hoặc xóa WAL ngay;
2. xác định process/connection giữ read transaction;
3. dừng nhận traffic mới hoặc drain pool;
4. đợi transaction có deadline kết thúc;
5. chạy lại checkpoint một lần có timeout;
6. nếu vẫn thất bại, rollback maintenance và điều tra lifecycle.

Checklist production

• Runtime SQLite ở bản đã sửa WAL-reset bug hoặc backport tương ứng.
• Xác nhận journal_mode thực sự trả wal.
• Database WAL không được multi-host trên network filesystem.
• Write transaction và read cursor đều có deadline ngắn.
• Autocheckpoint có chủ đích hoặc được thay bằng worker có monitoring.
• Theo dõi WAL bytes, log/checkpointed frames, busy và checkpoint latency.
• busy_timeout hữu hạn; retry có deadline và idempotency.
• synchronous phù hợp RPO và storage semantics.
• Backup dùng API/snapshot an toàn, không copy riêng database file.
• Restore drill và integrity_check chạy định kỳ.

Kết luận

WAL cải thiện concurrency vì reader giữ snapshot còn writer append transaction, nhưng checkpoint mới khép kín vòng đời. Một hệ thống ổn định phải kiểm soát thời gian sống transaction, hiểu PASSIVE/FULL/RESTART/TRUNCATE, quan sát tiến độ frame, xử lý busy hữu hạn và backup theo API. Khi các điểm này được quản lý, SQLite WAL có thể phục vụ workload production đáng kể mà vẫn giữ mô hình vận hành đơn giản.