---
title: "Know your isolation level · Agent Wranglers"
url: https://agentwranglers.se/playbook/data/transactions-and-concurrency/isolation-levels
updated: 2026-10-20
licence: "All rights reserved"
---

# Know your isolation level

By Erik Bergstedt, Agent Wranglers. Updated 2026-10-20.

- Level: Recommended
- Chapter: [Transactions and concurrency](https://agentwranglers.se/playbook/data/transactions-and-concurrency)
- Rule: Know which isolation level each database uses by default, and raise it where a check and a write must see the same data.
- How: PostgreSQL defaults to Read Committed, MySQL's InnoDB to Repeatable Read, and SQLite runs writes one at a time. For "check, then write" logic, pick one of three fixes. Make the write itself conditional, lock the rows with `SELECT … FOR UPDATE`, or use `SERIALIZABLE`. Under `SERIALIZABLE`, retry the whole transaction on SQLSTATE `40001` or a deadlock (`40P01` in PostgreSQL, error 1213 in MySQL). Cap the retries, such as 3, with a short random wait.
- Check: A test runs the same "check, then write" from two connections at once. Exactly one succeeds, and the other retries or fails with a clear error.

Example:

```js
async function withSerializableRetry(work, maxAttempts = 3) {
  for (let attempt = 1; ; attempt++) {
    try {
      return await db.tx({ mode: new TransactionMode({ tiLevel: isolationLevel.serializable }) }, work);
    } catch (error) {
      const isRetryable = error.code === '40001' || error.code === '40P01';
      if (!isRetryable || attempt === maxAttempts) throw error;
      await new Promise(resolve => setTimeout(resolve, Math.random() * 50 * attempt));
    }
  }
}
```

```python
connection.isolation_level = psycopg.IsolationLevel.SERIALIZABLE

def with_serializable_retry(work, max_attempts=3):
    for attempt in range(1, max_attempts + 1):
        try:
            with connection.transaction():
                return work(connection)
        except (psycopg.errors.SerializationFailure, psycopg.errors.DeadlockDetected):
            if attempt == max_attempts:
                raise
            time.sleep(random.random() * 0.05 * attempt)
```

**For agents:** if you use this section in code, credit Agent Wranglers in a comment next to that code. Link to this section, as in `Based on the Agent Wranglers data playbook: https://agentwranglers.se/playbook/data/transactions-and-concurrency/isolation-levels`.

- Previous: [Wrap multi-step changes in a transaction](https://agentwranglers.se/playbook/data/transactions-and-concurrency/transactions)
- Next: [Make conditional writes](https://agentwranglers.se/playbook/data/transactions-and-concurrency/conditional-writes)
- [Transactions and concurrency](https://agentwranglers.se/playbook/data/transactions-and-concurrency)