北京工业大学研究生数据库复试笔试课件chap6控制(0512)-72

1第6章SQL系统控制6.1事务6.1.1问题的提出：像银行业务或机票预定这样的应用每秒钟都会有上百个操作在数据库上执行。如果DBMS完全没有约束对数据库操作的顺序，将会出现错误。2例C语言嵌套SQL写一个函数chooseSeat()EXECSQLBEGINDECLARESECTION;intflight;chardate[10];charseat[3];intocc;EXECSQLENDDECLARESECTION;voidchooseSeat(){EXECSQLSELECToccupiedINTO:occFROMFlightsWHEREfltNum=:flightANDfltDate=:dateANDfltSeat=:seat;内存：以变量的形式存储Sql：以列的形式3if(!occ){EXECSQLUPDATEFlightsSEToccupid=TRUEWHEREfltNum=:flightANDfltDate=:dateANDfltSeat=:seat;}else……/*已经占用另选*/}4用户1发现座位空用户2发现座位空用户1占用用户2占用不同用户在不同售票点购票,调用chooseSeat()问题：不同用户占同一个座位5解决方法：用户1的操作执行完，再执行用户2的操作将一组操作组成一个事务，事务中进行封锁保证事务的可串行性。6例：银行转帐函数transfer():如果账户1余额够用，转帐到账户2EXECSQLBEGINDECLARESECTION;intacct1,acct2;intbalance1;intamount;EXECSQLENDDECLARESECTION;voidtransfer(){EXECSQLSELECTbalanceINTO:balance1FROMAccountsWHEREacctNo=:acct1;7if(balance1=amount){EXECSQLUPDATEAccountsSETbalance=balance+:amountWHEREacctNo=:acct2;EXECSQLUPDATEAccountsSETbalance=balance-:amountWHEREacctNo=:acct1;}else/*余额不足告知客户*/｝8设想：金额转入账户2之后，系统故障。数据库状态：账户2增加金额账户1未减金额对银行造成损失9问题：数据处于不一致状态结论：数据库操作的某些组合，需要原子地完成。即要么都做要么都不做。106.1.2事务的定义与特性事务:必须被原子地执行的一个或一组操作。如:一组SQL语句,一条SQL语句或一段程序。BEGINTRANSACTION开始COMMITTRANSACTION提交ROLLBACKTRANSACTION撤消夭折、回滚11保证事务正确执行的ACID特性。A原子性AtomicityC一致性ConsistencyI隔离性IsolationD持续性Durability12原子性:事务中的操作要么都执行,要么都不执行。一致性:以保证数据库从一个一致性状态到另一个一致性状态。13隔离性:一个事务的执行不能被其他事务干扰。持续性:事务提交后对数据库的改变是永久性的。即使出现故障,也不受影响。146.1.3只读事务例读数据判定座位是否有效EXECSQLSELECToccupiedINTO:occFROMFlightsWHEREfltNum=:flightANDfltDate=:dateANDfltSeat=:seat;只读、读写的设置SETTRANSACTIONREADONLYSETTRANSACTIONREADWRITE只读事务可以更自由地与其他事务并行156.1.4读脏数据脏数据（dirtydata）：未提交事务所写的数据脏读取（dirtyread）：对脏数据的读取读脏数据的风险：写数据的事务夭折，脏数据被移走，像不曾存在。如果别的事务读了这个脏数据，并提交对脏数据的计算或使用。16读脏数据有时出问题，有时无关紧要例以下述步骤进行账户转移1.将钱加到账户22.检测账户1是否有足够的钱没有：从账户2减去此金额（相当于回滚）有：从账户1减去此金额假设有可能读脏数据。账户：A1($100)A2($200)A3($300)T1$150T2$25017可能的执行顺序：T2将$250加到A3；A3有$550T1将$150加到A2；A2有$350T2测试，A2有足够金额（$350，脏数据）；允许转账T1测试，A1没有足够金额（$100）；不允许转账T2：从A2减去$250A2有$100结束T1：从A2减去$150A2有-$50结束（T1回滚）18例座位选择新方法1.发现有效座位，occ=TRUE占用。无有效座位，夭折。2.问顾客是否要此座位,要，提交。不要，occ=FALSE,重复步骤1，再选。19假设可能读脏数据T2只是因读脏数据，失去选喜欢的座位的机会……T1顾客放弃座位1occ=FALSET2发现座位1占用不能选T1occ=TRUE占用座位1可能的事务执行顺序：20#当读脏数据无关紧要时，允许读脏数据可以避免：1.DBMS用来预防读脏数据所做的费时的工作2.因等待而未去读脏数据造成的并发性的丢失#允许明确指定一个给定的事务可以读脏数据ISOLATIONLEVELREADUNCOMMITED216.1.5隔离级别四种隔离级别可串行化SERIALIZABLE(缺省)读未提交READUNCOMMITED读提交READCOMMITED可重复读REPEATABLEREAD226.2并发控制数据库系统是多用户环境。当多个用户同时使用数据库中相同数据时，称为并发操作。DBMS必须采取措施，对并发操作加以控制。事务＋封锁机制236.2.1事务并发执行可能带来的不一致不加控制时，可能带来下面三种数据库状态不一致的情况。#丢失修改#不可重复读#读脏数据241.丢失修改事务1读(x=3)事务2读(x=3)事务1改(x=x+2=5)事务2改(x=x+1=4)时间事务1的修改丢失252.读脏数据事务1更新(x=3x=9)事务2读(x=9)事务1撤消(x=3)时间事务2读脏数据263.不可重复读事务1读(x=1)事务2改(x=3)事务1再读(x=3)时间事务1不可重复读276.2.2调度调度（scheduling）主要是指对一组可能来自多个不同事务操作流的一种偏序序列安排。通常要求来自同一个事务的两个操作保持原来的次序。所谓偏序：来自不同事务的某些操作相对顺序并不重要。串行调度:每个事物内部连着做，事务间互相不交替非串行调度286.2.3可串行化调度并行事务正确性的准则-------可串行性几个事务并行执行是正确的当且仅当其结果与某一次序串行执行它们的结果相同。29可串行化调度：包含一组提交事务的调度S执行时，如果对于数据库一致性的影响与同样一组事务的某一串行调度的影响相同，称S为可串行化调度。冲突：两个操作满足如下两个条件，称为冲突的1)属于同一个事务，顺序由事务本身逻辑决定2)属于不同事务，对同一数据元素操作，其中之一是写操作。30冲突等价如果两个调度S1和S2满足以下两个条件，则称S1和S2冲突等价1）包含了同一组事务以及其中的操作集2）任意两个提交事务的每一个冲突操作对，在S1和S2中出现的次序相同。31冲突可串行化调度一定是可串行化调度。反之，不然。视等价调度也是可串行化调度。冲突可串行化：如果S与某一个串行调度冲突等价，则S是冲突可串行化的。32视等价：调度S1和S2包含同样的事务集，如果满足：1)S1中的事务Ti读数据库元素A的初值，S2中的事务Ti也读元素A的初值；2)S1中的事务Ti读被事务Tj写过的元素A的值，S2中的事务Ti也读被事务Tj写过的元素A的值；3)对于每一个数据库元素A，S1中Tk执行对A的最后的写操作，S2中也是Tk执行对A的最后的写操作。称为视等价的。一个调度视等价于某串行调度，该调度是视可串行化的。336.3基于封锁的并发控制排它锁(X)：拒绝其它事务任何类型的锁请求。共享锁(S)：拒绝其它事务的X锁请求，但允许其它事务的S锁请求。346.3.1封锁协议保证一致性的三级封锁协议:一级:写之前加排它锁直至事务结束解决“丢失修改”的问题35二级:写之前加排它锁直至事务结束读之前加共享锁,读完释放解决“读脏数据”问题36三级:写之前加排它锁直至事务结束读之前加共享锁,直至事务结束解决“不可重复读”问题37实际应用中，测试调度的可串行性是十分困难的。并发事务操作的交替是由操作系统调度程序来实现的，它为所有进程分配资源，操作系统通常把并发事务当作进程来执行。如：系统装载、事务提交的时间、进程的优先级等因素将有助于对调度中的操作排序。很难为确保可串行性而事先确定调度的操作如何交替。6.3.2两段锁协议38在大多数商业DBMS中采取的方法是设计协议以保证可串行性，而不必测试调度本身。如果每个单独事务都遵循协议，或者协议被一个DBMS并发控制子系统执行，那么将确保事务参与的所有调度都是可串行性的。39有多种保证可串行性的并发控制协议，最常用的是两阶段封锁（two-phaselocking）协议。该协议基于对数据项加锁以阻止并发事务相互干扰。其他协议：时间戳排序按照事务的时间戳顺序执行多版本协议维护数据项的多个版本最优化协议（验证）提交前验证是否违反可串行性40严格两段锁协议：1)事务在读元素前，必须获得共享锁；写元素前必须获得排他锁。2)一个事务所持有的所有锁只有在事务完成时才释放。41一般两段锁协议1）同严格2PL2）当一个事务已经释放了一个锁之后，就不能再请求其他的锁。第一阶段申请锁第二阶段释放锁42T1T2read_lock(Y);read_item(Y);unlock(Y);write_lock(X);read_item(X);X:=X+Y;write_item(X);unlock(X);read_lock(X);read_item(X);unlock(X);write_lock(Y);read_item(Y);Y:=Y+X;write_item(Y);unlock(Y);不遵循一般2PL协议的事务3-1433-2初始值:X=20,Y=30串行调度T1，T2（T2在T1之后进行）的结果：X=50，Y=80串行调度T2，T1（T1在T2之后进行）的结果：X=70，Y=50T1，T2可能的串行调度的结果443-3T1T2read_lock(Y);read_item(Y);unlock(Y);write_lock(X);read_item(X);X:=X+Y;write_item(X);unlock(X);read_lock(X);read_item(X);unlock(X);write_lock(Y);read_item(Y);Y:=Y+X;write_item(Y);unlock(Y);T1T2的一个不可串行化调度X=50Y=5045遵守一般2PL协议的事务T1read_lock(Y);read_item(Y);write_lock(X);unlock(Y);read_item(X);X:=X+Y;write_item(X);unlock(X);T2read_lock(X);read_item(X);write_lock(Y);unlock(X);read_item(Y);Y:=Y+X;write_item(Y);unlock(Y);466.3.3死锁问题在包含两个或两个以上的事务的集合中，当其中每个事务都在等待使用其他事务锁定的数据项时，就会发生死锁。集合中的每个事务都处于等待队列中，等待集合中另一事务释放对某一数据项的锁。47解决方法:1）预防死锁如果给每个事务一个优先权，规定低优先权的事务不等待高优先权的事务（或反之），即可预防死锁。例如：事务启动时，赋予时间戳，较早事务为高优先级。48附加：让被中止而重新启动的事务，保持原有时间戳。最终足够老，拥有高优先级，不会总是被中止。当事务Ti需要Tj持有的锁，可选则如下原则之一：等待-死亡：Ti高优