Transaction Processing in Database Management Systems

Transaction processing within the parlance of data base management systems is when SQL statements that are required to be executed on the database are grouped into units of transactions. This ensures that a single transaction executes a whole unit or does not execute at all.

Let us for example take a banking transaction during which X transfers some money to Y. There should be a debit of X’s account and a corresponding credit into Y’s account. Imagine if there is a fatality during the transaction where the transaction which increases the bank balance of Y cannot be completed due to a disk crash or due to a power failure etc., in such a case the system will cease to be consistent as it has completed a transaction namely a debit, but did not complete the corresponding credit transaction due to a power failure or a hard disk crash. Let us also consider the alternative where both these database operations are bundled into a transaction, so either both execute together or both do not execute at all. When all SQL statements inside a transaction have run successfully the modifications are committed to the database. If there is some portion of the transaction which fails then the modifications done to the database are “rolled back”.

In the case of physical implementation of control of transactions, SQL statements are logged into a redo log file. In case of failure during any portion of the transaction, the statements already executed on the database are recovered from the redo log file and the transactions are reversed.

The data from the database is held in a cache and all the modifications are made to the cache. In case there is no mishap during the execution of the SQL statements bundled in a transaction, the modifications are committed to the database.

The properties of a transaction processing systems arise out of the guarantee of ACID properties namely Atomicity, Consistency, Isolation and Durability.

In the case of Online transaction processing systems, there are many users simultaneously accessing the database example Railway or Air Ticket reservation system. The transaction done by one user will affect another user with respect to the number of available seats. Online transaction processing systems are transaction processing systems that have to function under concurrent access. During such cases the data table is locked at the level of each table or at the level of each row. (Table level locking or row level locking).

RDBMSs are also called as Transaction processing systems.