Tutorial Concurrency Control

Tutorial Concurrency control

What is a timestamp? How do timestamp-based protocols for concurrency control differ from locking based protocols?

Describe the basic timestamp ordering protocol for concurrency control. What is Thomas’s write rule and how does this affect the basic timestamp ordering protocol? How strict TO is differs from basic TO?

Consider the following timeline and explain the problem that this timestamp schedule represents

T writes X U reads x

T start U start T Aborts

Describe how versions can be used to increase concurrency.

Describe multiversioning concurrency control protocol (TO based and locking based).

Discuss the difference between pessimistic and optimistic concurrency control. In what cases will be suitable to use one or another?

SQL supports four isolation-levels. Each level implicitly defines a class of transactions;

Type of Violation

___________________________________

Isolation Dirty nonrepeatable

level read read phantom

_____________________ _____ _________ ____________________

READ UNCOMMITTED yes yes yes

READ COMMITTED no yes yes

REPEATABLE READ no no yes

SERIALIZABLE no no no

The following questions refer to these classes:

Consider the four SQL isolation levels. For each of the four isolation levels, give examples of transactions that could be run safely at that level.

Classify each of the schedules below, state if the schedule is serializable, recoverable, avoids cascade rollback, strict.

1. T1:R(X), T2:R(X), T1:W(X), T2:W(X)

2. T1:R(X), T2:R(Y), T3:W(X), T2:R(X), T1:R(Y)

3. T1:W(X), T2:R(X), T1:W(X), T2:Abort,