Database Transactions with Claude Code: Optimistic Locking, Deadlocks, and Saga Pattern (2026-03-11)

Wrong transaction usage leads to deadlocks, negative inventory, and duplicate orders. Claude Code generates safe transaction design automatically from your CLAUDE.md. CLAUDE.md for Transaction Safety # Database Transaction Rules - Multi-table writes always wrapped in a transaction - No I/O (HTTP calls, file reads) inside transactions - Keep transactions as short as possible - Low contention → optimistic locking (version number column) - High contention → SELECT FOR UPDATE (pessimistic) - Always acquire locks in the same table order - SET statement_timeout = '5s' on all transactions - External services (Stripe, email) → Saga pattern with compensation Optimistic Locking (version column) async function reserveStock ( productId : string , quantity : number ): Promise < void > { const MAX_RETRIES = 3 ; for ( let attempt = 0 ; attempt < MAX_RETRIES ; attempt ++ ) { const product = await prisma . product . findUnique ({ where : { id : productId }, select : { version : true , stock : true }, }