Properties of a Transaction
Atomicity: As a transaction is a set of logically related operations, either all of them should be executed or none. A debit transaction discussed above should either execute all three operations or none. If the debit transaction fails after executing operations 1 and 2 then its new value of 4000 will not be updated in the database which leads to inconsistency.
Consistency: If operations of debit and credit transactions on the same account are executed concurrently, it may leave the database in an inconsistent state.
- For Example, with T1 (debit of Rs. 1000 from A) and T2 (credit of 500 to A) executing concurrently, the database reaches an inconsistent state.
- Let us assume the Account balance of A is Rs. 5000. T1 reads A(5000) and stores the value in its local buffer space. Then T2 reads A(5000) and also stores the value in its local buffer space.
- T1 performs A=A-1000 (5000-1000=4000) and 4000 is stored in T1 buffer space. Then T2 performs A=A+500 (5000+500=5500) and 5500 is stored in the T2 buffer space. T1 writes the value from its buffer back to the database.
- A’s value is updated to 4000 in the database and then T2 writes the value from its buffer back to the database. A’s value is updated to 5500 which shows that the effect of the debit transaction is lost and the database has become inconsistent.
- To maintain consistency of the database, we need concurrency control protocols which will be discussed in the next article. The operations of T1 and T2 with their buffers and database have been shown in Table 1.
| T1 | T1’s buffer space | T2 | T2’s Buffer Space | Database |
|---|---|---|---|---|
| A=5000 | ||||
| R(A); | A=5000 | A=5000 | ||
| A=5000 | R(A); | A=5000 | A=5000 | |
| A=A-1000; | A=4000 | A=5000 | A=5000 | |
| A=4000 | A=A+500; | A=5500 | ||
| W(A); | A=5500 | A=4000 | ||
| W(A); | A=5500 |
Isolation: The result of a transaction should not be visible to others before the transaction is committed. For example, let us assume that A’s balance is Rs. 5000 and T1 debits Rs. 1000 from A. A’s new balance will be 4000. If T2 credits Rs. 500 to A’s new balance, A will become 4500, and after this T1 fails. Then we have to roll back T2 as well because it is using the value produced by T1. So transaction results are not made visible to other transactions before it commits.
Durable: Once the database has committed a transaction, the changes made by the transaction should be permanent. e.g.; If a person has credited $500000 to his account, the bank can’t say that the update has been lost. To avoid this problem, multiple copies of the database are stored at different locations.
Concurrency Control in DBMS
Concurrency control is a very important concept of DBMS which ensures the simultaneous execution or manipulation of data by several processes or user without resulting in data inconsistency. Concurrency Control deals with interleaved execution of more than one transaction.