At its root, a single database transaction is a logical unit of DBMS processing that may execute one or multiple DB access operations. In other words, database transactions may represent the real-world events of the enterprises. There are various types of databases with different structures and functional approaches. However, all types of databases are meant to access operations that are held between start and end transactional statements. All these activities are considered to be a single logical transaction. The database is inconsistent during such transactions.
Basics – DBMS transactions
A transaction is a unit of the program, execution of which may or may not change the structure and content of the given database. As we had seen above, a transaction from start to end is conducted as a single unit. If the database is not updated with the database operations, but it is only meant to fetch data from the DB, then it is denoted as a read-only transaction. All the database transactions need to be atomic, consistent, isolated, and durable (ACID). If the database was in an inconsistent state pre-transaction, then it will remain in an inconsistent state also after the transaction. Maintaining concurrency is very crucial in terms of database management, and the aftereffects of not do so maybe:
- System crash or hardware failure.
- Concurrent execution of the same transaction.
- Slow performance or deadlocks.
Different states of DBMS transactions
– The active State
In this state of operational sequence, the transaction enters into an active state while the execution begins. During this stage, the write or read operations can be executed. It also does not matter as to which step is in active execution, unless the transaction executes, it will remain in the active state.
– Committed State
When a transaction gets to the committed state, it means it had completed the execution already successfully. More than that, all the changes made are properly recorded in the database. Usually, all the transactions go from the partially committed state to committed states once executed successfully.
– Partially Committed
From the active state, a transaction next gets into the state of being partially committed if there are both read and write operations to run in the transaction.
– Failed State
If a transaction is attempted to run, but failure occurs either has a hardware failure or a software failure during the active state, then the transaction gets into failed status. If any single checks fail, if the transaction gets aborted, it can be considered as failed.
– Terminated State
The transaction state is changed to terminated when some transactions which leave the system cannot be started again.
The flow goes as below:
1. Once the transaction executes, it becomes an active state.
– At this state, the READ or WRITE operations can be issued.
2. Once the operation is complete, the transactions get into a partially committed state.
3. At the next phase, recovery protocols start to ensure that any instance of system failure may not result in any inability to record the changes in the transaction. If this checking is successful, then the transaction gets committed, and it enters into a committed state.
– If this check fails, then it gets into a failed state. Also, if the transaction gets aborted while being active, it gets into a failed state. In this case, the transaction has to be rolled back to undo its effect on the database.
4. Terminated state means the transaction leaves the system.
As we had seen above, ACID denotes Atomicity, Consistency, Isolation, and Durability of the database. In common, ACID properties are essential to ensure database reliability and integrity during a transaction. Now, let us explore the definition of each as described at the RemoteDBA.com website.
1. Atomicity: Every transaction act as a single operational unit. It is either executed in full or does not execute at all. There is no partial execution of a transaction. So, this property makes sure that either the entire operations are recorded in the database, or no changes are made.
E.g., Suppose in a banking transaction, suppose if Person A is transferring 100$ from the balance to Person B’s account. There are two operations, debiting from A’s account and crediting to B’s account. If the first runs and second fail, then it is not acceptable in banking transactions. Atomicity ensures it.
2. Consistency: It helps to preserve the database consistency by ensuring that the transaction executions are taking place in isolation. It means there are simultaneous transactions are running when one is already executing.
3. Isolation: For each transaction pairs or groups, in succession, one transaction gets executed only once the logical previous one finishes execution.
4. Durability: After successfully completing the transaction the change it makes to the database needs to permanent even when there is a system failure. The recovery management of the given system will make sure the transaction’s durability.
The schedule is the process of creating a group of transactions to run parallel to one another but executing them one after another. The schedule needs to preserve the right order in which the instructions appear in each of the transactions. If there are two transactions executed simultaneously, then there is a chance that the result of one among them may affect the output of the other one. The parallel execution of transactions is inevitable in any database. However, parallel execution of database transactions is only permitted when there are some equivalence relations among the transactions which are being executed in parallel.
1. Result equivalence
If there are two schedules displaying the same output, this schedule is called ‘result equivalent.’
2. View equivalence
This type of transaction occurs when transactions in both given schedules perform a similar action. For example, one transaction may insert the product details into the product table, whereas the other transaction may insert the product details in the archive table. Here, transactions are the same but done on different tables.
3. Conflict Equivalence
In conflict equivalence, there may be two transactions that update the same data set. There may be conflict amongst the transactions as the order of schedule may affect the output.
A database administrator needs to have a theoretical understanding of all these to ensure that your database is fundamentally strong and technically perfect.