实验11数据库并发控制.docx

实验数据库并发控制实验目的：了解并掌握数据库的保护措施并发控制机制，重点以SOL Server2000为平台加以 操作实践，要求认识典型并发问题的发生现象并掌握解决办法。实验背景：并发操作是数据库共享特性的一个体现，但却对数据库德一致性和完整性形成了巨大的 危机。事务是并发控制的基本单位。SOL Server2000的事务一般分为2类，一种是系统提供的事务，是指在执行语句时，一 条语句就是一个事务；另一种是用户定义的事务，即用户明确定义的事务。在实际应用中大 多数事务需要由用户自己定义事务来出来。用BEGING TRANSACTION定义事务的开始； 用 COMMIT TRANSACTION 来成功提交事务；用 ROLLBACK TRANSACTION 将事务 操作全部取消。SOL Server2000采用加锁技术确保事务运行不会影响数据库的一致性和完整性。加锁技 术可以防止用户读取正在由别的用户修改的数据，也可以防止两个用户同时修改同一个数 据。如果没有锁，数据库中的数据可能出现丢失修改、脏读、不可重复读、幻影等并发问题。 虽然SOL Server2000自动强制锁定，但是可以通过了解锁定并在应用程序中自定义锁来设 计更有效的并发控制程序。SOL Server2000提供了如下类型的锁：共享（S）、更新（U）、排它（X）、意向锁、架构 锁、大容量更新（BU），详见联机丛书SQL Server中的锁定介绍。SQL Server 2000具有 多粒度锁定，允许一个事务锁定不同类型的资源。隔离属性是ACID的四个属性之一，逻辑工作单元必须具备这四个属性才能称为事务。 该属性能够使事务免受其它并发事务所执行的更新的影响。每个事务的隔离级别实际上都是 可以自定义的。当锁定技术实施并发控制机制时，使所有事务得以在彼此完全隔离的环境中 运行，但是任何时候都可以有多个正在运行的事务。可串行性保证一组并发事务运行后所达 到的数据库状态，等同于这组事务按某种顺序连续执行时所达到的数据库状态。隔离级别是一个事务必须与其它事务进行隔离的程度。较低的隔离级别可以增加并发， 但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发 产生负面影响。设置事务隔离级别虽然使程序员承担了某些完整性问题所带来的风险，但可 以换取对数据更大的并发访问权。SQL-92定义了使用SET TRANSACTION ISOLATION LEVEL语句定义时可以选择的四种隔离级别： READ UNCOMMITTED 未提交读 READ COMMITTED 提交读（SQL Server 默认级别）。 REPEATABLE READ 可重复读。 SERIALIZABLE可串行读（事务隔离的最高级别，事务之间完全隔离）。SET TRANSACTION ISOLATION LEVEL的设置是在执行或运行时设置，而不是在分 析时设置。一次只能设置这些选项中的一个，而且设置的选项将一直对那个连接保持有效， 直到显式更改该选项为止。这是默认行为，除非在语句的FROM子句中在表级上指定优化 选项。如果事务在SERIALIZABLE隔离级别上运行，则可以保证任何并发重叠事务均是串 行的。实验内容：1、丢失修改（lost updates）丢失修改直接影响数据库的正确性，是并发控制首要解决的问题。事务必须运行于可重复读（REPEATABLE READ或者更高的隔离级别才防止丢失修改。当两个事务检索相同 的行，然后基于原检索的值对行进行更新时，会发生丢失更新。如果两个事务使用一个 UPDATE语句更新行，并且不基于以前检索的值进行更新，则在默认的READ COMMITTED隔离级别不会发生丢失修改。步骤1：创建一个实验数据库TEST，并创建表saleso步骤2：创建两个存储过程modi_m和modi_a，语句如图:步骤3：打开两个查询分析器窗口，分别运行两个存储过程modi_m和modi_ao （也可 以利用企业管理器的查询子窗体进行模拟，方法如图）2、llsysco llsysco llsvsde HU sysfile HU sysfiledtpropertiesdbo系统步骤4：可以看到同时运行两个存储过程，就可以实现对“数量”字段加1和减1各2000 次操作，“数量”字段最后应该得到的值是0，但是运算结果不是0。这说明发生了丢失 修改现象。步骤5：修改存储过程modi_m为如图所示，而modi_a也作相应的修改。那么“数量” 字段的值将一直为0。结论：多个进程同时运行，会发生丢失修改的现象。解决的办法是：避免先SELECT 后UPDATE的事务设计；如果必须这样安排事务，则可以指定更高的事务隔离级别， 如：repeatable read或serializable或在事务开始就对数据对象加排它锁(X)，如上图语 句：Select shu=数量 from sales with(tablockx) where 客户代号=A0001脏读(dirty read)隔离级别为未提交读(read uncommitted)时，会产生脏读，这表示事物中不发生 共享锁，也不接受排它锁，能读到事务提交前的中间值，即脏数据。步骤1：创建两个存储过程dirt_wroll和dirt_r，注意事先将salse表的数量字段值 更改为1000，如图:CREATE procedure dirt_wroll ASdeclare int创建扃部变量"declare shu intset transaction isolation level read uncouunittedselect 1/*翁局部变量赋值"_while (i = 16000 begin /* 注释:16000的值可以调节 */潘"勇'._,select ©shu:数里 from sales where 客户代w ： A0001update sales set 数量 ©shu 1 where 客户代号:,A000T rollback tranwaitf or delay ? 00: 00: 00. 002, 注释：延退时间可以自行调整CREATE procedure dirt_r ASdeclare i intdeclare shu intset transaction isolation level read uncoimittedselect i 1while(i =6000 beginselect i i 1begin transelect shu -数量 from sales where 客户代号A0001 if (shu3 1000 i raiserror(?发生了脏读！ '，16 1 i/*暇设数量字段的原值是1000*/commit tranend步骤2：并行运行这两个存储过程，会出现如图的提示，说明发生了脏读。步骤3：修改两个存储过程的隔离级别，将隔离级别从read uncommitted修改为read committed或者更高级别，从新并行运行上边的两个存储过程，察看是否还发生脏读。3、不可重复读(unrepeatable read)步骤1：创建存储过程rep_r和rep_w，如图："i1"查询HAOHAO.test.sa C:Documents and SettingsAdministratorMy Documentsrep.,CREATE procedure rep_r ASdeclare i int创建扃部变量"declare ©shul intdeclare shu2 intset transaction isolation level read uncouunitted /*或read conunitted*/select i 1/*给扃部变量赋值"while （i ：=3000 begin /* 注律：16000的值可以调节 */select i i 1begin transelect shul :数量 from sales where 客户代号:,A000Twaitf or delay , 00: 00: 00. 001, 一 注粹：筵迫时间可以自行调整select shu2 -数童 from sales where 客户代号A000Tif shul）shu2 raiserror 7两次读取的数量不一致，发生了不可重复读！ '16, 1 commit tranend"i'11 查询HAOHAO.test.sa C:Documents and 5ettingsAdministratorMy DocumentsrCREATE procedure rep_w ASdeclare i int/*创建扃部变量"I declare shu intset transaction isolation level read uncouunitted /*或者ccinunittEd*/select 1while （i ；= 1000 'beginbegin transelect shu=数量 from sales where 客户代号：，A000Twaitf or delay , 00: 00: 00. 002? 一 注释：甚返时间可以自行调整update sales set 数量 ©shu-i-l where 客户代号：，A000Tcoimnit transelect i i 1end步骤2：并行执行上边两个存储过程，会发生不可重复读现象。v 上旧3咨皆6Q曾Huse test exec rep_r11两次读取的数童不一致，发生了不可重复读!服务器：消息50000,级别16,状态1,过程rep_r,行13 两次读取的数童不一致，发生了不可重复读！服务器：消息50000,级别16,状态1,过程rep_r,行13 两次读取的数量不一致，发生了不可重复读！腻务器：消息50000,级别16,状态1,过程rep_r,行13步骤3：解决办法是，指定更高的隔离级别，如repeatable read> serializable或者人工指 定加锁锁定查询中使用的所有数据。本例中，存储过程rep_r可以在SELECT语句中使 用holdlock选项。这样，再运行，就可以实现重复读。CREATE procedure rep_r2 AS declare i int创建局部变量"declare ©shul intI declare shu2 intset transaction isolation level read uncounnitted 或read coimnitted*/ select i 1给扃部变童赋值*/while (i 3000'begin /* 注出:16DDD的值可以调节"select i i 1一一一 _ _ _begin tran-select Oshul -数 s x£rom sales wit h (ho 1 dl o ck)jme r e 客户代号A0001 waitf or delay , 00: 0oTTlU. 001' 江释i返迎PT向可以自行调整select shu2 =数量 from sales with holdlock) where 客户代号=, A0001, if (Oshul :/shu2 i raiserror 两次读取的数量不一致，发生了不可重复读！ ，1觅1 couunit tranend4、幻影问题(phantom)幻影问题是事务并发运行中，由于未达到更高级别的隔离，在事务对符合条件的数 据进行处理后，意外发现还有符合条件，但是没有处理的数据存在，实际上是由于其他 用户将新的幻象行插入到数据库中了。解决幻影问题需要设置更高的隔离级别。隔离级别serializable能在数据集上放置一个范围锁，以防止其他用户在本事务完 成前更新数据集或者将行插入数据集内。步骤1：创建两个存储过程hy1和hy2。同时执行他们，观察是否发生幻影现象。create procedure hyl ASdeclare i int/*创建局部变量"declare intdeclare k intset transaction isolation level repeat able readq郭苗野 、.while i<： 300)begin /* 注释：16000的值可以调节"select kselect j=count (*) from sales where 数量 10000if j< k raiserror A 生了幻甥现竦！16 1'/conunit t ranwaitf or delay , 00: 00: 00. 003, 注释：延返时间可以自行调整CREATE procedure hy2 ASdeclare i int/*创建扃部变量"set transaction isolation level repeat able read 一此处隔离缓别与幻影关系不大 delete from sales where i客户代号=, Al 111,) begin traninsert into sales 客户代号数values f Al 11 T 10000insert into sales 客户代号jvalues f Al 112, 10000counnit tran