Transaktionsövning (Databasteknik II)

Two transactions, T1 and T2, are run serially in a DBMS, according to this schedule:

StepTransaction T1Transaction T2
1Read(A) 
2Read(B) 
3Swap A and B 
4Write(B) 
5Write(A) 
6 Read(A)
7 B := A
8 Write(B)
9 A := 10
10 Write(A)

At the start, the data item A has the value 100, and B has the value 200.

Exercise 1. What is the state of the database after the two transactions?

Exercise 2. In another serial schedule, transaction T2 is run first, and then T1. What is the state of the database after the two transactions?

Assume instead that the transactions are run non-serially, according to this schedule:

StepTransaction T1Transaction T2
1Read(A) 
2Read(B) 
3Swap A and B 
4 Read(A)
5 B := A
6 Write(B)
7Write(B) 
8Write(A) 
9 A := 10
10 Write(A)

Exercise 3. No transaction management is used to isolate the transactions from each other. What is the state of the database after the two transactions?

Exercise 4. Assume that basic two-phase locking with simple exclusive locks are used. Show how locks are requested, how the transactions will have to wait for locks, and how the read and write operations in the schedule are re-ordered.

Exercise 5. Assume that two-phase locking with two-state locks (shared read locks and exclusive write locks) are used. Assume that a transaction doesn't request an exclusive lock on a data item until it actually wants to write that item. Show what happens.

Exercise 6. Assume that the basic timestamp method, as described in Elmasri/Navathe 3d Ed section 20.2 (pp 672-674) is used, with Thomas's Write Rule. Show how the timestamps of the data items A and B will be affected. If one or both transactions are aborted, show when and how, and explain why it had to be done. Also, explain why Thomas's Write Rule works.

Exercise 7. Assume that timestamps are used, as in the exercise above, but step 8 says:

StepTransaction T1Transaction T2
8Read(A) 

What happens?

Exercise 8. Assume that an optimistic method, as described in Elmasri/Navathe 3d Ed section 20.4 (pp 677-678) is used. Show what happens. If one or both transactions are aborted, show when and how, and explain why it had to be done.

[Solutions]


Thomas Padron-McCarthy (thomas.padron-mccarthy@tech.oru.se), 5 februari 2007