2008年04月12日 11:29:37
sqlserver事务处理全面解析三
|
指定申请锁资源所等待的毫秒数。我们可以看到锁超时是申请所等待的时间有效，并且不能获得该锁资源（但该事务前面获得的锁并不释放，后面的语句仍然可以获得锁），它只对被动方有用，如果阻塞者（主动方）设置了锁超时是没有意义的，因为它的锁是自己占有的，只有到自己的任务完成后才会释放（释放的方式跟隔离级有关系）。 另外要注意的是，设置了锁超时的语句，当真的发生超时，那么它自身的语句没有执行成功，但它仍可以继续执行下面的语句，除非执行IF @@ERROR = 1222 …或者设置SET XACT_ABORT ON才能解释，并释放该事务的所有资源。 在 SET XACT_ABORT ON 语句执行之后，任何运行时语句错误都将导致当前事务自动回滚。编译错误（如语法错误）不受 SET XACT_ABORT 的影响。 T1: SET LOCK_TIMEOUT 1800-- -1 --;1800 --SELECT @@LOCK_TIMEOUT begin tran SELECT * FROM TICKET (UPDLOCK) --commit tran T2: SET LOCK_TIMEOUT 10000 SET XACT_ABORT ON begin tran UPDATE TICKET SET NUM=2 SELECT * FROM TICKET commit tran OUTPUT: 服务器: 消息 1222，级别 16，状态 50，行 1 已超过了锁请求超时时段。 问题1，那些是基于会话的，是否基于全局的环境变量？ 好像没有 问题2，锁超时是针对的那个程序。 超时环境变量是基于会话的。 1.2. 锁升级： 锁升级是将众多细粒度锁转换为较少的粗粒度的锁的过程，以削减系统开销。当事务超过它的升级极限时，Microsoft® SQL Server™ 2000 自动将行锁和页锁升级为表锁。 如一次SELECT语句累计200 page locks, 它将升级成table lock。 1.3. 事务的三种模式 事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。 Microsoft® SQL Server™ 以三种事务模式运行： Ø 自动提交事务:每条单独的语句都是一个事务。 Ø 显式事务:每个事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT 或 ROLLBACK 语句显式结束。 Ø 隐性事务:在前一个事务完成时新事务隐式启动，但每个事务仍以 COMMIT 或 ROLLBACK 语句显式完成。 注意： T1: USE pubs [/pre]GO [/pre] [/pre]CREATE table t1 (a int) [/pre]GO [/pre]INSERT INTO t1 VALUES (1) [/pre]GO [/pre] [/pre]PRINT 'Use explicit transaction' [/pre]BEGIN TRAN [/pre]INSERT INTO t1 VALUES (2) [/pre]SELECT 'Tran count in transaction'= @@TRANCOUNT [/pre]COMMIT TRAN [/pre]SELECT 'Tran count outside transaction'= @@TRANCOUNT [/pre]GO [/pre] [/pre]PRINT 'Setting IMPLICIT_TRANSACTIONS ON' [/pre]GO [/pre]SET IMPLICIT_TRANSACTIONS ON [/pre]GO [/pre] [/pre]PRINT 'Use implicit transactions' [/pre]GO [/pre]-- No BEGIN TRAN needed here. [/pre]INSERT INTO t1 VALUES (4) [/pre]SELECT 'Tran count in transaction'= @@TRANCOUNT [/pre]COMMIT TRAN [/pre]SELECT 'Tran count outside transaction'= @@TRANCOUNT [/pre]GO [/pre] [/pre]PRINT 'Use explicit transactions with IMPLICIT_TRANSACTIONS ON' [/pre]GO [/pre]BEGIN TRAN [/pre]INSERT INTO t1 VALUES (5) [/pre]SELECT 'Tran count in transaction'= @@TRANCOUNT [/pre]COMMIT TRAN [/pre]--如果这个地方结束SQL语句，必须写两个COMMIT TRAN [/pre]SELECT 'Tran count outside transaction'= @@TRANCOUNT [/pre]GO [/pre] [/pre]SELECT * FROM t1 [/pre]GO [/pre] [/pre]-- Need to commit this tran too! [/pre]DROP TABLE t1 [/pre]COMMIT TRAN [/pre]GO [/pre]1.4. 事务保存点 保存点提供了一种机制，用于回滚部分事务。可以使用 SAVE TRANSACTION savepoint_name 语句创建一个保存点，然后再执行 ROLLBACK TRANSACTION savepoint_name 语句回滚到该保存点，从而无须回滚到事务的开始。 在不可能发生错误的情况下，保存点很有用。在很少出现错误的情况下使用保存点回滚部分事务，比让每个事务在更新之前测试更新的有效性更为有效。更新和回滚操作代价很大，因此只有在遇到错误的可能性很小，而且预先检查更新的有效性的代价相对很高的情况下，使用保存点才会非常有效。 下面的示例显示保存点在一个订购系统中的使用情况。该系统中存货不足的可能性很小，因为该公司具备有效的供应商和再订购点。通常应用程序在尝试更新订购记录时，会先验证手边是否有足够的存货。该示例假定由于某种原因（如连接到一个低速的调制解调器或广域网上），先验证可用存货的数量代价相对较大。可将应用程序编写为只进行更新，而且如果收到错误信息，表明库存不足时，将回滚该更新。在这种情况下，在插入之后快速检查 @@ERROR 比在更新之前验证库存数量速度要快得多。 InvCtrl 表有一个 CHECK 约束，如果 QtyInStk 列低于 0，就会触发 547 号错误。OrderStock 过程创建一个保存点。如果出现 547 错误，它将回滚到该保存点，并将当前手边有的项目数返回给调用进程。然后调用进程可以决定是否需要针对手边现有的数量重新下订单。如果 OrderStock 返回一个 0，则调用进程知道当前有足够的存货，可以满足订购需要。 SET NOCOUNT OFF [/pre]GO [/pre]USE pubs [/pre]GO [/pre]CREATE TABLE InvCtrl [/pre] (WhrhousID int, [/pre] PartNmbr int, [/pre] QtyInStk int, [/pre] ReordrPt int, [/pre] CONSTRAINT InvPK PRIMARY KEY [/pre] (WhrhousID, PartNmbr), [/pre] CONSTRAINT QtyStkCheck CHECK (QtyInStk > 0) ) [/pre]GO [/pre]CREATE PROCEDURE OrderStock @WhrhousID int, @PartNmbr int, [/pre] @OrderQty int [/pre]AS [/pre]DECLARE @ErrorVar int [/pre]SAVE TRANSACTION StkOrdTrn [/pre]UPDATE InvCtrl SET QtyInStk = QtyInStk - @OrderQty [/pre]WHERE WhrhousID = 1 [/pre] AND PartNmbr = 1 [/pre]SELECT @ErrorVar = @@error [/pre]IF (@ErrorVar = 547) [/pre]BEGIN [/pre] ROLLBACK TRANSACTION StkOrdTrn [/pre] RETURN (SELECT QtyInStk [/pre] FROM InvCtrl [/pre] WHERE WhrhousID = @WhrhousID [/pre] AND PartNmbr = @PartNmbr) [/pre]END [/pre]ELSE [/pre] RETURN 0 [/pre]GO [/pre] 1.5. 嵌套事务 显式事务可以嵌套。这主要是为了支持存储过程中的一些事务，这些事务可以从事务中已有的进程中调用，也可以从没有活动事务的进程中调用。 下面的示例显示了嵌套事务的用途。TransProc 过程强制执行其事务，而不管执行该事务的进程的事务模式如何。如果在事务活动时调用 TransProc，则 TransProc 中的嵌套事务几乎被忽略，而且其 INSERT 语句将根据对外部事务采取的最终操作提交或回滚。如果不含有未完成事务的进程执行 TransProc，则在该过程结束时，COMMIT TRANSACTION 将有效地提交 INSERT 语句。 SET QUOTED_IDENTIFIER OFF [/pre]GO [/pre]SET NOCOUNT OFF [/pre]GO [/pre]USE pubs [/pre]GO [/pre]CREATE TABLE TestTrans(Cola INT PRIMARY KEY, [/pre] Colb CHAR(3) NOT NULL) [/pre]GO [/pre]CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS [/pre]BEGIN TRANSACTION InProc [/pre]INSERT INTO TestTrans VALUES (@PriKey, @CharCol) [/pre]INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol) [/pre]COMMIT TRANSACTION InProc [/pre]GO [/pre]/* Start a transaction and execute TransProc */ [/pre]BEGIN TRANSACTION OutOfProc [/pre]GO [/pre]EXEC TransProc 1, 'aaa' [/pre]GO [/pre]/* Roll back the outer transaction, this will [/pre] roll back TransProc's nested transaction */ [/pre]ROLLBACK TRANSACTION OutOfProc [/pre]GO [/pre]EXECUTE TransProc 3,'bbb' [/pre]GO [/pre]/* The following SELECT statement shows only rows 3 and 4 are [/pre] still in the table. This indicates that the commit [/pre] of the inner transaction from the first EXECUTE statement of [/pre] TransProc was overridden by the subsequent rollback. */ [/pre]SELECT * FROM TestTrans [/pre]GO [/pre]Microsoft® SQL Server™ 忽略提交内部事务。根据最外部事务结束时采取的操作，将提交或者回滚事务。如果提交外部事务，则内层嵌套的事务也会提交。如果回滚外部事务，则不论此前是否单独提交过内层事务，所有内层事务都将回滚。 对 COMMIT TRANSACTION 或 COMMIT WORK 的每个调用都应用于最后执行的 BEGIN TRANSACTION。如果嵌套 BEGIN TRANSACTION 语句，那么 COMMIT 语句只应用于最后一个嵌套的事务，也就是在最内层的事务。即使嵌套事务内部的 COMMIT TRANSACTION transaction_name 语句引用外部事务的事务名，该提交也只应用于最内层的事务。 ROLLBACK TRANSACTION 语句的 transaction_name 参数引用一组命名的嵌套事务的内层事务是非法的，transaction_name 只能引用最外部事务的事务名。如果在一组嵌套事务的任意级别执行使用外部事务名称的 ROLLBACK TRANSACTION transaction_name 语句，那么所有的嵌套事务都将回滚。如果在一组嵌套事务的任意级别执行没有 transaction_name 参数的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句，那么它将回滚所有嵌套事务，包括最外部事务。 @@TRANCOUNT 函数记录当前事务的嵌套级。每个 BEGIN TRANSACTION 语句使 @@TRANCOUNT 加 1。每个 COMMIT TRANSACTION 或 COMMIT WORK 语句使 @@TRANCOUNT 减 1。没有事务名的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句将回滚所有嵌套事务，并使 @@TRANCOUNT 减小到 0。使用一组嵌套事务中最外部事务的事务名称的 ROLLBACK TRANSACTION 将回滚所有嵌套事务，并使 @@TRANCOUNT 减到 0。在无法确定是否已经在事务中时，可以用 SELECT @@TRANCOUNT 语句确定 @@TRANCOUNT 是 1 还是更大。如果 @@TRANCOUNT 是 0，则表明不在事务中。 1.6. 存储过程和触发器中回滚 如果 @@TRANCOUNT 的值在存储过程完成时与过程执行时不同，则会生成一个 266 信息类错误。该错误不是由触发器中同一个条件生成的。 当调用存储过程时，如果 @@TRANCOUNT 为 1 或更大，并且该过程执行 ROLLBACK TRANSACTION 或 ROLLBACK WORK 语句，则会产生 266 号错误。这是因为 ROLLBACK 回滚所有未完成的事务，并将 @@TRANCOUNT 减到 0，该值比调用过程时要小。 如果在触发器中发出 ROLLBACK TRANSACTION：
触发器继续执行 ROLLBACK 语句之后的所有其余语句。如果这些语句中的任意语句修改数据，则不回滚这些修改。执行其余的语句不会激发嵌套触发器。 在批处理中，所有位于激发触发器的语句之后的语句都不被执行。 触发器中的 ROLLBACK 关闭并释放所有在包含激发触发器的语句的批处理中声明和打开的游标。这其中包括了在激发触发器的批处理所调用的存储过程中声明和打开的游标。在激发触发器的批处理之前的批处理中所声明的游标将只是关闭，但是在以下条件下，STATIC 或 INSENSITIVE 游标不关闭：
静态游标要么是同步游标，要么是完全填充的异步游标。 当执行触发器时，触发器的操作总是好像有一个未完成的事务在起作用。如果激发触发器的语句是在隐性或显式事务中，则肯定会这样。在自动提交模式下，也是如此。当语句开始以自动提交模式执行时，如果遇到错误，则会有隐含的 BEGIN TRANSACTION 语句允许恢复该语句生成的所有修改。该隐含的事务对批处理中的其它语句没有影响，因为当语句完成时，该事务要么提交，要么回滚。但是，当调用触发器时，该隐含的事务将仍然有效。 这意味着，只要触发器中发出 BEGIN TRANSACTION 语句，则实际上就开始了一个嵌套事务。因为当回滚嵌套事务时，嵌套的 BEGIN TRANSACTION 语句将被忽略，触发器中发出的 ROLLBACK TRANSACTION 总是回滚过去该触发器本身发出的所有 BEGIN TRANSACTION 语句。ROLLBACK 回滚到最外部的 BEGIN TRANSACTION。 若要在触发器中进行部分回滚，则即使总是以自动提交模式进行调用，也必须使用 SAVE TRANSACTION 语句。以下的触发器阐明了这一点： CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS[/pre]SAVE TRANSACTION MyName [/pre]INSERT INTO TestAudit [/pre] SELECT * FROM inserted [/pre]IF (@@error <> 0) [/pre]BEGIN [/pre] ROLLBACK TRANSACTION MyName [/pre]END [/pre] 这也影响触发器中 BEGIN TRANSACTION 语句后面的COMMIT TRANSACTION 语句。因为 BEGIN TRANSACTION 启动一个嵌套事务，而随后的 COMMIT 语句只应用于该嵌套事务。如果在 COMMIT 之后执行 ROLLBACK TRANSACTION 语句，那么 ROLLBACK 将一直回滚到最外部的 BEGIN TRANSACTION。以下的触发器阐明了这一点： CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS[/pre]BEGIN TRANSACTION [/pre]INSERT INTO TrigTarget [/pre] SELECT * FROM inserted [/pre]COMMIT TRANSACTION [/pre]ROLLBACK TRANSACTION [/pre] 此触发器绝对不会在 TrigTarget 表中插入。BEGIN TRANSACTION 总是启动一个嵌套事务。COMMIT TRANSACTION 只提交嵌套事务，而下面的 ROLLBACK TRANSACTION 则一直回滚到最外部的 BEGIN TRANSACTION。 1.7. 编写有效的事务尽可能使事务保持简短很重要。启动事务后，DBMS 必须将很多资源控制到事务结束时，以保护事务的 ACID 属性。如果修改数据，则必须用排它锁保护修改过的行，以防止任何其它事务读取该行，并且必须将排它锁控制到提交或回滚事务时为止。根据事务隔离级别设置，SELECT 语句可以获取必须被控制到提交或回滚事务时为止的锁。特别是在有很多用户的系统中，必须尽可能使事务保持简短以减少并发连接间的资源锁定争夺。在有少量用户的系统中，运行时间长、效率低的事务可能不会成为问题，但是在有上千个用户的系统中，将不能忍受这样的事务。
|
一共有 1 条评论
告知164650194@qq.com