Database transaction can be defined as the separate actions which are in either processed or yet to be processed state. All or Nothing is the defining feature of a database transaction. Each and every database transaction will contain several SQL statements. The common database transaction scenario can be noted using the acronym ACID.
Each letter in the acronym ACID is used to remember the database transaction properties.
- Atomicity can be defines that the database transactions are either whole or nothing. Every database transaction will fully succeed or end up as a failed transaction. All changes made in a database will be saved if the transaction is declared as success. The database will be rolled back to the previous state for a failed database transaction.
- Consistency of a database transaction is that the state of database will not be changed to accommodate any database transaction.
- Each and every transaction in a database is isolated from the other transactions
- Durability of the database transactions are defined as the change made in database is permanent. The changes will not be lost even during the power loss. However the data used in the database transactions can be altered using a separate transaction.
How RDBMS support the database transaction?
It is possible for RDBMS to identify the start and end of a database transaction. The RDBMS also logs all changes made by a transaction in a database. The database transactions are rolled back during the transaction failure in a database.
What is database transaction log?
The database transaction is not declared as complete until the changes made by the transaction are logged by the RDBMS. The recording the log of database transaction is called as commit in RDBMS. The process of committing a database log in a RDBMS is called as database transaction log.
What is Serializability?
The process of handling multiple database transactions by an operating is called as serializability. The serialized database transactions will follow anyone of the following process
- Schedule can be defined as the chronological execution sequence. More than one transaction is allowed in a schedule. Each database transaction present in a schedule will contain specific instructions and tasks.
- Serial Schedule in a database transaction is aligned one by one. A transaction will be executed after the completion of the previous instruction. Each transaction is executed based on the order in which they are executed. Serial schedule is the benchmark for the multiple database transaction.
In serial schedule, two mutually independent transactions are not forced into the waiting period. The waiting period in serial schedule can be resolved using parallel execution. However, parallel execution will produce varying and inconsistent results.
State of Database Transactions
Each database transaction will be in any one of these five different states
- Active
- Partially Committed
- Failed
- Aborted
- Committed
Active
Active state of the database transaction is a state at which the transaction is being executed. This is the initial stage of every database transaction.
Partially Committed
A database transaction can be classified as partially committed when the execution of final operation occurs.
Failed
Database transaction is classified as a failed transaction when the recovery system of the database is no longer working. Failed database transactions are not allowed to proceed further.
Aborted
After reaching failed state, database transactions will rollback all the operations to the original state. Transactions in the failed state will be aborted and recovery module will follow any one of these following instructions
- Re-start the transaction
- Kill the transaction
Committed
If all database transactions are completed successfully then it is said to be in committed stage. The transaction present in the committed are established on permanent basis and all the effects will be applied.