Chapter 7: Transaction Control (TCL) & Concurrency

Transaction Control Language (TCL) manages the boundaries of logical units of work. In database engineering, a transaction is a sequence of one or more SQL statements that are executed as an atomic unit. This ensures that the database remains in a consistent state even in the face of system crashes or concurrent access by thousands of users.

I. The ACID Contract in SQL

All DML operations within a transaction must satisfy the ACID properties:

• Atomicity: All statements in the transaction succeed, or none are applied.
• Consistency: The transaction transforms the database from one valid state to another, respecting all schema constraints (Unique, FK, Check).
• Isolation: Concurrent transactions do not see each other's intermediate or uncommitted states.
• Durability: Once a transaction is committed, its changes are permanent, typically guaranteed by the Write-Ahead Log (WAL).

1. Transaction Boundaries

• BEGIN TRANSACTION (or START TRANSACTION): Marks the start of the logical unit.
• COMMIT: Permanently applies all changes made during the transaction to the database.
• ROLLBACK: Reverts all changes made since the start of the transaction, returning the data to its original state.

-- Example: Atomic Bank Transfer
BEGIN TRANSACTION;

-- Step 1: Deduct from source
UPDATE accounts 
SET balance = balance - 500 
WHERE account_id = 101 AND balance >= 500;

-- Step 2: Add to destination
UPDATE accounts 
SET balance = balance + 500 
WHERE account_id = 202;

-- Finalize: Commit if both succeeded, otherwise Rollback
COMMIT;

---

II. Concurrency Control & Isolation Levels

When multiple users access the same data, the database uses Multi-Version Concurrency Control (MVCC) or Locking to manage access. SQL defines four isolation levels to balance performance and consistency.

---

III. Production Anti-Patterns

• Implicit Transactions in Loops: Executing 1,000 updates without an explicit BEGIN/COMMIT block. This forces the database to wrap each statement in its own transaction, resulting in 1,000 synchronous disk flushes.
• Long-Running Transactions: Keeping a transaction open while waiting for a slow external API call. This holds locks on the database and prevents the MVCC garbage collector (VACUUM) from cleaning up old versions, leading to Table Bloat.
• Mixing TCL and DDL: Executing DROP TABLE inside a transaction in some engines (like MySQL) triggers an Implicit Commit, potentially committing preceding DML statements that you intended to rollback.

IV. Performance Bottlenecks

• Deadlock Contention: Two transactions waiting for locks held by each other. The database engine must detect this and kill one transaction, forcing an application-level retry.
• Undo Log Saturation: In high-write environments, the volume of "Undo" or "Rollback" data can exceed the allocated buffer, causing the entire database to stall.
• Write Skew: A concurrency anomaly in REPEATABLE READ where two transactions read the same data, modify different but related rows, and commit—violating a business constraint that neither transaction could see individually.