Database Transactions and Concurrency

Applications rarely change one value in isolation. A purchase reserves stock, records payment, and creates an order. A transfer debits one account and credits another. These steps describe one business action, even when they require several SQL statements.

A transaction groups those statements into one unit of work. The database either commits the unit or rolls it back. A commit accepts the changes. A rollback discards them.

Transactions also control what happens when many sessions work at the same time. That problem is concurrency. Without a concurrency strategy, valid operations can combine into an invalid result.

Imagine two customers buying the last item. Each session reads a quantity of one. Each then subtracts one. Both purchases may appear successful even though only one item existed. Each statement made sense alone. Their interleaving broke the business rule.

The database gives you mechanisms to prevent or detect that outcome. Your job is to choose the right mechanism, keep transactions bounded, and handle conflicts as expected events.

The transaction boundary

A transaction begins before a related set of reads and writes. It ends with either commit or rollback.

The boundary should match the smallest complete business action. If the boundary is too small, partial work can escape. If it is too large, locks and row versions live longer, conflicts become more likely, and recovery costs grow.

Do not hold a database transaction open while a person reviews a screen or while a service waits on a slow external API. Gather external input first. Then start the transaction, validate current database state, make the changes, and finish.

Savepoints provide a finer control point inside a transaction. You can roll back work performed after a savepoint without discarding earlier work. A savepoint does not make that earlier work visible to other transactions. Only the final commit does that.

ACID as a design checklist

ACID names four properties associated with transaction processing:

• Atomicity: the transaction takes effect as one unit or not at all.
• Consistency: a successful transaction moves the database between states that satisfy enforced rules.
• Isolation: concurrent transactions do not expose every intermediate interaction to each other.
• Durability: after the database acknowledges a commit, the committed result survives a crash under the system's durability guarantees.

Consistency is shared work. Database constraints can reject invalid rows and relationships. Application logic must still enforce rules that the schema does not express. A transaction does not turn incorrect business logic into correct logic.

Isolation also needs a precise interpretation. It does not always mean that every transaction behaves as if no other work exists. Database systems offer isolation levels because workloads need different balances between coordination cost and allowed observations.

What can go wrong concurrently

You can reason about isolation through observable anomalies:

• A dirty read sees data written by a transaction that has not committed.
• A nonrepeatable read reads one row twice and sees a committed change between the reads.
• A phantom read repeats a predicate query and sees a different matching set after another transaction commits.
• A serialization anomaly produces a result that no one-at-a-time ordering of the committed transactions could produce.
• A lost update occurs when one write overwrites the effect of another without preserving the intended combination.

The SQL isolation names are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. The standard defines them by the phenomena they must prevent. Implementations can provide stronger behavior than a name requires, and their internal mechanisms differ.

Treat an isolation-level name as the start of an investigation. Confirm the exact guarantees, defaults, and retry errors in your database version.

Isolation levels as contracts

Read Uncommitted permits the broadest set of observations. Some systems do not expose dirty reads even when you request this level.

Read Committed prevents dirty reads. One statement sees committed data, but a later statement in the same transaction may see newer commits. It often suits short operations that validate state close to each write.

Repeatable Read keeps previously read data stable according to the implementation's rules. It prevents more anomalies than Read Committed, but it does not universally provide serial execution.

Serializable requires an outcome equivalent to some serial order. A database may enforce it with locks, conflict detection, predicate tracking, or a combination. Serializable execution can abort a transaction instead of making it wait. The application must retry the complete transaction when the database reports a serialization failure.

Stronger isolation is not a substitute for transaction design. It can reduce the set of allowed outcomes while increasing blocking or abort rates. Choose it from the invariant you must protect, then test the actual workload.

Locks and row versions

Two broad mechanisms shape concurrent access.

A lock reserves access to a resource in a particular mode. Shared and exclusive modes are common examples. Conflicting lock requests wait, fail immediately, or skip locked rows when the database and statement support those choices.

Multiversion concurrency control, or MVCC, keeps row versions so a reader can use a consistent snapshot while another transaction changes the current row. This reduces read-write blocking. It does not remove write-write conflicts, storage costs, or the need for transaction boundaries.

Many systems combine row versions with locks. A normal read may use a snapshot, while an update locks the current row. A locking read such as SELECT ... FOR UPDATE tells the database that you plan to change selected rows. The exact syntax and behavior are product-specific.

Use explicit locking when a business action must reserve current rows before a later write. Do not add locks by reflex. Extra locks can reduce throughput and create more deadlock paths.

Optimistic and pessimistic coordination

Pessimistic concurrency control coordinates before the write. A transaction locks the data it plans to change. Contenders wait or fail to acquire the lock.

Optimistic concurrency control proceeds without holding a long reservation. The write includes an expected version or old value. If no row matches, the application knows that another writer changed the state and can reload, reject, or retry.

For example, an update can require both an object identifier and the version last read by the application. The same statement increments the version. A zero-row result signals a conflict without a separate check-then-write race.

Neither style wins everywhere. Pessimistic control fits short, high-value reservations with likely contention. Optimistic control fits workloads where conflicts are uncommon and waiting would be wasteful. Atomic SQL updates can avoid a read-modify-write race entirely when the new value can be expressed from the current value.

Deadlocks are recoverable outcomes

A deadlock forms when transactions wait in a cycle. One holds a resource needed by the other, while the other holds a resource needed by the first. The database detects the cycle or reaches a timeout, then ends at least one participant so work can continue.

Applications must treat the chosen victim as retryable when the operation is safe to repeat. Retry the whole transaction, not only the statement that happened to fail. The transaction's earlier reads may no longer describe current state.

Reduce deadlock frequency by touching shared resources in a consistent order. Keep write transactions short. Index predicates used to locate rows for updates. Still keep retry handling because timing and execution plans can produce conflicts you did not predict.

Correct retries require idempotency

A transaction rollback reverses database changes within that transaction. It cannot unsend an email or recall an HTTP request already accepted by another service.

Design side effects around durable intent. One common approach writes the business change and an outbox record in the same transaction. A separate worker delivers the external action and records delivery with an idempotency key.

An idempotency key identifies one logical request. Repeating the request with the same key does not create a second logical effect. This makes retries safer across network failures and database conflicts.

Retry with a limit, fresh reads, and a small delay strategy appropriate to the service. A permanent constraint violation is not a concurrency retry. Classify errors instead of retrying every failure.

A practical decision sequence

Start with the invariant. State what must remain true after all committed transactions.

Then identify the competing operations. Write down the reads and writes each operation performs. Consider more than two sessions and different statement orderings.

Next, choose the smallest transaction boundary that protects the invariant. Prefer one atomic statement when it can express the change safely. Otherwise choose an isolation level plus locking or optimistic checks.

Finally, define failure behavior. Decide which errors trigger a full retry, which return a conflict to the caller, and how external side effects remain idempotent. Test with real concurrent sessions. A single-session test cannot demonstrate a concurrency guarantee.

Limits and next steps

Local database transactions do not automatically make several independent services commit as one unit. Distributed coordination introduces different failure modes and availability tradeoffs. Outbox patterns, sagas, and idempotent consumers build on the same core discipline: explicit boundaries, durable state, and recoverable failure.

After this course, practice by reproducing a lost update with two sessions. Then prevent it with an atomic update, a locking read, an optimistic version check, and Serializable isolation. Compare waits, errors, and retry paths in the database you use.