Guarantee conflict Serializable schedule - Lecture Notes | EECS 647, Study notes of Deductive Database Systems

Download Guarantee conflict Serializable schedule - Lecture Notes | EECS 647 and more Study notes Deductive Database Systems in PDF only on Docsity!

EECS 647: Introduction to

Database Systems

Instructor: Luke Huan

Spring 2009

4/26/

Luke Huan Univ. of Kansas

2

Today’s Topic

z

Guarantee conflict-serializable schedule

4/26/

Luke Huan Univ. of Kansas

4

Basic locking is not enough

T

1

T

2

r(

A

w(

A

r(

A

w(

A

r(

B

w(

B

r(

B

w(

B

lock-X(

A

lock-X(

B

unlock(

B

unlock(

A

)^

lock-X(

A

unlock(

A

unlock(

B

lock-X(

B

Possible schedule

under locking

But still not

conflict-serializable!

T^1 T^2

Read 100 Write 100+

Read 101Write 1012Read 100Write 100

Read 200 Write 200+

Add 1 to both

A

and

B

(preserve

A

= B

)

Multiply both

A

and

B

by 2

(preserves

A

= B

)

A^

≠^

B!

4/26/

Luke Huan Univ. of Kansas

5

Two-phase locking (2PL)

z

All lock requests precede all unlock requests^ z

Phase 1: obtain locks, phase 2: release locks

T^1

T^2

r(

A ) w(

A )

r(

A ) w(

A ) r(

B ) w(

B )

r(

B ) w(

B )

lock-X(

A )

lock-X(

B )

unlock(

B )

unlock(

A )

lock-X(

A )

lock-X(

B ) Cannot obtain the lock on

B

until

T

unlocks 1

T^1

T^2

r( A ) w(

A )

r( A ) w(

A )

r( B ) w(

B )

r( B ) w(

B )

2PL guarantees a

conflict-serializable

schedule

4/26/

Luke Huan Univ. of Kansas

7

Strict 2PL

z

Only release locks at commit/abort time^ z

A writer will block all other readers until the writercommits or aborts

)

Used in most commercial DBMS

4/26/

Luke Huan Univ. of Kansas

8

Next ...

z

A few examples

4/26/

Luke Huan Univ. of Kansas

10

Unlock(A)Read(B)Unlock(B) PRINT(A+B)

Read(B)B := B +50Write(B)Unlock(B)

Read(A)Lock_S(B)

A: = A-50Write(A)Lock_X(B)Unlock(A)

Lock_S(A)

Lock_X(A)Read(A)

2PL, A= 1000, B=2000, Output =?

4/26/

Luke Huan Univ. of Kansas

11

Read(A)Lock_S(B)Read(B)PRINT(A+B)Unlock(A) Unlock(B)

A: = A-50Write(A)Lock_X(B)Read(B)B := B +50Write(B)Unlock(A)Unlock(B)

Lock_S(A)

Strict 2PL, A= 1000, B=2000, Output =?Lock_X(A)Read(A)

Lock Management, cont.

z

When lock request arrives:^ z

Does any other xact hold a conflicting lock? z

If no, grant the lock. z

If yes, put requestor into wait queue.

z

Lock upgrade:^ z

Shared lock can request to upgrade to exclusive

Deadlocks

z

Deadlock

: Cycle of transactions waiting for locks to be

released by each other.

z

Two ways of dealing with deadlocks:^ z

prevention

z

detection

z

Many systems just punt and use Timeouts^ z

What are the dangers with this approach?

Deadlock Detection (Continued)

Example:T1: S(A), S(D),

S(B)

T2:

X(B)

X(C)

T3:

S(D), S(C),

X(A)

T4:

X(B)

T

T

T

T

Deadlock!

Deadlock Prevention

z

Assign priorities based on timestamps.

z

Say Ti wants a lock that Tj holdsTwo policies are possible: Wait-Die

: If Ti has higher priority, Ti waits for Tj; otherwise Ti aborts

Wound-wait

: If Ti has higher priority, Tj aborts;

otherwise Ti waits

z

Why do these schemes guarantee no deadlocks?

z

Important detail: If a transaction re-starts, make sure it gets itsoriginal timestamp. -- Why?

Recovery Motivation

z

Atomicity:^ z

Transactions may abort (“Rollback”).

z

Durability:^ z

What if DBMS stops running? (Causes?)

crash!

™

Desired state after systemrestarts:

-^

T1 & T3 should be durable.

-^

T2, T4 & T5 should beaborted (effects not seen).

T1T2T3T4T

Abort

Commit

Commit

4/26/

Luke Huan Univ. of Kansas

20

Handling Failures

z

System crashes in the middle of a transaction

T

; partial

effects of

T

were written to disk

z

How do we undo

T

(atomicity)?

z

System crashes right after a transaction

T

commits; not

all effects of

T

were written to disk

z

How do we complete

T

(durability)?