DBMS Introduction to transaction concepts

Introduction to transaction concepts

Transaction is a logical unit of work that represents real-world events of any organisation or an enterprise whereas concurrency control is the management of concurrent transaction execution.  Transaction processing systems execute database transactions with large databases and hundreds of concurrent users, for example, railway and air reservations systems, banking system, credit card processing, stock market monitoring, super market inventory and checkouts and so on.


A transaction is a logical unit of work of database processing that includes one or more database access operations.
A transaction can be defined as an action or series of actions that is carried out by a single user or application program to perform operations for accessing the contents of the database. The operations can include retrieval, (Read), insertion (Write), deletion and modification.  A transaction must be either completed or aborted. 
It can either be embedded within an application program or can be specified interactively via a high-level query language such as SQL.  Its execution preserves the consistency of the database.
Each transaction should access shared data without interfering with the other transactions and whenever a transaction successfully completes its execution; its effect should be permanent.
This basic abstraction frees the database application programmer from the following concerns :

  •  Inconsistencies caused by conflicting updates from concurrent users.
  •  Partially completed transactions in the event of systems failure.
  •  User-directed undoing of transactions.
A transaction is a sequence of READ and WRITE actions that are grouped together to from a database access. A transaction may consist of a simple SELECT operation to generate a list of table contents, or it may consist of a series of related UPDATE command sequences.

A transaction can include the following basic database access operations:


Operations Descriptions
Retrive To retrive data stored ina database.
Insert To store new data in database.
Delete To delete existing data from database.
Update To modify existing data in database.
Commit To save the work done permanently.
Rollback To undo the work done.

Transaction that changes the contents of the database must alter the database from one consistent state to another. A consistent database state is one in which all data integrity constraints are satisfied.
To ensure database consistency, every transaction must begin with the database in a known consistent state.


Transaction Execution and Problems :

A transaction which successfully completes its execution is said to have been committed. Otherwise, the transaction is aborted.
Thus, if a committed transaction performs any update operation on the database, its effect must be reflected on the database even if there is a failure.

A transaction can be in one of the following states:
State Description
Active state  A transaction goes into an active state immediately after it starts execution, where it can issue READ and WRITE operations.
A transaction may be aborted when the transaction itself detects an error during execution which it cannot recover from, for example, a transaction trying to debit loan amount of an employee from his insufficient gross salary. A transaction may also be aborted before it has been committed due to system failure or any other circumstances beyond its control.
Partially committed When the transaction ends, it moves to the partially committed state.When the last state is reached.
To this point, some recovery protocols need to ensure that a system failure will not result in an inability to record the changes of the transaction permanently. Once this check is successful, the transaction is said to have reached its commit point and enters the committed state. 
Aborted When the normal execution can no longer be performed.
Failed or aborted transactions may be restarted later, either automatically or after being resubmitted by the user as new transactions.
Committed After successful completion of transaction.
A transaction is said to be in a committed state if it has partially committed and it can be ensured that it will never be aborted. 

Transaction Execution with SQL :

The American National Standards Institute (ANSI) has defined standards that govern SQL database transactions.Transaction support is provided by two SQL statements namely COMMIT and ROLLBACK.
The ANSI standards require that, when a transaction sequence is initiated by a user or an application program, it must continue through all succeeding SQL statements until one of the following four events occur :
  • A COMMIT statement is reached, in which case all changes are permanently recorded within the database. The COMMIT statement automatically ends the SQL transaction. The COMMIT operations indicates successful end-of-transaction. 
  • A ROLLBACK statement is reached, in which case all the changes are aborted and the database is rolled back to its previous consistent state. The ROLLBACK operation indicates unsuccessful end-of-transaction. 
  • The end of a program is successfully reached, in which case all changes are permanently recorded within the database. This action is equivalent to COMMIT. 
  • The program is abnormally terminated, in which case the changes made in the database are aborted and the database is rolled back to its previous consistent state. This action is equivalent to ROLLBACK.

Transaction Properties :

A transaction must have the following four properties, called ACID properties (also called ACIDITY of a transaction), to ensure that a database remains stable state after the transaction is executed:
  1. Atomicity. 
  2. Consistency.
  3. Isolation.
  4. Durability.
The atomicity property of a transaction requires that all operations of a transaction be completed, if not, the transaction is aborted. In other words, a transaction is treated as single, individual logical unit of work. 
Therefore, a transaction must execute and complete each operation in its logic before it commits its changes.As stated earlier, the transaction is considered as one operation even though there are multiple read and writes.  Thus, transaction completes or fails as one unit.
The atomicity property of transaction is ensured by the transaction recovery subsystem of a DBMS.
In the event of a system crash in the midst of transaction execution, the recovery techniques undo any effects of the transaction on the database.
Database consistency is the property that every transaction sees a consistent database instance.In other words, execution of a transaction must leave a database in either its prior stable state or a new stable state that reflects the new modifications (updates) made by the transaction.
If the transaction fails, the database must be returned to the state it was in prior to the execution of the failed transaction. 
If the transaction commits, the database must reflect the new changes.Thus, all resources are always in a consistent state.
The preservation of consistency is generally the responsibility of the programmers who write the database programs or of the DBMS module that enforces integrity constraints. 
A database program should be written in a way that guarantees that, if the database is in a consistent state before executing the transaction, it will be in a consistent state after the complete execution of the transaction, assuming that no interference with other transactions occur.
In other words, a transaction must transform the database from one consistent state to another consistent state.
 Isolation property of a transaction means that the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed. This property isolates transactions from one another.In other words, if a transaction T1 is being executed and is using the data item X, that data item cannot be accessed by any other transaction (T2………..Tn) until T1 ends. 
The isolation property is enforced by the concurrency control subsystem of the DBMS.
The durability property of transaction indicates the performance of the database's consistent state. It states that the changes made by a transaction are permanent. 
They cannot be lost by either a system failure or by the erroneous operation of a faulty transaction. When a transaction is completed, the database reaches a consistent state and that state cannot be lost, even in the event of system's failure.
Durability property is the responsibility of the recovery subsystem of the DBMS.

 Transaction Log (or Journal)  :

To support transaction processing, DBMSs maintain a transaction record of every change made to the database into a log (also called journal).
Log is a record of all transactions and the corresponding changes to the database. 
The information stored in the log is used by the DBMS for a recovery requirement triggered by a ROLLBACK statement, which is program's abnormal termination, a system (power or network) failure, or disk crash.
Some relational database management systems (RDBMSs) use the transaction log to recover a database forward to a currently consistent state. 
The DBMS automatically update the transaction log while executing transactions that modify the database. The transaction log stores before-and-after data about the database and any of the tables, rows and attribute values that participated in the transaction.
The beginning and the ending (COMMIT) of the transaction are also recorded in the transaction log.

For each transaction, the following data is recorded on the log:
  • A start-of-transaction marker.
  • The transaction identifier which could include who and where information.
  • The record identifiers which include the identifiers for the record occurrences.
  • The operation(s) performed on the records (for example, insert, delete, modify).
  • The previous value(s) of the modified data. This information is required for undoing the changes made by a partially completed transaction. It is called the undo log. Where the modification made by the transaction is the insertion of a new record, the previous values can be assumed to be null.
  • The updated value(s) of the modified record(s). This information is required for making sure that the changes made by a committed transaction are in fact reflected in the database and can be used to redo these modifications. This information is called the redo part of the log. In case the modification made by the transaction is the deletion of a record, the updated values can be assumed to be null.
  • A commit transaction marker if the transaction is committed, otherwise an abort or rollback transaction marker.

The log is written before any updates are made to the database.  This is called write-ahead log strategy. 
 In this strategy, a transaction is not allowed to modify the physical database until the undo portion of the log is written to stable database.
In case of a system failure, the DBMS examines the transaction log for all uncommitted or incomplete transactions and restores (ROLLBACK) the database to its previous state based on the information in the transaction log.

When the recovery process is completed, the DBMS writes in the transaction log all committed transactions that were not physically written to the physical database before the failure occurred.  If a ROLLBACK is issued before the termination of a transaction, the DBMS restores the database only for that particular transaction, rather than for all transactions, in order to maintain the durability of the previous transactions. In other words, committed transactions are not rolled back.

Share This Page on:

Ask Question