Please enable JavaScript.
Coggle requires JavaScript to display documents.
Database Transaction Management (Chapter 5) - Coggle Diagram
Database Transaction
Management
(Chapter 5)
5.1.1 Describe transaction system
A transaction is a logical unit of work that must be entirely completed or aborted.
A transaction may consist of:
1.A simple SELECT statement to list the contents of DB table
2.A series of UPDATE statements to change attributes values in various database
3.A series of INSERT statements to add rows to one or more tables
4.A combination of SELECT, UPDATE, and INSERT statements
Transaction system refers to set of mechanisms and processes that ensure database transactions are executed reliably.
5.1.2 Identify the transaction processing systems category
Batch Transaction Processing System
Transactions are collected and processed together in a batch.
Example : Payroll System, Generation of Bills, Cheque Clearing, etc
Advantages / Disadvantages
1.Efficient for large volumes of transactions
2.Reduces transaction management effort by grouping similar operations
3.Data takes time to be processed
4.Difficult to identify and correcting errors due to transactions are processed after collection.
Online Transaction Processing System
Online transactions are processed individually and updated at the time of the event (real-time).
Example : Online Banking System, Ticketing and Reservation System, etc
Advantages / Disadvantages :
1.Immediate transaction processing and results
2.Can handle multiple transactions simultaneously
3.High demand on system resources to maintain data consistency and real-time processing
Real-Time Transaction Processing System
Transactions are processed individually and updated at the time of the event (real-time) with strict time constraints.
Example : Airline Ticketing Reservation, Point of Sales Terminals (POS), etc.
Advantages / Disadvantages
1.Immediate transaction processing and results
2.Ensure up-to-date and accurate data due to immediate processing
3.Complex to design and implement due to the need for immediate processing and high reliability
5.1.3 Describe the properties of database transaction
Atomicity (A)
Ensures that all operations within a transaction are completed successfully.
Consistency (C)
Ensures that a transaction takes the database from one valid or consistent state to another.
Isolation (I)
Ensures that transactions are executed in isolation or independent from one another
Durability (D)
Ensures that once a transaction changes are done and committed, its effects are permanent and will survive system failures
5.1.4 Perform transaction of a given database using SQL statements
The ANSI has defined standards that govern SQL database transactions.
The following commands are used to control transactions
COMMIT − To save the changes.
SAVEPOINT − To create points within the groups of transactions in which to ROLLBACK.
ROLLBACK − To roll back the changes
5.1.5 Use START TRANSACTION and COMMIT statements
START TRANSACTION
It is used to begin the transaction. Changes made after this point won’t be saved until COMMIT is used.
START TRANSACTION;
COMMIT
The COMMIT command saves all changes made by a transaction to the database since the last COMMIT or ROLLBACK.
COMMIT;
SAVEPOINT
A SAVEPOINT is a point within a transaction in SQL that can be defined as a "bookmark" to which roll back is performed without undoing the entire transaction.
SAVEPOINT savepoint_name;
ROLLBACK
The ROLLBACK command is used to undo changes made by a transaction in the database.
ROLLBACK;
5.1.6 State the purpose of concurrency control
Concurrency Control
Preserve Data Integrity
Prevents issues like lost updates, where changes from one transaction might overwrite another, and ensures accurate data.
Isolate Transactions
Ensures each transaction is executed in isolation so that they do not interfere with each other.
Prevent Inconsistent Retrievals
Ensures data is read accurately during concurrent transactions, avoiding erroneous data being accessed.
5.1.7 Describe the problems of concurrency control
Lost Update Problems
The lost update problems occur when two concurrent transitions (T1 & T2) are updating the same data element and one of the update is lost (overwrite by other transaction).
Uncommitted Data
The uncommitted data problems occur when one transition (T1) makes changes to the data, but those changes are not yet saved or “committed”. Meanwhile, another transaction (T2) accesses this uncommitted data and uses it in its operation.
If T1 then encounters an error and rolls back, all T1's changes are undone. However, since T2 has already used the uncommitted data, it creates an inconsistency because T2 is relying on data that no longer exists.
Scheduler
The Scheduler is a special DBMS process that manages how multiple transactions are executed to prevent conflicts and maintain data consistency.
5.1.8 Define the concurrency control with locking methods
Locking methods
Locking methods are commonly used in databases to manage concurrent transactions.
Pessimistic locking
Pessimistic locking is a method used when it's assumed that conflicts between transactions are likely.
5.1.9 Database Recovery Management
Database recovery
Database recovery restores a database from an inconsistent state to a previously stable, consistent state.
Transaction recovery
Database transaction recovery uses data in the transaction log to recover a database from inconsistent state to a previously stable, consistent state.
Database backup
Database Backup is the process of creating a copy of the database data and structure, stored in a separate location.