Transaction States
Transactions can be implemented using SQL queries and Servers. In the diagram, you can see how transaction states work.
The transaction has four properties. These are used to maintain consistency in a database, before and after the transaction.
Property of Transaction:
- Atomicity
- Consistency
- Isolation
- Durability
Atomicity
- States that all operations of the transaction take place at once if not, the transactions are aborted.
- There is no midway, i.e., the transaction cannot occur partially. Each transaction is treated as one unit and either run to completion or is not executed at all.
- Atomicity involves the following two operations:
- Abort: If a transaction aborts, then all the changes made are not visible.
- Commit: If a transaction commits then all the changes made are visible.
Consistency
- The integrity constraints are maintained so that the database is consistent before and after the transaction.
- The execution of a transaction will leave a database in either its prior stable state or anew stable state.
- The consistent property of database states that every transaction sees a consistent database instance.
- The transaction is used to transform the database from one consistent state to another consistent state.
Isolation
- It shows that the data which is used at the time of execution of a transaction cannot be used by the second transaction until the first one is completed.
- In isolation, if the transaction T1 is being executed and using the data item X, then that data item can’t be accessed by any other transaction T2 until the transaction T1ends.
- The concurrency control subsystem of the DBMS enforced the isolation property
Durability
- The durability property is used to indicate the performance of the database’s consistent state. It states that the transaction made the permanent changes.
- They cannot be lost by the erroneous operation of a faulty transaction or by the system failure. When a transaction is completed, then the database reaches a state known as the consistent state. That consistent state cannot be lost, even in the event of a system’s failure.
- The recovery subsystem of the DBMS has the responsibility of Durability property.
Transaction Management
Transactions are a set of operations used to perform a logical set of work. It is the bundle of all the instructions of a logical operation. A transaction usually means that the data in the database has changed. One of the major uses of DBMS is to protect the user’s data from system failures. It is done by ensuring that all the data is restored to a consistent state when the computer is restarted after a crash. The transaction is any one execution of the user program in a DBMS. One of the important properties of the transaction is that it contains a finite number of steps. Executing the same program multiple times will generate multiple transactions.
Example: Consider the following example of transaction operations to be performed to withdraw cash from an ATM vestibule.
Steps for ATM Transaction
- Transaction Start.
- Insert your ATM card.
- Select a language for your transaction.
- Select the Savings Account option.
- Enter the amount you want to withdraw.
- Enter your secret pin.
- Wait for some time for processing.
- Collect your Cash.
- Transaction Completed.
A transaction can include the following basic database access operation.
- Read/Access data (R): Accessing the database item from disk (where the database stored data) to memory variable.
- Write/Change data (W): Write the data item from the memory variable to the disk.
- Commit: Commit is a transaction control language that is used to permanently save the changes done in a transaction
Example: Transfer of 50₹ from Account A to Account B. Initially A= 500₹, B= 800₹. This data is brought to RAM from Hard Disk.
R(A) -- 500 // Accessed from RAM.
A = A-50 // Deducting 50₹ from A.
W(A)--450 // Updated in RAM.
R(B) -- 800 // Accessed from RAM.
B=B+50 // 50₹ is added to B's Account.
W(B) --850 // Updated in RAM.
commit // The data in RAM is taken back to Hard Disk.
Note: The updated value of Account A = 450₹ and Account B = 850₹.
All instructions before committing come under a partially committed state and are stored in RAM. When the commit is read the data is fully accepted and is stored on a Hard Disk.
If the transaction is failed anywhere before committing we have to go back and start from the beginning. We can’t continue from the same state. This is known as Roll Back.