Class 46 of 58 • Enterprise Database Architecture

SQL Transactions: BEGIN, COMMIT, ROLLBACK & Isolation Levels

In multi-user database environments, hundreds of concurrent users read and modify records simultaneously. A Transaction guarantees that related database operations execute reliably as an indivisible unit of work, strictly adhering to the ACID model.

ANSI SQL Transaction Isolation Levels and Concurrency Anomalies

Figure 4.4: Matrix of ANSI SQL Isolation Levels vs Dirty Read, Non-Repeatable Read, and Phantom Read anomalies.

Transaction Control Language (TCL) Statements

• START TRANSACTION / BEGIN: Demarcates the boundary of an atomic transaction.

• COMMIT: Permanently saves all modifications made during the transaction to non-volatile disk.

• ROLLBACK: Aborts the transaction and restores all modified records to their pre-transaction state.

• SAVEPOINT: Marks a partial milestone within a long-running transaction.

Hands-on Implementation: Atomic Bank Fund Transfer

-- Atomic Transfer of $500 from Account 101 to Account 202
START TRANSACTION;

-- Step 1: Deduct from Account 101
UPDATE bank_accounts
SET balance = balance - 500.00
WHERE account_id = 101 AND balance >= 500.00;

-- Step 2: Credit Account 202
UPDATE bank_accounts
SET balance = balance + 500.00
WHERE account_id = 202;

-- If both succeeded, permanently commit:
COMMIT;

-- If any error occurred, execute:
-- ROLLBACK;

The 4 ANSI SQL Isolation Levels

Isolation controls the degree to which a transaction is protected from concurrent modifications by other transactions:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
1. READ UNCOMMITTED	Allowed	Allowed	Allowed
2. READ COMMITTED (Postgres default)	Prevented	Allowed	Allowed
3. REPEATABLE READ (MySQL InnoDB default)	Prevented	Prevented	Prevented via MVCC
4. SERIALIZABLE	Prevented	Prevented	Prevented

Next, let us learn how to encapsulate reusable backend procedural routines using SQL Stored Procedures & Functions!

Frequently Asked Questions (FAQ)

1. What is a transaction in SQL?

A transaction is a logical unit of database processing consisting of one or more SQL statements that are executed together atomically (either all succeed and commit, or all fail and rollback).

2. What is a SAVEPOINT in SQL transactions?

A SAVEPOINT establishes a partial checkpoint within an active transaction, allowing you to rollback a portion of the transaction (ROLLBACK TO SAVEPOINT) without discarding previous successful statements.

3. What is a Dirty Read in database concurrency?

A Dirty Read occurs when Transaction A reads uncommitted modifications made by Transaction B. If Transaction B subsequently rolls back, Transaction A operated on invalid data that never technically existed.