Know your isolation level

How to do it

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.

Example

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));
    }
  }
}

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)

How to 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.