Transaction Isolation

Transaction isolation defines how a database separates concurrent units of work. A transaction might read inventory, reserve a quantity, and create an order before committing those changes together. Without appropriate isolation or concurrency controls, two transactions can both observe the same available inventory and make incompatible decisions. Atomicity ensures that each transaction’s changes succeed or fail together; isolation governs how those transactions interact.

Common isolation levels include read uncommitted, read committed, repeatable read, and serializable. Read committed prevents dirty reads, meaning a transaction cannot read another transaction’s uncommitted changes. Repeated queries can nevertheless see different committed values. Repeatable read provides stronger guarantees for previously read data, but its exact behavior, including phantom protection, depends on the database engine. Serializable requires successful concurrent transactions to have an effect equivalent to some serial execution order. It does not necessarily execute transactions one at a time.

Databases implement isolation using locks, multiversion concurrency control, conflict detection, or combinations of these techniques. Multiversion concurrency control lets readers access appropriate committed versions while writers create new versions, reducing reader-writer blocking. However, it does not eliminate write conflicts, schema locks, or the need to protect business invariants. Snapshot isolation alone can allow write skew, where transactions update different records after reading the same state and jointly violate a rule.

Locks coordinate access to rows, key ranges, tables, or other database objects. Shared locks commonly permit compatible readers, while exclusive locks prevent conflicting access. Exact compatibility rules depend on the engine and operation. A write lock does not universally prevent ordinary reads: a versioned reader may still see an earlier committed value. Locks are concurrency controls, not substitutes for authentication, database privileges, or IAM authorization.

For example, an order service using a relational database can begin a transaction, read an inventory row with SELECT FOR UPDATE, check availability, update the quantity, and commit. In engines that support this pattern, another transaction requesting a conflicting lock waits or fails according to its configuration. An atomic conditional update can sometimes accomplish the same business requirement with less application coordination. The transaction must retain the necessary locks through the protected work; a locking read followed by a separate autocommit transaction does not protect the later update.

In Amazon RDS and Amazon Aurora, isolation defaults, supported levels, lock granularity, and diagnostic views depend on the selected database engine. PostgreSQL and MySQL behavior must not be treated as interchangeable. Amazon Redshift uses multiversion concurrency control and supports snapshot and serializable isolation. Its explicit LOCK command obtains an ACCESS EXCLUSIVE table lock, which blocks other transactions from reading or writing the table until the transaction ends. This is a coarse coordination tool, not a row-locking substitute for an online transaction processing database.

Stronger isolation and broader locks can increase waiting, reduce concurrency, and cause transaction aborts. Keep transactions short, acquire locks in a consistent order, index selective operations where applicable, and investigate blocking sessions before terminating them. Configure appropriate lock-wait limits and retry complete transactions after retryable deadlock or serialization failures, using bounded backoff and safe handling of external side effects. Long-running transactions can retain locks and old row versions, increasing operational cost.

For data-store selection and exam scenarios, identify the invariant first: does the workload need a stable reporting snapshot, protected updates to individual records, or serializable decisions across multiple records? Then match that requirement to the engine’s actual guarantees and lock behavior. Serializable isolation is not the same as replica freshness, and a transaction in one database does not automatically isolate a workflow spanning several services.