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.
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:
- Go to Transaction management in DBMS page
- Go to Important keywords in DBMS page
No comments:
Post a Comment