Transaction processing within the language of database management systems is when the SQL statements that are required to be executed in the database are grouped into units of transactions. This ensures that a single transaction runs an entire unit or not at all.
Take, for example, a bank transaction during which X transfers some money to Y. There should be a debit to X’s account and a corresponding credit to Y’s account. Imagine if there is a fatality during the transaction where the transaction that increases the bank balance of Y cannot be completed due to a disk crash or power outage etc., in which case the system will no longer be as consistent as it has been. you completed a transaction, i.e. a debit, but you did not complete the corresponding credit transaction due to a power outage or hard drive crash. Let’s also consider the alternative where both database operations are bundled into one transaction, so both are executed together or both are not executed at all. When all the SQL statements within a transaction have been executed successfully, the modifications are committed to the database. If there is any part of the transaction that fails, the modifications made to the database are “rolled back”.
In the case of the physical implementation of transaction control, the SQL statements are logged in a redo log file. On failure during any part of the transaction, the statements already executed in the database are retrieved from the redo log file and the transactions are rolled back.
The data in the database is kept in a cache and all modifications are made in the cache. In case there are no hitches during the execution of the SQL statements packed in a transaction, the modifications are committed to the database.
The properties of transaction processing systems arise from 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, for example, the train or plane ticket reservation system. The transaction made 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. In such cases, the data table is locked at the level of each table or at the level of each row. (Lock at table level or lock at row level).
RDBMS are also called transaction processing systems.