Beyond the SQLite Single-Writer Limitation with Concurrent Writes

Summary

Modern applications demand more from their database than ever before. With multi-core processors and multi-threaded architectures becoming the norm, the old single-writer model that SQLite pioneered has started to show its age in today’s concurrent world. Although SQLite is the most ubiquitous database there is, we believe the single-writer concurrency model limits its ability to truly be deployed everywhere. Today we announce Turso Beta, featuring a tech preview of concurrent writes. When concurrent writes are used, we achieve up to 4x the write throughput of SQLite, while also removing the dreaded SQLITE_BUSY error that is all too familiar to many SQLite users. #The Single-Writer Bottleneck SQLite has a single-writer transaction model, which means whenever a transaction writes to the database, no other write transactions can make progress until that transaction is complete. Of course, SQLite is a highly optimized piece of software and can easily write 500k rows per second with proper batching. However, there's a catch! Adding more threads doesn't scale the writes, and when your transactions need to perform business logic or other processing, you're stuck waiting. That exclusive write lock prevents any concurrency, leaving your CPU cores underutilized. Many developers using SQLite have experienced this: when a database is locked, they encounter the dreaded SQLITE_BUSY error. You don't need a high-volume application to encounter this issue; even modest concurrent access patterns can trigger blocking scenarios that complicate your code. The single-writer model can be a major bottleneck for write-intensive applications. For example, let's take the following benchmark that executes 100 row inserts per transaction using SQLite: let mut stmt = conn.prepare("INSERT INTO test_table (id, data) VALUES (?, ?)")?; conn.execute("BEGIN", [])?; for i in 0..100 { let id = thread_id * iterations * batch_size + iteration * batch_size + i; stmt.execute([&id.to_string(), &format!("dat...