电子工业出版社-15040-《SQL Server实例教程(2008版)》-电子教案-第10章

SQLServer实例教程(2008版)第10章游标、事务和锁不知则问，不能则学——董仲舒SQLServer案例教程（2008版）本章导航SQLServer案例教程（2008版）本章任务描述任务编号子任务任务内容任务1使用游标实现报表形式显示“促销”商品信息任务2使用SQLServer2008中的事务任务2-1产生编译错误的批处理任务2-2产生运行错误的批处理任务2-3使用事务实现商品表和订单详情表中商品信息的删除操作的同步任务2-4使用事务实现指定类别编号的商品信息的查询和对应的类别名称修改任务3了解SQLServer2008中的锁机制，使用TRY-CATCH来解决死锁任务3-1检查对数据表Goods的执行插入和查询操作过程中锁的使用情况任务3-2设置事务隔离级别REPEATABLEREAD任务3-3使用TRY-CATCH进行死锁处理SQLServer案例教程（2008版）游标一、游标概述在SQLServer2008中使用游标的一般步骤如下：（1）声明游标（DECLARECURSOR）。（2）打开游标（OPENCURSOR）。（3）提取游标（FETCHCURSOR）。（4）根据需要，对游标中当前位置的行执行修改操作（更新或删除）。（5）关闭游标（CLOSECURSOR）。（6）释放游标（DEALLOCATECURSOR）。SQLServer案例教程（2008版）游标主要用于存储过程、触发器和T-SQL脚本中，使用游标时通常要用到以下的基本语句。（一）DECLARECURSOR。（二）OPENCURSOR。（三）FETCHCURSOR。（四）CLOSECURSOR。（五）DEALLOCATECURSOR。SQLServer案例教程（2008版）二、游标实例任务1使用游标实现报表形式显示“促销”商品的g_ID（商品号）、g_Name（姓名）和g_Price（价格）。SQLServer案例教程（2008版）二、游标实例【提示】使用@pricevarchar(50)而不使用@pricefloat便于接受数据并显示。DECLARECURSOR定义T-SQL服务器游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集的查询。OPEN语句填充结果集。FETCH从结果集返回行。CLOSE语句释放与游标关联的当前结果集。DEALLOCATE语句释放游标所使用的资源。SQLServer案例教程（2008版）事务任务2应用SQLServer2008中的自动处理事务、显式事务和隐式事务进行数据处理，帮助实现数据的一致性和完整性。SQLServer案例教程（2008版）一、事务概述1、事务是一个不可分割的工作逻辑单元，在SQLServer中应用事务来保证数据库的一致性和可恢复性。2、一个逻辑工作单元必须具备以下四种属性，也称做ACID（每种属性英文名称的首字母缩写）属性。（1）原子性（2）一致性（3）隔离性（4）持久性3、SQLServer中的事务有以下几种类型：（1）自动处理事务。（2）隐式事务。（3）显式事务。（4）批处理级事务。SQLServer案例教程（2008版）二、自动提交事务【任务2-1】产生编译错误的批处理。【提示】因为CREATETABLE语句与INSERT语句不在同一个批处理（以GO分隔）中，因此TestTran表创建成功，在遇到编译错误时也没有回滚。如果将CREATETABLE语句之后的GO删除，即将CREATETABLE语句与INSERT语句放在同一个批处理中，在执行时遇到编译错误时CREATETABLE语句也不能成功执行。SQLServer案例教程（2008版）【任务2-2】产生运行错误的批处理。二、自动提交事务SQLServer案例教程（2008版）（一）定义和提交事务通常在程序中用BEGINTRANSACTION命令来标识一个事务的启动开始，用COMMITTRANSACTION命令标识事务结束。这两个命令之间的所有语句被视为一体，即事务。三、显式事务SQLServer案例教程（2008版）【任务2-3】删除商品号为‘010006’的商品记录，考虑到在订单详情表中保存了该商品的销售记录，出于数据一致性考虑，要求要么在商品表和订单详情表中都删除该商品信息，要么都不删除。三、显式事务SQLServer案例教程（2008版）【提示】单独的一个DELETE语句是一个自动提交事务，由数据库系统自动维护。使用BEGINTRANSACTION将两个自动提交事务设置为一个显式事务，保证两个DELETE语句要么同时执行，要么都不执行。如果采用了主键和外键约束，则删除操作不能成功执行。三、显式事务SQLServer案例教程（2008版）（二）回滚事务【任务2-4】查询类别编号为‘03’的商品信息，并将此商品类别编号对应的类别名称修改为‘电器产品’。三、显式事务SQLServer案例教程（2008版）当连接以隐性事务模式进行操作时，SQLServer数据库引擎实例将在提交或回滚当前事务后自动启动新事务。隐性事务模式既可以使用T-SQL的SET语句来设置，也可以通过数据库API函数和方法来设置。四、隐式事务SQLServer案例教程（2008版）【课堂实践1】操作要求：（1）使用游标实现以报表形式显示“热点”商品的g_ID（商品号）、g_Name（姓名）和g_Price（价格）。（2）在产生订单时，将对Orders表的订单记录插入操作和对OrderDetails表中的订单详情记录插入操作组合成一个事务进行处理。SQLServer案例教程（2008版）锁任务3了解SQLServer2008中的锁以实现数据库并发操作，使用TRY-CATCH来解决死锁。SQLServer案例教程（2008版）当多个用户同时访问一个数据库而没有进行锁定时，如果他们的事务同时使用相同的数据时可能会发生问题，这些由于同时操作数据库产生的问题称为并发问题，主要包括:1、丢失更新2、未确认的相关性（脏读）3、不一致的分析（非重复读）4、幻象读一、并发问题SQLServer案例教程（2008版）1、共享锁2、排他锁3、修改锁4、意向锁:意向锁又分为共享意向锁、排他意向锁和共享式排他意向锁三种。5、架构锁:6、大容量更新锁7、键范围锁二、锁的类型SQLServer案例教程（2008版）三、查看锁【任务3-1】对数据表Goods执行插入和查询操作，检查在程序执行过程中锁的使用情况。SQLServer案例教程（2008版）隔离本质上是一种封锁机制。它是指自动数据处理系统中的用户和资源的相关牵制关系，也就是用户和进程彼此分开且与操作系统的保护控制也分开来。在SQLServer中，隔离级是指一个事务和其他事务的隔离程度，即指定了数据库如何保护锁定那些当前正在被其他用户或服务器请求使用的数据。在SQLServer中有以下四种隔离级:（1）READCOMMITTED。（2）READUNCOMMITTED。（3）REPEATABLEREAD。（4）SERIALIZABLE。四、设置事务隔离级别SQLServer案例教程（2008版）【任务3-2】设置事务隔离级别REPEATABLEREAD。USEWebShopGOSETTRANSACTIONISOLATIONLEVELREPEATABLEREADGOBEGINTRANSACTIONGOSELECT*FROMGoodsGOSELECT*FROMOrdersGOCOMMITTRANSACTIONGO四、设置事务隔离级别SQLServer案例教程（2008版）五、死锁的处理（一）死锁概述在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。死锁状态如图所示。SQLServer案例教程（2008版）（二）死锁检测SQLServer能够自动定期搜索和处理死锁问题。SQLServer在每次定期搜索中标识所有等待锁定请求的会话，如果在下一次搜索中被标识进程仍处于等待状态，SQLServer将开始递归死锁搜索。五、死锁的处理SQLServer案例教程（2008版）（三）死锁处理【任务3-3】使用TRY-CATCH进行死锁处理。五、死锁的处理SQLServer案例教程（2008版）【提示】ROLLBACK后面使用WAITFOR语句，保证在死锁发生后等待一段时间。@count为重新尝试的次数，可以根据实际情况进行调整。重新定义发生的错误，以便于RaiseError抛出。因为RaiseError可以抛出异常，但却不能直接抛出死锁中的异常。五、死锁的处理SQLServer案例教程（2008版）【课堂实践2】操作要求：（1）将数据表Customers的修改和查询操作组合成一个事务，在执行事务过程中检查锁的使用情况。（2）完成“任务3-3”的死锁处理的例子。SQLServer案例教程（2008版）小结本章学习了如下内容：游标，包括游标概述、使用DECLARECURSOR声明游标、使用OPEN打开游标、使用FETCH获取游标、使用CLOSE关闭游标、使用DEALLOCATE删除游标引用，游标实例；事务，包括事务概述、自动提交事务、显式事务（使用BEGINTRAN定义事务、使用COMMIT提交事务、使用ROLLBACK回滚事务）、隐式事务；锁，包括并发问题、锁的类型、查看锁、设置事务隔离级别、死锁的处理。