Transaction Management in DBMS: What are ACID Properties?
One or more database access operations are part of a Database Transaction, which is a logical unit of activity in a database management system (DBMS). Database transactions, to put it succinctly, are the real-world occurrences that each business must deal with.
In DBMS, all different kinds of database access operations that are carried out while the beginning and end transaction statements are being processed are grouped together and treated as a single logical transaction. The database will be inconsistent while the transaction is being processed. The state does not transition from one consistent state to another until after the database transaction has been committed.
Transactions in a Database Transactions in a Database
You will acquire the following knowledge through the course of this tutorial:
Facts about Database Transactions
A transaction is a unit of a programme whose execution may or may not alter the data contained in a database. Transactions are typically used in relational databases.
The idea of a transaction in DBMS is something that gets carried out all at once.
This kind of transaction is known as a read-only transaction, and it is characterised by database activities that do not update the database but instead simply obtain data.
It is possible for a database to transition from one CONSISTENT STATE to another when a transaction completes successfully. DBMS transactions are required to be atomic, consistent, isolated, and durable.
If the database was already in an inconsistent state when a transaction was performed on it, then after the transaction it would continue in the same inconsistent state.
Why do you need concurrency in Transactions?
A database is a shared resource that can be accessed by several users. It is simultaneously utilised by a large number of users and processes. For instance, the banking system, the reservation systems for trains and aeroplanes, the monitoring of the stock market, the inventory and checkout systems in supermarkets, etc.
States of Transactions
The various states of a transaction concept in DBMS are listed below:
| State | Transaction types |
|---|---|
| Active State | A transaction enters into an active state when the execution process begins. During this state read or write operations can be performed. |
| Partially Committed | A transaction goes into the partially committed state after the end of a transaction. |
| Committed State | When the transaction is committed to state, it has already completed its execution successfully. Moreover, all of its changes are recorded to the database permanently. |
| Failed State | A transaction considers failed when any one of the checks fails or if the transaction is aborted while it is in the active state. |
| Terminated State | State of transaction reaches terminated state when certain transactions which are leaving the system can’t be restarted. |
Failure to properly manage concurrent access may result in problems such as:
Failures in the hardware and the software of the system
Multiple copies of the same transaction being executed concurrently, a standstill, or poor performance
Let’s take a look at a state transition diagram that illustrates how a transaction can travel between all of these different states.
After a transaction has entered the execution phase, it is considered to be active. It is able to do either a READ or WRITE action.
The transactions enter a state of being partially committed once the READ and WRITE operations have been completed successfully.
Next, some recovery mechanisms need to be implemented to guarantee that a failure of the system will not result in an inability to record modifications to the transaction in a way that is irreversible. The transaction will be considered committed and will go into the committed state if the check is successful.
In the event that the check generates an error, the transaction will be moved to the Failed state.
When a transaction is cancelled while it is in the active state, the transaction will transition to the failed state. It is necessary to undo the effect that the transaction’s write operations had on the database by rolling back the transaction.
When a transaction reaches the terminated state, it means that it has left the system.
What are ACID Properties?
During transaction processing, ACID Properties are utilised in order to ensure that the database’s integrity is preserved. Atomicity, Consistency, Isolation, and Durability are the abbreviations used in relational database management systems (DBMS).
Atomicity refers to the concept that a transaction represents a single unit of execution. Either you carry out the action in its entirety or you do not carry it out at all. There is no such thing as a partial death sentence.
Maintaining consistency requires that once a transaction has been processed, the system should transition from one consistent state to another.
Isolation: Each individual transaction ought to be carried out separately from all other transactions (no Locks). During the execution of concurrent transactions, the intermediate transaction outcomes from transactions that are being conducted simultaneously ought not to be made available to one another. (Level 0,1,2,3)
Durability: After a transaction has been successfully completed, the changes that were made to the database should be able to persist. Even in the event that there are problems with the system.
Property of ACID in DBMS illustrated with an example:
An example of the ACID property in DBMS can be found below:
Transaction 1: Start with X equal to X plus 50, and Y equal to Y minus 50
Transaction 2: Start with X = 1.1 * X, and End with Y = 1.1 * Y
The first transaction is a fifty dollar wire transfer from account X to account Y.
The second transaction will apply an interest payment of ten percent to all of the accounts.
Types of Transactions
If you submit both transactions at the same time, there is no certainty that Transaction 1 will complete before Transaction 2 or that Transaction 2 will process before Transaction 1. Regardless of the sequence in which the transactions take place, the result must be as if they are carried out one after the other in sequential order.
Transaction Categories Determined by Their Applicability to Various Use Cases
a non-distributed version against a distributed version
Accounting for financial dealings
Transactions Comparing the timing of actions in real time versus in batches
The Restricted Action model with Two Steps, Based on Structure
Flat transactions, also known as simple transactions, are made up of a series of primitive operations that are carried out between a start operation and an end operation.
A transaction that contains other transactions is referred to as a nested transaction.
Workflow
What is a Schedule?
A procedure that creates a single group out of numerous concurrent transactions and then executes each of those groups in turn is called a schedule. It ought to maintain the order in which the instructions are presented within each transaction. It is possible for the outcome of one transaction to have an effect on the outcome of another transaction if they are both carried out at the same time.
Example
The initial quantity of the product is 10.
Update the product quantity to 50 in the first transaction.
Read the quantity of the product in the second transaction.
In the event that Transaction 2 is carried out before Transaction 1, then information that is no longer accurate regarding the product quantity will be read. As a result, timetables are necessary.
It is impossible to avoid parallel execution in a database. On the other hand, parallel execution is allowed if there is a relation of equivalence between the different transactions that are happening at the same time. There are three different types of equivalences.
RELATIONSHIP OF RESULTS:
It is referred to as a result equivalent schedule when two different schedules reflect the same outcome after being executed. It’s possible that they’ll provide the same answer for one set of variables but a different outcome for another group of data. As an illustration, one transaction may update the product quantity, while another may update the details of the consumer.
View Equivalence
View The two schedules are said to be equivalent if the transactions they include both carry out the same or very similar tasks. As an illustration, the product information are inserted into the product table by one transaction, while the archive table is updated by another transaction. However, the tables have been altered to include the new information.
CONFLICT Equivalence
In this instance, two separate transactions will view or update the same collection of data. There is competition among the transactions because the sequence in which they are carried out will influence the results.
What exactly is the term “serializability”?
The practise of searching for a concurrent schedule whose output is equivalent to a serial schedule—one in which transactions are carried out one after the other—is known as serializability. There are two distinct types of serializability, each of which is determined by the nature of the schedules involved:
Summary:
In a database management system (DBMS), transaction management is a logical unit of processing that involves one or more database access operations.
A transaction is a software unit, often known as a piece of code, the execution of which may or may not alter the data contained within a database.
Failure to properly manage concurrent access could result in difficulties such as broken hardware and unstable software.
Important transaction statuses include Active, Partially Committed, Committed, and Failed in addition to Terminate and Terminate.
Atomicity, Consistency, Isolation, and Durability are the four components that make up the entire acronym for ACID Properties in DBMS.
Base on Application Areas, Action, and Structure are the three types of transactions that can be performed by a DBMS.
A procedure that creates a single group out of numerous concurrent transactions and then executes each of those groups in turn is called a schedule.
The process of searching for a concurrent schedule whose output is equivalent to a serial schedule — one in which transactions are carried out one after the other — is what is meant by “serializability.”