Always check the behavior of your specific database.
21️⃣ Transaction Isolation Levels
Isolation is one of the deeper transaction concepts.
Common isolation levels include:
• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE
Some databases also support additional modes or implement these differently.
The general idea is:
• More isolation ↓ Stronger guarantees between concurrent transactions ↓ Potentially more locking/contention or reduced concurrency
The exact behavior is database-specific.
22️⃣ READ UNCOMMITTED
This is the weakest commonly described isolation level.
A transaction may potentially see changes that another transaction has not committed.
This can lead to phenomena such as:
• Dirty reads
It is not appropriate for every workload.
23️⃣ READ COMMITTED
A transaction generally sees committed data rather than another transaction's uncommitted changes.
This is a common default isolation level in some database systems.
However, behavior across databases can differ.
24️⃣ REPEATABLE READ
The goal is to ensure that repeated reads within a transaction provide a stable view of previously read data under the database's isolation model.
It provides stronger guarantees than READ COMMITTED.
The exact implementation differs between database systems.
25️⃣ SERIALIZABLE
This provides the strongest standard isolation level among these four.
The goal is to make concurrent transactions behave as if they were executed serially.
Conceptually: Transaction A ↓ Transaction B rather than allowing certain conflicting operations to interact concurrently.
The trade-off can be reduced concurrency or increased contention.
26️⃣ Common Transaction Problems
When transactions run concurrently, several phenomena can occur depending on the isolation level and database.
Dirty Read
• Transaction A reads data changed by Transaction B before B commits.
• B → UPDATE ↓ A → reads uncommitted value ↓ B → ROLLBACK
• A saw a value that never became permanent.
Non-Repeatable Read
• A transaction reads the same row twice and gets different values because another transaction committed a change between the reads.
• A → Read = 100
• B → Update = 200, B → COMMIT
• A → Read again = 200
Phantom Read
• A transaction repeats a query and sees additional or missing rows because another transaction inserted or deleted matching rows.
• A → SELECT ... WHERE amount > 1000 → 5 rows
• B → INSERT another matching row, B → COMMIT
• A → same query → 6 rows
The exact handling depends on the database and isolation level.
27️⃣ Transaction vs Query
Don't confuse these concepts.
A query is a SQL statement such as:
SELECT * FROM customers;
A transaction is a logical unit containing one or more operations.
For example:
Transaction
│
├── INSERT
├── UPDATE
├── UPDATE
└── COMMIT
So:
• Query → Individual SQL operation
• Transaction → Unit of work containing one or more operations
28️⃣ Real-World Analytics Example
Suppose an operations team needs to correct payment statuses.
There are 10,000 affected records.
Instead of blindly running:
UPDATE payments
SET status = 'Completed'
WHERE payment_date IS NOT NULL;
first inspect:
SELECT payment_id, status, payment_date
FROM payments
WHERE payment_date IS NOT NULL;
Then, if the correction is confirmed:
BEGIN;
UPDATE payments
SET status = 'Completed'
WHERE payment_date IS NOT NULL;
SELECT COUNT(*) AS updated_rows
FROM payments
WHERE payment_date IS NOT NULL
AND status = 'Completed';
COMMIT;
If the validation reveals an unexpected result:
ROLLBACK;