Advanced Database Management System - Tutorials and Notes: Transaction Management in DBMS solved exercise on Transaction Isolation Level concept

Tuesday, 5 May 2020

Transaction Management in DBMS solved exercise on Transaction Isolation Level concept



Transaction Management Exercise – Transaction Isolation Level

Question:
Assume that the following schedules are allowed by the database system with a particular isolation level. Indicate which of the three phenomena, as defined by ANSI-SQL, are occurring in these schedules. What are the locking-isolation levels for the two transaction schedules?
(a) Schedule S1
Instruction No.
T1
T2
1
2
3
4
5
6
R(A)
W(A)



Abort


R(A)
W(A)
Commit

(b) Schedule S2.
Instruction No.
T1
T2
1
2
3
4
5
6
7
8
R(A)


R(A)


Commit

R(A)
W(A)

R(B)
W(B)

Commit

Answer:
Three phenomena that either permitted or not at a given isolation level are;
Dirty read – You're permitted to read uncommitted, or dirty, data.
Non-repeatable read – Reading a row at time t1 and t2 may show different values due to deletion or update that happened between time t1 and t2.
Phantom read – Reading rows at time t1 and t2 may show additional rows that may have been added between time t1 and t2. More records resulted in the query that is executed at time t2 than the result of execution at t1.
Refer for more here - Facts about database transactions.

(a) Schedule S1:
Phenomena happened in S1: Dirty read – You're permitted to read the data that are uncommitted, or dirty.
T2 has a dirty read. Transaction T2 reads A (refer instruction 3) which was written by T1 (refer instruction 2). Later, T1 aborted.
Isolation level for S1: READ UNCOMMITTED
Only READ UNCOMMITTED isolation level can permit this transaction. The basic goal of a READ UNCOMMITTED isolation level is to provide a standards-based definition that allows for non-blocking reads.
Read Uncommitted isolation level is implemented by requiring no read locks for reads and requiring long duration write locks for all writes; thus R2(A) is possible because no read lock is required, even though we have W1(A) before it.

(b) Schedule S2:
Phenomena happened in S2: Non-repeatable read – What you are seeing as a result of second read is different from the first one.
T1 has a non-repeatable read. Transaction T1 reads data item A (refer instruction 1). T2 then modified data item A (refer instruction 3). Later, T1 reads a new value for A (refer instruction 4).
Isolation level for S2: READ COMMITTED
READ COMMITTED isolation level permits non-repeatable read and phantom read phenomena. It does not permit dirty read. It states that a transaction may read only data that has been committed in the database.
READ COMMITTED isolation Level will allow the above schedule; this isolation Level is implemented by requiring short duration read locks for all reads and long duration write locks for all writes. Thus, W2(A) is possible after R1(A) because R1(A) can be a short duration read lock.

*********************

Related links:


Solved exercise in transaction management of dbms

Database transaction solved exercise on isolation levels

Find whether the schedules permit different transaction isolation levels given the phenomena dirty read, nonrepeatable read, and phantom read

Exercise with solution on transaction isolation levels

No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents