SQL transactions provide the foundation for reliable database operations by grouping multiple statements into a single logical unit of work. The ACID properties describe the guarantees that make transactions dependable: atomicity ensures that all statements succeed or none do, consistency preserves database constraints, isolation controls what concurrent transactions can observe, and durability ensures committed changes survive failures. Transaction boundaries are controlled with BEGIN, COMMIT, and ROLLBACK, while SAVEPOINT enables partial rollback inside a larger transaction. Keeping transactions short is equally important because long-running transactions can retain locks, increase contention, and prevent old row versions from being cleaned up efficiently. Concurrency becomes difficult when multiple transactions access the same data simultaneously. Common anomalies include dirty reads, non-repeatable reads, phantom reads, lost updates, and write skew. Database isolation levels determine which of these effects are prevented. READ COMMITTED is commonly appropriate for ordinary OLTP workloads, while REPEATABLE READ provides a consistent snapshot across multiple statements and is useful for multi-query reports. SERIALIZABLE provides the strongest isolation by making concurrent execution behave as though transactions were executed one after another, but conflicting transactions may be aborted and therefore require application-level retry logic. The exact behaviour differs across database engines, so applications should never assume that the default isolation level is universal. Explicit locking is essential when correctness depends on reading a value and then making a decision based on it. SELECT ... FOR UPDATE locks selected rows until the transaction ends, preventing another transaction from modifying them concurrently. In simpler cases, an atomic statement such as UPDATE items SET stock = stock - 1 WHERE id = 5 AND stock > 0 is even better because the database performs the decision and modification as one operation. NOWAIT can fail immediately instead of waiting for a lock, while SKIP LOCKED allows workers to ignore already-claimed rows and is particularly useful for database-backed job queues. Deadlocks occur when transactions wait on each other in a cycle; the standard defence is to acquire locks in a deterministic order, keep transactions short, and retry the transaction that the database chooses as the deadlock victim. Safe concurrent writes also require avoiding application-level race conditions. A classic check-then-insert pattern can allow two clients to observe that a record does not exist and then both attempt to insert it. Database-enforced uniqueness combined with INSERT ... ON CONFLICT or MERGE allows the database to resolve that race atomically. Idempotency keys provide another important pattern: a unique key associated with a request allows retries to become harmless no-ops rather than duplicate payments or operations. For background processing, FOR UPDATE SKIP LOCKED can be combined with a status column to let multiple workers claim different jobs without blocking one another. Optimistic concurrency using a version column is preferable for long-lived user interactions, while pessimistic locking with FOR UPDATE is better suited to short, high-contention operations such as inventory or stock decrements. The central principle of SQL concurrency is to make correctness explicit rather than relying on timing or assumptions. Transactions should be opened as late as possible, perform the required database work, and commit immediately; applications should never wait for user input or external network calls while holding database locks. A robust money-transfer transaction, for example, can lock both accounts in deterministic order, verify that the source balance is sufficient, perform the debit and credit, and write the audit record within the same transaction so the audit cannot exist independently of the transfer. Production systems should combine atomic updates, appropriate isolation, deterministic lock ordering, unique constraints, idempotency keys, retry handling, and monitoring for blocked or idle-in-transaction sessions. Together, these patterns turn concurrency from a source of intermittent bugs into a deliberately controlled part of database design.
DECISION STATS 