week 10
| Site: | Newgate University Minna - Elearning Platform |
| Course: | Data Management |
| Book: | week 10 |
| Printed by: | Guest user |
| Date: | Sunday, 14 June 2026, 4:00 AM |
Description
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
10.2 ACID Properties
The four ACID properties guarantee reliable transaction processing in a DBMS:
|
ACID Property |
Definition and Banking Example |
|
ATOMICITY |
A transaction is treated as a single indivisible unit — it either completes fully (COMMIT) or has no effect (ROLLBACK). In the bank transfer: if the credit to Ngozi fails, the debit from Amina is automatically rolled back. |
|
CONSISTENCY |
A transaction brings the database from one valid state to another valid state. All integrity constraints must be satisfied before and after. E.g., bank balances must never go negative (if that's a constraint), and total money in the system is conserved. |
|
ISOLATION |
Concurrent transactions execute as if they were serial (one at a time). One transaction's intermediate results are not visible to others. E.g., while the transfer is in progress, neither Amina nor Ngozi's balances should show half-updated values. |
|
DURABILITY |
Once a transaction is committed, its changes are permanent — even if the system crashes immediately after. The ₦10,000 transfer is permanent once COMMIT executes. A power cut cannot undo it. |
10.3 Concurrency Problems
When multiple transactions execute simultaneously without proper control, several problems can occur:
|
Concurrency Problem |
Description and Example |
|
Lost Update |
Two transactions read the same data and both update it, but one update overwrites the other. E.g., two bank tellers simultaneously process deposits to the same account — one deposit is lost. |
|
Dirty Read |
Transaction T2 reads data written by T1 before T1 commits. If T1 rolls back, T2 has read invalid data. E.g., T2 reads a balance that T1 just updated but then rolled back — T2 acts on a 'phantom' value. |
|
Non-Repeatable Read |
T1 reads a value, T2 updates it and commits, T1 reads it again and gets a different value. E.g., a report reads an account balance, another transaction changes it, the report reads it again — inconsistent report. |
|
Phantom Read |
T1 executes a query returning a set of rows, T2 inserts/deletes rows matching T1's criteria, T1 repeats the query and gets different rows. E.g., counting active loans — a new loan is added mid-count. |
10.4 Concurrency Control Protocols
10.4.1 Lock-Based Protocols
Locking is the most common mechanism for concurrency control. Before accessing a data item, a transaction must acquire a lock on it.
|
Lock Type |
Description |
|
Shared Lock (S-lock / Read Lock) |
Multiple transactions can hold a shared lock on the same item simultaneously. Used for READ operations. Any transaction can read, but none can write while a shared lock exists. |
|
Exclusive Lock (X-lock / Write Lock) |
Only ONE transaction can hold an exclusive lock on an item. Used for WRITE operations. No other transaction can read or write the item. |
TWO-PHASE LOCKING (2PL) PROTOCOL:
1. Phase 1 (Growing Phase): A transaction may only ACQUIRE locks. It cannot release any locks.
2. Phase 2 (Shrinking Phase): A transaction may only RELEASE locks. It cannot acquire any new locks.
2PL guarantees serialisability (concurrent execution is equivalent to some serial order), but can cause DEADLOCKS.
DEADLOCK: Two transactions each waiting for a lock held by the other. E.g., T1 holds lock on Account_A and wants Account_B; T2 holds lock on Account_B and wants Account_A — circular wait. DBMS resolves deadlock by aborting (rolling back) one transaction.
10.4.2 Timestamp-Based Protocols
Each transaction is assigned a unique timestamp when it begins. The DBMS uses timestamps to order transactions and ensure serialisability without locks — eliminating deadlocks.
3. Each data item has two timestamps: Read Timestamp (W-TS) and Write Timestamp (R-TS)
4. If a younger transaction tries to write data that an older transaction has already read, the younger transaction is rolled back (Thomas Write Rule)
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;
10.6 Database Recovery
Database recovery restores the database to a correct, consistent state after a failure. Types of failures:
|
Failure Type |
Description and Recovery Approach |
|
Transaction Failure |
A single transaction aborts (logic error, integrity violation, deadlock). Recovery: ROLLBACK the failed transaction using the log. |
|
System Failure |
Power outage, OS crash — main memory lost but disk data intact. Recovery: Use transaction logs to REDO committed transactions and UNDO uncommitted ones (ARIES algorithm). |
|
Media Failure |
Disk crash — data permanently lost. Recovery: Restore from last full backup + apply transaction log to roll forward to point of failure. |
|
Communication Failure |
Network failure in distributed databases. Recovery: Distributed transaction protocols (2-Phase Commit). |
10.6.1 Write-Ahead Logging (WAL)
The Write-Ahead Log (WAL) or transaction log records every change made to the database BEFORE the change is written to disk. This is the foundation of most recovery systems.
1. BEFORE IMAGE (BFIM) — the value before the change
2. AFTER IMAGE (AFIM) — the value after the change
3. Transaction ID, Operation type, Timestamp
PRINCIPLE: A transaction's log records must be written to stable storage BEFORE the actual data pages are written to disk. This ensures that even if the system crashes mid-write, the log allows recovery.
10.6.2 Checkpoints
A checkpoint is a periodic snapshot of the database state written to disk, along with a list of active transactions at that point. Checkpoints reduce the amount of log that must be replayed during recovery.
|
Recovery Analogy: Think of checkpoints like a save point in a video game. If your game crashes, you don't restart from the beginning — you restart from the last save point. The transaction log is like the game's action history that lets you replay up to where you were when the crash happened. |
10.6.3 ARIES Algorithm (Overview)
ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) is the recovery algorithm used by IBM DB2 and many other commercial DBMS. It operates in three phases:
1. ANALYSIS PASS — Scan the log forward from the last checkpoint to determine which transactions were active at the time of failure and which pages were dirty (modified but not yet written to disk).
2. REDO PASS — Redo all operations from the oldest dirty page to the end of the log, restoring the database to the exact state it was in at the moment of crash.
3. UNDO PASS — Undo all operations of transactions that were active (uncommitted) at the time of the crash, ensuring atomicity.
10.7 Backup Strategies
|
Backup Type |
Description and Nigerian Bank Use |
|
Full Backup |
Complete copy of all database data. Done weekly for most Nigerian banks. Stored off-site (or in cloud — AWS/Azure). |
|
Incremental Backup |
Only data changed since the last backup (full or incremental). Done daily to reduce backup time and storage. |
|
Differential Backup |
Data changed since the last FULL backup. Faster to restore than incremental (only need full + latest differential). |
|
Transaction Log Backup |
Backup of the transaction log (for point-in-time recovery). Done hourly or continuously for critical systems. |
|
Snapshot Backup |
A consistent point-in-time copy of the database (using OS/storage-level snapshot). Near-instantaneous — used for 24/7 systems. |
Course Summary
|
Week |
Core Topics Covered |
|
Data vs. Information vs. Knowledge; IM Concepts; File-based vs. Database approach; DBMS overview |
|
|
DBMS components; DBMS functions; Three-schema architecture; Data independence; Database languages; Roles |
|
|
Conceptual modelling; ER model; Entities, attributes, relationships; Cardinality; ER diagram construction |
|
|
Relational data model; Keys (PK, FK, candidate, composite); Entity integrity; Referential integrity; ER→Relational mapping |
|
|
SQL DDL/DML/DQL; SELECT, WHERE, ORDER BY; Aggregate functions; GROUP BY/HAVING; JOINs; Subqueries |
|
|
Functional dependencies; Update anomalies; 1NF, 2NF, 3NF, BCNF; Normalisation impact; Denormalisation |
|
|
Week 7 |
Semi-structured data; XML structure and syntax; JSON format; Relational vs. semi-structured; JSON in MySQL |
|
DB security threats; Authentication/Authorisation; GRANT/REVOKE; Views; SQL injection; Encryption; NDPR |
|
|
Query processing pipeline; Relational algebra; Query optimisation; Indexes (B-Tree, Hash, Composite); EXPLAIN |
|
|
Week 10 |
Transactions; ACID properties; Concurrency problems; Lock-based/timestamp protocols; Isolation levels; Recovery; WAL; ARIES; Backups |
Reading List / References
1. Ramakrishnan, R. & Gehrke, J. (2003). Database Management Systems, Chapters 16-18: Concurrency & Recovery. McGraw-Hill.
2. Silberschatz et al. (2020). Database System Concepts, Chapters 17-19: Transactions & Recovery. McGraw-Hill.
3. Mohan, C. et al. (1992). ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging. ACM TODS, 17(1), 94-162.
Activities
|
Self-Assessment Quiz: 1. What does ACID stand for? Define each property with a banking example. 2. What is a dirty read? Give a specific scenario from a Nigerian fintech context. 3. Explain the difference between a shared lock and an exclusive lock. 4. What is Write-Ahead Logging (WAL)? Why is it essential for database recovery? 5. A Nigerian bank's database server crashes mid-transaction while processing salary payments. Describe step by step how the ARIES algorithm would recover the database. |
|
Concurrency Scenario Exercise: Consider three concurrent transactions in a bank database: T1: Transfer ₦50,000 from Account A to Account B T2: Generate end-of-day balance report for Account A T3: Apply monthly interest to all Savings accounts including Account A (a) What concurrency problems could arise without any concurrency control? (b) Using two-phase locking, describe what locks each transaction needs and in what order. (c) Could a deadlock occur? If yes, describe the scenario and how it would be resolved. (d) Which isolation level would you recommend for T1, T2, and T3 respectively, and why? |