6 Transactions and Concurrency Basics

Why transactions matter

A transaction groups related database operations into one logical unit. Consider transferring money between two accounts: the system must subtract from one account and add to the other. If only one update succeeds, the recorded balances no longer reflect the intended transfer. A transaction lets both changes succeed together or cancels the uncommitted work if a problem occurs.

This all-or-nothing approach is useful whenever several operations jointly represent one outcome, such as updating related records.

The ACID properties

The ACID properties describe important goals for reliable transactions:

• Atomicity: All operations take effect, or none do. If an operation fails before the transaction commits, its uncommitted changes can be rolled back.

• Consistency: A transaction preserves defined database rules, such as constraints and business rules. A check constraint can prohibit a negative balance, while other rules may require application logic.

• Isolation: Concurrent transactions should not interfere in ways that produce unacceptable results. Isolation levels determine which concurrent changes are visible.

• Durability: After a successful commit, changes are recorded to survive a system failure, subject to the database’s guarantees and configuration.

Together, these properties help ensure that a transaction is complete, respects rules, behaves appropriately alongside other work, and remains recorded after success.

Controlling a transaction

Use transaction control commands to group statements and decide whether to keep their changes:

• BEGIN or START TRANSACTION starts a transaction block.

• COMMIT makes the transaction’s changes permanent and visible to other transactions.

• ROLLBACK cancels uncommitted changes.

• SAVEPOINT marks a position within a transaction. ROLLBACK TO SAVEPOINT undoes later work while preserving earlier work in the transaction.

For example, a transfer can group its two updates:

If a problem is detected before COMMIT, use ROLLBACK instead. In a real application, check that the expected account rows were updated and enforce business rules such as sufficient funds; the example alone does not do so.

Many databases use Autocommit by default: each standalone statement is treated as its own transaction and committed if it succeeds. To make multiple statements succeed or fail together, explicitly group them in a transaction. Syntax and behavior can vary by database system and client library.

Isolation and concurrent work

When transactions run at the same time, their reads and writes may overlap. Isolation levels govern what changes a transaction can see while other transactions are running. Common names are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

In broad terms, stronger isolation aims to make concurrent work behave more like transactions ran one at a time. It may also reduce concurrency or cause a transaction to be rejected, in which case an application may need to retry it. The exact guarantees and implementation vary by database system. For example, PostgreSQL treats READ UNCOMMITTED as READ COMMITTED; its SERIALIZABLE level can report a serialization failure that applications should be prepared to retry.

Keep transactions focused and reasonably short, validate important conditions before committing, and handle errors by rolling back or retrying when appropriate.