ACID Properties in DBMS
A transaction in a database has the following four properties, known as ACID properties. These properties are used to maintain the consistency of the database in the case of system failure and concurrent access:
1. Atomicity
2. Consistency
3. Isolation
4. Durability
Atomicity (Either all or none)
This property ensures that no transaction in the database occurs partially completed. This property also referred to as all or nothing rule i.e. all operations of a transaction is either fully completed or not execute at all.
Atomicity property involves abort and commit operations.
Abort: If a transaction in a
database aborts, then all the changes made are not visible to the user.
Commit: If a transaction in a database commits, then all the changes made are visible to the user.
Example:
Let’s suppose a transaction T through which you want to transfer money from account ‘X’ (source account) to account ‘Y’ (destination account). Assume that the account balance of X is 100 and Y is 400. This transaction T can be represented as:
| Transaction T |
| Read(X) …..(1) |
| X=X-50; …..(2) |
| Write(X) …..(3) |
| Read(Y) …..(4) |
| Y=Y+50 …..(5) |
| Write(Y) …..(6) |
| Commit; …..(7) |
In above transaction T, the steps 1 – 7 must be completed to ensure the atomicity property. If any failure happens before reaching COMMIT operation, then we must see old consistent values of X=100 and Y=400 using the rollback command. If no failure occurs, then we must see the new consistent values of X=50 and Y=450.
Consistency
This property states that every database must remain in a consistent state after the transaction. If any transaction violates the consistency rules of the database, the whole transaction will be rolled back, and the database will be restored to a consistent state with those rules. It is the responsibility of the application programmer and DBMS to maintain the consistency of the database.
Example:
Let’s suppose a transaction T through which you want to transfer money from account ‘X’ (source account) to account ‘Y’ (destination account). Assume that the account balance of X is 100 and Y is 400. This transaction T can be represented as:
| Transaction T |
| Read(X) …..(1) |
| X=X-50; …..(2) |
| Write(X) …..(3) |
| Read(Y) …..(4) |
| Y=Y+50 …..(5) |
| Write(Y) …..(6) |
| Commit; …..(7) |
In the above transaction, the old values of X and Y are 100 and 400 respectively i.e.,(X+Y=500 before the transaction). The operations or calculations that change X and Y values are X = X-50 and Y= Y+50. If the steps (1-7) in a transaction are executed successfully. And there is no other transaction which change values of X or Y during execution of T, then the new values of X and Y will be 50 and 450 i.e., (X+Y=500 after the transaction). So, the value of X+Y is 500 before and after the transaction. This shows the transaction is in the consistent state.
Isolation
Isolation property is needed when there are concurrent transactions in a database. This property states that a data of transaction T1 which is in execution, then transaction T2 cannot access the result of transaction T1 until the operations of a transaction T1 is completed. Or, we can say that a user cannot perform the same operation in multiple transactions at the same time. The execution of all transaction should be isolated from other transaction.
Example:
Let’s suppose a transaction T through which you want to transfer money from account ‘X’ (source account) to account ‘Y’ (destination account). Assume that the account balance of X is 100 and Y is 400. This transaction T can be represented as:
| Transaction T |
| Read(X) …..(1) |
| X=X-50; …..(2) |
| Write(X) …..(3) |
| Read(Y) …..(4) |
| Y=Y+50 …..(5) |
| Write(Y) …..(6) |
| Commit; …..(7) |
In above transaction T, first, we change X from 100 to 50. Then we change Y from 400 to 450. During T transaction, we should not allow other transactions to see or to use the old and the new values of X or Y before commit the statement, i.e., step 7. The transaction T should not be interfered by other transactions during its execution process.
Durability
This property states that, when all the operations of a transaction are completed successfully, then the changes made by the transaction saved to the database should be permanent. These changes are never lost if there occurs any kind of failure.
Example:
Let’s suppose a transaction T through which you want to transfer 50Rs from account ‘X’ (source account) to account ‘Y’ (destination account). Assume that the account balance of X is 100 and Y is 400. This transaction T can be represented as:
| Transaction T |
| Read(X) …..(1) |
| X=X-50; …..(2) |
| Write(X) …..(3) |
| Read(Y) …..(4) |
| Y=Y+50 …..(5) |
| Write(Y) …..(6) |
| Commit; …..(7) |
In above transaction T, we must see 50 and 450 as the current balances of account X and Y respectively after the commit statement has reached. If any failure occurs, we must not lose the updates after the commit statement.