• 沒有找到結果。

Database Systems (資料庫系統) Chapter 17

N/A
N/A
Protected

Academic year: 2021

Share "Database Systems (資料庫系統) Chapter 17"

Copied!
20
0
0

加載中.... (立即查看全文)

全文

(1)

Database Systems

( 資料庫系統 )

December 27, 2004

Chapter 17

(2)

Announcements

• Assignment #10 (Crash Recovery) will be (is) out on the

course homepage

• We will cover chapter 17.1 ~ 17.4 only.

• Final exam period 1.10.2005 ~ 1.15.2005

(3)

Cool & Practical Ubicomp Project

(4)

Concurrency Control

(5)

Conflict Serializable Schedules

• Two schedules are

conflict equivalent

if:

– Involve the same (write/read) actions of the same transactions – Every pair of conflicting actions is ordered the same way (conflict

ing actions are actions on the same data object and at least one of the action is a write.)

• Schedule S is

conflict serializable

if S is conflict equivale

nt to a

serial schedule

– A serial schedule is a schedule with no interleaving actions from different transactions.

– A serializable schedule is a schedule that produces identical res ult as some serial schedule.

– A conflict serializable schedule is serializable.

(6)

Example

• The right schedule is

serializable, but not conflict

serializable:

– Why serializable? Same result as T1,T2,T3.

– Why not conflict serializable? Writes of T1 and T2 (conflicting actions) are ordered differently than the serial schedule of

T1,T2,T3. T1 T2 T3 R(A) W(A) Commit W(A) Commit W(A) Commit

(7)

Precedence Graph

• How do we know a given

schedule is conflict serializable?

• Capture the conflicting actions on

a

precedence graph

& check for

cycle in the graph.

– One node per transaction

– Edge from Ti to Tj if an action of Ti

precedes and conflicts (R/W, W/R, W/R) with one of Tj’s actions.

• Schedule is conflict serializable if

and only if its precedence graph

is acyclic.

T1 T2 T3 R(A) W(A) Commit W(A) Commit W(A) Commit T1 T2 1 2 1 2

(8)

Review: Strict 2PL

• Strict Two-phase Locking (Strict 2PL) Protocol

:

– If a transaction T wants to read an object, it requests a shared lock. Denote as S(O). If a transaction T wants to write an object, it requests an exclusive lock. Denote as X(O).

– Locks are released only when transaction is completed (aborted).

– If a transaction holds an X lock on an object, no other

transaction can get a lock (S or X) on that object.

• Strict 2PL allows only schedules whose precedence

graph is acyclic!

– Strict 2PL allows only conflict serializable schedules.

– Why? Say, T1 & T2 have conflicting actions. If T1 obtains the X lock on the data object first, T2 must wait until T1 is done. T2’s potential conflicting actions cannot precede T1’s conflicting actions.

(9)

Two-Phase Locking (2PL)

• 2PL Protocol is the same as Strict 2PL, except

A transaction can release locks before the end (unlike Strict

2PL), i.e., after it is done with reading & writing the objects.

– However, a transaction can not request additional locks after it

releases any locks.

– 2PL has lock growing phase and shrinking phase.

2PL also produces conflict serializable schedules.

What is the benefit of 2PL over strict 2PL?

Smaller lock holding time -> better concurrency

What is the benefit of strict 2PL over 2PL?

(10)

Non-recoverable

Schedule

• Schedule allowed by 2PL

may not be recoverable in

aborts:

– Say T1 aborts and we need to undo T1.

– But T2 has read a value for A that should never been there.

– But T2 has committed! (may not be able to undo committed actions). T1 T2 X(A) R(A) W(A) Release X(A) X(A) R(A) W(A) X(B) R(B) Release X(A) W(B) Release X(B) Commit Abort

(11)

View Serializability

• Conflict serializable is sufficient but not nec essary for serializability. (too strict)

– More general sufficient condition is view seri alizability.

• Schedules S1 and S2 are view equivalent if :

1. If Ti reads initial value of A in S1, then Ti als

o reads initial value of A in S2.

2. If Ti reads value of A written by Tj in S1, the

n Ti also reads value of A written by Tj in S2.

3. If Ti writes final value of A in S1, then Ti also

writes final value of A in S2.

• A view serializable schedule is view equival

T1 T2 T3 R(A) W(A) W(A) W(A) T1 T2 T3 R(A) W(A) W(A) 1 3 3 1

(12)

Lock Management

• Lock and unlock requests are handled by the

lock

manager

.

• Each lock manager maintains a

lock table

of

l

ock table

entries.

• Each lock table entry keeps info about:

– The data object (page, record) being locked

– Number of transactions currently holding a lock (>1 if shared

mode)

Type of lock held (shared or exclusive) – Lock request queue

(13)

Lock Management Implementation

• If a shared lock is requested:

– Check if the request queue is empty. Check if the lock is in exclusive/share mode. If (yes,share), grant the lock & update the lock table entry.

• When a transaction aborts or commits, it releases all

lock.

– Update the lock table entry. Check for lock request queue, …

• Locking and unlocking have to be atomic operations:

– E.g., cannot have concurrent operations on the same lock table entry.

• Latches: locks for reading and writing pages to disks

(ensure they are atomic)

(14)

Lock Conversions

• Lock upgrade: transaction that holds

a shared lock can be upgraded to

hold an exclusive lock.

– Get shared lock on each row in a table. – When a row meets the condition, get an

exclusive lock.

• Alternative approach is lock

downgrade.

– Get exclusive lock on each row in a table.

– When a row does not meet the

condition, downgrade to shared lock.

UPDATE Sailors S SET S.age=10

WHERE S.name=“Joe” AND S.rating=8

(15)

Deadlocks

• Deadlock: Cycle of transactions waiting for

locks to be released by each other.

• Two ways of dealing with deadlocks:

Deadlock detectionDeadlock prevention

• Deadline Detection:

– Create a waits-for graph:

Nodes are transactions

There is an edge from Ti to Tj if Ti is waiting for Tj to

release a lock

– Periodically check for cycles in the waits-for graph,

• cycle = Deadlock

• Resolve a deadlock by aborting a transaction on a cycle.

T1 T2 S(A) R(A) S(B) R(B) X(B): W(B) X(A): W(A)

(16)

Deadlock Detection

T1

T2

T4

T3

T1

T2

T4

T3

T1 T2 T3 T4 S(A) R(A) X(B) W(B) S(B) S(C) R(C) X(C) X(B) X(A)

(17)

Deadlock Prevention

• Basic ideas:

– Assign priorities to transactions based on timestamps when they start up. (lower timestamps = higher priority)

– Lower priorities cannot wait for higher-priority transactions.

• Assume Ti wants a lock that Tj holds. Two policies are

possible to prevent deadlocks:

Wait-Die: If Ti has higher priority, Ti waits for Tj; otherwise Ti aborts (lower-priority T never waits for higher-priority T)

Wound-wait: If Ti has higher priority, Tj aborts; otherwise Ti waits. (higher-priority T never waits for lower-priority T)

• Why these two policies prevent deadlocks?

(18)

Wait-Die Policy

• Lower-priority T never waits for higher-priority T • If Ti has higher priority, Ti waits for Tj; • Otherwise Ti aborts T1 T2 T3 T4 S(A) R(A) X(B) W(B) S(B) S(C) R(C) X(C) X(B) // abort X(A) // abort

(19)

Wound-Wait Policy

• Higher-priority T

never waits for l ower-priority T • If Ti has higher p riority, Tj aborts; • Otherwise Ti wai ts. T1 T2 T3 T4 S(A) R(A) X(B) W(B) S(B) // Abort T2 Abort

(20)

Deadlock Prevention (2)

• If a transaction re-starts, make sure it has its original tim

estamp.

– It will not be forever aborted due to low priority.

• Conservative 2PL

: ensure no deadlock (no blocking) duri

ng transaction

– Each transaction begins by getting all locks it will ever need – What is the tradeoff?

參考文獻

相關文件

– Write special code to protect against system crashes – Optimize applications for efficient access and query – May often rewrite applications. • Easier to buy a DBMS to handle

– File and index layers organize records on files, and manage the indexing data

ArchIS’ architecture uses (a) XML to support temporally grouped (virtual) representations of the database history, (b) XQuery to express powerful temporal queries on such views,

• Each row corresponds to one truth assignment of the n variables and records the truth value of φ under that truth assignment. • A truth table can be used to prove if two

In this project, we discovered a way to make a triangle similar to a target triangle that can be inscribed in any given triangle. Then we found that every triangle we’ve made in a

Through shared vision, mission, objectives and strategies, we aim to build a quality teaching profession comprising teachers and school leaders who, in pursuit of

For ASTROD-GW arm length of 260 Gm (1.73 AU) the weak-light phase locking requirement is for 100 fW laser light to lock with an onboard laser oscillator. • Weak-light phase

The Method of Shared Concern: A Positive Approach to Bullying in Schools.. Victoria: