Concurrency Control deals with interleaved execution of more than one transaction. In the next article, we will see what is serializability and how to find whether a schedule is serializable or not.
A set of logically related operations is known as transaction. The main operations of a transaction are:
Note: It doesn’t always need to write it to database back it just writes the changes to buffer this is the reason where dirty read comes into picture
Let us take a debit transaction from an account which consists of following operations:
R(A);
A = A - 1000;
W(A);
Assume A’s value before starting of transaction is 5000.
But it may also be possible that transaction may fail after executing some of its operations. The failure can be because of hardware, software or power etc. For example, if debit transaction discussed above fails after executing operation 2, the value of A will remain 5000 in the database which is not acceptable by the bank. To avoid this, Database has two important operations:
Properties of a transaction
1. Atomicity: As a transaction is 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 debit transaction fails after executing operation 1 and 2 then its new value 4000 will not be updated in the database which leads to inconsistency.
2. Consistency: If operations of debit and credit transactions on same account are executed concurrently, it may leave database in an inconsistent state.
Table 1 3. Isolation: Result of a transaction should not be visible to others before 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 rollback T2 as well because it is using value produced by T1. So a transaction results are not made visible to other transactions before it commits.
4. Durable: Once 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, bank can’t say that the update has been lost. To avoid this problem, multiple copies of database are stored at different locations.
A schedule is a series of operations from one or more transactions. A schedule can be of two types:
Q. Consider the following transaction involving two bank accounts x and y: [GATE 2015]
1. read(x);
2. x := x – 50;
3. write(x);
4. read(y);
5. y := y + 50;
6. write(y);
The constraint that the sum of the accounts x and y should remain constant is that of?
(a) Atomicity
(b) Consistency
(c) Isolation
(d) Durability
Ans: (b)
Solution: As discussed in properties of transactions, consistency properties says that sum of accounts x and y should remain constant before starting and after completion of transaction. So, the correct answer is B.
62 videos|66 docs|35 tests
|
1. What is concurrency control in DBMS? |
2. Why is concurrency control important in DBMS? |
3. What are the different types of concurrency control techniques in DBMS? |
4. How does two-phase locking (2PL) work as a concurrency control technique in DBMS? |
5. What is optimistic concurrency control in DBMS? |
|
Explore Courses for Computer Science Engineering (CSE) exam
|