Database Transactions: ACID, Isolation Levels, and Deadlocks

Database Transactions: ACID, Isolation Levels, and Deadlocks Two users buy the last item simultaneously. Both succeed. You shipped one item you do not have. Transactions prevent this. ACID Properties Atomicity : All operations succeed or all fail. No partial updates. Consistency : Data moves from one valid state to another. Isolation : Concurrent transactions do not interfere with each other. Durability : Committed data survives crashes. Transaction in Practice async function purchaseItem ( userId : string , itemId : string ) { return db . transaction ( async ( trx ) => { const item = await trx ( " items " ). where ({ id : itemId }). where ( " stock " , " > " , 0 ). forUpdate (). first (); if ( \ ! item ) throw new Error ( " Out of stock " ); await trx ( " items " ). where ({ id : itemId }). decrement ( " stock " , 1 ); return trx ( " orders " ). insert ({ userId , itemId , price : item . price }). returning ( " * " ); }); } Isolation Levels READ COMMITTED (PostgreSQL default): prevent