Transcript Concurrency Control Timestamp Ordering Prepared by: Remedios de Dios Bulos Prepared by: Remedios de Dios Bulos.

Concurrency Control
Timestamp Ordering
Prepared by:
Remedios de Dios Bulos
Prepared by: Remedios de Dios Bulos
Review
• 2PL
–
–
–
–
Basic
Conservative
Strict
Rigorous
Prepared by: Remedios de Dios Bulos
Summary: 2PL
• 2PL produces serializable schedules
– Conservative 2PL produces deadlock-free +
serializable schedules
– Strict 2PL produces serializable + recoverable
schedules
– Rigorous 2PL produces serializable +
recoverable + cascadeless schedules
– Both strict 2PL and conservative 2PL use locks
Prepared by: Remedios de Dios Bulos
What is Timestamp Ordering?
• Timestamp Ordering of transactions is another
way to produce serializable schedules without
using locks.
• It is a concurrency control technique in which:
– the fundamental goal is to order transactions globally
– in such a way that older transactions ( transactions with
smaller timestamps) get priority in the event of conflict
– There is no waiting; transactions involved in conflict
are simply rolled back and restarted
Prepared by: Remedios de Dios Bulos
What are Timestamps?
• A timestamp is a unique identifier created
by the DBMS to identify a transaction.
• A timestamp can be thought of as the
transaction start time.
• Timestamp of transaction T is referred to as
TS(T).
• With the use of timestamps, No deadlocks
occur because there are no locks.
Prepared by: Remedios de Dios Bulos
How are Timestamps generated?
1. Transaction timestamps are numbered
1,2,3, …. A computer counter has a finite
maximum value, so the system must
periodically reset the counter to zero.
2. Using the current value of the system clock
and ensuring that no two timestamp values
are generated during the same tick of the
clock.
Prepared by: Remedios de Dios Bulos
transaction timestamp: TS(T)
• The timestamps are typically ordered based
on the order in which transactions are
started; hence, if T1 starts before transaction
T2, then TS(T1) < TS(T2)
• The older transaction T1 has the smaller
timestamp value
Prepared by: Remedios de Dios Bulos
Timestamp Ordering (TO) Algorithm
• Basic Idea: to order the transactions based
on their timestamps.
• Why call it timestamp ordering (TO)?: the
equivalent serial schedule has the
transactions in order of their timestamp
values.
Prepared by: Remedios de Dios Bulos
Timestamp Ordering Algorithm
The TO algorithm must ensure that, for each item
accessed by conflicting operations (by more
than one transaction) in the schedule, the order
in which the item is accessed does not violate
the serializability order of the schedule.
To do this, the TO algorithm associates with each
database item X two timestamp (TS) values:
–
–
read_TS(X) : the read timestamp of item X
write_TS(X) : the write timestamp of item X
Prepared by: Remedios de Dios Bulos
transaction timestamp: TS(T)
• A transaction timestamp TS(T) is a unique
identifier assigned to each transaction.
• The timestamps are typically ordered based
on the order in which transactions are
started; hence, if T1 starts before transaction
T2, then TS(T1) < TS(T2)
• The older transaction T1 has the smaller
timestamp value
Prepared by: Remedios de Dios Bulos
Timestamps
• TS(T) – timestamp of transaction T
• read_TS(X) : the read timestamp of item X
• write_TS(X) : the write timestamp of item X
Prepared by: Remedios de Dios Bulos
read_TS(X)
read_TS(X) : the read timestamp of item X
– This is the largest timestamp among all the timestamps of
transactions that have successfully read item X.
– that is, read_TS(X) = TS(T), where T is the youngest
(latest) transaction that has read X successfully.
Time
t1
t2
T1
begin
read(X)
t3
…
T2
begin
read(X)
TS(T1) =t1
TS(T2) = t2
read-TS(X) = TS(T1)= t1
read-TS(X) = TS(T2)= t2
Prepared by: Remedios de Dios Bulos
read_TS(X)
Time
t1
t2
t3
T1
begin
…
t4
read(X)
…
T2
TS(T1) =t1
begin
read(X)
TS(T2) = t2
read-TS(X) = TS(T2)= t2
read-TS(X) = TS(T2)= t2
TS(T2) > TS(T1)
Prepared by: Remedios de Dios Bulos
write_TS(X)
write_TS(X) : the write timestamp of item X
– This is the largest of all the timestamps of transactions
that have successfully written item X.
– that is, write_TS(X) = TS(T), where T is the youngest
(latest) transaction that has written X successfully.
Time
t1
t2
T1
begin
write(X)
t3
…
T2
TS(T1) = t1
begin
write(X)
Prepared by: Remedios de Dios Bulos
TS(T2) = t2
write-TS(X) = TS(T1)= t1
write-TS(X) = TS(T2)= t2
write_TS(X)
Time
t1
t2
t3
T1
begin
…
t4
write(X)
…
T2
TS(T1) =t1
begin
write(X)
TS(T2) = t2
write-TS(X) = TS(T2)= t2
write-TS(X) = TS(T2)= t2
TS(T2) > TS(T1)
Prepared by: Remedios de Dios Bulos
Timestamp: example
• Problem:
– Three transactions (T19, T20 , T21) are executing
concurrently
– T19 has a timestamp of TS(T19)
– T20 has a timestamp of TS(T20)
– T21 has a timestamp of TS(T21)
– TS(T19) < TS(T20) < TS(T21)
Prepared by: Remedios de Dios Bulos
Timestamp: example
Op
t1
T19
T20
T21
begin _transaction
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
begin _transaction
begin _transaction
read (baly)
write (baly)
Prepared by: Remedios de Dios Bulos
Timestamp: example
Op
t1
T19
T20
T21
begin _transaction
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
begin _transaction
begin _transaction
read (baly)
write (baly)
read-TS(balx) = TS(T19)
Prepared by: Remedios de Dios Bulos
Timestamp: example
Op
t1
T19
T20
T21
begin _transaction
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
begin _transaction
begin _transaction
read (baly)
write (baly)
read_TS(balx) = TS(T19)
write_TS(balx) = TS(T19)
Prepared by: Remedios de Dios Bulos
Timestamp: example
Op
t1
T19
T20
T21
begin _transaction
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
begin _transaction
begin _transaction
read (baly)
write (baly)
read_TS(balx) = TS(T19)
write_TS(balx) = TS(T19)
read_TS(baly) = TS(T20)
Prepared by: Remedios de Dios Bulos
Timestamp: example
Op
t1
T19
T20
T21
begin _transaction
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
read_TS(balx) = TS(T19)
begin _transaction
begin _transaction
read (baly)
write (baly)
write_TS(balx) = TS(T19)
read_TS(baly) = TS(T20)
Prepared by: Remedios de Dios Bulos
read_TS(baly) = TS(T
21)
Timestamp: example
Op
t1
T19
T20
T21
begin _transaction
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
begin _transaction
begin _transaction
read (baly)
write (baly)
read_TS(balx) = TS(T19)
write_TS(balx) = TS(T19)
read_TS(baly) = TS(T20)
write_TS(baly) = TS(T20)
Prepared by: Remedios de Dios Bulos
read_TS(baly) = TS(T
21)
Timestamp: example
Op
t1
T19
T20
T21
begin _transaction
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
begin _transaction
begin _transaction
read (baly)
write (baly)
read-TS(balx) = TS(T19)
write_TS(balx) = TS(T19)
read_TS(baly) = TS(T20)
write_TS(baly) = TS(T20)
Prepared by: Remedios de Dios Bulos
read_TS(baly) = TS(T
write_TS(baly) = TS(T21)
21)
Basic Timestamp Ordering (TO)
Whenever some transaction T tries to issue a
read(X) or a write(X) operation, the basic
TO algorithm compares the timestamp of T
with the:
– read timestamp of X : read-TS(X) and
– write timestamp of X: write-TS(X)
Why? : to ensure that the timestamp order of
transaction execution is not violated.
Prepared by: Remedios de Dios Bulos
Result of Timestamp Ordering Violation
• If the timestamp order is violated, then
transaction T is aborted and resubmitted to
the system as a new transaction with a new
timestamp.
• If T is aborted and rolled back, any
transaction T1 that may have used a value
written by T must also be rolled back.
• NOTE: Schedules produced are serializable
but not recoverable.
Prepared by: Remedios de Dios Bulos
Result of Timestamp Ordering Violation
• Similarly, any transaction T2 that may have
used a value written by T1 must also be
rolled back, and so on.
This effect is known as cascading rollback –
a problem with the basic TO algorithm.
• An additional protocol must be enforced to
ensure that the schedules are recoverable,
cascadeless, or strict.
Prepared by: Remedios de Dios Bulos
Basic Timestamp Ordering Algorithm
Transaction T issues a write(X) operation:
if
read-TS(X) > TS(T) or write-TS(X) > TS(T)
abort and roll back T with a new
timestamp.
else
execute the write(X) operation;
write-TS(X) := TS(T);
endif;
Prepared by: Remedios de Dios Bulos
Basic Timestamp Ordering Algorithm
Why?: This should be done because some transaction with a
timestamp greater than TS(T) – hence after T in the
timestamp ordering – has already read or written the value
of item X before T had a chance to write X, thus violating the
timestamp ordering.
Time
T
U
t1
begin
t2
…
ABORT
begin
read(X)
write(X)
write(X)
TS(T) = t1
TS(U) =t2
read-TS(X) = TS(U)= t2
write-TS(X) = TS(U)= t2
…
Violates timestamp ordering; U has
Prepared by: Remedios
Diosthe
Bulos
not de
read
value written by U.
Basic Timestamp Ordering Algorithm
Transaction T issues a read(X) operation:
If
write-TS(X) > TS(T)
abort and roll back T with a new
timestamp.
else
execute the read(X) operation;
read-TS(X):= max(read-TS(X), TS(T));
endif;
Prepared by: Remedios de Dios Bulos
Basic Timestamp Ordering Algorithm
Why? :This should be done because some transaction with a
timestamp greater than TS(T) – hence after T in the
timestamp ordering – has already written the value of item
X before T had a chance to read X .
TS(T) = t1
Time
T
t1
begin
t2
…
TS(U) =t2
U
ABORT
read-TS(X) = TS(U)= t2
begin
read(X)
write-TS(X) = TS(U)= t2
write(X)
read(X)
…
T will read the new value of X
Prepared by: Remedios de Dios Bulos
Basic Timestamp Ordering Algorithm
•
•
Hence, the basic TO algorithm checks
whenever two conflicting operations occur
in the incorrect order, and rejects the later
of the two operations by aborting the
transaction that issued it.
Gives priority to transactions which first
read/written a data item.
Prepared by: Remedios de Dios Bulos
Timestamp: example
• Problem:
– Three transactions are executing concurrently
– Transaction T19 has a timestamp of TS(T19)
– T20 has a timestamp of TS(T20)
– T21 has a timestamp of TS(T21)
– TS(T19) < TS(T20) < TS(T21)
Prepared by: Remedios de Dios Bulos
Timestamp: example
Op
T19
t1
begin _transaction
read-TS(balx) = 0
T20
write-TS(balx) = 0
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
t10
write (baly)
t11
balz=100
t12
write (balz)
t13
T19: adds
10 to balx
T21
read-TS(baly) = 0
begin _transaction
write-TS(baly)
=0
read-TS(balz) = 0
begin _transaction
write-TS(balz)read
= 0(baly)
write (baly)
baly=baly + 30
T20: adds
20 to baly
write (baly)
balz=100
write (balz)
commit
T21: adds
30 to bal ; initializes balz to 100
Prepared by: Remediosyde Dios Bulos
Basic TO
Transaction T issues a read(X) operation:
if
write_TS(X) > TS(T)
abort and roll back T
else
execute the read(X) operation;
read_TS(X):= max(read_TS(X), TS(T));
T19 endif;
T20
T21
Timestamp: example
read-TS(balx) = 0
write-TS(bal
)=0
Opx
t1
begin _transaction
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
begin _transaction
begin _transaction
read (baly)
write (baly)
Prepared by: Remedios de Dios Bulos
read_TS(balx) = TS(T
19)
Basic TO
Transaction T issues a write(X) operation:
if
read_TS(X) > TS(T) or write_TS(X) > TS(T)
abort and roll back T
write-TS(balx) = 0
else
execute the write(X) operation;
Op
T19 write_TS(X) := TS(T);
T20
T21
endif;
t
begin
_transaction
1
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
begin _transaction
begin _transaction
read (baly)
write (baly)
Prepared by:
de Dios Bulos bal ) = TS(T )
read_TS(balx) = TS(T
write_TS(
x
19) Remedios
19
Transaction T issues a read(X) operation:
if
write_TS(X) > TS(T)
read-TS(baly) = 0
abort and roll back T
else
execute the read(X) operation;
write-TS(baly) = 0
read_TS(X):=
max(read_TS(X),
Op
T19
T20
T21 TS(T));
endif;
t
begin _transaction
1
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
read_TS(balx) = TS(T19)
begin _transaction
begin _transaction
read (baly)
write (baly)
write_TS(balx) = TS(T19)
Prepared by: Remedios de Dios Bulos
read_TS(baly) = TS(T
20)
t3
If transaction T issues a read(X) operation:
if
write_TS(X) > TS(T)
Op
T19
T20roll back T
T21
abort and
begin _transaction
else
execute the read(X) operation;
read_TS(X):= max(read_TS(X), TS(T));
read (balx)
read (balx)
balx=balx + 10 balx=balx + endif;
10
t4
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
t1
t2
write (balx)
read_TS(balx) = TS(T19)
begin _transaction
begin _transaction
read (baly)
write (baly)
write_TS(balx) = TS(T19)
read_TS(baly) = TS(T20)
Prepared by: Remedios de Dios Bulos
read_TS(baly) = TS(T
21)
t3
If transaction T issues a write(X) operation:
if
read_TS(X) > TS(T) or write_TS(X) > TS(T)
Op
T19 abort and roll back
T20T
T21
begin
_transaction
else
execute the write(X) operation;
read (balx)
read (balx) write_TS(X) := TS(T);
bal =bal + 10 balendif;
=bal + 10
t4
write (balx)
t1
t2
t5
x
x
x
x
write (balx)
begin _transaction
read_TS(bal
T20 )
read (baly)
read
y) > TS(T20) : TS(T
21 )(bal
> TS(
y)
t6
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
baly=baly + 30
t10
write (baly)
write (baly)
read_TS(balx) = TS(T19)
baly=baly + 20
begin _transaction
read (baly)
write (baly)
write_TS(balx) = TS(T19)
read_TS(baly) = TS(T20)
Prepared by: Remedios de Dios Bulos
read_TS(baly) = TS(T
21)
Op
t1
T19
T20
T21
begin _transaction
t2
read (balx)
read (balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
t10
write (baly)
read_TS(balx) = TS(T19)
begin _transaction
begin _transaction
read (baly)
write (baly)
rollback
ROLLBACK: T20
baly=baly + 30
write (baly)
write_TS(balx) = TS(T19)
read_TS(baly) = TS(T20)
Prepared by: Remedios de Dios Bulos
read_TS(baly) = TS(T
21)
If T is aborted and rolled back, any transaction T1 that may have
usedOp
a value writtenT19by T must also be
T20 rolled back.
T21
t1
begin _transaction
t2
NO(bal
need
read
x)
for
rollback:
T20 hasn’t written any value
read
(balx)
t3
balx=balx + 10
balx=balx + 10
t4
write (balx)
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
t10
write (baly)
read_TS(balx) = TS(T19)
begin _transaction
begin _transaction
read (baly)
write (baly)
rollback
ROLLBACK
baly=baly + 30
write (baly)
write_TS(balx) = TS(T19)
read_TS(baly) = TS(T20)
Prepared by: Remedios de Dios Bulos
read_TS(baly) = TS(T
21)
t3
Transaction T issues a write(X) operation:
if
read_TS(X) > TS(T) or write_TS(X) > TS(T)
Op
T19 abort and roll back
T20T
T21
begin
_transaction
else
execute the write(X) operation;
read (balx)
read (balx) write_TS(X) := TS(T);
bal =bal + 10 balendif;
=bal + 10
t4
write (balx)
t5
read (baly)
read (baly)
t6
baly=baly + 20
baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
t10
write (baly)
t1
t2
x
x
x
x
write (balx)
begin _transaction
begin _transaction
read (baly)
write (baly)
rollback
ROLLBACK
baly=baly + 30
write (baly)
read_TS(balx) = TS(T19)
write_TS(balx) = TS(T19)
read_TS(baly) = TS(T20)
write_TS(baly) = TS(T21)
Prepared by: Remedios de Dios Bulos
read_TS(baly) = TS(T
21)
If transaction T issues a write(X) operation:
if
read_TS(X) > TS(T) or write_TS(X) > TS(T)
abort and roll back T
else
execute the write(X) operation;
write_TS(X) := TS(T);
endif; T19
T20
T21
t11
balz=100
balz=100
t12
write (balz)
write (balz)
t13
commit
t14
read_TS(balx) = TS(T19)
write_TS(balx) = TS(T19)
read_TS(baly) = TS(T20)
write_TS(baly) = TS(T21)
Prepared by: Remedios
de Dios Bulos bal ) = TS(T )
write_TS(
z
read_TS(baly) = TS(T
21
21)
Timestamp: example
T19
T20
T21
t11
balz=100
balz=100
t12
write (balz)
write (balz)
t13
..
.
t14
begin _transaction
t15
read (baly)
t16
baly=baly + 20
baly=baly + 20
t17
write (baly)
write (baly)
t18
commit
commit
read (baly)
commit
T20 is restarted at t14 after T21
has committed
Prepared by: Remedios de Dios Bulos
Timestamp Ordering vs 2PL
• The schedules produced by basic TO are
hence guaranteed to be conflict serializable,
like the 2PL protocol.
• Deadlock does not occur with timestamp
ordering. However, cyclic restart (and hence
starvation) may occur if a transaction is
continually aborted and restarted.
Prepared by: Remedios de Dios Bulos
Review
read_TS(X) : the read timestamp of item X
– This is the largest timestamp among all the timestamps of
transactions that have successfully read item X.
– that is, read_TS(X) = TS(T), where T is the youngest
(latest) transaction that has read X successfully.
write_TS(X) : the write timestamp of item X
– This is the largest of all the timestamps of transactions
that have successfully written item X.
– that is, write_TS(X) = TS(T), where T is the youngest
(latest) transaction that has written X successfully.
Prepared by: Remedios de Dios Bulos
Review
Transaction T issues a read(X) operation:
if
write_TS(X) > TS(T)
abort and roll back T
else
execute the read(X) operation;
read_TS(X):= max(read_TS(X), TS(T));
endif;
Transaction T issues a write(X) operation:
if
read_TS(X) > TS(T) or write_TS(X) > TS(T)
abort and roll back T
else
execute the write(X) operation;
write_TS(X) := TS(T);
endif;
Prepared by: Remedios de Dios Bulos
Variations of Timestamp Ordering
• Thomas write rule
• Strict timestamp ordering
Prepared by: Remedios de Dios Bulos
Thomas write rule
•
A modification of basic TO algorithm,
known as Thomas’ write rule;
–
–
it does not enforce conflict serializability (but
makes use of view serializability);
but it rejects fewer write operations, by
modifying the checks for the write (X)
operation.
Prepared by: Remedios de Dios Bulos
Review: View Serializability
• Let S and S´ be two schedules with the same set of
transactions. S and S´ are view equivalent if the
following three conditions are met:
1. For each data item Q, if transaction Ti reads the initial value of
Q in schedule S, then transaction Ti must, in schedule S´, also
read the initial value of Q.
2. For each data item Q, if transaction Ti executes read(Q) in
schedule S, and that value was produced by transaction Tj (
write(Q) if any), then transaction Ti must in schedule S´ also
read the value of Q that was produced by transaction Tj .
3. For each data item Q, the transaction (if any) that performs the
final write(Q) operation in schedule S must perform the final
write(Q) operation in schedule S´.
Prepared by: Remedios de Dios Bulos
Schedule S
T1
read(Q)
T2
T3
First read(Q)
instruction
write(Q)
For each data item Q, if
transaction Ti reads the
initial value of Q in schedule
S, then transaction Ti must,
in schedule S´, also read the
initial value of Q.
write(Q)
write(Q)
transaction T1 reads the initial
Serial Schedule: S’
value of Q in schedule S,
First
read(Q)
and T1 in schedule S’, also
T1
T
T3
2
reads the initial value of Q.
instruction
read(Q)
write(Q)
write(Q)
Review: View
Prepared by: Remedios de Dios Bulos
write(Q)
Serializability
Schedule S
T1
read(Q)
T2
T3
write(Q)
write(Q)
write(Q)
Serial Schedule S’
T1
T2
read(Q)
write(Q)
write(Q)
T3
For each data item Q, if
transaction Ti executes
read(Q) in schedule S,
and that value was
produced by transaction Tj
( write(Q) if any), then
transaction Ti must in
schedule S´ also read the
value of Q that was
produced by transaction Tj
.
This condition
does not exist
for the given
schedules
Review: View
Prepared by: Remedios de Dios Bulos
write(Q)
Serializability
Schedule S
T1
read(Q)
T2
T3
write(Q)
write(Q)
For each data item Q, the
transaction (if any) that
performs the final
write(Q) operation in
schedule S must
perform the final
write(Q) operation in
schedule S´.
write(Q)
Serial Schedule S’
T1
T2
read(Q)
write(Q)
write(Q)
T3
T3 performs the final write
of Q in both schedules
Review: View
Prepared by: Remedios de Dios Bulos
write(Q)
Serializability
Schedule S
T1
read(Q)
T2
T3
write(Q)
write(Q)
write(Q)
Serial Schedule S’
T1
T2
read(Q)
write(Q)
write(Q)
T3
A schedule S is view
serializable if it is
view equivalent
to a serial
schedule.
S and S’ are view
equivalent. They
meet all three
conditions.
Therefore, S is
view serializable.
Review: View
Prepared by: Remedios de Dios Bulos
write(Q)
Serializability
Schedule S
T1
read(Q)
T2
T3
write(Q)
write(Q)
write(Q)
Serial Schedule S’
T1
T2
read(Q)
write(Q)
write(Q)
T3
Review: View
Serializability
Blind writes –
operations that
perform write
without having
performed a read
operation.
Prepared by: Remedios de Dios Bulos
write(Q)
Schedule S
T1
read(Q)
T2
T3
write(Q)
write(Q)
write(Q)
Cannot swap
read(Q) of T1 and
write(Q) of T2
Cannot swap
write(Q) of T2
and write(Q) of
T1
Serial Schedule S’
Cannot swap
T
T
T
1
2schedule 3
Therefore,
write(Q) of T1
read(Q)
S is not a conflict
and write(Q) of
write(Q)
serializable
T3
schedule
write(Q)
Review: View
Prepared by: Remedios de Dios Bulos
write(Q)
Serializability
Thomas Write Rule
Problem: Given the schedule below
T1
T2
read(Q)
write(Q)
write(Q)
TS(T1) < TS(T2)
Prepared by: Remedios de Dios Bulos
Applying Basic Time Stamp Ordering
If transaction T issues a read(X) operation:
if
write_TS(X) > TS(T)
abort and roll back T
else
execute the read(X) operation;
read_TS(X):= max(read_TS(X), TS(T));
endif;
T1
T2
read(Q)
write(Q)
write(Q)
read_TS(Q) = TS(T1)
Prepared by: Remedios de Dios Bulos
Applying Basic Time Stamp Ordering
If transaction T issues a write(X) operation:
if
read_TS(X) > TS(T) or write_TS(X) > TS(T)
abort and roll back T
else
execute the write(X) operation;
write_TS(X) := TS(T);
endif;
T1
T2
read(Q)
write(Q)
write(Q)
Q) = TS(T2)
read_TS(Q) = TS(T
) Remedios dewrite_TS(
Prepared 1
by:
Dios Bulos
Applying Basic Time Stamp Ordering
If transaction T issues a write(X) operation:
if
read_TS(X) > TS(T) or write_TS(X) > TS(T)
abort and roll back T
else
execute the write(X) operation;
write_TS(X) := TS(T);
endif;
T1
T2
read(Q)
write(Q)
write(Q)
write_TS(Q) > TS(T) : TS(T2 ) > TS( T1)
Q) = TS(T2)
read_TS(Q) = TS(T
) Remedios dewrite_TS(
Prepared 1
by:
Dios Bulos
Applying Basic Time Stamp Ordering
If transaction T issues a write(X) operation:
if
read_TS(X) > TS(T) or write_TS(X) > TS(T)
abort and roll back T
else
execute the write(X) operation;
write_TS(X) := TS(T);
endif;
T1
T2
read(Q)
write(Q)
write(Q)
ROLLBACK: T1
Q) = TS(T2)
read_TS(Q) = TS(T
) Remedios dewrite_TS(
Prepared 1
by:
Dios Bulos
Rollback is unnecessary. Since T2 has already
written Q, the value that T1 is attempting to
write is one that will never need to be read.
T1
T2
read(Q)
write(Q)
write(Q)
ROLLBACK: T1
read_TS(Q) = TS(T1)
write_TS(Q) = TS(T2)
Prepared by: Remedios de Dios Bulos
Basic Time Stamp Ordering
If transaction TThomas
issues a write(X)
operation:
write
rule
if
read_TS(X) > TS(T) or write_TS(X) > TS(T)
abort and roll back T
else
the write(X) operation;
Transaction T issues
aexecute
write_item(X)
operation:
write_TS(X) := TS(T);
endif;
if
then
else if
else
then
endif;
endif;
read_TS(X) > TS(T)
abort and roll back T
write_TS(X) > TS(T)
ignore the write_item(X) operation;
execute the write_item(X) operation;
write_TS(X):= TS(T);
Prepared by: Remedios de Dios Bulos
Thomas write rule
This is because some transaction with timestamp
greater than TS(T) – and hence after T in the
timestamp ordering – has already written the
value of X.
Hence, we must ignore the write_item(X)
operation of T because it is already outdated
and obsolete.
Prepared by: Remedios de Dios Bulos
Thomas write rule
Transaction T issues a read_item(X) operation:
if
then
else
write_TS(X) > TS(T)
abort and roll back T
execute the read_item(X) operation;
read_TS(X):= max(read_TS(X), TS(T));
endif;
Prepared by: Remedios de Dios Bulos
Applying Thomas Write Rule
Problem: Given the schedule below
T1
T2
read(Q)
write(Q)
write(Q)
TS(T1) < TS(T2)
Prepared by: Remedios de Dios Bulos
Thomas Write Rule – Time Stamp Ordering
If transaction T issues a read(X) operation:
if
write_TS(X) > TS(T)
abort and roll back T
else
execute the read(X) operation;
read_TS(X):= max(read_TS(X), TS(T));
endif;
T1
T2
read(Q)
write(Q)
write(Q)
read_TS(Q) = TS(T1)
Prepared by: Remedios de Dios Bulos
Thomas Write Rule – Time Stamp Ordering
If transaction T issues a write(X) operation:
if read_TS(X) > TS(T)
abort and roll back T
else
if write_TS(X) > TS(T)
ignore the write_item(X) operation;
else
execute the write_item(X) operation;
write_TS(X):= TS(T);
endif;
endif;
T1
T2
read(Q)
write(Q)
write(Q)
write_TS(Q) = TS(T2)
read_TS(Q) = TS(T
)
1
Prepared by: Remedios de Dios Bulos
Thomas Write Rule – Time Stamp Ordering
If transaction T issues a write(X) operation:
if read_TS(X) > TS(T)
abort and roll back T
else
if write_TS(X) > TS(T)
ignore the write_item(X) operation;
else
execute the write_item(X) operation;
write_TS(X):= TS(T);
endif;
endif;
write_TS(Q) > TS(T)
T1 : TS(T2 ) > TS( T1)T2
read(Q)
write(Q)
write(Q)
Rejected write
operation
write_TS(Q) = TS(T2)
read_TS(Q) = TS(T
)
1
Prepared by: Remedios de Dios Bulos
Thomas Write Rule – Time Stamp Ordering
read-TS(baly) = TS(T21 )
Timestamp:
example
If transaction T issues
a write(X) operation:
Op
t6
T21
19
if Tread_TS(X)
> TS(T)T20
begin _transaction
abort and roll back T
if write_TS(X) > TS(T)
read (balx)
read else
(balx)
ignore the write_item(X) operation;
balx=balx + 10 balx=balx + 10
else
execute the write_item(X) operation;
write (balx)
write (balx)
begin _transaction
write_TS(X):= TS(T);
read (baly)
read
(baly)
endif;
baly=baly + 20
begin _transaction
endif; baly=baly + 20
t7
read (baly)
t8
write (baly)
t9
baly=baly + 30
t10
write (baly)
t1
t2
t3
t4
t5
read (baly)
write (baly) *
Rollback T20
baly=baly + 30
write (baly)
read_TS(baly) > TS(T) : TS(T21 ) > TS( T20 )
*At time t8 , the write by transaction T20 violates the first timestamping write
rule described above and therefore is aborted and restarted at time t14.
Prepared by: Remedios de Dios Bulos
Thomas Write Rule – Time Stamp Ordering
If transaction T issues a write(X) operation:
if read_TS(X) > TS(T)
write-TS(balz) = TS(T21 )
abort and roll back T
else
if write_TS(X) > TS(T)
ignore the write_item(X) operation;
else
execute theTwrite_item(X)
operation;
T20
T21
19
write_TS(X):= TS(T);
t11 balz=100
balz=100
endif;
t12 write (balz)
write (balz)
endif;
Timestamp: example
t13
balz=50
balz=50
t14
write (balz)
write (balz)**
t15
read (baly)
t16
baly=baly + 20
t17
write (baly)
t18
commit
commit
begin _transaction
read (baly)
baly=baly + 20
write (bal )
y
write_TS(balz) > TS(T)
: TS( T21 ) > TS( T19 )
commit
** At time t14, the write by transaction T19 can safely be ignored using the
ignore obsolete write rule, as it would have been overwritten by the
write of transaction T21 at time t12 .
Prepared by: Remedios de Dios Bulos
Strict timestamp ordering
T’ with a lesser timestamp has
written item X;
• Strict TO ensures that the
schedules are both strict (for
easy recoverability) and
(conflict) serializable.
• A transaction T that issues a
read_item(X) or write_item(X)
such that TS(T) > write_TS(X)
has its read or write operation
delayed until the transaction T’
that wrote the value of X (hence
TS(T) = write_TS(T)) has
committed or aborted.
• This algorithm does not cause
deadlock, since T waits for T’
only if TS(T) > TS(T’), that is,
always wait on the older
transaction to commit or abort.
t
T
Write-TS(X) = T’
TS(T’)
balx
t1
begin
_transaction
100
t2
read(X)
100
t3
X=X+ 100
100
write(X)
200
…
200
t4
t5
begin _transaction
Read is delayed
read(X)
t6
X=X - 10
t7
write(X)
t8
100
abort/commit
commit
TS(T) > write_TS(X)
Prepared by: Remedios de Dios Bulos
190
190
Concurrency Control
Optimistic Techniques
Prepared by: Remedios de Dios Bulos
Optimistic Techniques
• They are based on the assumption that :
– conflict is rare and
– it is more efficient to allow transactions to
proceed without imposing delays to ensure
serializability
Prepared by: Remedios de Dios Bulos
Optimistic Techniques
• When a transaction wishes to commit:
– A check is performed to determine whether a
conflict has occurred
– The sets of read and write elements are
compared with the write sets of other active
transactions
– If there is a conflict the transaction must be
rolled back and restarted.
Prepared by: Remedios de Dios Bulos
Validation-Based Protocol
The scheduler must be told for each transaction T:
–
–
the set of database elements T reads RS(T)
the set of data elements T writes, WS(T)
Prepared by: Remedios de Dios Bulos
Time T5
T6
balx
baly
balz
t1
begin _transaction
100
50
25
sum = 0
100
50
25
0
t2
begin _transaction
sum
t3
read(balx)
read(balx)
100
50
25
0
t4
balx=balx – 10
sum = sum + balx
100
50
25
100
t5
write(balx)
read(baly)
90
50
25
100
t6
read(balz)
sum = sum + baly
90
50
25
150
t7
balz=balz + 10
90
50
25
150
t8
write(balz)
90
50
35
150
read(balz)
90
50
35
150
sum = sum + balz
90
50
35
185
90
50
35
185
t9
t10
t11
commit
commit
RS(T5) = (balx ,balz ); RS(T6 ) = (balx ,baly ,balz )
WS(T5 ) = (balx ,balz ) ; WS(T6) is empty
Prepared by: Remedios de Dios Bulos
Validation-Based Protocol
T
U
read(B)
read(B)
Execution of transaction T is done in
three phases.
1. Read and execution phase
2. Validation phase
3. Write phase
B:- B-50
read(A)
A:- A+50
read(A)
display (A+B)
(validate)
write (B)
commit
write (A)
(validate)
commit
Prepared by: Remedios de Dios Bulos
Validation-Based Protocol
T
U
read(B)
Read and execution phase:
transaction T writes only to
temporary local variables
• Extends from the start of the
transaction until immediately
before the commit
• The transaction reads the values
of all data items it needs (read
set) from the database and stores
them in local variables
• Updates are applied to a local
copy of the data, not to the
database itself.
read(B)
B:- B-50
read(A)
A:- A+50
read(A)
display (A+B)
(validate)
write (B)
commit
write (A)
Prepared by: Remedios de Dios Bulos
(validate)
commit
Validation-Based Protocol
Validation phase:
• determines whether transaction T
can copy to the database the
temporary local variables (as a
result of write operations)
• The scheduler validates the
transaction T by:
– comparing its read(RS) and
write (WS) sets with those of
other transactions that has
validated (write sets);
– If validation fails, the
transaction is rolled back,
otherwise it proceeds to the
third phase.
T
U
read(B)
read(B)
B:- B-50
read(A)
A:- A+50
read(A)
display (A+B)
(validate)
write (B)
commit
write (A)
Prepared by: Remedios de Dios Bulos
(validate)
commit
Validation-Based Protocol
T
U
read(B)
read(B)
Write phase:
• Assumes that transaction
T succeeded in the
validation phase
• The transaction writes to
the database its values
for the elements in its
write set (WS), that is,
the updates made to the
local copy are applied to
the database.
B:- B-50
read(A)
A:- A+50
read(A)
display (A+B)
(validate)
write (B)
commit
write (A)
Prepared by: Remedios de Dios Bulos
(validate)
commit
Validation-Based Protocol
To support the decision whether to validate a transaction, the
scheduler maintains three sets:
 START: the set of transactions that have started but not yet
completed validation; for each transaction T in the set, the
scheduler maintains a timestamp Start(T), the time at which T
started.
 VAL: the set of transactions that have been validated but not
yet finished the writing of phase 3; for each T in the set, the
scheduler maintains both Start(T) and Val(T), which is the
time at which T is validated.
 FIN : set of transactions that have completed the WRITE
phase. For each T, the scheduler records Start(T), Val(T) and
Fin(T), the time at which T finished.
Prepared by: Remedios de Dios Bulos
What canTgo
Given 2 transactions
andwrong?:
U
•
Case 1
U is in VAL or FIN; U has validated
• Fin(U) > Start(T); U did not finish before T started
Since we do not know whether or not T got to read U’s
• value,
RS(T)Tmust
WS(U)
is not back
empty;
as an element
be rolled
to with
avoidX inconsistent
operations
T reads X
U writes X
in the DB
U fin
If U finished before T started, then surely T would read
the value of X that either U or some later transaction
T validating
U start
T
start
U
validated
wrote.
Prepared by: Remedios de Dios Bulos
What can go wrong?: Case 2
Problem:
a)
U has successfully validated
T and
U must
both write
of Xbefore T entered its validation
b)
Fin(U)
> Val(T);
U didvalues
not finish
phase
If we
let T validate, it is possible that it will write X before U does.
c)
WS(T)
 WS(U)
not Tempty;
let back
X be to
both
in write
Since
we cannot
be is
sure,
is rolled
make
suresets
it does not
violate the assumed serial order in which it follows U.
T writes X
in the DB
U writes X
in the DB
If U finished before T validated, then surely U wrote X
before T did.
U validated
T validating
Prepared by: Remedios de Dios Bulos
U finished
Validation Rules for Transaction T
Given: Transactions T and U
1. Compare RS(T) with WS(U) and check that
RS(T) WS(U ) = for any U that did not finish
before T started, i.e., if Fin(U) > Start(T).
2. Compare WS(T) with WS(U) and check that WS(T)
 WS(U) =  for any U that did not finish before T
validated, i.e., if Fin(U) > Val(T).
Prepared by: Remedios de Dios Bulos
Example: Schedule Produced by
Validation
U validates
U
T
read(B)
read(B)
There are no other
validated transactions,
so validation succeeds
B:- B-50
read(A)
A:- A+50
read(A)
display
(A+B)
(validate)
write (B)
write (A)
(validate)
commit
Prepared by: Remedios de Dios Bulos
commit
Example: Schedule
Produced by Validation
U
T
read(B)
read(B)
B:- B-50
read(A)
A:- A+50
read(A)
T begins validation
display
(A+B)
T is validated against U
(validate)
write (B)
write (A)
(validate)
commit
Prepared by: Remedios de Dios Bulos
commit
Example: Schedule Produced
by Validation
U
T
read(B)
read(B)
U has validated but not finished
B:- B-50
read(A)
fin(U) >start( T)
A:- A+50
read(A)
1.
Compare RS(T) with WS(U) and check
that RS(T) WS(U ) = for any U that
did not finish before T started, i.e., if
Fin(U) > Start(T).
display
(A+B)
(validate)
write (B)
write (A)
Fin(U) > Start(T)?
YES
(validate)
commit
Prepared by: Remedios de Dios Bulos
commit
Example: Schedule Produced by
Validation
U
T
read(B)
U has validated but not finished
read(B)
B:- B-50
fin(U) >start( T)
read(A)
A:- A+50
1. Compare RS(T) with WS(U) and check that
RS(T) WS(U ) = for any U that did
not finish before T started, i.e., if Fin(U) >
Start(T).
read(A)
Do the writes of U affect reads of T ?
NO.
RS(T) WS(U) = 0
U does not write any item
T does not read any item written by U.
(validate)
display
(A+B)
write (B)
write (A)
(validate)
commit
Prepared by: Remedios de Dios Bulos
commit
Example: Schedule Produced by
Validation
U has not finished before T
validated
U
T
read(B)
read(B)
B:- B-50
fin(U) >Val( T)
read(A)
A:- A+50
read(A)
2. Compare WS(T) with WS(U) and check that
WS(T)  WS(U) =  for any U that did not
finish before T validated, i.e., if Fin(U) >
Val(T).
display
(A+B)
(validate)
write (B)
write (A)
Fin(U) > Val(T)?
YES
(validate)
commit
Prepared by: Remedios de Dios Bulos
commit
Example: Schedule
Produced by Validation
U has not finished before T
validated
U
T
read(B)
read(B)
B:- B-50
fin(U) >Val( T)
read(A)
A:- A+50
read(A)
2. Compare WS(T) with WS(U) and check that
WS(T)  WS(U) =  for any U that did
not finish before T validated, i.e., if Fin(U)
> Val(T).
display
(A+B)
(validate)
write (B)
write (A)
If U finished before T validated, then
surely U wrote X before T did.
(validate)
commit
Prepared by: Remedios de Dios Bulos
commit
Example: Schedule Produced by Validation
U
start(U) < start(T)
T
read(B)
val(U) < val(T)
read(B)
B:- B-50
fin(U) < fin( T)
read(A)
Compare WS(T) with WS(U) and check that WS(T)
 WS = 0 for any U that did not finish before
T validated, i.e., if Fin(U) > Val(T).
A:- A+50
read(A)
(validate)
display
(A+B)
WS(T)  WS = 0?
YES
U does not write any items
write (B)
commit
Therefore T is validated
Prepared by: Remedios de Dios Bulos
write (A)
(validate)
commit
END
Prepared by: Remedios de Dios Bulos