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.1 Transactions
A transaction is a logical unit of work that consists of one or more database operations (read, write) that must be executed as a single, indivisible unit. Either ALL operations in the transaction complete successfully, or NONE of them take effect.
|
Transaction Example — Bank Transfer: Amina transfers ₦10,000 from her Savings to her sister Ngozi's account: Step 1: Debit Amina's Savings: UPDATE ACCOUNT SET Balance = Balance - 10000 WHERE AccountNo='Amina'; Step 2: Credit Ngozi's account: UPDATE ACCOUNT SET Balance = Balance + 10000 WHERE AccountNo='Ngozi'; BOTH steps must succeed or NEITHER should execute. If the system crashes between step 1 and step 2, Amina loses ₦10,000 without Ngozi receiving it — this is catastrophic without proper transaction management. |
SQL TRANSACTION SYNTAX:
START TRANSACTION;
UPDATE ACCOUNT SET Balance = Balance - 10000 WHERE AccountNo = 'Amina_001';
UPDATE ACCOUNT SET Balance = Balance + 10000 WHERE AccountNo = 'Ngozi_002';
COMMIT; -- Make changes permanent
-- OR: ROLLBACK; -- Undo all changes if an error occurs