Need for Concurrency

Need for Concurrency  

AU: MAY-17,19, Marks 15

Following are the purposes of concurrency control -

• To ensure isolation

• To resolve read-write or write-write conflicts

• To preserve consistency of database

• Concurrent execution of transactions over shared database creates several data integrity and consistency problems - these are

(1) Lost update problem: This problem occurs when two transactions that access the same database items have their operations interleaved in a way that makes the value of some database item incorrect.

For example - Consider following transactions

(1) Salary of Employee is read during transaction T1.

(2) Salary of Employee is read by another transaction T2.

(3) During transaction T1, the salary is incremented by 200

(4) During transaction T2, the salary is incremented by 500

The result of the above sequence is that the update made by transaction T1 is completely lost. Therefor this problem is called as lost update problem.

(2) Dirty read or Uncommited read problem: The dirty read is a situation in which one transaction reads the data immediately after the write operation of previous transaction

For example - Consider following transactions -

Assume initially salary is = 1000

(1) At the time t1, the transaction T2 updates the salary to 1200

(2) This salary is read at time t2 by transaction T1. Obviously it is 1200

(3) But at the time t3, the transaction T2 performs Rollback by undoing the changes made by T1 and T2 at time t1 and t2.

(4) Thus the salary again becomes = 1000. This situation leads to Dirty Read or Uncommited Read because here the read made at time t2(immediately after roid update of another transaction) becomes a dirty read.

(3) Non-repeatable read problem

This problem is also known as inconsistent analysis problem. This problem occurs when a particular transaction sees two different values for the same row within its lifetime. For example-

(1) At time t₁, the transaction T₁ reads the salary as 1000

(2) At time t₂ the transaction T₂ reads the same salary as 1000 and updates it to 1200

(3) Then at time t₃, the transaction T₂ gets committed.

(4) Now when the transaction T₁ reads the same salary at time t₄, it gets different value than what it had read at time t₁. Now, transaction T₁, cannot repeat its reading operation. Thus inconsistent values are obtained.

Hence the name of this problem is non-repeatable read or inconsistent analysis problem.

(4) Phantom read problem

The phantom read problem is a special case of non repeatable read problem.

This is a problem in which one of the transaction makes the changes in the database system and due to these changes another transaction can not read the data item which it has read just recently. For example -

(1) At time t₁, the transaction T₁ reads the value of salary as 1000

(2) At time t₂, the transaction T₂ reads the value of the same salary as 1000

(3) At time t₃, the transaction T₁ deletes the variable salary.

(4) Now at time t₄, when T₂ again reads the salary it gets error. Now transaction T₂ can not identify the reason why it is not getting the salary value which is read just few time back.

This problem occurs due to changes in the database and is called phantom read problem.

Review Questions

1. Discuss the violations caused by each of the following: dirty read, non repeatable read and phantoms with suitable example. AU: May-17, Marks 13

2. What is concurrency control? How it is implemented in DBMS ? Briefly elaborate diagrams and examples. AU: May-19, Marks 15