from sqlalchemy import Column, Integer, BigInteger, String
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class InsufficientFunds(Exception):
pass
class Account(Base):
__tablename__ = "accounts"
id = Column(Integer, primary_key=True)
owner = Column(String(120), nullable=False)
balance_cents = Column(BigInteger, nullable=False, default=0)
version = Column(Integer, nullable=False, default=0)
__mapper_args__ = {"version_id_col": version}
def debit(self, amount_cents):
if amount_cents <= 0:
raise ValueError("amount must be positive")
if self.balance_cents < amount_cents:
raise InsufficientFunds(f"account {self.id} lacks funds")
self.balance_cents -= amount_cents
def credit(self, amount_cents):
if amount_cents <= 0:
raise ValueError("amount must be positive")
self.balance_cents += amount_cents
import random
import time
from sqlalchemy.orm.exc import StaleDataError
from .db import SessionLocal
def with_optimistic_retry(work, max_attempts=5, base_delay=0.02):
last_error = None
for attempt in range(1, max_attempts + 1):
session = SessionLocal()
try:
result = work(session)
session.commit()
return result
except StaleDataError as exc:
session.rollback()
last_error = exc
if attempt == max_attempts:
break
delay = base_delay * (2 ** (attempt - 1))
time.sleep(delay + random.uniform(0, base_delay))
except Exception:
session.rollback()
raise
finally:
session.close()
raise RuntimeError("optimistic lock retries exhausted") from last_error
from flask import Blueprint, jsonify, request
from .models import Account, InsufficientFunds
from .locking import with_optimistic_retry
bp = Blueprint("transfers", __name__)
@bp.post("/transfers")
def create_transfer():
body = request.get_json(force=True)
src_id = body["from_account_id"]
dst_id = body["to_account_id"]
amount = int(body["amount_cents"])
def move(session):
# re-read both rows on every attempt so version is current
src = session.get(Account, src_id)
dst = session.get(Account, dst_id)
if src is None or dst is None:
raise LookupError("account not found")
src.debit(amount)
dst.credit(amount)
return {"from_balance": src.balance_cents, "to_balance": dst.balance_cents}
try:
result = with_optimistic_retry(move)
except InsufficientFunds as exc:
return jsonify(error=str(exc)), 422
except LookupError as exc:
return jsonify(error=str(exc)), 404
return jsonify(result), 201
Optimistic locking assumes conflicts are rare: instead of holding a database lock for the whole read-modify-write cycle, each row carries a version counter that is checked and bumped atomically on every write. If two transactions read the same row and both try to save, only the first UPDATE ... WHERE version = :expected matches a row; the second updates zero rows and is rejected as a stale write. This avoids the throughput cost and deadlock risk of pessimistic SELECT ... FOR UPDATE while still guaranteeing that no update silently clobbers another.
The Account model tab wires this into SQLAlchemy declaratively. Setting __mapper_args__ with version_id_col tells the ORM to manage the version column itself: it appends the version predicate to the WHERE clause on flush and increments the value automatically. When the affected row count is zero, SQLAlchemy raises StaleDataError, which is the signal that a concurrent writer won the race. The debit method contains only the domain rule — reject overdrafts, subtract funds — and stays blissfully unaware of versioning.
Because a conflict is expected occasionally rather than exceptional, the losing transaction should simply retry with a fresh read. The with_optimistic_retry helper tab implements a bounded retry loop that opens a new Session per attempt, runs the caller's work function, and commits. On StaleDataError it rolls back, sleeps with exponential backoff plus jitter, and tries again from a clean slate — critically, it re-reads the row inside work so the next attempt sees the latest version. Retrying without re-reading would loop forever on the same stale data. After max_attempts it re-raises so the caller can surface a real error rather than hang.
The transfer endpoint tab shows the payoff: the Flask route defines move as a closure that loads both accounts fresh, calls debit and credit, and returns a result, then hands the whole thing to the retry helper. The endpoint code reads like a straightforward transaction while the helper absorbs contention transparently. Two pitfalls worth noting: the work callback must be idempotent because it can run several times, and side effects like sending email belong after a successful commit, never inside work. This pattern shines for low-contention, high-read workloads such as account balances, inventory counts, or document edits.
Related snips
export interface RetryOptions {
retries: number;
baseMs: number;
maxMs: number;
signal?: AbortSignal;
onRetry?: (attempt: number, delay: number, err: unknown) => void;Exponential backoff with jitter for retries
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
class SignupForm
include ActiveModel::Model
include ActiveModel::Attributes
attribute :account_name, :string
attribute :email, :stringShallow Controller, Deep Params: Form Object Pattern
use crossbeam::channel::unbounded;
use std::thread;
fn main() {
let (tx, rx) = unbounded();Crossbeam for advanced concurrent data structures
// Creating a Promise
const myPromise = new Promise((resolve, reject) => {
const success = true;
setTimeout(() => {
if (success) {Promises and async/await patterns for asynchronous JavaScript
Share this code
Here's the card — post it anywhere.
