Part VI: Advanced SQL Concepts Chapter 14: Transactions and Concurrency

 

Chapter 14: Transactions and Concurrency

In a multiuser environment, concurrent access to the same data can lead to inconsistencies, lost updates, and other anomalies. Transactions and concurrency control ensure that your database remains accurate, reliable, and performant even under heavy load. In this chapter you’ll explore:

• The ACID properties: Atomicity, Consistency, Isolation, Durability

• Transaction control commands and best practices

• Isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE)

• Techniques for detecting and resolving deadlocks

• Strategies for building robust, concurrent applications

1. The ACID Properties

ACID defines the four guarantees every transaction must uphold:

1. Atomicity A transaction is an all-or-nothing unit. Either every operation succeeds, or all effects are rolled back on failure.

2. Consistency A transaction transforms the database from one valid state to another, respecting all schema constraints, triggers, and business rules.

3. Isolation Concurrent transactions do not interfere with each other. Intermediate, uncommitted changes remain invisible to other transactions.

4. Durability Once a transaction commits, its changes persist—even if the system crashes immediately afterward.

These properties let you reason about concurrent operations as if they executed serially, simplifying application logic.

2. Transaction Control Commands and Best Practices

Controlling the boundaries of transactions is key to data integrity and performance.

2.1 Basic Commands

sql

BEGIN;      -- Start a transaction
UPDATE accounts
  SET balance = balance - 100
  WHERE id = 1;
UPDATE accounts
  SET balance = balance + 100
  WHERE id = 2;
COMMIT;     -- Persist all changes

-- If something goes wrong:
ROLLBACK;   -- Undo all operations since BEGIN

• BEGIN (or START TRANSACTION) marks the start of work.

• COMMIT makes all changes permanent.

• ROLLBACK reverts changes back to the state before BEGIN.

2.2 Savepoints

For complex workflows, you can define intermediate rollback points:

sql

BEGIN;
SAVEPOINT before_deduction;

UPDATE inventory
SET stock = stock - 5
WHERE product_id = 42;

-- If an error occurs in the next step:
ROLLBACK TO before_deduction;

-- Continue with other operations…
COMMIT;

Use SAVEPOINT to partially undo work without abandoning the entire transaction.

2.3 Best Practices

• Keep transactions short to minimize lock contention.

• Group related statements together—don’t mix reporting queries with updates.

• Avoid user interaction within a transaction (e.g., user prompts) to prevent long-held locks.

• Explicitly control commits; disable autocommit for multi-statement tasks.

3. Isolation Levels

Isolation levels balance strictness and performance. Higher isolation prevents more anomalies but increases locking and blocking.

Level	Description	Phenomena Allowed
READ UNCOMMITTED	No read locks; can see uncommitted changes (dirty).	Dirty Read, Non-repeatable Read, Phantom Read
READ COMMITTED	Only committed data is read; default in many DBs.	Non-repeatable Read, Phantom Read
REPEATABLE READ	Guarantees no change to rows read twice.	Phantom Read only
SERIALIZABLE	Highest isolation; transactions appear serial.	No anomalies

3.1 Setting Isolation Levels

sql

-- PostgreSQL, SQL Server
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Choose the lowest level that satisfies your consistency needs to maximize concurrency.

4. Detecting and Resolving Deadlocks

A deadlock occurs when two or more transactions wait for each other’s locks, halting progress.

4.1 Deadlock Scenario

1. Transaction A locks Row X, then requests Row Y—but Row Y is locked by Transaction B.

2. Transaction B locks Row Y, then requests Row X—but Row X is locked by A.

Neither can proceed.

4.2 Automatic Deadlock Detection

Most RDBMS engines detect deadlocks and abort one “victim” transaction, returning an error:

• PostgreSQL, MySQL InnoDB: Reports “deadlock detected” errors.

• SQL Server: Chooses the victim based on resource cost.

4.3 Avoiding Deadlocks

• Access resources in a consistent order across all transactions.

• Break large transactions into smaller chunks.

• Acquire locks only when needed (use SELECT ... FOR UPDATE sparingly).

• Implement retry logic in your application:

pseudo

max_retries = 3
for attempt in 1..max_retries:
  try:
    run_transaction()
    break
  except DeadlockError:
    if attempt == max_retries:
      raise
    sleep(backoff_time)

5. Building Robust Concurrent Applications

Combining transaction control and isolation with sound design yields scalable, reliable systems.

• Minimize Lock Footprint Only touch the rows you need. Avoid “update all” patterns.

• Use Read-Only Transactions For reporting queries, set a lower isolation level (READ COMMITTED) or use snapshots to avoid blocking writers.

• Monitor Locks and Waits Query system views (pg_locks, SQL Server’s DMVs) to detect blocking chains and optimize hotspots.

• Batch Operations For bulk updates or deletes, process in chunks to reduce lock duration.

• Automate Testing Include concurrency tests (simulated multiuser scenarios) in your CI pipeline to catch race conditions early.

Conclusion

Transactions and concurrency control are the foundation of any reliable, multiuser database system. By embracing the ACID properties, using explicit transaction boundaries, selecting appropriate isolation levels, and proactively handling deadlocks, you can build applications that maintain data consistency and deliver high performance under load. Rigorous monitoring, testing, and best practices will ensure your systems scale gracefully without sacrificing correctness.