Database transaction interview questions covered in this section:
What is a Transaction?
What are the different states of Transaction?
What are the main properties of Transaction?
What are the advantages of a Transaction?
Do all storage engines support transaction?
What are the ACID properties of a database system?
An independent and logical unit of work that is performed to complete read and write operations on a database and in an RDBMS is known as Transaction. A Transaction may contain several sub-tasks and after a complete transaction, the overall data remain consistent.
Let’s take an example of a Banking System where Money is transferred from one account to another.
Step 1: Account A has amount = a
Step 2: Account B has an amount = b
Step 3: The total amount that A and B have combined = (a+b)
Step 4: Now A transfers 1000 rupees to B.
Step 5: So After transaction A has amount = a-1000
Step 6: and After transaction B has amount = b+1000
and after transaction combined amount that A and B have is
= (a-1000) + (b+1000)
= (a+b) and this the amount total that A and B have before the transaction.
States of Transaction
A transaction in its life cycle may have in different states at different time. There are the following states that a transaction may have:
- Partially Committed
When a transaction starts it is in Active state. The very initial stage of a transaction.
After initialization operation starts and the state is known as partially committed. The partial word is added because the operation is not yet completed and the transaction does not reflect the data manipulation results.
A transaction is failed by many reasons like any checks made by the database system, fails OR Database systems fails or system crashed. In such cases transaction is called failed and can not proceed further.
When any checks fail then It is necessary to abort the transaction and kill it. In this phase, the changes made by write queries undone and database is backed to initial state.
Committed is a state when all the operations in a transaction completed and unit of the task done completely. After commit, changes can not be undone and can be reflected in the result.
A single independent logical unit in a database or a transaction need to maintain consistency during processing. So there are some standard and norms that need to follow by these transactions and these are the mandatory features of any transactions. These features are known as ACID properties. ACID means a system must maintain Atomicity, Consistency, Isolation, and Durability.
There is no intermediate state between all and none. A transaction is an atomic unit and either it’s executed all the operations or none of them executed that means either the whole transactions take place or none of them happened. If some of them executed and the transaction fails then in partially committed condition then Roll Back occurs and all the parts done operations, undone.
Let’s say that the amount of data must be consistent before and after any transaction. This is very important to maintain the integrity of data during a transaction. An example given above can explain the transaction.
A transaction is an independent thread of all the other transactions. Failure of success of any transaction does not affect the presence of any other transaction. There may be many transactions are executing at a time. While multiple transactions are executing on a system the integrity of data is maintained.
After successful completion of any transaction, the data is saved to disk and should be persistent. The changes become permanent after a transaction committed. Even after system failures and several restarts.