SQL Transactions, ACID Properties & Concurrency Control

In This Chapter

Understanding Transactions
The ACID Properties
Concurrency Control

1 Understanding Transactions

A transaction is a group of SQL operations that must succeed or fail as a unit. If any step fails, everything rolls back as if nothing happened. The classic example: a bank transfer.

The Bank Transfer Problem

Transferring $500 from Account A to Account B requires two operations:

Subtract $500 from Account A
Add $500 to Account B

If step 1 succeeds but step 2 fails (server crash, network error), $500 vanishes. Transactions prevent this.

SQL

-- Without a transaction (DANGEROUS)
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- 💥 Server crashes here!
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- $500 is gone forever

SQL

-- With a transaction (SAFE)
START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;  -- Both succeed → make it permanent
-- If anything fails → ROLLBACK undoes everything

Transaction Commands

Command	What It Does
`START TRANSACTION`	Begins a new transaction
`COMMIT`	Saves all changes permanently
`ROLLBACK`	Undoes all changes since START TRANSACTION
`SAVEPOINT name`	Creates a checkpoint you can roll back to
`ROLLBACK TO name`	Rolls back to a savepoint (not the entire transaction)

Using SAVEPOINT for Partial Rollbacks

SQL

START TRANSACTION;

INSERT INTO orders (customer_id, product, amount)
VALUES (1, 'SQL Course', 49.99);

SAVEPOINT before_bonus;

UPDATE customers SET loyalty_points = loyalty_points + 50
WHERE id = 1;

-- Oops, bonus logic was wrong. Undo just the bonus, keep the order.
ROLLBACK TO before_bonus;

COMMIT;  -- The order is saved; the bonus is not

MySQL auto-commit: By default, MySQL runs in auto-commit mode — every single statement is its own transaction that auto-commits. When you explicitly use START TRANSACTION, auto-commit is disabled until you COMMIT or ROLLBACK.

Key Takeaways

A transaction groups multiple operations into an all-or-nothing unit
START TRANSACTION → do work → COMMIT (save) or ROLLBACK (undo)
Use SAVEPOINT for partial rollbacks within a transaction
Without transactions, failures can leave your data in an inconsistent state

2 The ACID Properties

Top

ACID is the set of four guarantees that every reliable database transaction must provide. It's what separates a real database from a spreadsheet.

Property	Meaning	Real-World Analogy
Atomicity	All operations succeed, or none do	A bank transfer moves money or doesn't — no halfway state
Consistency	Data always moves from one valid state to another	Total money in the system never changes during a transfer
Isolation	Concurrent transactions don't interfere with each other	Two tellers serving different customers don't see each other's in-progress work
Durability	Once committed, data survives crashes	After the receipt prints, the transfer is permanent even if power fails

Atomicity in Action

SQL

START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- Check: does sender have enough balance?
SELECT balance FROM accounts WHERE id = 1;
-- If balance < 0 → ROLLBACK (insufficient funds)
-- If balance >= 0 → COMMIT (transfer complete)

Consistency: Constraints Enforce It

Consistency means the database always satisfies its constraints — PRIMARY KEYs, FOREIGN KEYs, CHECK constraints, NOT NULL, UNIQUE. If a transaction would violate any of these, it's automatically rejected.

SQL

-- This fails because of the CHECK constraint (balance >= 0)
UPDATE accounts SET balance = -100 WHERE id = 1;
-- ERROR: Check constraint violated

Isolation: The Tricky One

When two transactions run at the same time, isolation determines how much they can see of each other's uncommitted work. Too much isolation = slow. Too little = data bugs. We'll cover isolation levels in detail in the Advanced course.

Durability: Write-Ahead Logging

After COMMIT, the database writes changes to a transaction log on disk before confirming success. Even if the server crashes immediately after, the data can be recovered from the log on restart. This is why databases can promise durability.

Why ACID matters: Imagine an e-commerce checkout. The system must deduct inventory, charge the credit card, and create the order record. If the charge succeeds but the inventory deduction fails, you've sold something you didn't have. ACID ensures all three happen together or not at all.

Key Takeaways

Atomicity: All or nothing — no partial transactions
Consistency: Constraints keep data valid before and after
Isolation: Concurrent transactions don't interfere
Durability: Committed data survives crashes (write-ahead logging)
ACID is what makes SQL databases trustworthy for financial and critical data

3 Concurrency Control

Top

When multiple users or applications access the same data simultaneously, the database must prevent conflicts. Concurrency control is how the DBMS manages this.

The Problems Without Concurrency Control

Problem	What Happens	Example
Dirty Read	Reading data that another transaction hasn't committed yet	You see a $0 balance that was just a temporary state during a transfer
Non-Repeatable Read	Reading the same row twice gets different values	Balance is $1000 in your first read, $500 in your second — someone changed it between
Phantom Read	A second query returns rows that didn't exist in the first	You count 10 orders, then count again and get 11 — someone inserted one
Lost Update	Two transactions overwrite each other's changes	Both read stock=100, both deduct 1, write stock=99 instead of 98

How Databases Solve It: Locking

The primary mechanism is locking — when one transaction is working with a row, others must wait.

SQL

-- SELECT ... FOR UPDATE locks the selected rows
START TRANSACTION;

SELECT balance FROM accounts
WHERE id = 1
FOR UPDATE;  -- Lock this row! No one else can change it until we COMMIT

-- ... do calculations ...

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

COMMIT;  -- Release the lock

Isolation Levels

Databases let you choose how strict the isolation should be. More isolation = fewer bugs, but slower performance:

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Speed
READ UNCOMMITTED	Possible	Possible	Possible	Fastest
READ COMMITTED	Prevented	Possible	Possible	Fast
REPEATABLE READ	Prevented	Prevented	Possible	Moderate
SERIALIZABLE	Prevented	Prevented	Prevented	Slowest

SQL

-- Set isolation level for the current session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Check current level (MySQL)
SELECT @@transaction_isolation;

MySQL default: REPEATABLE READ. PostgreSQL default: READ COMMITTED. For most applications, the default is fine. Only change it if you understand the trade-offs. We go deeper in the Advanced course.

Deadlocks: When Transaction A locks row 1 and waits for row 2, while Transaction B locks row 2 and waits for row 1, neither can proceed. This is a deadlock. The database detects it and automatically kills one transaction. We cover deadlock prevention in Transactions, Locks & Deadlocks (Advanced).

Key Takeaways

Concurrency problems: dirty reads, non-repeatable reads, phantom reads, lost updates
Databases use locking to prevent concurrent modification conflicts
SELECT ... FOR UPDATE locks rows until you COMMIT or ROLLBACK
Isolation levels let you trade consistency for performance
Stick with the default isolation level unless you have a specific reason to change it

What's next? Transactions can fail — and when they do, you need to handle the error gracefully. The next chapter covers Exception Handling in SQL with TRY-CATCH blocks, RAISERROR, and THROW.

All Chapters in SQL Intermediate Course

Continue Learning

Previous ChapterDatabase Normalization Next Chapter Exception Handling

Transactions & Concurrency

In This Chapter

1 Understanding Transactions

The Bank Transfer Problem

Transaction Commands

Using SAVEPOINT for Partial Rollbacks

Key Takeaways

2 The ACID Properties

Atomicity in Action

Consistency: Constraints Enforce It

Isolation: The Tricky One

Durability: Write-Ahead Logging

Key Takeaways

3 Concurrency Control

The Problems Without Concurrency Control

How Databases Solve It: Locking

Isolation Levels

Key Takeaways

All Chapters in SQL Intermediate Course

Continue Learning

Courses

Services

Support

Connect