Write Transactions Are a Footgun with Rust's SQLx and SQLite
7 days ago
- #Rust
- #Database
- #SQLite
- Write transactions in SQLx with SQLite can cause lock starvation and performance issues.
- SQLite is single-writer, requiring an EXCLUSIVE lock for writes, which can lead to contention.
- Async transactions in SQLx may hold locks across await points, blocking other writers.
- Batched writes can mitigate lock starvation by reducing the number of write operations.
- Atomic writes to multiple tables require raw SQL, posing SQL injection risks.
- SQLx could potentially use Rust's type system to prevent holding write locks across awaits.
- Documentation updates and community discussions are suggested to raise awareness of this issue.