Please enable JavaScript.
Coggle requires JavaScript to display documents.
DATABASE TRANSACTION MANAGEMENT - Coggle Diagram
DATABASE TRANSACTION MANAGEMENT
Transaction System
A
logical unit of work
that must be entirely completed or aborted.
Any action that reads from and/or writes to a database.
Consists of:
Simple SELECT statement
Series of UPDATE statements
Series of INSERT statements
Combination of SELECT , UPDATE , and INSERT statements
Refer to
set of mechanisms and processes
that
ensure database transactions are executed reliably
.
Transaction processing systems categories:
Batch
Collect and process transactions in a batch
Example : Payroll System
Advantages / Disadvantages
Efficient for large volumes of transactions
Data takes time to be processed
Online
Process transactions individually and updated real-time
Example : Online Banking System
Advantages / Disadvantages
Immediate transaction processing and results
High demand on system resources to maintain data consistency and real-time processing
Real-Time
Processed individually and updated real-time with strict time constraints.
Example : Airline Ticketing Reservation
Advantages / Disadvantages
Immediate transaction and processing results
Complex to design and implement due to the need for immediate processing and high reliability
Properties
Atomicity (A)
100% transaction in all operations
Database remains unchanged if a part of transaction failed
Consistency (C)
Database transaction from one valid or consistent state to another.
Transaction is aborted if a transaction within a database is not in consistent state
Isolation (I)
Isolation or independent transactions
Data can be used in a single operation only before next use of operation
Durability (D)
The effect of a transaction is permanent and will survive system failures
The changes cannot be undone or lost, even if the system fails
A transaction will keep running until one of these conditions is met
A program finish and saves with
COMMIT
User or application commits the changes
Rollback if an error occurs
A program stops abnormally which triggers rollback
START TRANSACTION;
COMMIT;
SAVEPOINT <savepoint_name>;
ROLLBACK;
ROLLBACK TO SAVEPOINT <savepoint_name>;
Undo changes made by a transaction in the database
AKA "bookmark" where rollback can be performed without undo the entire transaction
Saves all changes made
Used to begin the transaction.
Changes will not be saved until COMMIT is used
Concurrency Control
Problems
Lost Updates
Uncommitted Data
Inconsistent Retrieval
Solutions
Scheduler
With Locking Methods
Lock Granularity
Lock Types
Binary Lock
Shared / Exclusive Lock
Operations
Read_Lock
Write_Lock
Unlock
Two-Phase Locking (2PL)
Deadlocks
Control Techniques
Detection
Prevention
Avoidance
Database Recovery Management
Database Recovery
Critical Errors:
Hardware / Software Failures
Human-Caused Incidents
Intentional
Unintentional
Natural Disaster
Transaction Recovery
Concept
Write-Ahead Log Protocol
Redundant Transaction Logs
Buffers
Checkpoints
Recovery Procedures
Deferred-Write (Deferred Update)
Record changes in the log first
Update the database at commit
Handling transaction failure
Recovery process using deferred-write
Write-Through (Immediate Update)
Immediate update to database
Update database at commit
Rollback for failed transactions
Recovery process with Writh-Through
Database Backup
Physical
Logical
Methods
Full
Incremental
Differential