数据库系统教学课件：第15讲-并发控制技术

1、第15讲:(第12章) 第15讲-事务管理(并发控制技术)1课程名称: 数据库系统 -2一 锁与封锁协议1.锁2)什么是锁相容矩阵，一个S锁能带来多大并发能力? 讨论1.加锁的目的，及如何有效使用？1)为何要引入锁,及不同类型锁的作用? 引入锁机制：是为了保证数据的一致性(事务的隔离性)和提高系统的并发处理能力!或者说，为保了证应用的有效性(两人不会订到同一位置火车票，查看到一个不存在的成绩-如教务处刚误输一个学生的成绩后马上又删除)可大可小(大到整个数据库，小到一个记录或属性值)指多大对象？S锁已明显增强了并发能力：因为可能95%以上的应用是读数据，更新应用的频率非常小。没有S锁，系统的发处

2、理能力会大大降低，仅比串行调度略好。32.封锁协议 T1: lock-X(B); read (B); B:=B-50; write (B); unlock(B); Lock-X(A) read(A) A:=A+50 write(A) unlock(A)T2: lock-S(A); read (A); unlock(A); lock-S(B); read (B); unlock(B); display(A+B)图25-44）这是一个可串行化调度？仍不是可串行化调度！若：A is 100B is 200则 T2调度将会显示：A+B=250-而串行调度后T2显示A+B=300-且串行调度后T2显示A

3、+B=300冲突操作冲突操作3)什么是封锁协议，加锁后的调度什么样? 转 账查总账指一组加锁规则！grant-X(B,T1)grant-S(A,T2)释放-S(B,T2)释放-S(A,T2)（并发控制器）释放-X(B,T1)grant-S(B,T2)grant-X(A,T1)释放-X(A,T1)一 锁与封锁协议43.死锁与活锁grant-X(B,T3)-成功grant-S(A,T4) -成功grant-S(B,T4) 等待grant-X(A,T3) -等待形成两个事务相互等待对方释放资源各自才能往下继续做的僵局！Starvation饿死(活锁) is also possible if conc

4、urrency control manager is badly designed. For example:1) A transaction may be waiting for an X-lock on an item, while a sequence of other transactions request and are granted an S-lock on the same item. 2) The same transaction is repeatedly rolled back due to deadlocks.出现一个事务永远(长时间)等待某一数据项被其它事务释放后才

5、能进行封锁的现象！ T1 T2 T3Lock-S(A)Read(A)lock-S(B)read(B)unlock(B)lock-S(B)read(B)unlock(B) Lock-X(B)Write(B)unlock(B) Tngrant-X(B,T1) 永远等待grant-S(B,T2) 成功grant-S(B,T2) 成功释放-S(B,T2)释放-S(B,T3)一 锁与封锁协议5二 两阶段封锁协议1. 两阶段封锁协议这些协议都是两阶段封锁协议吗，封锁点在何处？2)两阶段封锁协议的主要用途？T1和T2不是两阶段封锁协议，T3和T4都是两阶段封锁协议！封锁点封锁点（两阶段封锁协议可串行化的例示

6、分析）（p.376但两阶段封锁并不能保证不会发生死锁的示例分析）6两阶段封锁协议可串行化(例示)两阶段封锁-并发调度A封锁点封锁点grant-X(B,T3)-成功grant-X(A,T3)-成功grant-S(A,T4)-等待grant-S(B,T4)-成功grant-S(A,T4)-成功释放-X(B,T3)释放-X(A,T3)释放-S(A,T4)释放-S(B,T4)并发调度A等价的串行调度3)两阶段封锁调度A可正常执行，等价的串行调度？二 两阶段封锁协议调度A顺利执行！7两阶段封锁并不能保证不会发生死锁(示例)二 两阶段封锁协议两阶段封锁-并发调度B调度A和B有何差异？封锁点封锁点grant

7、-X(B,T3)-成功grant-X(A,T3)-等待grant-S(A,T4)-成功grant-S(B,T4)-等待两阶段封锁-并发调度A封锁点封锁点调度B形成死锁！3)两阶段封调度B也能够顺利执行完成？调度A顺利执行！唯一差异82.其它的两阶段封锁协议（符合两阶段封锁协议）二 两阶段封锁协议要求更强！若T5撤销，会引发级联卷回(因T6,T7读了T5写的数据)9四 死锁处理死锁特点：多个事务相互等待某一方释放资源！1.死锁检测方法讨论4. DBMS如何检查和处理死锁?grant-X(B,T3)-成功grant-S(A,T4) -成功grant-S(B,T4) 等待grant-X(A,T3)

8、-等待其等待图中一定带环102.死锁的解除方法四 死锁处理(某一事务)对法1-2可能引起不足的避免113.死锁的预防方法p.381四 死锁处理三预防：避免死锁出现！老礼让: 新事务不等待,主动回滚!年轻礼让:老事务不等待,强制使新事务回滚!Motivation: Decision support queries that read large amounts of data have concurrency conflicts with OLTP transactions that update a few rowsPoor performance resultsSolution 1: Giv

9、e logical “snapshot” of database state to read only transactions, read-write transactions use normal lockingMultiversion 2-phase lockingWorks well, but how does system know a transaction is read only?Solution 2: Give snapshot of database state to every transaction, updates alone use 2-phase locking

10、to guard against concurrent updatesProblem: variety of anomalies such as lost update can resultPartial solution: snapshot isolation level (next slide)Proposed by Berenson et al, SIGMOD 1995Variants implemented in many database systems E.g. Oracle, PostgreSQL, SQL Server 2005Snapshot Isolation快照隔离A t

11、ransaction T1 executing with Snapshot Isolationtakes snapshot of committed data at startalways reads/modifies data in its own snapshotupdates of concurrent transactions are not visible to T1 writes of T1 complete when it commitsSnapshot IsolationSnapshot ReadConcurrent updates invisible to snapshot

12、readSnapshot WriteProblem of invisible updates: Lost updateSolution:First-committer-wins rule:Commits only if no other concurrent transaction has already written data that T1 intends to write.First-updater-winsCheck for concurrent updates when write occurs by locking itemBut lock should be held till

13、 all concurrent transactions have finishedUpdate checkingSI breaks serializability when txns modify different items, each based on a previous state of the item the other modifiedNot very common in practiceE.g., the TPC-C benchmark runs correctly under SIwhen txns conflict due to modifying different

14、data, there is usually also a shared item they both modify too (like a total quantity) so SI will abort one of themBut does occurApplication developers should be careful about write skewSI can also cause a read-only transaction anomaly, where read-only transaction may see an inconsistent state even

15、if updaters are serializableWe omit detailsUsing snapshots to verify primary/foreign key integrity can lead to inconsistencyIntegrity constraint checking usually done outside of snapshotSnapshot Isolation AnomaliesReading is never blocked, and also doesnt block other txns activitiesPerformance simil

16、ar to Read CommittedAvoids the usual anomaliesNo dirty readNo lost updateNo non-repeatable readPredicate based selects are repeatable (no phantoms)Problems with SISI does not always give serializable executionsSerializable: among two concurrent txns, one sees the effects of the otherIn SI: neither s

17、ees the effects of the otherResult: Integrity constraints can be violatedBenefits of SI19练习(考虑下面两个事务：T34:read(A);read(B);if A=0 then B:=B+1;write(B);T35:read(B);read(A);if B=0 then A:=A+1;write(A);a. 给事务T34与T35增加加锁、解锁指令，使它们遵从两阶段封锁协议。b. 这两个事务会引起死锁吗？说明：两阶段封锁协议(保证冲突可串行化，但不保证不发生死锁)1.增长阶段：事务可以获得锁，但不能释放锁；2.缩减阶段：事务可以释放锁，但不能获得新锁。20T 34: lock-S(A)read(A)lock-X(B)read(B)if A = 0 then B := B + 1write(B)unlock(A)unlock(B)解答b. 有可能发生死锁。如：T 35: lock-S(B)read(B)lock-