chapter 21 concurrency control techniquesorion.towson.edu/~karne/teaching/c657sl/ch21notes.pdf3 (b)...

1

Chapter 21

Concurrency Control Techniques

- Database Concurrency Control

(1) Purpose

(2) Two-phase locking

(3) Limitations

(4) Index locking

(5) Lock compatibility matrix

(6) Lock granularity

2

Process of maintaining consistency is also referred to as

serialization of the execution of concurrent transactions.

Pessimistic: take pessimistic measures to stop problems from

happening (inconsistent data and deadlocks)

Purpose of Concurrency Control

- To force isolation (through mutual exclusion) among conflicting

Txns

- To preserve database consistency through consistency preserving

execution of transactions

- To resolve read-write and write-write conflicts

Ex. In concurrent execution environment, if T1 conflicts with T2

over a data item A, then the existing concurrency control decides

if T1 or T2 should get the A and the other Txn is rolled-back or

waits.

Two-Phase Locking Techniques

- Locking is an operation which secures

o Permission to read

o Permission to write a data item for a Txn

o Ex. Lock(X). data item X is locked on behalf of Txn

- Unlocking is an operation which removes these permissions from

the data item

- Ex. Unlock(X). data item X is made available for all other Txns

- Lock and Unlock are atomic operations

- Essential components:

(a) Shared (read lock)

i. More than one Txn can apply share lock on X

for reading its value but no write lock can be

applied on X by any other Txn

3

(b) Exclusive (write lock)

i. Mutual exclusion: only one write lock on X can

exist at any time and no shared lock can be

applied by other Txn on X

<Data_Item, Lock, Locking Txn> (Lock manager

subsystem manages locks)

Conflict Matrix

Request Lock Read Write

Cur-Read Y N

Cur-Write N N

- Lock manger locks on data items

- Lock table: Lock manager uses it to store the identity of Txn

locking a data item;

Transaction ID Data item id Lock mode Ptr to next data item

T1 X1 Read Next

- Database requires that all transactions should be well-formed; a

transaction is well-formed if:

o It must lock the data item before it reads or writes to it

4

o It must not lock already locked data items and it must not

try to unlock a free data item

(at most one Txn can hold a lock on item X)

Lock Operation:

B:

If Lock(X) = 0 //item is unlocked

Then Lock (X) 1 //lock the item

Else

Begin

Wait (until lock (X) = 0) and the lock manager wakes up the Txn

Goto B:

End;

Unlock Operation:

Lock(X) 0 //unlock the item

If any Txns are waiting then wake up one of the transaction;

5

Read Lock Operation: read_lock(X)

B:

If Lock(X) = “unlocked” then

Begin

Lock(X) “read-locked”;

No_of_reads (X) 1;

End

Else if Lock(X) “read-locked” then

No_of_reads(X) no_reads(X) + 1

Else

Begin

Wait (until Lock(X) = “unlocked” and

The lock manager wakes up the transaction);

Goto B

End;

6

Write Lock Operation: write_lock(X)

B:

If Lock(X) = “unlocked” then

Lock(X) = “write-locked”;

Else

Begin

Wait (until Lock(X) = “unlocked” and

The lock manager wakes up the transaction);

Goto B

End;

7

Unloxk operation: unlock(X)

B:

If Lock(X) = “write-locked” then

Begin

Lock(X) = “unlocked”;

Wakes up one of the transaction if any

End

Else

If (Lock(X) = “read-locked” then

Begin

No_of_reads (X) No_of_reads(X) - 1

If No_of_reads(X) = 0 then

Begin

Lock(X) = “unlocked’;

Wake up one of the transactions if any

End

End;

8

- Lock Conversion

o Lock upgrade: existing read lock to write lock

If Ti has a read-lock(X) and Tj has no read-lock(x) (i~=j)

then convert read-lock(X) to write-lock(X)

Else force Ti to wait until Tj unlocks X

o Lock downgrade: existing write lock to read lock

Ti has a write-lock(X) //no Txn can have any lock on X

Convert write-lock(X) to read-lock(X)

Current State of Locking of Data Item

Lock Mode of Request

Unlocked Shared Exclusive

Unlock - YES YES

Shared YES YES NO

EXCLUSIVE YES NO NO

Any lock request for a data item can only be granted if it is compatible

with the current mode of locking of the data item. If the request is not

compatible, then wait.

Two Phases

1. Locking (growing)

2. Unlocking (Shrinking)

Locking phase: a Txn applies locks (read or write) on desired data

items one at a time

Unlocking phase: a Txn unlocks its locked data items one at a time

9

Requirement: for a Txn these two phases must be mutually

exclusive, that is during the locking phase unlocking phase must not

start and during unlocking phase locking phase must not begin

T1 T2

r1L(Y) lock r2L(X) lock

r1(Y) r2(X)

u1(Y) unlock u2(X) unlock

w1L(x) lock w2L(Y) lock

r1(X) r2(Y}

X=X+Y Y=X+Y

w1(X) w2(Y) u1(X) unlock u2(Y)

Not following Two-phase Locking (not serializable)

T1 T2

r1L(Y) lock r2L(X) lock

r1(Y) r2(X)

w1l(X) lock w2L(X) lock

u1(Y) unlock u2(X) unlock

r1(X) r2(Y}

X=X+Y Y=X+Y

w1(X) w2(Y)

u1(X) unlock u2(Y) unlock

following Two-phase Locking

10

T1 T2

r1L(Y)

r1(Y)

u1(Y)

r2L(X)

Y r2(X)

u2(X)

w2L(Y)

r2(Y)

Y= X + Y

w2(Y)

X u2(Y)

w1L(X)

r1(X)

X = X+Y

w1(X)

u1(X)

r1(Y)

Y

T1 T2

Non-serializable

11

If every Txn in a schedule follows the 2PL protocol, the schedule is

guaranteed to be serializable (no need to test for serializability).

2PL limits concurrency, and many cause deadlocks

2PL policy generates two locking algorithms: Basic and Conservative

Basic: Transactions lock data incrementally, this may cause deadlocks

Conservative: prevents deadlocks by locking all desired data items

before transaction begins execution

Strict: a more stricter version of basic algorithm, where unlocking is

performed after a Txn terminates, aborts or rollback). This is the most

commonly used 2PL. No deadlocks.

Dealing with Deadlocks and Starvation

T1 T2

read_lock(Y)

read_item(Y)

read_lock(X)

read_item(Y)

write_lock(X)

(waits for X)

write_lock(Y)

(waits for Y)

12

T1 T2

Has Y has X

Need X Need Y

Deadlock

Deadlock prevention:

- A Txn locks all data items it refers to before it begins execution

- This way of locking prevents deadlock since a Txn never waits for

a data item

- The conservative 2PL uses this approach

- Transaction Time Stamp:

o Unique identifier assigned to each Txn (TS)

o TS (T1) < TS(T2) T1 started before T2

o Wait_die:

if TS(Ti) < TS(Tj) then Ti is allowed to wait

else Ti dies and restart later with the same time

stamp

o Wound_wait:

If TS(Ti) < TS(Tj) then

abort Ti then restart later with the same time stamp

Else Ti is allowed to wait

o No waiting (NW):

If a Txn is unable to obtain a lock, it is aborted and

restarted after a certain delay (causes needless waits

and aborts)

o Continuous waiting (CW): reduces needless waiting and

aborts

13

Ti tries to lock X, but is not able to lock because X is

locked by Tj

If Tj is not blocked (not waiting for some other lock

item), then Ti is blocked and allowed to wait,

otherwise abort Ti

o New method: In this approach deadlocks are allowed to

happen; the scheduler maintains a wait-for-graph for

detecting cycle. If a cycle exists, then one Txn involved in the

cycle (victim) is selected and rolled-back

Dealing with Starvation

- Starvation occurs when a particular Txn consistently waits or

restarted and never gets a chance to proceed further

- In a deadlock resolution it is possible that the same Txn may

consistently be selected as a victim and rolled-back

- In a wound-wait scheme a younger Txn may always be wounded

(aborted) by a long running older transaction which may create

starvation.

Timestamp based Concurrency Control Algorithm

- A monotonically increasing variable (integer) indicating the age of

an operation or a transaction. A larger time stamp indicates a

more recent event or operation

- This uses time stamp to serialize the execution of concurrent Txns

Conflict:

- Older Txn tries to read a value written by an younger Txn

- Older Txn tries to modify a value already read or written by an

younger Txn

14

Two Time Stamps: write and read

Data item:

X: {X, Wx, Rx} value, write time stamp, read time stamp

Transaction Ta with time ta issues a read operation:

- Will succeed if ta >= Wx, also if ta > Rx and the new value of Rx=ta

(younger Txn tries to read)

- Request will fail if ta < Wx; rollback Ta, assign a new time stamp

and start again (Older Txn tries to read value already written by

an younger Txn)

|--------------------------------------------------------------------------Older Txn

|Wx|write

|----------------------------------------------------------------------Younger Txn

Ta |Rx| read ta

ta >= Wx Rx = ta

-----------------------------------------------------------------------------------------------

|--------------------------------------------------------------------------Older Txn

Ta (ta) |Rx|read

|----------------------------------------------------------------------Younger Txn

|Wx| write

ta < Wx

15

Transaction Ta with time ta to issue a write operation:

- If ta >= Wx, ta >= Rx , ta is allowed to write, Wx = ta (younger Txn

tries to write)

- If ta < Rx, younger Txn is already using the value of X, Ta is rolled

back with current system generated time stamp and restarted

(older Txn is writing to write)

- If Rx<= ta < Wx, a younger Txn already updated the value; write

operation is ignored (Ta operation’s value is obsolete; ta >= Rx

(older Txn read the value); ta < Wx (younger Txn updated the

value)

16

RULES FOR TIME STAMP ALGORITHM

Data Item: X: {x, Wx, Rx}; //value, write stamp, read stamp

Ta with time ta issues a read operation:

1. If (ta >= Wx and ta > Rx) then Rx = ta (younger Txn

read)

2. If (ta<Wx) then rollback Ta; assign a new time stamp

and start (older Txn read; written by younger Txn)

Ta with time ta issues a write operation:

1. If (ta >= Wx, ta >= Rx) then Wx = ta (younger Txn

write)

2. If (ta < Rx) then rollback; assign a new time stamp

and start (younger Txn is using x)

3. If (Rx <= ta < Wx) then ignore the write operation;

(younger Txn already updated the value and older Txn

read the value)

17

t23>t22

Step Schedule T22 (older) T23(younger) Actions A:=400,Wa,Ra B:=500, Wb,Rb Sum:=0

1 Sum :=0 Sum:=0

2 Read(A) Read(A)

3 Sum:=Sum+A Sum:=Sum+A

4 Sum:=0 Sum:=0

5 Read(A) Read(A)

6 A := A-100 A := A-100

7 Write(A) Write(A)

8 Read(B) Read(B)

9 Sum:=sum+B Sum:=sum+B

10 Show(sum) Show(sum)

11 Sum:=sum+A Sum:=sum+A 12 Read(B) Read(B)

13 B:=B+100 B:=B+100

14 Write(B) Write(B)



18

t23>t22


1 Sum :=0 Sum:=0 Sum=0

2 Read(A) Read(A) assume t22>=Wa and t22>Ra Ra=t22 400, Wa,t22 500, Wb,Rb

3 Sum:=Sum+A Sum:=Sum+A Sum=400

4 Sum:=0 Sum:=0 Sum=0

5 Read(A) Read(A) t23>Wa and t23>t22 Ra=t23 400, Wa, t23 500, Wb,Rb

6 A := A-100 A := A-100 A=300

7 Write(A) Write(A) t23>Wa and t23>=t23 Wa=t23 300, t23,t23 500,Wb,Rb

8 Read(B) Read(B) Assume t22>=Wb t22>Rb Rb = t22 300, t23,t23 500,Wb,t22

9 Sum:=sum+B Sum:=sum+B Sum=500

19

10 Show(sum) Show(sum) 500

11 Sum:=sum+A Sum:=sum+A 800

12 Read(B) Read(B) t23>=Wb t23>t22 Rb=t23 300,t23,t23 500,Wb,t23

13 B:=B+100 B:=B+100 B=600

14 Write(B) Write(B) t23>=Wb t23>=t23 Wb=t23 300,t23,t23 600,t23,t23

15 Sum:=sum+B Sum:=sum+B 1400


Serializable Schedule Based On Time Stamp Scheme (normal

operations)

20

t23>t22


1 Sum :=0 Sum :=0 Sum=0


3 Read(A) Read(A)

4 A:=A-100 A:=A-100

5 Write(A) Write(A)

6 Read(A) Read(A)

7 Sum:=sum+A Sum:=sum+A

8 Read(B) Read(B)

9 B:=B+100 B:=B+100




13 Sum:=0 Sum:=0

14 Read(A) Read(A)

15 Sum:=sum+A Sum:=sum+A

16 Read(B) Read(B)



21

t23>t22 (Wa, Ra, Wb, Rb are initial values, can be zero


1 Sum :=0 Sum :=0 Sum=0


3 Read(A) Read(A) Assume t23>=Wa t23>Ra Ra = t23 400,Wa,t23 500,Wb,Rb

4 A:=A-100 A:=A-100 A=300

5 Write(A) Write(A) t23>=Wa t23>=t23 Wa=t23 300,t23,t23 500,Wb,Rb

6 Read(A) Read(A) t22’>t23

ta<Rx t22<t23 rollback Txn with t22’ 300,Wa,t23 500,Wb,Rb

7 Sum:=sum+A Sum:=sum+A Sum=400

8 Read(B) Read(B) Assume t23>=Wb t23>Rb Rb=t23

22

300,t23,t23 500,Wb,t23

9 B:=B+100 B:=B+100 B=600

10 Write(B) Write(B) t23>=Wb t23>=t23 Wb=t23 300,t23,t23 600,t23,t23




14 Read(A) Read(A) t22’ >= t23 t22’ > t23 Ra=t22’ 300,t23,t22’ 600,t23,t23

15 Sum:=sum+A Sum:=sum+A Sum=300

16 Read(B) Read(B) t22’>=t23 t22’>=t23 Rb=t22’ 300,t23,t22’ 600,t23,t22’



Schedule where an older transaction is rollback

23

t24>t25<t26

Step Schedule T24 (older) T25(younger) T26(younger)

1 Read(A) Read(A)

2 A =A+1 A=A+1

3 Write(A) Write(A)

4 Read(C) Read(C)

5 C=C*3 C=C*3

6 Read(C) Read(C)

7 Write(C) Write(C)

8 C=C*2 C=C*2

9 Write(C) Write(C)

10 B=100 B=100


12 B=150 B=150


14 Read(C) Read(C)

15 C = C*3 C=C*3

16 Write(C) Write(C)

24

t24>t25<t26

Step Schedule T24 (older)

T25 (younger)

T26 (younger) A:10,Wa,Ra B:50,Wb,Rb C:5,Wc,Rc

1 Read(A) Read(A) Assume t24>=Wa t24>Ra Ra=t24 10,Wa, t24 50,Wb,Rb 5,Wc,Rc

2 A =A+1 A=A+1 A=11

3 Write(A) Write(A) t24 >=Wa t24>=t24 Wa=t24 11,t24,t24 50,Wb,Rb 5,Wc,Rc

4 Read(C) Read(C) t25>=Wc t25>Rc Rc=t25 11,t24,t24 50,Wb,Rb 5,Wc,t25

5 C=C*3 C=C*3 C=15

6 Read(C) Read(C) t26>=t25 T26>t25 Rc=t26

25

11,t24,t24 50,Wb,Rb 5,Wc,t26

7 Write(C) Write(C) ta<Rx t25<t26 Rollback T25

8 C=C*2 C=C*2

9 Write(C) Write(C) 10 B=100 B=100


12 B=150 B=150


14 Read(C) Read(C)

15 C = C*3 C=C*3

16 Write(C) Write(C)

Rollback (Write operation can be ignored)

SKIP rest of the sections

chapter 21 concurrency control techniquesorion.towson.edu/~karne/teaching/c657sl/ch21notes.pdf3 (b)...

Documents