3.1 Transactions and Locks

In a single-user or single-thread execution thread, writing to a database is straightforward and inherently safe. However, in modern high-concurrency environments (such as flash sales, ticket booking, and banking systems), multiple connections read and modify identical records simultaneously. Without appropriate guards, data integrity would instantly collapse.

Relational databases use two critical engineering shields—Transactions and Locks—to preserve consistency under heavy concurrent load.

What is a Transaction?

A transaction is a logical unit of work that bundles multiple SQL operations into a single, indivisible sequence (All or Nothing).

Consider a classic banking transfer:

1. First step: Deduct $100 from Account A's balance (UPDATE accounts SET balance = balance - 100 WHERE id = A;).

2. Second step: Deposit $100 into Account B's balance (UPDATE accounts SET balance = balance + 100 WHERE id = B;).

If the first step succeeds but a system crash, network failure, or balance-limit exception occurs before the second step, the $100 vanishes into thin air. Transactions prevent this by guaranteeing that all bundled operations either commit together or roll back as if nothing ever happened:

START TRANSACTION;   -- Or BEGIN; Starts transaction, creating a private sandbox workspace

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';

-- Commit only if both requests succeeded without errors
COMMIT;              -- Writes all changes from the sandbox permanently to disk

If any violation occurs (such as negative balance checks, constraint errors, or server disconnects), abort the transaction immediately:

ROLLBACK;            -- Discards all changes in the current sandbox, restoring the data to its pre-BEGIN state

Deconstructing ACID Principles

A transaction's behavior is governed by four foundational rules (known as the ACID rules):

1. Atomicity: The transaction behaves like a single nuclear atom. It either succeeds entirely or rolls back entirely (All or Nothing). No partial changes can ever exist in the permanent state.

2. Consistency: The database must transition from one valid state to another. Before starting and after committing, all schema limits, table constraints (unique keys, foreign keys, CHECK clauses), and integrity requirements must be completely satisfied.

3. Isolation: Concurrent transactions must execute without leaking intermediate states to one another. Isolation levels determine how visible dirty writes or uncommitted values are across active sessions.

4. Durability: Once a transaction is COMMITted, the database guarantees that the changes are safely flushed. Even if power cuts out 0.01 seconds after receiving confirmation, the database must recover the committed state during startup (typically achieved via write-ahead logging or transaction logs).

Concurrency Problems and Isolation Levels

To maximize throughput, database engines avoid processing every transaction sequentially in a single line. However, running transactions in parallel risks multiple concurrency anomalies. SQL defines four isolation levels to manage the trade-off between concurrency and correctness:

Database Locks: Traffic Lights of Concurrency

To guard records against concurrent corruption, engines employ mathematical locks:

1. Shared Locks (S-Locks / Read Locks): Allow other transactions to read rows but prevent modifications. Multiple read locks can overlap. Think of this as an open encyclopedia in a library—anyone can read it together, but nobody can edit it.

2. Exclusive Locks (X-Locks / Write Locks): Acquired during updates. Prevents other transactions from writing or reading the locked rows. It behaves as a private diary—until released, others must wait outside.

🚨 Warning: Deadlocks

A deadlock occurs when two transactions hold a resource the other needs to continue, causing both sessions to freeze indefinitely:

【Transaction A】                       【Transaction B】
Holds write lock on checking           Holds write lock on savings
Requests lock on savings ...           Requests lock on checking ...
       (Waiting ⏳)                           (Waiting ⏳)

This is a deadlock!

• Resolution: Most industrial-grade engines have an active Deadlock Detector. When a cyclic wait-for tree is detected, the engine instantly sacrifices the transaction with the fewest accumulated writes, throwing a rollback exception and allowing the surviving transaction to finish.

Best Practices for Transaction Design

To build robust transaction boundaries, always follow these rules:

1. Keep Transactions Short: Never fetch external API resources, calculate complex models, or perform heavy disk I/O between BEGIN and COMMIT! Longer transactions hold locks longer, immediately causing lock timeouts and system-wide bottlenecks under high traffic.

2. Access Resources in a Consistent Order: If every transaction programmatically locks Account A before Account B, concurrent sessions will flow in the same direction, reducing the possibility of deadlocks to near zero.

3. Avoid Overusing High Isolation Levels: Stick to standard isolation levels (like READ COMMITTED or the default REPEATABLE READ in MySQL InnoDB) and leverage MVCC (Multi-Version Concurrency Control) for non-blocking reads instead of relying on heavy locking SERIALIZABLE tables.

4. Always Implement Robust Error Handling: Catch database exceptions and explicitly invoke ROLLBACK on failures to avoid leaking uncommitted, locked connections.