Transactions Activity – Monday November 28
Problem 1: Transaction Isolation Levels
Consider a table Football(college, league, rank). Write two transactions, X1 and X2, consisting of SQL
queries and/or updates over Football. Assume that transaction X1 always executes with isolation level
Serializable, and that both transactions always run to completion. Your transactions should be
designed so that their possible behaviors are different for the four possible isolation levels for
transaction X2:
Serializable
Repeatable Read
Read Committed
Read Uncommited
In addition to specifying your two transactions, give an initial state of table Football so that the values
retrieved in Select statements by one or both of the transactions, and/or the final state of table
Football, may be different for each of the four isolation levels for transaction X2.
Problem 2: Implementing Transactions via Locking
Consider a locking protocol intended to enforce the various isolation levels offered to transactions.
Assume each transaction T acquires the necessary locks just before each operation it performs, retains
acquired locks for the duration of transaction T, then releases all of its locks when T either commits or
rolls back. There are three types of locks:
A READ lock on a tuple t ― records that a transaction is reading tuple t
A WRITE lock on a tuple t ― records that a transaction is updating or deleting tuple t
An INSERT lock on a table T ― records that a transaction is inserting into table T
Here's the proposed protocol. In each group after the first one, the italicized line indicates what’s
changed from the previous group.
SERIALIZABLE transactions
1) To perform any operation on a table: no other INSERT lock
2) To read a tuple: no other WRITE lock, set READ lock
3) To update or delete a tuple: no other READ or WRITE lock, set WRITE lock
4) To insert a tuple: set INSERT lock
REPEATABLE READ transactions
1) No global checking for INSERT locks
2) To read a tuple: no other WRITE lock, set READ lock
3) To update or delete a tuple: no other READ or WRITE lock, set WRITE lock
4) To insert a tuple: set INSERT lock
docsity.com
