SQL TL;DR: Transactions & Concurrency (DCL)

Transactions & Concurrency (DCL)

/tldr: Guaranteeing data integrity across multiple operations and users.

Data Control Language (DCL) Integrity Concurrency

THE CORE IDEA: ACID Properties

A transaction is a single logical unit of work, which must either be executed entirely or not at all. The ACID properties guarantee reliability in a transactional database.

Atomicity: All or Nothing. If any part of the transaction fails, the entire transaction is rolled back.

Consistency: The transaction must bring the database from one valid state to another, preserving all rules and constraints.

Isolation: Concurrent transactions execute independently, as if they were running sequentially. This is controlled by **Isolation Levels**.

Durability: Once a transaction is committed, the changes are permanent and survive system failures (power loss, crashes).

1. Transaction Control (TCL)

These commands manage the status of a transaction in progress. Most databases run in autocommit mode unless explicitly started.

BEGIN TRANSACTION

Starts the transaction. All subsequent DML (INSERT, UPDATE, DELETE) commands are now provisional.

BEGIN TRANSACTION;
-- or START TRANSACTION;

COMMIT

Makes all changes permanent. The data is written to disk and is visible to all other transactions/users.

UPDATE accounts SET balance = ...;
COMMIT;

ROLLBACK

Undoes all changes made since the transaction started, restoring the database to its state before the BEGIN command.

DELETE FROM records WHERE id = 5;
ROLLBACK;

2. Isolation Levels & Anomalies

Isolation levels define how much a concurrent transaction must be isolated from others. Higher isolation means fewer anomalies but more overhead (locking).

The Four Anomalies (In ascending order of severity)

Dirty Read: T1 reads data that T2 has modified but not yet committed. If T2 rolls back, T1 read invalid data.

Non-Repeatable Read: T1 reads a row twice and gets different values because T2 updated the row and committed the change between T1's reads.

Phantom Read: T1 reads a set of rows (e.g., `< 10`). T2 inserts a new row that meets T1's criteria and commits. T1 re-reads and sees a new row (a "phantom").

Serialization Anomaly: Occurs when the result of concurrent execution is not the same as *any* possible sequential execution. (Only addressed by the highest level: Serializable).

Isolation Levels (Standard ANSI SQL)

**READ UNCOMMITTED:** Lowest level. Allows Dirty Reads. Fastest but least safe.
**READ COMMITTED:** Default in many databases (e.g., Postgres). Prevents Dirty Reads. Allows Non-Repeatable and Phantom Reads.
**REPEATABLE READ:** Prevents Dirty Reads and Non-Repeatable Reads. Allows Phantom Reads (though some implementations prevent it). Common default for MySQL/InnoDB.
**SERIALIZABLE:** Highest level. Prevents all four anomalies by executing transactions strictly sequentially (via heavy locking). Slowest but safest.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

3. Deadlocks & Best Practice

A deadlock occurs when Transaction A holds a lock on Resource X and waits for a lock on Resource Y, while Transaction B holds a lock on Resource Y and waits for a lock on Resource X. Neither can proceed.

Deadlock Prevention

Always access tables/resources in the **same order** across all parts of your application.
Keep transactions **as short as possible** to minimize lock time.
Use lower isolation levels (e.g., Read Committed) if data integrity requirements allow.

Database Handling

Databases automatically detect deadlocks. The system typically chooses one transaction (the "victim") and automatically performs a **ROLLBACK** on it, allowing the other transaction to continue.

Application Fix:

Any application performing database writes must implement **retry logic** to handle deadlock rollback exceptions gracefully.

High isolation equals low concurrency, and vice versa.