Consider a database with objects X and Y and assume that there are two transacti

Question

Consider a database with objects X and Y and assume that there are two transactions T1 and T2. Transaction T1 reads objects X and Y and then writes X. Transaction T2 reads objects X and Y and then writes objects X and Y. Give three examples of schedules for the transactions T1, T2 such that: i. Your first schedule should contain a write-read conflict. ii. Your second schedule should contain a write-write conflict. iii. Your third schedule should contain a read-write conflict. In each case your schedule may contain additional conflicts, but should contain at least one conflict of the type indicated. (In particular you may give a single schedule, which illustrates all three conflicts!) In each case, indicate the conflict of the type you are illustrating.

Explanation / Answer

Dirty read occure when a transaction reads data from a row that has been modified by another transaction, but has not commited.

We come across the situation where in one transaction can change a value and a second transaction can read this value before the original has been committed or rolled back. This is known as dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value.

SQL> select * from employee;

USER_ID EMP_NAME SALARY
---------- -------------------------------------------------- ----------
1 Lalchand 48000
2 Rahul 23000

SQL> update employee
2 set salary=67000
3 where user_id=1;

1 row updated.
Not commited yet but we are reading wrong value from Database next time.

SQL> select * from employee;

USER_ID EMP_NAME SALARY
---------- -------------------------------------------------- ----------
1 Lalchand 67000
2 Rahul 23000

1.Write-read conflict:
======================================
Let assume T2 read salary of employee 1 and 2 {T2(1),T2(2)} and now assume that T2 modify the salary of user_id 1 to 67000(write operation) and not committed. If now T1 read the salary of user_id 1 then it will read salary as 67000 which is wrong because T2 has not committed yet.

Assume user_id 1 as X and user_id 2 as Y
T2(Read X),T2(read Y),T2(write X),T1(read X)....

So T1(Read X) is dirty read(write-read conflict)

2.read-write Conflict:
======================================
Let assume T1 and T2 read salary of employee 1 and 2 {T2(Read 1),T2(Read 2),T1(read 1),T1(Read 2)}. Now assume T1 has update salary of user_id 1 to 67000 and commited it.
In this case T2 already have old value(48000) and it will do processing on old value, It leads to wrong value or cacculation.

Assume user_id 1 as X and user_id 2 as Y

T2(Read X),T2(read Y),T1(Read X),T1(read Y),T1(Write X)....

3. write-Write Conflict:
======================================
Let assume T1 and T2 read salary of employee 1 and 2 {T2(Read 1),T2(Read 2),T1(read 1),T1(Read 2)}. Now assume T1 has update salary of user_id 1 to 67000 and not committed yet and T2 also Updated salary of user_id 1 to 89000(assume).

so T2 is overwritten the T1 uncommitted value.

Assume user_id 1 as X and user_id 2 as Y

T2(Read X),T2(read Y),T1(Read X),T1(read Y),T1(Write X),T2(Write Y)...

Overwritten data of T1 by T2.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.