Tính đồng thời của SQLite và lý do cần cân nhắc điều này

• Cấu trúc dựa trên tệp của SQLite rất đơn giản, nhưng khi thực hiện nhiều thao tác ghi cùng lúc có thể xảy ra xung đột khóa (locking)
• Jellyfin đã sử dụng SQLite trong thời gian dài, nhưng trên một số hệ thống đã phát sinh vấn đề ứng dụng bị dừng do lỗi cơ sở dữ liệu bị khóa trong lúc giao dịch
• Bằng cách tận dụng tính năng interceptor của EF Core, họ đã triển khai ba chiến lược khóa (No-Lock, Optimistic, Pessimistic) để giảm nhẹ vấn đề
• Cách tiếp cận Optimistic dựa trên cơ chế thử lại để giảm thiểu tổn thất hiệu năng, còn cách tiếp cận Pessimistic tăng độ ổn định nhưng phải chấp nhận giảm tốc độ
• Cách làm này có cấu trúc dễ áp dụng cho các ứng dụng EF Core khác, cung cấp một phương án thay thế thực tế để giải quyết vấn đề tính đồng thời của SQLite

---

Cấu trúc cơ bản và các giới hạn của SQLite

• SQLite là một bộ máy cơ sở dữ liệu quan hệ dựa trên tệp chạy bên trong ứng dụng
  • Toàn bộ dữ liệu được lưu trong một tệp duy nhất, không cần ứng dụng máy chủ riêng biệt
• Vì ứng dụng quản lý hoàn toàn một tệp duy nhất này, nên khi nhiều tiến trình truy cập đồng thời sẽ có nguy cơ xung đột
• Do đó, ứng dụng dùng SQLite về cơ bản phải chỉ thực hiện một thao tác ghi tại một thời điểm

Chế độ Write-Ahead-Log (WAL)

• SQLite giảm bớt các ràng buộc về tính đồng thời thông qua tính năng WAL (Write-Ahead-Log)
  • Tệp WAL đóng vai trò là tệp journal ghi lại các thay đổi của cơ sở dữ liệu
  • Nó xếp hàng nhiều thao tác ghi theo kiểu song song và áp dụng các thay đổi trong WAL khi đọc
• Tuy vậy, WAL cũng không hoàn hảo, và trong một số tình huống vẫn xảy ra xung đột khóa

Vấn đề giao dịch trong SQLite

• Giao dịch đảm nhiệm bảo đảm tính nguyên tử của các thao tác thay đổi và kiểm soát việc chặn đọc
• Trên một số hệ thống chạy Jellyfin, đã xuất hiện hiện tượng SQLite trả về lỗi “database is locked” trong lúc giao dịch và dừng ngay lập tức
  • Vấn đề này được báo cáo là xảy ra không phụ thuộc vào hệ điều hành, tốc độ đĩa hay việc có dùng ảo hóa hay không
  • Do khó tái hiện và xảy ra không đều, việc xác định nguyên nhân rất khó khăn

Cách Jellyfin sử dụng SQLite và các vấn đề phát sinh

• Trong môi trường được khuyến nghị (không dùng lưu trữ mạng, dùng SSD), vấn đề hiếm khi xảy ra, nhưng do lỗi giới hạn tác vụ song song ở các phiên bản trước 10.11 nên
  • Các tác vụ quét thư viện bị chạy song song quá mức, tạo ra hàng nghìn yêu cầu ghi đồng thời
  • Điều này vượt quá giới hạn thử lại và timeout của bộ máy SQLite, dẫn đến quá tải cơ sở dữ liệu và phát sinh lỗi
• Các giao dịch kéo dài và truy vấn kém hiệu quả cũng làm vấn đề tệ hơn

Giải pháp dựa trên EF Core

• Khi Jellyfin chuyển codebase sang EF Core, họ có thể kiểm soát cấu trúc tốt hơn
• Bằng cách dùng Interceptors của EF Core để chặn mọi lệnh và giao dịch, họ đã triển khai cơ chế kiểm soát khóa minh bạch
• Ba chiến lược khóa đã được đưa vào
  1. No-Lock: chế độ mặc định, không có khóa bổ sung. Được dùng trong đa số trường hợp để tránh suy giảm hiệu năng
  2. Optimistic Locking: khi thất bại sẽ thử lại bằng thư viện Polly
  3. Pessimistic Locking: trước mọi thao tác ghi sẽ khóa toàn bộ cơ sở dữ liệu bằng ReaderWriterLockSlim

Cách hoạt động của Optimistic Locking

• Đây là cách tiếp cận giả định thao tác sẽ thành công và chỉ thử lại khi thất bại
  • Nếu hai thao tác ghi xung đột, một bên sẽ thất bại, đợi một khoảng thời gian rồi thử lại
• Thư viện Polly được dùng để chỉ thử lại với các lỗi phát sinh do khóa
• So với cách Pessimistic, nó có ít overhead hơn và ít làm giảm hiệu năng hơn

Cách hoạt động của Pessimistic Locking

• Tại thời điểm mọi thao tác ghi diễn ra, hệ thống sẽ khóa toàn bộ cơ sở dữ liệu
  • Trong lúc ghi, mọi thao tác đọc và ghi khác đều bị chặn
• Đây là cách ổn định nhất nhưng cũng chậm nhất
  • Ví dụ, dù về lý thuyết có thể ghi vào bảng “Bob” trong khi đang đọc bảng “Alice”, cách này vẫn không cho phép
• ReaderWriterLockSlim được dùng để cho phép nhiều thao tác đọc nhưng chỉ một thao tác ghi

Kế hoạch tiếp theo: Smart Locking

• Họ đang xem xét áp dụng Smart Locking kết hợp giữa Optimistic và Pessimistic
  • Mục tiêu là kết hợp ưu điểm của cả hai để cân bằng giữa hiệu năng và độ ổn định

Kết quả và khả năng áp dụng

• Kết quả thử nghiệm ban đầu cho thấy cả hai chế độ khóa đều hiệu quả trong việc giải quyết vấn đề
• Dù nguyên nhân gốc rễ vẫn chưa được xác định rõ, người dùng nay đã có lựa chọn để sử dụng Jellyfin ổn định hơn
• Trên Internet cũng có nhiều báo cáo về lỗi tương tự, nhưng chưa có giải pháp hoàn chỉnh nào tồn tại
• Cách triển khai của Jellyfin có cấu trúc dựa trên EF Core interceptor, dễ sao chép và áp dụng
  • Bên gọi không cần nhận biết cơ chế khóa bên trong đang hoạt động như thế nào
• Các ứng dụng EF Core khác gặp cùng vấn đề tính đồng thời của SQLite cũng có thể tận dụng ngay cách làm này

[GN⁺]

Ý kiến Hacker News

• Trước đây tôi từng gặp vấn đề blocking của SQLite, và nguyên nhân là do phân mảnh đĩa (fragmentation)
  Những người dùng sử dụng ứng dụng trên tablet Android cũ suốt nhiều năm, mỗi ngày 8 tiếng, đã than phiền về hiện tượng chậm đi và lỗi lock
  Khi sao chép dữ liệu về để nhận thì không tái hiện được, nhưng cuối cùng sau khi nhận trực tiếp thiết bị để kiểm tra, tôi đã “defrag” bằng cách sao chép file DB sang vị trí mới rồi đổi lại về tên cũ, và vấn đề biến mất hoàn toàn
  Tôi cũng đã thấy hiệu năng được cải thiện với DB của Jellyfin bằng cùng cách này

  • Có lẽ đó không phải là phân mảnh mà là sự xuống cấp của bộ nhớ flash. Tôi tò mò không biết đó có phải là tablet giá rẻ dùng bộ nhớ eMMC không
  • Tôi tự hỏi liệu hàm VACUUM của SQLite có thể cho hiệu quả tương tự không
  • Đây là một trường hợp thú vị. Nhưng không thể yêu cầu người dùng tự defrag được, nên cần một giải pháp thực tế hơn

• Transaction của SQLite mặc định khởi động ở chế độ “deferred”
  Nghĩa là nó không giữ write lock cho đến khi thực sự thử thực hiện thao tác ghi
  Lỗi SQLITE_BUSY xảy ra khi một transaction đọc cố chuyển sang ghi nhưng lúc đó một transaction khác đã giữ write lock
  Cách giải quyết là đặt busy_timeout và khởi động các transaction có ghi ở chế độ “immediate”
  Phần giải thích liên quan được tổng hợp khá tốt trong bài blog này

  • Ban đầu tôi cũng nghĩ đây là vấn đề SQLITE_BUSY. Tôi có gom các trường hợp liên quan ở đây
  • Tôi xem SQLITE_BUSY như một dạng mùi kiến trúc. Trong chế độ WAL, tôi thiết kế tách riêng pool kết nối chỉ đọc và pool kết nối ghi đơn. Làm vậy sẽ giúp nắm rõ trạng thái giữ lock và có thể thiết kế trước các tình huống tranh chấp
  • busy_timeout không áp dụng trong trường hợp này. Ở chế độ WAL, các trang được thêm vào một file log duy nhất, nên nếu đang đọc mà cố chuyển sang ghi thì SQLite sẽ thất bại ngay để đảm bảo tính tuần tự hóa. Chế độ “immediate” ngăn điều đó xảy ra
  • Rốt cuộc, ai dùng SQLite rồi cũng sẽ có lúc dính phải vấn đề này và tốn thời gian lần theo nguyên nhân
  • Trong bài blog không thấy nhắc tới SQLITE_BUSY, có lẽ là do thiếu cấu hình đó

• Có vẻ một phần giải thích trong bài là sai
  SQLite tự quản lý lock, nên ứng dụng không cần trực tiếp kiểm soát việc truy cập file
  Ngoài ra, WAL không cho phép nhiều ghi song song. Nó chỉ cho phép đọc và một ghi đơn diễn ra đồng thời

  • Tôi cũng rất thích SQLite, nhưng bài này sai ngay từ các khái niệm đồng thời cơ bản, nên tôi không thể khuyến nghị

• SQLite là một cơ sở dữ liệu tuyệt vời, nhưng các giá trị mặc định (defaults) quá bảo thủ nên khá đáng tiếc
  Nếu dùng cho dịch vụ thực tế thì cần điều chỉnh nhiều thiết lập PRAGMA

  • Tôi muốn biết nên bật mặc định những PRAGMA nào thì tốt
  • Trong tình huống này, có lẽ fork ra để tạo bộ mặc định mới còn hợp lý hơn

• Khi tính năng hctree mới của SQLite ổn định, từ lúc đó tôi chỉ định dùng SQLite thôi
  hc trong tên có lẽ là viết tắt của High Concurrency
  Liên kết tài liệu chính thức

• Đọc những bài như thế này khiến tôi có cảm giác nó chỉ dừng ở giải pháp tạm thời hơn là phân tích nguyên nhân gốc rễ của vấn đề
  Chia sẻ thực sự có giá trị là phải tìm ra nguyên nhân chính xác bằng cách debug và nghiên cứu sâu hơn

  • Có lẽ tác giả chỉ điều tra một phần và chia sẻ một giải pháp chưa hoàn chỉnh. Cũng có thể là có ý định gợi ra câu trả lời tốt hơn trên HN. Giống như câu “đăng đáp án sai thì sẽ nhận được đáp án đúng nhanh hơn”

• Có vẻ người viết cũng chưa hiểu rằng chế độ WAL rốt cuộc vẫn là cấu trúc một ghi, nhiều đọc
  Không thể ghi song song; nó chỉ giúp transaction đọc không bị thao tác ghi chặn lại
  Sẽ rất tốt nếu có MVCC hoàn chỉnh, nhưng cấu trúc hiện tại vẫn hoạt động đủ tốt nếu hiểu nguyên lý của nó

• Tôi cũng từng gặp vấn đề tương tự trên Jellyfin
  Bình thường thì nó chạy tốt, nhưng trong một số tình huống nhất định DB sẽ bị khóa rồi treo
  Trong log chỉ còn lại dòng “database is locked”, và cuối cùng phải khởi động lại container Docker mới giải quyết được
  Thường xảy ra khi thao tác nhanh nhiều nút trên giao diện TV

• Hơi khác chủ đề một chút, nhưng nếu dùng in-memory DB của SQLite cho khối lượng lớn thao tác insert/delete thì mức dùng bộ nhớ sẽ tăng dần
  Ví dụ cứ 5 phút lại thêm rồi xóa 100.000 hàng, lặp lại vài ngày thì trên macOS bộ nhớ tăng tới 1GB
  Tôi tự hỏi trong trường hợp này có thiết lập nào đáng để tinh chỉnh không

  • Bạn nên kiểm tra xem có đang chạy VACUUM định kỳ không, và auto_vacuum có đang bật không
    Tài liệu VACUUM
  • Cũng có thể đây là hành vi bình thường khi buffer được điều chỉnh động để phù hợp với mẫu sử dụng
  • Nếu là trường hợp xóa toàn bộ các hàng, thì đơn giản drop bảng rồi tạo lại sẽ hiệu quả hơn

• SQLite rất tốt, nhưng nhìn những vấn đề như thế này đôi khi tôi lại nghĩ dùng Postgres có phải sẽ tốt hơn không
  Nếu không cần tính di động của một file đơn hay mục đích nhúng, thì Postgres giải quyết vấn đề đồng thời đơn giản hơn

  • Nhưng Jellyfin là một máy chủ media tự host nên nếu yêu cầu Postgres thì việc cài đặt và bảo trì sẽ phức tạp hơn. SQLite phù hợp hơn
  • Jellyfin phần lớn là môi trường gia đình một người dùng, nên SQLite là đủ. Chỉ là cấu hình hiện tại có vẻ chưa tối ưu
  • Bỏ qua các ưu điểm của SQLite để chuyển sang Postgres thì chẳng khác gì nói “muốn đi cắm trại thì hãy dựng một căn nhà gỗ”
  • Nếu dùng Postgres thì không chỉ cài đặt mà cả migration khi nâng cấp phiên bản cũng phải để tâm. SQLite không có gánh nặng đó
  • Jellyfin gần đây đã viết lại mã DB bằng Entity Framework, nên về sau họ đang chuẩn bị để việc lựa chọn DB trở nên linh hoạt hơn

[ndrgrd]

Hả? Có một đoạn khiến mình thấy lạ nên kiểm tra bình luận ngay, và đúng là như vậy...