SQL应用实例PPT.ppt

1、SQL Server 2005实例教程,刘志成 主编 E-mail:,第 10 章 游标、事务和锁,教学目标,本章主要学习SQL Server 2005中的游标、事务和锁的相关知识，包括游标概述、游标实例、事务处理、并发控制、锁的类型、锁的使用和死锁。,教学重点,游标的基本概念及其使用 SQL Server中事务的操作方法 SQL Server中锁的操作方法,本章导航,1、游标（Cursor）的定义： 关系数据库中的操作会对整个行集产生影响，由SELECT语句返回的行集包括所有满足该语句中WHERE子句中条件的行，所返回的所有行被称为结果集。游标允许用户访问处理结果集中的一行或部分行，而不是对

2、整个行集进行操作。 SQL Server 2005通过游标提供了对一个结果集进行逐行处理的能力。游标也可以被看作是一个表中的记录指针，该指针与某个查询结果集相联系。,游标,一、游标概述,2、游标的优点如下： 允许定位在结果集的特定行。 从结果集的当前位置检索一行或多行。 支持对结果集中当前位置的行进行数据修改。 注意：游标实际上作为面向集合的数据库管理系统和面向行的程序设计的桥梁，使得这两种处理方式通过游标沟通起来。,3、在SQL Server 2005中使用游标的一般步骤如下： （1）声明游标（DECLARE CURSOR）。 （2）打开游标（OPEN CURSOR）。 （3）提取游标（FE

3、TCH CURSOR）。 （4）根据需要，对游标中当前位置的行执行修改操作（更新或删除）。 （5）关闭游标（CLOSE CURSOR）。 （6）释放游标（DEALLOCATE CURSOR）。,（1）声明游标 声明游标是指利用SELECT查询语句创建游标的结构，指明游标的结果集中包含哪些数据。 格式：DECLARE cursor_name -指定游标名 INSENSITIVESCROLLCURSOR -指定游标类型 FOR SELECT_statement -指定查询语句 说明: INSENSITIVE:游标结果集合会被拷贝到一个临时表（tempdb）中，所有对游标的操作都是基于该临时拷贝数据

4、。则基表数据修改不能反映到当前游标结果集中。 SCROLL:为滚动游标，否则为只进游标,(2)打开游标 声明了游标后，必须打开才能使用。T-SQL提供了打开游标语句OPEN。 格式：OPEN cursor_name 说明： 当游标打开成功时，游标位置指向结果集的第一行之前。 只能打开已经声明但尚未打开的游标。,(3)读取数据 打开游标后，就可以从结果集中提取数据了。 格式：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n | nvar| RELATIVE n| nvar FROM cursor_name INTO 说明： NEXT：表示提取下一条记录(默认)。 PR

5、IOR：表示提取前一条记录。 FIRST：表示提取第一条记录。 LAST：表示提取最后一条记录。 ABSOLUTE n ：若为正，则返回游标头开始的第n行，若为负，则返回游标末尾开始的第n行。若为0，则不返回行。 RELATIVE n | nvar：若为正数，则返回当前行开始的第n行，若为负，则返回当前行之前的第n行。若为0，则返回当前行 INTO :允许将提取操作的列数据放到局部变量中。要求变量数与列数相等，结果一致 ROWCOUNT:保存自游标打开后第一个FETCH语句至最近FETCH语句为止，已从游标结果集中提取的行数。 注意：只进游标只能使用next提取类型，其余皆不可能，而滚动游标则

6、可以使用以上所有类型。,5、关闭游标 如果一个已打开的游标暂时不用，就可以关闭。T-SQL提供了关闭游标语句CLOSE。 格式：CLOSE 注意： 关闭游标即删除游标当前结果集合，并释放游标对数据库的所有锁定。关闭游标并不改变它的定义，要使用已关闭游标的数据，可以再次用OPEN打开。,6、释放游标 如果一个游标不需要，就可以释放。T-SQL提供了释放游标的语句DEALLOCATE。 格式：DEALLOCATE 说明：如果释放一个已经打开尚未关闭的游标，系统会自动先关闭这个游标，然后再释放。,二、游标实例,任务1,使用游标实现报表形式显示“促销”商品的g_ID（商品号）、g_Name（商品名）和

7、g_Price（价格）。,DECLARE NO char(6),name varchar(50),price varchar(50) DECLARE cur_goods CURSOR FOR SELECT g_id,g_name,g_price FROM goods where g_status=促销 OPEN cur_goods fetch next from cur_goods into NO,name,price print space(6)+-商品信息表- print while fetch_status=0 begin print 商品号：+no+商品名称：+name+商品价格：+p

8、rice fetch next from cur_goods into NO,name,price end close cur_goods deallocate cur_goods 说明： fetch_status表示FETCH 语句执行的最后游标的状态 fetch_status=0表示FETCH语句成功。 fetch_status=-1表示FETCH语句失败或此行不在结果集中。 fetch_status=-2表示被提取的行不存在。,DECLARE NO char(6),name varchar(50),price varchar(50) DECLARE cur_goods scroll CU

9、RSOR FOR SELECT g_id,g_name,g_price FROM goods where g_status=促销 OPEN cur_goods fetch next from cur_goods into NO,name,price print space(6)+-商品信息表- print while fetch_status=0 begin print 商品号：+no+商品名称：+name+商品价格：+price fetch next from cur_goods into NO,name,price end print fetch last from cur_goods i

10、nto NO,name,price while fetch_status=0 begin print 商品号：+no+商品名称：+name+商品价格：+price fetch relative -2 from cur_goods into NO,name,price end close cur_goods deallocate cur_goods,【提示】 使用price varchar(50)而不使用price float便于接受数据并显示。 DECLARE CURSOR 定义 T-SQL服务器游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集的查询。 OPEN 语句填充结果集。 F

11、ETCH 从结果集返回行。 CLOSE 语句释放与游标关联的当前结果集。 DEALLOCATE 语句释放游标所使用的资源。,数据库系统的特点之一数据共享，可以供多个用户使用。 在多用户使用的数据库中，同一时刻并行运行的事务可达数据百个，会产生多个事务同时存取同一数据的情况，若不加控制，则破坏数据库的一致性。例如：一个用户可以查看数据表中的数据，执行一些基于数据的查询操作。而另外一个用户在第一个用户查询期间更新了表，那么第一个用户第二次会看到不同的数据，造成第一次查询操作失效。 DBMS提供并发控制机制，通过对事务的执行过程进行控制来实现共享正确可靠的数据服务。 SQL Server 2005以

12、事务为单位，使用锁来保证数据的一致性，并解决数据库操作时的并发问题；,事务,任务2,应用SQL Server 2005中的自动处理事务、显式事务和隐式事务进行数据处理，帮助实现数据的一致性和完整性。,一、事务概述,1、事务的定义 事务是用户定义的数据库操作序列，这些操作可作为一个完整的工作单元。一个事务内的所有语句是一个整体，要么全部执行，要么全部不执行。 如在银行业务中，“从帐户A转移资金X到帐户B”就是一个典型的事务。这个事务可以分解为两个动作：（1）从账户A减去金额X。（2）在账户B中加上金额X。 事务是一个不可分割的工作逻辑单元，在SQL Server中应用事务来保证数据库的一致性和可

13、恢复性。 在关系数据库中，一个事务可以是一条SQL语句、一组SQL语句或整个程序，一般地讲，一个程序中包含多个事务。,2、事务的特性：即ACID（每种属性英文名称的首字母缩写）属性。 （1）原子性（Atomicity）：一个事务必须作为SQL Server工作的原子单位，要么都执行，要么都不执行。 （2）一致性（Consistency）：在一个事务执行之前和执行之后数据库都必须处于一致性状态。例如，当数据库处于一致性状态S1时，对数据库执行一个事务，在事务执行期间假定数据库的状态是不一致的，当事务执行结束时，数据库处在一致性状态S2。 （3）隔离性（Isolation）：一个事务所做的修改必须

14、能够与其他事务所做的修改隔离开来。即在并发处理过程中，一个事务所看到的数据状态必须为另一个事务处理前或处理后的数据，而不能为正被其他事务所修改的数据。 （4）持久性（Durability）： 即一旦一个事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，耐得住任何系统故障。如果提交一个事务后计算机瘫痪，重新启动计算机，该事务的结果仍然存在,3、SQL Server中的事务有以下几种类型： （1）自动处理事务。系统默认每个T-SQL语句都是一个事务，由系统自动开始并提交。每个T-SQL语句完成时，都被提交或回滚。如：Insert table values (1,abc) ，它就是一个事务

15、，若不是所有字段都合法，就不会把这个记录插入到相应的数据表中。 （2）隐式事务：表示在当前事务提交或回滚后，SQL Server自动开始的事务。隐式事务无须使用BEGIN TRANSACTION语句标志事务的开始，只需结束或回滚事务。在回滚后，SQL Server又自动开始一个新的事物。即在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK显示结束。INSERT 、UPDATE、DELETE是系统自带的隐式事务。可以通过 SET IMPLICIT_TRANSACTIONS ON |OFF启用或关闭隐式事务模式。 （3）显式事务。每个事务均以BEGIN TRANSACT

16、ION语句显示开始，以COMMIT或ROLLBACK显示结束。这两个命令之间的所有语句被视为一体。只有执行到COMMIT TRANSACTION命令时，事务中对数据库的更新操作才算确认。 （4）批处理级事务。只能应用于多个活动结果集（MARS），在MARS会话中启动的T-SQL显示或隐式事务变为批处理事务。,自动提交模式：是SQL Server数据库引擎的默认事务管理模式。在该模式下，当遇到编译错误时数据库引擎回滚整个批处理而不是仅仅一个SQL语句。编译错误会阻止数据库引擎生成计划，这样批处理中的任何语句都不会执行。,二、自动提交事务,【任务2-1】产生编译错误的批处理。,USE Websho

17、p go create table testtran(t_id int primary key,t_name char(3) go insert into testtran values(1,aaa) insert into testtran values(2,bbb) insert into testtran valuse(3,ccc) go select * from testtran,【提示】 因为CREATE TABLE语句与INSERT语句不在同一个批处理（以GO分隔）中，因此TestTran表创建成功，在遇到编译错误时也没有回滚。 如果将CREATE TABLE语句之后的GO删除，

18、即将CREATE TABLE语句与INSERT语句放在同一个批处理中，在执行时遇到编译错误时CREATE TABLE语句也不能成功执行。,【任务2-2】产生运行错误的批处理。,USE Webshop go create table testtran(t_id int primary key,t_name char(3) go insert into testtran values(1,aaa) insert into testtran values(2,bbb) insert into testtran values(1,ccc) go select * from testtran,三、显式事

19、务 BEGIN TRANSACTION 事务名|事务变量名：事务的开始标记。 COMMIT TRANSACTION 事务名|事务变量名 ：事务的结束标记。 ROLLBACK TRANSACTION 事务名|事务变量名|保存点名|保存点变量名 ：回滚事务到指定位置。 SAVE TRANSACTION :设置保存点 （一）定义和提交事务 通常在程序中用BEGIN TRANSACTION 命令来标识一个事务的启动开始，用COMMIT TRANSACTION 命令标识事务结束。这两个命令之间的所有语句被视为一体，即事务。,【任务2-3】删除商品号为010006的商品记录，考虑到在订单详情表中保存了该商

20、品的销售记录，出于数据一致性考虑，要求要么在商品表和订单详情表中都删除该商品信息，要么都不删除。,begin transaction delete goods where g_id=010006 delete orderdetails where g_id=010006 commit transaction,【提示】 单独的一个DELETE语句是一个自动提交事务，由数据库系统自动维护。 使用BEGIN TRANSACTION将两个自动提交事务设置为一个显式事务，保证两个DELETE语句要么同时执行，要么都不执行。 如果采用了主键和外键约束，则删除操作不能成功执行。,（二）回滚事务,【任务2-4

21、】查询类别编号为03的商品信息，并将此商品类别编号对应的类别名称修改为电器产品。,USE Webshop go begin transaction select g_id,goods.t_id,t_name,g_name,g_price from types,goods where goods.t_id=types.t_id and types.t_id=03 save transaction after_query update types set t_name=电器产品 where t_id=03 if error!=0 or rowcount=0 begin rollback trans

22、action after_query commit transaction print 更新商品类别表时产生错误！ return end select g_id,goods.t_id,t_name,g_name,g_price from types,goods where goods.t_id=types.t_id and types.t_id=03 commit transaction,四、隐式事务 当连接以隐性事务模式进行操作时，SQL Server 数据库引擎实例将在提交或回滚当前事务后自动启动新事务。 隐性事务模式既可以使用T-SQL的SET语句来设置，也可以通过数据库API函数和方法

23、来设置。可以通过 SET IMPLICIT_TRANSACTIONS ON |OFF启用或关闭隐式事务模式。 注意】使用隐式事务时不能忘记提交或回滚事务。由于没有显式的BEGIN TRANSACTION语句，这些步骤很容易被遗忘，并导致事务长期运行，在连接关闭时产生的不必要的回滚，以及与其他连接之间产生的阻塞问题。,SET IMPLICIT_TRANSACTIONS ON GO -检验事务是否已经启动 CREATE TABLE Table1 (C1 int PRIMARY KEY) - 使用TRANCOUNT测试一个事务是否已经打开 SELECT TRANCOUNT AS Transactio

24、n Count -结果为“1”表示当前连接已经打开了一个事务。结果为“0”表示当前没有事务，若结果大于“1”表示有嵌套事务。 INSERT INTO Table1 VALUES(10) GO SELECT TRANCOUNT AS Transaction Count -TRANCOUNT的值仍为“1”。因为没有开始一个新的事务。 -回滚该事务并再次检查TRANCOUNT。其值转变为“0”。 ROLLBACK TRANSACTION GO SELECT TRANCOUNT AS Transaction Count -查询表Table1中的数据 SELECT * FROM Table1 /* 这个

25、隐式事务起始于CREATE TABLE语句，并且ROLLBACK TRAN语句取消了第一个语句后所做的所有工作。*/ -关闭隐式事务 commit SET IMPLICIT_TRANSACTIONS OFF,【课堂实践1】,操作要求： （1）使用游标实现以报表形式显示“热点”商品的g_ID（商品号）、g_Name（姓名）和g_Price（价格）。 （2）在产生订单时，将对Orders表的订单记录插入操作和对OrderDetails表中的订单详情记录插入操作组合成一个事务进行处理。,锁,任务3,了解SQL Server 2005中的锁以实现数据库并发操作，使用TRY-CATCH来解决死锁。,一、

26、并发问题 事务和锁是并发控制的主要机制，SQL Server通过支持事务并发控制来管理多个事务，保证事务的一致性，并使用事务日志保证修改的完整性和可恢复性。 当多个用户同时访问一个数据库而没有进行锁定时，如果他们的事务同时使用相同的数据时可能会发生问题，这些由于同时操作数据库产生的问题称为并发问题，主要包括: 丢失更新 未确认的相关性（脏读） 不一致的分析（非重复读） 幻象读,1、丢失更新（Lost Update） 丢失更新是指事务T1与事务T2从数据库中读入同一数据并修改，事务T2提交的修改结果破坏了事务T1提交的修改结果，导致事务T1的修改被丢失。,2、未确认的相关性（脏读Dirty Re

27、ad） 事务T1读取了经过事务T2修改过的数据，但是由于事务T2因为流产而撤消了对该数据的修改，数据库恢复到事务T2执行前的状态，从而导致事务T1读取的内容与数据库中的内容不一致。,3、非重复读（Unrepeatable Read） 事务T1两次读取同一数据项A的内容不一致。究其原因是在两次读操作之间，事务T2也修改了数据项A。,4、幻像读取 （phantom read）事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录，这行记录恰好可以满足T1所使用的查询条件中的WHERE 子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的

28、新行。这个新行就称为“幻像”，因为对T1来说这一行就像突 然出现的一样。,产生上述几类数据不一致的主要原因是并发操作破坏了事务的孤立性 ，故必须在事务中使用锁。,二、锁的类型（ SQL Server 2005） 封锁：就是事务T在对某个数据对象，例如表、记录等操作之前，先向系统发出请求，对其加锁。加锁后事务T就对该数据有了一定的控制，在事务T释放它的锁之前，其他的事务不能更新此数据对象 被封锁的数据对象的范围可大可小，可以是属性、元组，也可以是关系、数据库等，我们把封锁对象的大小称为封锁粒度 。 1、共享锁 （S锁） ：又称为读锁，它允许多个并发事务读取所锁定的资源，但禁止其他事务对锁定资源的

29、修改操作。默认情况下，当读取数据的事务读完数据之后，立即释放所占用的资源。如 SELECT 语句。 注意：共享锁不会阻止其他用户读，但是阻止其他的用户写和修改,2、排他锁（X锁） : 又称为写锁。例如 INSERT、UPDATE 或 DELETE语句，系统自动在所修改的事务上放置排他锁 。 排他锁就是在同一时间上只允许一个事务访问一种资源，其他事务都不能在有排他锁的资源上访问。只有当产生排他锁的事务结束之后，排他锁锁定的资源才能被其他事务使用。,3、修改锁（U锁）:在修改操作的初始化阶段用于锁定可能被修改的资源，以防止使用共享锁可能产生的死锁现象。在使用共享锁时，一个事务对数据进行修改为：首先

30、获得一个共享锁，读取记录，然后将共享锁申请升级为排他锁，再执行数据修改操作。若两个并发事务获得同一资源的共享锁后都需要修改数据时，两者均申请将共享锁升级为排他锁。这时，双方都不再释放共享锁而一直等对方释放共享锁，从而造成死锁。为避免这种现象的发生，一个数据修改事务在开始时直接申请修改锁，之后，在需要修改数据时，再将修改锁转换成排他锁。,4、意向锁:说明SQL Server有该锁所锁定资源的低层资源上获得共享锁或排他锁的意向，在表级设置意向锁可以阻止其他事务获得该表的排他锁。意向锁又分为共享意向锁、排他意向锁和共享式排他意向锁三种。 共享意向锁（IS锁）：说明一个事务准备在共享意向锁定所锁定资源

31、的低层上放置共享锁来读取资源数据。例如，要对某个元组加 S锁，则要首先对关系和数据库加 IS锁。 排他意向锁(IX锁)：说明一个事务准备在排他意向锁所锁定资源的低层上放置排他锁来修改资源数据。例如，要对某个元组加 X锁，则要首先对关系和数据库加 IX锁。 共享式排他意向锁(SIX锁)：说明当前事务允许其他事务使用共享锁来并发读取顶层资源，并准备在资源的低层资源上放置排他意向锁。例如对某个表加 SIX锁，则表示该事务要读整个表（所以要对该表加 S锁），同时会更新个别元组（所以要对该表加 IX锁）。,5、架构锁:在执行依赖于表架构的操作时使用。它包括架构稳定锁和架构修改锁。 架构修改锁(Sch-M

32、) :数据库引擎在表数据定义语言 (DDL) 操作（例如添加列或删除表）的过程中使用架构修改 锁。保持该锁期间，Sch-M 锁将阻止对表进行并发访问。这意味着在释放架构修改锁之前，该锁之外的所有操作都将被阻止。 架构稳定锁Sch-S：数据库引擎在编译和执行查询时使用架构稳定性 (Sch-S) 锁，该锁不会阻止某些事务锁，其中包括排他 (X) 锁。因此，在编译查询的过程中，其他事务（包括那些针对表使用 X 锁的事务）将继续运行。但是，无法针对表执行获取 Sch-M 锁的并发 DDL 操作和并发 DML 操作。,6、大容量更新锁：数据库引擎在将数据大容量复制到表中时使用了大容量更新 (BU) 锁，

33、并指定了 TABLOCK 提示或使用 sp_tableoption 设置了 table lock on bulk load 表选项。大容量更新锁（BU 锁）允许多个线程将数据并发地大容量加载到同一表，同时防止其他不进行大容量加载数据的进程访问该表。,7、键范围锁:通过保护行之间键的范围，防止对事务访问的记录集进行幻像插入或删除。即：键范围锁通过覆盖索引行和索引行之间的范围来工作（而不是锁定整个基础表的行）。第二个事务在该范围内进行任何行插入、更新或删除操作时均需要修改索引，而键范围锁覆盖了索引项，所以在第一个事务完成之前会阻塞第二个事务的进行。,命令一:SELECT * FROM sys.dm

34、_tran_locks 命令二：exec sp_lock 操作：SSMS “管理” “活动监视器”,三、查看锁,【任务3-1】对数据表Goods执行插入和查询操作，检查在程序执行过程中锁的使用情况。,USE Webshop go begin transaction select * from goods exec sp_lock insert into goods values(020003,爱国者MP3-1G,02,128,0.8,20,2007-08-1,PImage/020003.gif,热点,容量G) SELECT * FROM GOODS EXEC sp_lock commit tr

35、ansaction,四、设置事务隔离级别 隔离是计算机安全学中的一种概念，其本质上是一种封锁机制。它是指自动数据处理系统中的用户和资源的相关牵制关系，也就是用户和进程彼此分开且与操作系统的保护控制也分开来。在SQL Server中，隔离级是指一个事务和其他事务的隔离程度，即指定了数据库如何保护锁定那些当前正在被其他用户或服务器请求使用的数据。事务隔离级别定义了可为读取操作获取的锁类型。,在SQL Server中有以下四种隔离级: （1）READ COMMITTED：在此隔离级下，SELECT命令不会返回尚未提交的数据，也不能返回脏数据，但它允许不可重复读取。可以通过“瞬间共享读锁”和“排他写锁

36、”来实现，读取数据的事务允许其他事务继续访问该行数据，但是未提交写事务将会禁止其他事务访问该行。这是SQL Server 默认的级别。,（2）READ UNCOMMITTED：它允许读取已经被其他用户修改但沿未提交的数据，允许脏读取但不允许更新丢失，如果一个事务已经开始写数据则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。事务隔离的最低级别，仅可保证不读取物理损坏的数据。,（3）REPEATABLE READ：用SELECT命令读取的数据在整个命令执行过程中不会被更改。禁止不可重复读取和脏读取。但是有时可能出现幻影数据，这可以通过“共享读锁”

37、和“排他写锁”实现，读取数据事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。此选项会影响系统的效能，非必要情况最好不用此隔离级。,（4）SERIALIZABLE：整个SELECT命令执行的过程中设定的共享锁会一直存在。 也称可串行读。提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作事务访问到。事务隔离的最高级别，事务之间完全隔离。如果事务在可串行读隔离级别上运行，则可以保证任何并发重叠事务均是串行的。,【任务3-2】设置事务隔离级别REPEATA

38、BLE READ。,USE Webshop go set transaction isolation level repeatable read go begin transaction go select * from goods go select * from orders go commit transaction go,五、死锁的处理 （一）死锁概述 在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。死锁状态如图所示。,（二）死锁检测 SQL Server能够自动定期搜索和处理死锁问题。SQL Server在每次定期搜索中标识所有等待锁定请求的会话，如果在下一次搜索中被标识进程仍处于等待状态，SQL Server将开始递归死锁搜索。通常使用一个事务等待图，它动态地反映所有事务的等待情况，并发控制子系