modkit_db/
contention.rs

1//! Database contention detection utility.
2//!
3//! Detects transient lock-contention errors that are safe to retry.
4//! The entire transaction must be retried from `BEGIN` — not just the
5//! failing statement — because the database has already rolled it back.
6//!
7//! # Covered engines
8//!
9//! * **`MySQL` / `MariaDB`** — `InnoDB` deadlock (SQLSTATE `40001`).
10//!   `InnoDB` detects deadlocks instantly and rolls back one transaction.
11//!
12//!   > "Always be prepared to re-issue a transaction if it fails due to
13//!   > deadlock. Deadlocks are not dangerous. Just try again."
14//!   > — [MySQL 8.0 Reference Manual, InnoDB Deadlocks](https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html)
15//!
16//! * **`PostgreSQL`** — serialization failure (SQLSTATE `40001`) and
17//!   deadlock detected (SQLSTATE `40P01`).
18//!
19//!   > "Applications using this level must be prepared to retry transactions
20//!   > due to serialization failures."
21//!   > — [PostgreSQL docs, Transaction Isolation](https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE)
22//!
23//! * **`SQLite`** — `SQLITE_BUSY` (code 5) and `SQLITE_BUSY_SNAPSHOT` (code 517).
24//!   `SQLite` supports only one writer at a time; concurrent writers receive
25//!   `SQLITE_BUSY` when the `busy_timeout` expires, or `SQLITE_BUSY_SNAPSHOT`
26//!   immediately when a WAL snapshot cannot be upgraded.
27//!   See [Result Codes — SQLITE_BUSY](https://www.sqlite.org/rescode.html#busy).
28//!
29//! # Backend dispatch
30//!
31//! The caller must supply the [`DbBackend`] so that pattern matching is scoped
32//! to the correct engine, avoiding false positives from shared SQLSTATE codes
33//! (e.g., `40001` means different things in `MySQL` vs `PostgreSQL`).
34//!
35//! This module provides detection helpers for callers that manage their own
36//! transaction lifecycle (e.g., the outbox sequencer).
37
38use sea_orm::{DbBackend, DbErr};
39
40/// `MySQL` deadlock SQLSTATE code.
41const MYSQL_DEADLOCK_SQLSTATE: &str = "40001";
42
43/// `PostgreSQL` retryable SQLSTATE codes.
44const PG_SERIALIZATION_FAILURE: &str = "40001";
45const PG_DEADLOCK_DETECTED: &str = "40P01";
46
47/// `SQLite` error codes for write contention.
48///
49/// sqlx surfaces these as `"error returned from database: (code: N) database is locked"`.
50const SQLITE_BUSY_CODE: &str = "(code: 5)";
51const SQLITE_BUSY_SNAPSHOT_CODE: &str = "(code: 517)";
52const SQLITE_LOCKED_MSG: &str = "database is locked";
53
54/// Returns `true` if the error is a transient lock-contention error that is
55/// safe to retry.
56///
57/// Covers:
58/// * `MySQL` / `MariaDB` deadlock — SQLSTATE `40001`
59/// * `PostgreSQL` serialization failure (`40001`) / deadlock (`40P01`)
60/// * `SQLite` `SQLITE_BUSY` (code 5) — `busy_timeout` expired
61/// * `SQLite` `SQLITE_BUSY_SNAPSHOT` (code 517) — WAL snapshot conflict
62///
63/// Detection is based on the error's string representation, which avoids a
64/// direct dependency on `sqlx` types.
65#[must_use]
66pub fn is_retryable_contention(backend: DbBackend, err: &DbErr) -> bool {
67    match err {
68        DbErr::Exec(runtime_err) | DbErr::Query(runtime_err) => {
69            let msg = runtime_err.to_string();
70            match backend {
71                DbBackend::MySql => is_mysql_deadlock(&msg),
72                DbBackend::Postgres => is_pg_contention(&msg),
73                DbBackend::Sqlite => is_sqlite_busy(&msg),
74            }
75        }
76        _ => false,
77    }
78}
79
80fn is_mysql_deadlock(msg: &str) -> bool {
81    msg.contains(MYSQL_DEADLOCK_SQLSTATE)
82}
83
84fn is_pg_contention(msg: &str) -> bool {
85    msg.contains(PG_SERIALIZATION_FAILURE) || msg.contains(PG_DEADLOCK_DETECTED)
86}
87
88fn is_sqlite_busy(msg: &str) -> bool {
89    (msg.contains(SQLITE_BUSY_CODE) || msg.contains(SQLITE_BUSY_SNAPSHOT_CODE))
90        && msg.contains(SQLITE_LOCKED_MSG)
91}
92
93#[cfg(test)]
94mod tests {
95    use sea_orm::RuntimeErr;
96
97    use super::*;
98
99    fn exec_err(msg: &str) -> DbErr {
100        DbErr::Exec(RuntimeErr::Internal(msg.to_owned()))
101    }
102
103    fn query_err(msg: &str) -> DbErr {
104        DbErr::Query(RuntimeErr::Internal(msg.to_owned()))
105    }
106
107    // ── MySQL ────────────────────────────────────────────────────────
108
109    #[test]
110    fn mysql_deadlock_detected() {
111        let err = exec_err("MySqlError { ... SQLSTATE 40001: Deadlock found ... }");
112        assert!(is_retryable_contention(DbBackend::MySql, &err));
113    }
114
115    // ── PostgreSQL ────────────────────────────────────────────────────
116
117    #[test]
118    fn pg_serialization_failure_detected() {
119        let err = exec_err("error returned from database: error with SQLSTATE 40001");
120        assert!(is_retryable_contention(DbBackend::Postgres, &err));
121    }
122
123    #[test]
124    fn pg_deadlock_detected() {
125        let err = exec_err("error returned from database: error with SQLSTATE 40P01");
126        assert!(is_retryable_contention(DbBackend::Postgres, &err));
127    }
128
129    // ── SQLite BUSY (code 5) ─────────────────────────────────────────
130
131    #[test]
132    fn sqlite_busy_exec_detected() {
133        let err =
134            exec_err("Execution Error: error returned from database: (code: 5) database is locked");
135        assert!(is_retryable_contention(DbBackend::Sqlite, &err));
136    }
137
138    #[test]
139    fn sqlite_busy_query_detected() {
140        let err =
141            query_err("Query Error: error returned from database: (code: 5) database is locked");
142        assert!(is_retryable_contention(DbBackend::Sqlite, &err));
143    }
144
145    // ── SQLite BUSY_SNAPSHOT (code 517) ──────────────────────────────
146
147    #[test]
148    fn sqlite_busy_snapshot_detected() {
149        let err = exec_err(
150            "Execution Error: error returned from database: (code: 517) database is locked",
151        );
152        assert!(is_retryable_contention(DbBackend::Sqlite, &err));
153    }
154
155    // ── Cross-engine isolation ──────────────────────────────────────
156
157    #[test]
158    fn sqlstate_40001_not_retryable_on_sqlite() {
159        let err = exec_err("SQLSTATE 40001");
160        assert!(!is_retryable_contention(DbBackend::Sqlite, &err));
161    }
162
163    #[test]
164    fn sqlite_busy_not_retryable_on_mysql() {
165        let err =
166            exec_err("Execution Error: error returned from database: (code: 5) database is locked");
167        assert!(!is_retryable_contention(DbBackend::MySql, &err));
168    }
169
170    // ── Negative cases ───────────────────────────────────────────────
171
172    #[test]
173    fn sqlite_constraint_not_retryable() {
174        let err = exec_err(
175            "Execution Error: error returned from database: (code: 19) UNIQUE constraint failed",
176        );
177        assert!(!is_retryable_contention(DbBackend::Sqlite, &err));
178    }
179
180    #[test]
181    fn unrelated_errors_not_retryable() {
182        assert!(!is_retryable_contention(
183            DbBackend::Sqlite,
184            &DbErr::Custom("something".into()),
185        ));
186        assert!(!is_retryable_contention(
187            DbBackend::Postgres,
188            &DbErr::RecordNotFound("x".into()),
189        ));
190    }
191
192    #[test]
193    fn code_5_without_locked_msg_not_retryable() {
194        let err = exec_err("error returned from database: (code: 5) something else");
195        assert!(!is_retryable_contention(DbBackend::Sqlite, &err));
196    }
197}
modkit_db/contention.rs

modkit_db/
contention.rs
