1 / 100

PART 5

PART 5. TRANSACTION MANAGEMENT. Chapter 16 Concurrency Control. Introduction to Chapter 16. Concurrency control of processes OS 中多个 进程 对 共享资源 的使用： 互斥使用 进程并发控制（互斥与同步）机制： 软件方法，硬件方法， 信号量 ，管程，临界域 信号量方法： wait(S) critical section signal(S)

paulaallen
Télécharger la présentation

PART 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PART 5 TRANSACTION MANAGEMENT

  2. Chapter 16 Concurrency Control

  3. Introduction to Chapter 16 • Concurrency control of processes • OS中多个进程对共享资源的使用：互斥使用 • 进程并发控制（互斥与同步）机制： 软件方法，硬件方法，信号量，管程，临界域 • 信号量方法： wait(S) critical section signal(S) • 锁/lock S：二元信号量，实现对共享资源的互斥使用，{0，1} 表示资源使用情况，锁操作控制进程的执行和等待 • deadlock handling • deadlock prevention, deadlock avoidance, deadlock detection and recovery Database System Concepts - Chapter 16 Concurrency Control -

  4. Introduction to Chapter 16 (cont.) • Transaction concurrency control • /*根据可串行化基本原理, 采用以下机制控制多个进程对共享数据的互斥访问 , 实现对事务的并发调度 • lock-based protocols (§16.1) • timestamp-based protocols (§16.2) • validation-based protocols (验证，§16.3) • multi-version schemes (多版本，§16.4) • deadlock handling (§16.6) • deadlock prevention • deadlock detection and recovery Database System Concepts - Chapter 16 Concurrency Control -

  5. §16.1 Lock-based Protocol • Principles /*用二元信号量“lock”控制并发事务Ti对共享数据Q的访问 • Ti存取Q时，先向DBMS申请对Q加锁 • 如果此时Q未被其它事务加锁，则Ti加锁成功，继续执行对Q的访问 • 如果此时Q已经被其它事务加锁，则Ti等待，直至Q上的锁被释放， Ti获得锁后继续执行 • Ti执行完对Q的操作后，解锁Q • DBMS根据lock-compatibility matrix将lock授予请求的事务 • 事务执行过程: 申请锁——加锁——读写——解锁 Database System Concepts - Chapter 16 Concurrency Control -

  6. 16.1-1 Locks • DBMS use the lock (as the binary semaphore) to control transaction’s access to shared data items • a transaction is allowed to access a data item only if it is currently holding a lock at that item • With respect to a shared data item Q, two modes in which Q is locked, and corresponding two modes of locks on Q • shared-mode lock: S(Q), only read(Q) are permitted • exclusive-mode lock: X(Q), read(Q) and write(Q) are permitted • Lock primitives • lock-S(Q), unlock-S(Q) • lock-X(Q), unlock-X(Q); Database System Concepts - Chapter 16 Concurrency Control -

  7. 16.1-1 Locks (cont.) • Concurrency-control manager/scheduler in DBMS grants lock to transactions who want to access Q according to lock-compatibility matrix • assuming that Ti requests a lock of mode A on data item Q on which another transaction Tj currently holds a lock of mode B, if Ti can be granted a lock of mode A, then mode A is compatible with modeB Tj B Ti A Fig.16.1 Lock-compatibility matrix Database System Concepts - Chapter 16 Concurrency Control -

  8. 16.1-1 Locks (cont.) • In accordance with Fig.16.1, several shared locks can be held simultaneously (by different transactions) on shared data item Q; • When Ti requests a lock of mode Ato operate on data item Q, e.g. S(Q) related to read(Q), while Q is already locked in a incompatible mode B byTj, e.g. X(Q) related to write(Q), then • Ti must wait until the lock in mode B held by Tj is released, the lock is then granted to Ti Database System Concepts - Chapter 16 Concurrency Control -

  9. 16.1-1 Locks (cont.) • Procedures of Ti’s executing to access a data item Q (1) other operations in Ti (2) request a lock S(Q) or X(Q), by the primitive lock-S(Q) or lock-X(Q) (3) wait for concurrency-control manager’s granting of the lock requested; (4) whenever obtaining the lock, make access on Q; (5) after finishing data access on Q, release lock on Q, by unlock-S(Q) or unlock-X(Q); (6) other operations; Database System Concepts - Chapter 16 Concurrency Control -

  10. An Example • T1: transfer $50 from account-B to account-A, Fig.16.2 • T2: display the total amount of A and B, Fig.16.3 • T1 and T2unlock data item A and B immediately after its final access of that data • Initially, A=100, B=200 • For the concurrent schedule S1 (given in Fig.16.4) on T1 and T2 , an inconsistent result display(A+B) = 250 exist, why? • T1 unlocked data item B too early, as a result of which T2 saw a inconsistent state Database System Concepts - Chapter 16 Concurrency Control -

  11. A T4 T3 B An Example (cont.) • T3: transfer $50 from account-B to account-A, Fig.16.5 • T4: display the total amount of A and B, Fig.16.6 • T3 and T4unlock data item A and B at the end of transaction • Initially, A=100, B=200 • The concurrent schedule S2 (given in Fig.16.7) on T3 and T4 leads to deadlock Database System Concepts - Chapter 16 Concurrency Control -

  12. Locking protocol • As revealed by this example, in lock-based schemes, the orders of granting locks to and revoking locks from transactions must be limited to avoid inconsistent DB states and starvation of transactions • Locking protocol • a set of rules that transactions should follow, indicating when a transaction may lock and unlock each of the data items • Locking protocols restrict the number of possible schedules, and lead to a subsetof all possible conflict serializable schedules Database System Concepts - Chapter 16 Concurrency Control -

  13. 16.1-2 Two Phase Locking Protocol/2PL • In accordance with 2PL, each transaction Ti issues lock and unlock requests in two phases • growing phase: Ti may obtain locks, but may not release any locks • shrinking phase:Ti may release locks, but may not obtain any locks • when Ti begins, it enters growing phase • when Ti releases its first lock by unlock, it enters shrinking phase, and is not allowed to apply for locks, i.e. not allowed to enter growing phase Database System Concepts - Chapter 16 Concurrency Control -

  14. 16.1-2 Two Phase Locking Protocol (cont.) • E.g. T3 in Fig.16.5 and T4 in Fig.16.6 obey 2PL, but T1 in Fig.16.2 and T2 in Fig.16.3 not T2: lock-S(A); read (A); unlock(A); lock-S(B); read (B); unlock(B); display(A+B) Fig.16.3 Database System Concepts - Chapter 16 Concurrency Control -

  15. Ti Tj lock-X(R) waiting … obtaining the lock read(R) lock-X(Q) read(Q) lock-S(R) read(R) unlock(R) write(Q)

  16. 16.1-2 Two Phase Locking Protocol (cont.) • Lock point • define1. the point in the transaction/schedule where the transactions has obtain its final lock, but has not yet unlock any locks • define2. the point just before the transaction releases its first lock • E.g. lock-point in T5 in Fig.16.8 Database System Concepts - Chapter 16 Concurrency Control -

  17. T5 Lock-X(A) read (A) Lock-S(B) read (B) Unlock (B) write (A) Unlock (A) growing phase lock point Shrinking phase 16.1-3 Two Phase Locking Protocol (cont.) Fig.16.8 Partial schedule under two-phase locking Database System Concepts - Chapter 16 Concurrency Control -

  18. !! Properties of 2PL • 2PL guarantees conflict serializability, i.e. generates conflict serializable concurrent schedules • 2PL does not ensure freedom from deadlock • 2PL does not avoid cascading rollback Database System Concepts - Chapter 16 Concurrency Control -

  19. 16.1-3 Two Phase Locking Protocol (cont.) • Given a concurrent schedule S in line with 2PL, S is equivalent to a serial schedule S’, such that • the ordering of lock points of each transactions in S is the same as the ordering of transactions in S’ • E.g. S3 in Fig.16.0.1 is conflict serializable, and equivalent to <T5; T6’ > • the lock point of T5 is before the lock point of T6’ Database System Concepts - Chapter 16 Concurrency Control -

  20. T5 Lock-X(A) read (A) Lock-S(B) read (B) Unlock (B) write (A) Unlock (A) T6’ Lock-X(B) read (B) write (B) Unlock (B) LP5 Lock-X(B) waiting LP6’ Fig. 16.0.1 A concurrent schedule S3 under 2PL protocol constraints

  21. How to construct the schedule in line with 2PL • /*给定若干事务, 构造满足2PL的并发调度S！！ • 为各事务添加加锁和解锁操作，各事务应满足2PL要求，即事务内部分为生长和收缩2个阶段 • 根据各事务的锁点位置和锁的互斥性 (相容关系)，安排各事务的并发执行 • S中各事务的growing phase和shrinking phase 应尽可能错开，不允许访问同一数据的2个事务（冲突事务）同时处于growing phase • E.g. A concurrent schedule S4 in Fig.16.0.2 Database System Concepts - Chapter 16 Concurrency Control -

  22. T6’ Lock-X(B) read (B) write (B) Unlock (B) T5 Lock-X(A) read (A) Lock-S(B) read (B) Unlock (B) write (A) Unlock (A) G G S S • S4 is equivalent to <T6’;T5> Fig. 16.0.2 A concurrent schedule S4 under 2PL protocol constraints

  23. T7 read (A) read (B) write (A) T7’ Lock-X(A) read (A) Lock-S(B) read (B) Unlock (B) write (A) Unlock (A) T8 read (B) write (B) read (C) T8’ Lock-X(B) read (B) Lock-S(C) write (B) read (C) Unlock (B) Unlock (C) Fig.16.0.3-1 A concurrent schedule S5 How to construct the schedule in line with 2PL (cont.) • E.g. Construct a concurrent schedule that is equivalent to < T7;T8 > • step1. 分别为T7和T8加锁操作, 构成T7’和T8’ Database System Concepts - Chapter 16 Concurrency Control -

  24. step2. 根据锁的相容性构造调度，假设T7’的锁点先于T8’的锁点 T7’ Lock-X(A) read (A) Lock-S(B) read (B) Unlock (B) write (A) Unlock (A) T8’ Lock-X(B) read (B) Lock-S(C) write (B) read (C) Unlock (B) Unlock (C) Fig.16.0.3-2 A concurrent schedule S5

  25. §16.1-3 Enhanced Protocol • There are two types of enhanced 2PL protocols, that is, strict 2PL and rigorous 2PL • Strict (严格) 2PL protocol • all X-lockstaken by a transaction are held until that transaction commits, that is, all X-locks can only be unlocked at the end of the transaction • leading to cascadeless and serializable schedules Database System Concepts - Chapter 16 Concurrency Control -

  26. T1 Lock-X(A) read (A) Lock-S(B) read (B) Unlock (B) write (A) Unlock (A) growing phase lock point shrinking phase §16.1-3 Enhanced Protocol (cont.) • E.g. schedule S6 in Fig.16.0.4 Fig.16.0.4 A schedule S6 under strict 2PL Database System Concepts - Chapter 16 Concurrency Control -

  27. §16.1-3 Enhanced Protocol (cont.) • Rigorous (强制) 2PL protocol • all X-locks and S-locks taken by a transaction are held until that transaction commits • leading to transactions are serialized in the order in which they commit • Schedule S7 in Fig.16.0.5 • Most DBMS implement either strict or rigorous 2PL protocol. Database System Concepts - Chapter 16 Concurrency Control -

  28. T1 Lock-X(A) read (A) Lock-S(B) read (B) write (A) Unlock (A) Unlock (B) growing phase lock point shrinking phase §16.1-3 Enhanced Protocol (cont.) Fig.16.0.5 A schedule S7 under rigorous 2PL Database System Concepts - Chapter 16 Concurrency Control -

  29. §16.2 Timestamp-based Protocol • 16.2-1 Timestamps • Each transaction Ti is allocated a timestamp TS(Ti)when it enters the system, i.e. it is created • for an“old” transaction Ti and a “new/young” transaction Tj , TS(Ti) <TS(Tj). /*事务启动时被赋予时间戳，DBMS利用时间戳对事务进行全局排序：较早启动的事务具有较小的时间戳 • note: timestamps can be implemented by system clock or logical counter Database System Concepts - Chapter 16 Concurrency Control -

  30. 16.2-1 Timestamps (cont.) • Each data Q in database is associated with two timestamp values • W-timestamp(Q) is the largest time-stamp of any transaction that executed write(Q) successfully, i.e. W-timestamp(Q) = max {TS(Ti) | Tisuccessfully executes write on Q } /* 成功执行“写Q”的“最年轻”事务Ti的时间戳， 或 /*最后/新一次写Q的事务Ti的时间戳 • refer to Fig.16.0.6-1 and Exercise 16.11 in the textbook in P678 • in Fig.16.0.6-1 (b), the write issued by Ti1 will be rejected, Ti1is roll backed and reassigned a new timestamp that is larger than TS (Ti2) and TS (Ti3) Database System Concepts - Chapter 16 Concurrency Control -

  31. be rejected Ti1: … .. write(Q) … Ti1: … write(Q) … W-timestamp(Q) = TS (Ti3) W-timestamp(Q) = TS (Ti3) … write(Q) … … … Ti2: … write(Q) … … Ti2: Ti3: … … write(Q) … Ti3: … … write(Q) … t t W W W W W W Q Q (a) (b) Fig.16.0.6-1 Definitions of W-timestamp(Q)

  32. 16.2-1 Timestamps (cont.) • R-timestamp(Q) is the largest time-stamp of any transaction that executed read(Q) successfully, i.e., R-timestamp(Q) = max {TS(Ti) | Ti successfully executes read on Q } /* 成功执行“读Q”的“最年轻”事务Ti的时间戳， • refer to Fig.16.0.6-2 • in Fig.16.0.6-2 (b), the read issued by Ti1 will not be rejected and Ti1 conducts read successfully • because the read issued by Ti1 is not conflict with those issued by Ti2 and Ti3 Database System Concepts - Chapter 16 Concurrency Control -

  33. … read(Q) … .. Ti1: Ti2: … read(Q) …. Ti3: … read(Q) … … t R R R Q Ti1: … read(Q) … … R-timestamp(Q) = TS (Ti3) … read(Q) … … Ti2: Ti3: … … read(Q) … t W W W Q R-timestamp(Q) = TS (Ti1) Fig.16.0.6-2 Definitions of timestamps on data item Q

  34. 16.2-2 Timestamp-ordering Protocol • For a set of concurrent transactions, the orders of conflicting read and write operations are arranged in accordance with timestamp orders • /* 2事务发生冲突时，较早启动的事务(具有较小的时间戳)中的read and write具有较高的执行优先级 • /*冲突操作按时间戳顺序依次串行执行, 即先启动事务中的冲突操作应该先执行!! • For a transaction Ti , when it issues a read(Q) or write(Q), the protocol operates as one of the following five cases Database System Concepts - Chapter 16 Concurrency Control -

  35. 16.2-2 Timestamp-ordering Protocol (cont.) — Case One • When Ti issues a read(Q), if TS(Ti ) < W-timestamp(Q), then • the read (Q) is rejected • Ti is rolled back （撤销Ti对DB的修改）and assigned a new timestamp • refer to Fig.16.0.7 • Why? • /*假设W-timestamp(Q) = TS(Tj), 即最近一次对Q的写操作是由Tj完成的，则TS(Ti ) < TS(Tj)= W-timestamp(Q) • write(Q) in Tj与read(Q) in Ti相互冲突，后启动/年轻的Tj先于Ti的读操作之前执行了写操作，不满足执行顺序要求 Database System Concepts - Chapter 16 Concurrency Control -

  36. Ti: … …. … read(Q) …. …. write(Q) … …. … Tj: t W TS(Ti) TS(Tj) R Q Tj is younger than Ti newTS(Ti) TS(Ti ) <W-timestamp(Q) = TS(Tj) Fig. 16.0.7 Read-Writeconflicton data item Q

  37. 16.2-2 Timestamp-ordering Protocol (cont.) — Case Two • When Ti issues a read(Q), if TS(Ti )  W-timestamp(Q), then • the read (Q) is successfully executed • R-timestamp(Q) is set to R-timestamp(Q) = max{R-timestamp(Q), TS(Ti)} • refer to Fig. 16.0.8 • Why? • /*假设TS(Ti ) > W-timestamp(Q) = TS(Tj), 则最近一次对Q的写操作是由Tj完成的; • /*假设TS(Ti ) = W-timestamp(Q), 最近一次对Q的写操作是由Ti自己完成的; • /* write(Q) in Tj先于read(Q) in Ti执行，即先启动的事务Tj先执行操作，满足执行顺序要求，无冲突 Database System Concepts - Chapter 16 Concurrency Control -

  38. Tj: … … write(Q) … … … write(Q) … read(Q) …. Ti: t W TS(Tj) TS(Ti) R Q Tj is older than Ti TS(Tj ) <TS(Ti) (a) TS(Ti ) >W-timestamp(Q) = TS(Tj), or (b) TS(Ti ) = W-timestamp(Q) Fig. 16.0.8 Non-conflictRead-Writeon data item Q

  39. 16.2-2 Timestamp-ordering Protocol (cont.) — Case Three • When Ti issues a write(Q), if TS(Ti ) < R-timestamp(Q), then • the write(Q) is rejected • Ti is rolled back （撤销Ti对DB的修改）and assigned a new timestamp • refer to Fig. 16.0.9 • Why? • /*假设R-timestamp(Q)= TS(Tj), 即最近一次对Q的读操作read(Q)是由Tj完成的，则TS(Ti ) <TS(Tj) • read(Q) in Tj与write(Q) in Ti相互冲突，并且后启动的Tj先于Ti的写操作write(Q)之前执行了读操作read(Q) ，不满足执行顺序要求 Database System Concepts - Chapter 16 Concurrency Control -

  40. … … … write(Q) … Ti: Tj: … read(Q) … … R TS(Ti) TS(Tj) W Q Tj is younger than Ti t newTS(Ti) TS(Ti) <TS(Tj) TS(Ti ) <R-timestamp(Q) = TS(Tj) Fig. 16.0.9 Write-Readconflicton data item Q

  41. 16.2-2 Timestamp-ordering Protocol (cont.) — Case Four • When Ti issues a write(Q), if TS(Ti ) < W-timestamp(Q), then • the write(Q) is rejected • Ti is rolled back （撤销Ti对DB的修改）and assigned a new timestamp • refer to Fig. 16.0.10 • Why? • /*假设W-timestamp(Q)= TS(Tj), 即最近一次对Q的写操作write(Q)是由Tj完成的，则TS(Ti ) <TS(Tj) • write(Q) in Tj与write(Q) in Ti相互冲突，并且后启动的Tj先于Ti的写操作write(Q)之前执行了写操作write(Q) ，不满足执行顺序要求 Database System Concepts - Chapter 16 Concurrency Control -

  42. …. write(Q) … ………… Ti: Tj: … … write(Q) …. …. t W TS(Ti) TS(Tj) W Q TS(Ti) <TS(Tj) Tj is younger than Ti newTS(Ti) TS(Ti ) <W-timestamp(Q) = TS(Tj) Fig.16.0.10 Conflict Write-Writeon data item Q

  43. 16.2-2 Timestamp-ordering Protocol (cont.) — Case Five • When Ti issues a write(Q), if TS(Ti )  W-timestamp(Q) and TS(Ti )  R-timestamp(Q), then • the write(Q) is executed, and • W-timestamp(Q) is set to TS(Ti), i.e. W-timestamp(Q) := TS(Ti) • refer to Fig.16.0.11 • Why? • 已经完成的对Q的读read(Q)或写write(Q)都是由先于Ti启动的事务（>）或Ti自身（=）完成的 • write(Q) in Ti与以前完成的 in Tj或in Tj无冲突，满足执行顺序要求 Database System Concepts - Chapter 16 Concurrency Control -

  44. … …. read(Q)… write(Q) … Tj: read(Q)… write(Q)…… … write(Q) …. Ti: t R W TS(Tj) TS(Ti) W Q Tj is older than Ti TS(Tj ) <TS(Ti) TS(Ti ) >W-timestamp(Q) = TS(Tj), orTS(Ti ) =W-timestamp(Q) ; AND TS(Ti ) >R-timestamp(Q) = TS(Tj), or TS(Ti ) = R-timestamp(Q) Fig.16.0.11 Non-conflict Write-Read or Write-Write on data item Q

  45. 16.2-3 Properties of Timestamp-based Protocol • It guarantees conflict serializability • It ensures freedom from deadlock • Why? no transaction ever waits • the transaction that can not successfully conduct write or read are rolled back and reassigned a new timestamp to restart • In contrast with the properties of 2PL ( ) and strict 2PL( ) • Timestamp-based protocol may lead to starvation of long transaction • The schedule in line with this protocol may not be cascade-free, and may not even be recoverable Database System Concepts - Chapter 16 Concurrency Control -

  46. T2 read(A) A:= A-50 write(A) read(B) B:= B+50 write(B) display(A+B) T1 read(B) read(A) display(A+B) 16.2-4 Examples about Timestamp-based Protocol • E.g.1 Assuming TS(T1) < TS(T2), is the following schedule S under the timestamp protocol ? Why? • S is not under the timestamp protocol • because the read(A) in T1 is executed after the write(A) in T2 Database System Concepts - Chapter 16 Concurrency Control -

  47. 16.2-4 Examples about Timestamp-based Protocol (cont.) • E.g.2A timestamp-based partial schedule for transactions T1, T2, T3, T4, and T5, which are with initial timestamps 1, 2, 3, 4, 5 respectively, and operate on data items X, Y, and Z • refer toFig.16.0.12 • write(Z) in T3 is rejected • T3 is reallocated a new timestamp, e.g. TS(T3 )=30, to restart (denoted as T3’ ) • T5 that is dependent on T3 may also needed to rollback • E.g.3Analysisof timestamp-based schedule S3 for T14 and T15 in Fig.16.13 in the textbook • Fig. 16.0.13 Database System Concepts - Chapter 16 Concurrency Control -

  48. timestamps: 1 2 3 4 5 30 transaction: T3’ T1 T2 T3 T4 T5 … … … … … read(X) read(Y) read(Y) write(Y) write(Z) read(Z) read(X) read(X) write(Z) rollback write(Y) write(Z) write(Y) restart write(Z) t write(Z) Fig.16.0.12 A timestamp-based schedule under timestamp constraints

  49. T14 T15 TS(T14) TS(T15) R-TS(A) W-TS(A) R-TS(B) W-TS(B) 1 0 0 0 0 .. .. 2 1 > 0→ 1 0 read(B) read(B) B:=B-50 write(B) 2 > 1→ 2 0 2 = 2 > 0 0→ 2 read(A) 1 > 0→1 0 read(A) A:=A+50 write(A) dis(A+B) 0 2 > 1→2 dis(A+B) 2 = 2 > 0 0→2 Fig.16.0.13 Analysisof S3 in Fig.16.13

  50. 16.2-4 Examples about Timestamp-based Protocol (cont.) • E.g.4 Analysisof timestamp-based schedule for T16 and T17, in which T16 is rolled back • Fig.16.0.14 Database System Concepts - Chapter 16 Concurrency Control -

More Related