week 10
Teaching and Learning Methods to be Employed
1. Illustrated lecture on transactions, ACID properties, and isolation levels
2. Concurrency problem demonstration with parallel transaction scenarios
3. Recovery mechanisms and log-based recovery exercise
4. Final course review and exam preparation session
Learning Outcomes / Objectives
By the end of this week, students should be able to:
1. Define a transaction and explain the ACID properties.
2. Describe the concurrency problems that arise when transactions execute simultaneously.
3. Explain lock-based and timestamp-based concurrency control protocols.
4. Describe transaction isolation levels and their trade-offs.
5. Explain the concept of database failure and recovery.
6. Describe log-based recovery and the ARIES algorithm concepts.
10.5 Transaction Isolation Levels
SQL defines four standard isolation levels, representing trade-offs between consistency and concurrency:
|
Isolation Level |
Concurrency Problems Prevented |
Nigerian Bank Use Case |
|
READ UNCOMMITTED |
None (allows all problems) |
Almost never used in banking — too risky |
|
READ COMMITTED |
Prevents Dirty Reads |
Default in Oracle, PostgreSQL. Used for routine banking queries where slight inconsistency is acceptable. |
|
REPEATABLE READ |
Prevents Dirty Reads + Non-Repeatable Reads |
Default in MySQL InnoDB. Good for financial reporting within a transaction. |
|
SERIALIZABLE |
Prevents ALL problems (full isolation) |
Used for critical financial operations: salary payments, regulatory reporting, inter-bank transfers. |
SQL: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;