Transactions

A Transaction is a logical unit of work. It is a sequence of one or more SQL statements that are executed together as a “single atomic action.” Either they all succeed, or they all fail.

Definition: ACID Properties

For a database to be considered reliable, it must follow the ACID model:

• Atomicity: “All or nothing.” If any part of the transaction fails, the whole thing is rolled back.
• Consistency: A transaction takes the database from one valid state to another, respecting all constraints.
• Isolation: Multiple transactions can run at the same time without interfering with each other.
• Durability: Once a transaction is committed, it is permanently saved, even if the power fails.

Context: The Bank Transfer Problem

Imagine Alice wants to send $100 to Bob. This requires two steps:

1. Subtract $100 from Alice’s account.
2. Add $100 to Bob’s account.

If the power goes out after step 1 but before step 2, Alice’s money disappears into thin air. Transactions prevent this.

Detailed Explanation: Controlling Transactions

1. BEGIN / START TRANSACTION

Tells the database that the following commands are part of a single unit.

2. COMMIT

Permanently saves all changes made during the transaction.

3. ROLLBACK

Undoes all changes made during the transaction, returning the data to exactly how it was before the BEGIN.

Transaction Control

BEGIN;

-- Step 1
UPDATE Accounts SET Balance = Balance - 100 WHERE UserID = 'Alice';

-- Step 2
UPDATE Accounts SET Balance = Balance + 100 WHERE UserID = 'Bob';

-- If both succeeded, save them!
COMMIT;

Isolation Levels (The “I” in ACID)

What happens if you are reading data while another user is updating it? The database uses Isolation Levels to decide:

• Read Uncommitted: You can see changes even before they are committed (Dangerous!).
• Read Committed: You only see data that has been officially saved (Default for most).
• Serializable: Transactions are run as if they were one after another. No overlaps allowed (Slowest but most accurate).

Example: Error Handling in a Script

Manual Rollback

BEGIN;

INSERT INTO Invoices (ID, Amount) VALUES (500, 120.00);

-- Imagine this second insert fails because ID 500 already exists
INSERT INTO InvoiceItems (InvoiceID, Product) VALUES (500, 'Laptop');

-- If an error occurs, the engine rolls back the first insert automatically
-- or you can trigger it manually:
ROLLBACK;

Under the Hood: The Undo Log

How does the database “undo” a command?

Every database maintains an Undo Log (or Rollback Segment). As you run commands inside a BEGIN block:

1. The database performs the action in memory.
2. It records the “Reverse” action in the Undo Log.
3. If you call ROLLBACK, the engine simply reads the log in reverse and applies the changes.
4. If you call COMMIT, the engine discards the log and writes the changes to the permanent storage.

Locks

To ensure isolation, the database uses Locks. If you are UPDATING a specific row inside a transaction, the database locks that row. Any other user who tries to update the same row will be “blocked” (forced to wait) until you call COMMIT or ROLLBACK.

Caution

Keep your transactions as short as possible. Long-running transactions hold locks on your data, preventing other users from doing their work and slowing down the entire system.