第10章游标、事务和锁PPT课件

1、SQL Server SQL Server 实 例 教 程实 例 教 程(20 0 8(20 0 8 版版 ) )第 10 章 游标、事务和锁SQL ServerSQL Server案例教程（案例教程（20082008版）版）本章导航本章导航SQL ServerSQL Server案例教程（案例教程（20082008版）版）本章任务描述本章任务描述任务编号子任务任 务 内 容任务1使用游标实现报表形式显示“促销”商品信息任务2使用SQL Server 2008中的事务任务2-1产生编译错误的批处理任务2-2产生运行错误的批处理任务2-3使用事务实现商品表和订单详情表中商品信息的删除操作的同步任

2、务2-4使用事务实现指定类别编号的商品信息的查询和对应的类别名称修改任务3了解SQL Server 2008中的锁机制，使用TRY-CATCH来解决死锁任务3-1检查对数据表Goods的执行插入和查询操作过程中锁的使用情况任务3-2设置事务隔离级别REPEATABLE READ任务3-3使用TRY-CATCH进行死锁处理SQL ServerSQL Server案例教程（案例教程（20082008版）版）游标游标 一、游标概述一、游标概述在在SQL Server 2008SQL Server 2008中使用游标的一般步骤如下：中使用游标的一般步骤如下：（1 1）声明游标（）声明游标（DECLAR

3、E CURSORDECLARE CURSOR）。）。（2 2）打开游标（）打开游标（OPEN CURSOROPEN CURSOR）。）。（3 3）提取游标（）提取游标（FETCH CURSORFETCH CURSOR）。）。（4 4）根据需要，对游标中当前位置的行执行修改操作（更新）根据需要，对游标中当前位置的行执行修改操作（更新或删除）。或删除）。（5 5）关闭游标（）关闭游标（CLOSE CURSORCLOSE CURSOR）。）。（6 6）释放游标（）释放游标（DEALLOCATE CURSORDEALLOCATE CURSOR）。）。SQL ServerSQL Server案例教程（案

4、例教程（20082008版）版）游标主要用于存储过程、触发器和游标主要用于存储过程、触发器和T-SQLT-SQL脚本中，脚本中，使用游标时通常要用到以下的基本语句。使用游标时通常要用到以下的基本语句。（一）（一）DECLARE CURSORDECLARE CURSOR。（二）（二）OPEN CURSOROPEN CURSOR。（三）（三）FETCH CURSORFETCH CURSOR。（四）（四）CLOSE CURSORCLOSE CURSOR。 （五）（五）DEALLOCATE CURSORDEALLOCATE CURSOR。 SQL ServerSQL Server案例教程（案例教程（2

5、0082008版）版）二、游标实例二、游标实例 使用游标实现报表形式显示使用游标实现报表形式显示“促销促销”商品商品的的g_ID（商品号）、（商品号）、g_Name（姓名）和（姓名）和g_Price（价格）。（价格）。SQL ServerSQL Server案例教程（案例教程（20082008版）版）二、游标实例二、游标实例【提示提示】v 使用使用price varchar(50)price varchar(50)而不使用而不使用price floatprice float便于接受数据并显示。便于接受数据并显示。v DECLARE CURSOR DECLARE CURSOR 定义定义 T-SQ

6、LT-SQL服务器游标的属性，例如游标的滚动行为和服务器游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集的查询。用于生成游标所操作的结果集的查询。v OPEN OPEN 语句填充结果集。语句填充结果集。v FETCH FETCH 从结果集返回行。从结果集返回行。v CLOSE CLOSE 语句释放与游标关联的当前结果集。语句释放与游标关联的当前结果集。v DEALLOCATE DEALLOCATE 语句释放游标所使用的资源。语句释放游标所使用的资源。SQL ServerSQL Server案例教程（案例教程（20082008版）版）事务事务 应用应用SQL Server 2008中的

7、自动处理事务、中的自动处理事务、显式事务和隐式事务进行数据处理，帮助实现数显式事务和隐式事务进行数据处理，帮助实现数据的一致性和完整性。据的一致性和完整性。SQL ServerSQL Server案例教程（案例教程（20082008版）版）一、事务概述一、事务概述 1 1、事务事务是一个不可分割的工作逻辑单元，在是一个不可分割的工作逻辑单元，在SQL ServerSQL Server中应用事务来中应用事务来保证数据库的一致性和可恢复性。保证数据库的一致性和可恢复性。 2 2、一个逻辑工作单元必须具备以下四种属性，也称做、一个逻辑工作单元必须具备以下四种属性，也称做ACIDACID（每种属性（每

8、种属性英文名称的首字母缩写）属性。英文名称的首字母缩写）属性。（1 1）原子性原子性（2 2）一致性一致性（3 3）隔离性隔离性（4 4）持久性持久性 3 3、SQL ServerSQL Server中的事务有以下几种中的事务有以下几种类型类型：（1 1）自动处理事务。）自动处理事务。（2 2）隐式事务。）隐式事务。（3 3）显式事务。）显式事务。（4 4）批处理级事务。）批处理级事务。SQL ServerSQL Server案例教程（案例教程（20082008版）版）二、自动提交事务二、自动提交事务 【提示提示】 因为因为CREATE TABLECREATE TABLE语句与语句与INSER

9、TINSERT语句不在同一个批处理（以语句不在同一个批处理（以GOGO分隔）中，分隔）中，因此因此TestTranTestTran表创建成功，在遇到编译错误时也没有回滚。表创建成功，在遇到编译错误时也没有回滚。 如果将如果将CREATE TABLECREATE TABLE语句之后的语句之后的GOGO删除，即将删除，即将CREATE TABLECREATE TABLE语句与语句与INSERTINSERT语句放在同一个批处理中，在执行时遇到编译错误时语句放在同一个批处理中，在执行时遇到编译错误时CREATE TABLECREATE TABLE语句也语句也不能成功执行。不能成功执行。 SQL Ser

10、verSQL Server案例教程（案例教程（20082008版）版） 二、自动提交事务二、自动提交事务SQL ServerSQL Server案例教程（案例教程（20082008版）版）（一）定义和提交事务（一）定义和提交事务 通常在程序中用BEGIN TRANSACTION 命令来标识一个事务的启动开始，用COMMIT TRANSACTION 命令标识事务结束。这两个命令之间的所有语句被视为一体，即事务。 三、显式事务三、显式事务SQL ServerSQL Server案例教程（案例教程（20082008版）版） 三、显式事务三、显式事务SQL ServerSQL Server案例教程（案

11、例教程（20082008版）版） 【提示提示】 单独的一个单独的一个DELETEDELETE语句是一个自动提交事务，由数据库系统语句是一个自动提交事务，由数据库系统自动维护。自动维护。 使用使用BEGIN TRANSACTIONBEGIN TRANSACTION将两个自动提交事务设置为一个显将两个自动提交事务设置为一个显式事务，保证两个式事务，保证两个DELETEDELETE语句要么同时执行，要么都不执行。语句要么同时执行，要么都不执行。 如果采用了主键和外键约束，则删除操作不能成功执行。如果采用了主键和外键约束，则删除操作不能成功执行。三、显式事务三、显式事务SQL ServerSQL Se

12、rver案例教程（案例教程（20082008版）版）（二）回滚事务（二）回滚事务 三、显式事务三、显式事务SQL ServerSQL Server案例教程（案例教程（20082008版）版） 当连接以隐性事务模式进行操作时，当连接以隐性事务模式进行操作时，SQL Server 数据库引擎实例将在提交或回滚当前事务后数据库引擎实例将在提交或回滚当前事务后自动启动新事务。自动启动新事务。 隐性事务模式既可以使用隐性事务模式既可以使用T-SQL的的SET语句语句来设置，也可以通过数据库来设置，也可以通过数据库API函数和方法来设置。函数和方法来设置。 四、隐式事务四、隐式事务SQL ServerSQ

13、L Server案例教程（案例教程（20082008版）版）【课堂实践课堂实践1 1】操作要求：操作要求：（1 1）使用游标实现以报表形式显示）使用游标实现以报表形式显示“热点热点”商品的商品的g_IDg_ID（商品号）、（商品号）、g_Nameg_Name（姓名）和（姓名）和g_Priceg_Price（价（价格）。格）。（2 2）在产生订单时，将对）在产生订单时，将对OrdersOrders表的订单记录插入操表的订单记录插入操作和对作和对OrderDetailsOrderDetails表中的订单详情记录插入操作表中的订单详情记录插入操作组合成一个事务进行处理。组合成一个事务进行处理。SQL

14、 ServerSQL Server案例教程（案例教程（20082008版）版）锁锁 了解了解SQL Server 2008中的锁以实现数据库中的锁以实现数据库并发操作，使用并发操作，使用TRY-CATCH来解决死锁。来解决死锁。SQL ServerSQL Server案例教程（案例教程（20082008版）版） 当多个用户同时访问一个数据库而没有进行锁当多个用户同时访问一个数据库而没有进行锁定时，如果他们的事务同时使用相同的数据时可能会定时，如果他们的事务同时使用相同的数据时可能会发生问题，这些由于同时操作数据库产生的问题称为发生问题，这些由于同时操作数据库产生的问题称为并发问题，主要包括并发

15、问题，主要包括:1、丢失更新、丢失更新2、未确认的相关性（脏读）、未确认的相关性（脏读）3、不一致的分析（非重复读）、不一致的分析（非重复读）4、幻象读、幻象读 一、并发问题一、并发问题SQL ServerSQL Server案例教程（案例教程（20082008版）版）1、共享锁、共享锁 2、排他锁、排他锁3、修改锁、修改锁4、意向锁、意向锁:意向锁又分为共享意向锁、排他意向锁意向锁又分为共享意向锁、排他意向锁和共享式排他意向锁三种。和共享式排他意向锁三种。5、架构锁、架构锁:6、大容量更新锁、大容量更新锁7、键范围锁、键范围锁 二、锁的类型二、锁的类型SQL ServerSQL Server

16、案例教程（案例教程（20082008版）版）三、查看锁三、查看锁 SQL ServerSQL Server案例教程（案例教程（20082008版）版） 隔离本质上是一种封锁机制。它是指自动数据处理系统隔离本质上是一种封锁机制。它是指自动数据处理系统中的用户和资源的相关牵制关系，也就是用户和进程彼此分开且中的用户和资源的相关牵制关系，也就是用户和进程彼此分开且与操作系统的保护控制也分开来。在与操作系统的保护控制也分开来。在SQL Server中，隔离级是中，隔离级是指一个事务和其他事务的隔离程度，即指定了数据库如何保护锁指一个事务和其他事务的隔离程度，即指定了数据库如何保护锁定那些当前正在被其他

17、用户或服务器请求使用的数据。定那些当前正在被其他用户或服务器请求使用的数据。 在在SQL Server中有以下中有以下四种隔离级四种隔离级: （1）READ COMMITTED。（2）READ UNCOMMITTED。（3）REPEATABLE READ。（4）SERIALIZABLE。 四、设置事务隔离级别四、设置事务隔离级别 SQL ServerSQL Server案例教程（案例教程（20082008版）版） USE WebShopGOSET TRANSACTION ISOLATION LEVEL REPEATABLE READGOBEGIN TRANSACTIONGOSELECT * F

18、ROM GoodsGOSELECT * FROM OrdersGOCOMMIT TRANSACTIONGO四、设置事务隔离级别四、设置事务隔离级别 SQL ServerSQL Server案例教程（案例教程（20082008版）版）五、死锁的处理五、死锁的处理 （一）死锁概述（一）死锁概述 在两个或多个任务中，如果每个任务锁定了其他任务试在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。死锁状态如图所示。死锁状态如图所示。SQL ServerSQL Server案例教程（案例教程（20

19、082008版）版）（二（二）死锁检测）死锁检测 SQL Server能够自动定期搜索和处理死锁问题。能够自动定期搜索和处理死锁问题。SQL Server在每次定期搜索中标识所有等待锁定请求在每次定期搜索中标识所有等待锁定请求的会话，如果在下一次搜索中被标识进程仍处于等待状的会话，如果在下一次搜索中被标识进程仍处于等待状态，态，SQL Server将开始递归死锁搜索。将开始递归死锁搜索。 五、死锁的处理五、死锁的处理SQL ServerSQL Server案例教程（案例教程（20082008版）版）（三）死锁处理（三）死锁处理 五、死锁的处理五、死锁的处理SQL ServerSQL Server案例教程（案例教程（20082008版）版） 【提示提示】 ROLLBACKROLLBACK后面使用后面使用WAITFORWAITFOR语句，保证在死锁发生后等待一段语句，保证在死锁发生后等待一段时间。时间。 count count为重新尝试的次数，可以根据实际情况进行调整。为重新尝试的次数，可以根据实际情况进行调整。 重新定义发生的错误，以便于重新定义发生的错误，以便于RaiseErrorRaiseError抛出。因为抛出。因为RaiseErrorRaiseError可以抛出异常，但却不能直接抛出死锁中的异常。可以抛出异常，但却不能直接抛出死锁中的异常。五、死锁的处理五、