S1 returns the total number of credits for which Joe is registered, together with his Id. T maintains the integrity constraint “no student shall register for more than 20 credits.” If Joe has less than 20 credits, T executes S2 to increment the number of credits for which Joe has registered in a particular course. Suppose Joe executes two instances of T concurrently at the following isolation levels. In each case say whether or not the named violation of the constraint can occur and, if the answer is yes, explain how(e.g., what locks are or are not held).

A database has two tables:

Student(Id, Name, ...)—Id and Name are both unique

Registered(Id, CrsCode, Credit, ...)—contains one rowfor each course each

student is taking this semester

A transaction type, T, has two SQL statements, S1 followed by S2 (w ith local computations between them):

S 1 SELECT SUM(R.Credits), S.Id

INTO :sum, :id

FROM Student S, Registered R

WHERE S.Name = ’Joe’ AND S.Id = R.Id

GROUP BY S.Name, S.Id

S 2 UPDATE Registered

SET Credits = Credits + 1

WHERE Id = :id AND CrsCode = :crs

a. READ COMMITTED
lost update
b. REPEATABLE READ
lost update
c. SNAPSHOT
lost update

a. Solution:
no
violation of the integrity constraint
Solution:
yes - T1 and T2 both get short term shared locks on all Joe’s row s in Registered and calculate the total number of credits. Assume it is 19. Then T1 gets a long term exclusive lock on one of Joe’s rows, increments it and commits. T2 then does the same on another (possibly the same) row.
deadlock
Solution:
no

b. Solution:
no
violation of the integrity constraint
Solution:
no
deadlock
Solution:
yes - Both T1 and T2 get long term shared locks on all Joe’s rows in Registered. Then T1 requests an exclusive lock on one of those rows and T2 requests an exclusive lock on another (possibly the same).

c. Solution:
no
violation of the integrity constraint
Solution:
yes - T1 and T2 both execute from the same version. Then they execute S2 using different values of :crs.
deadlock
Solution:
no