A database transaction is a concept processing the logical units of data in the given DBMS, which may most of the time entail more than one database access for better operations. So, at the baseline, a database transaction is the functioning of an enterprise/enterprise application’s daily real-time operations. All types of database operations between the beginning and end of the applications can be considered a single transaction to be run logically in this approach. At the time of the transaction, the database remains inconsistent. Once the database gets committed, the state changes from one consistent to another consistent state.
Some facts about database transaction
- A database transaction is the program unit for which may execute to change or not change the content of the database.
- Each transaction in the DBMS is executed in a single unit.
- If the DBMS operations are not updated in the database but retrieve the data, such a transaction is known as a read-only transaction.
- A successful database transaction will change the database from a consistent state to another one.
- DBMS transactions should be atomic, consistent, isolated, and durable (ACID).
- If a database was in an inconsistent state before the execution of a transaction, it should remain in an inconsistent state once the transaction is executed.
Need for concurrency in transaction
A database is a shared resource, which is accessed by many applications. It is also used by different users and processed simultaneously. Say, for example, in a banking system, reservation system, stock market, supermarket, checkouts, and so on. So, it is essential to manage concurrent access effectively, and failing to do so will end up in system crashes and hardware failures.
There are various states of concurrent transactions in DBMS. Let us have an overview of the same.
|Active State||In this state, a transaction enters an active phase at the beginning of the execution. While at this state, read or write operations are performed.|
|Partially committed state||At this phase, a transaction goes into a partially committed son after the end of the transaction.|
|Committed state||While the transaction gets into the committed state, it has already completed the execution. All of the changes it makes get recorded into the DB permanently.|
|Failed state||In this state, a transaction is considered to be failed while any of the checks fail or the transaction is aborted while it was in active state.|
|Terminated stage||At this stage, the transaction moves onto the terminated state while certain transactions leave the system and cannot be restarted.|
If you doubt the database administration process and get expert assistance in database administration, RemoteDBA can offer you reliable services in DB management.
In a standard database, ACID properties are mandatory to maintain the database integrity during various transaction processing states. As we have seen above, ACID stands for:
- Isolation, and
Let us have a quick overview of these properties.
Atomicity: Any given transaction remains as a single operational unit. You have to either execute it fully or do not execute it at all. There is no scope of partial execution. This is known as the atomicity of the database.
Consistency: After executing a transaction, it must move from a consistent state to another consistent state.
Isolation: Each transaction must be executed in isolation. There should not be any locks with other transactions. During the execution of concurrent transactions, the intermediate transactions result from simultaneous transactions and should be available for each other.
Durability: After completing a transaction successfully, changes in the give DB should persist even in a transactional failure.
What is the schedule?
A schedule is a process of creating a single group of parallel transactions. In a schedule, each transaction is executed one by one. When executing this way, it helps preserve the order in which instructions come up in each transaction. If two or more transactions execute simultaneously, then the result of one transaction may affect that of another one.
Concurrent execution of transactions is inevitable in any database. Parallel execution is ideally permitted when there are two equivalent relations among the transactions executed simultaneously. These equivalences are of three distinct types.
If two schedules display similar results after the execution, then it is known as result equivalent schedules. It may offer the same results for some of the values and different results for another set. For example, one transaction will update a product quantity, whereas another one may update the customer details.
This type of equivalence happens while the transactions in both schedules perform the same action. For example, one transaction may insert the product details in a table, whereas another transaction may insert the archive table’s product details. Transactions remain the same, whereas the tables may be different.
Here, two transactions may view/update the same data set. Here, there is a conflict among the transactions as execution order may affect its output.
Serializability is the search process for concurrent schedules, whereas its output is equal to a serial schedule in which each transaction executes one after another. Based on the type of these schedules executed, there are two types of serializability.
Let us have a recap. Transaction management in database operations is the processing of a program’s logical unit, which entails one or more database operations. The transaction is the program which may or may not change the database content ultimately. Not managing the concurrent access based on the above principles will cause significant damage to the system or end up in a system crash. As we have seen above, there are active, committed, partially committed, failed, and terminated states of transactions.
ACID properties define the capability of a database, and the full form of ACID in terms of DBMS operations is Atomicity, Consistency, Isolation, and Durability. There are three types of DBMS transactions based on various applications as areas, action, and structure. A schedule is a process, which will create a group of parallel transactions by executing this one by one. Serializability is the process of searching for concurrent schedules for which the output is equal to the serial execution of the schedules in which the transactions are executed one after another.