SQLServer数据库事务处理详解TRANSACTION

1、 SQLServer数据库事务处理详解TRANSACTION务定义：事务是单个的作单元。如果某事务成功，则在该事务中进的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。事务三种运模式：动提交事务每条单独的语句都是个事务。显式事务每个事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT 或ROLLBACK 语句显式结束。隐性事务在前个事务完成时新事务隐式启动，但每个事务仍以 COMMIT 或 ROLLBACK 语句显式完成。事务操作的语法：BEGIN TRANSACTIONBEGIN DISTRIBUTED TR

2、ANSACTIONCOMMIT TRANSACTIONCOMMIT WORKROLLBACK WORKSAVE TRANSACTIONBEGIN TRANSACTIONBEGIN TRANSACTION标记个显式本地事务的起始点。BEGIN TRANSACTION将 TRANCOUNT 加 1。BEGIN TRANSACTION 代表点，由连接引的数据在该点是逻辑和物理上都致的。如果遇上错误，在 BEGIN TRANSACTION 之后的所有数据改动都能进回滚，以将数据返回到已知的致状态 。每个事务继续执直到它误地完成并且 COMMIT TRANSACTION对数据库作永久的改动，或者遇上错误

3、并且 ROLLBACK TRANSACTION 语句擦除所有改动语法BEGIN TRAN SACTION transaction_name | tran_name_variable WITH MARK description 例：BEGIN TRAN T1UPDATE table1 .-nest transaction M2BEGIN TRAN M2 WITH MARKUPDATE table2 .SELECT * from table1COMMIT TRAN M2UPDATE table3 .COMMIT TRAN T1BEGIN DISTRIBUTED TRANSACTION指定个由 Mi

4、crosoft 分布式事务处理协调器 (MS DTC) 管理的 Transact-SQL 分布式事务的起始。语法BEGIN DISTRIBUTED TRAN SACTION transaction_name | tran_name_variable 参数transaction_name是户定义的事务名，于跟踪 MS DTC 实具中的分布式事务。 transaction_name 必须符合标识符规则，但是仅使头 32 个字符tran_name_variable是户定义的个变量名，它含有个事务名，该事务名于跟踪 MS DTC 实具中的分布式事务。必须 char、varchar、nchar 或nva

5、rchar 数据类型声明该变量。 注释执BEGIN DISTRIBUTED TRANSACTION 语句的服务器是事务创建，并且控制事务的完成当连接发出后续 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句时，主控服务器请求 MS DTC 在所涉及的服务器间管理分布式事务的完成。有两个法可将远程 SQL 服务器登记在个分布式事务中：分布式事务中已登记的连接执个远程存储过程调，该调引个远程服务器。分布式事务中已登记的连接执个分布式查询，该查询引个远程服务器。例本例在本地和远程数据库上更新作者的姓。本地和远程数据库将同时提交或同时回滚本事务。说明当前的SQL

6、 Server 上必须安装 MS DTC.USE pubsGOBEGIN DISTRIBUTED TRANSACTIONUPDATE authorsSET au_lname = McDonald WHERE au_id = 409-56-7008EXECUTE link_Server_T.pubs.dbo.changeauth_lname 409-56-7008,McDonaldCOMMIT TRANGONote:如果需要连接远程DB,如果是linkServer 式连接的话，定要修该linkServer的 RPC 选项置为 True。SET XACT_ABORT指定当 Transact-SQL

7、 语句产运时错误时，Microsoft? SQL Server? 是否动回滚当前事务。( 可以较简单的理解，如果中间有任何句SQL 出错，所有SQL全部回滚.特别适于 Procedure 中间调Procedure ，如果第个Procedure Ok，被调的Procedure 中间有错误，如果SET XACT_ABORT=false，则出错的部分回滚，其他部分提交，当然外部Procedure 也提交。）.-在分布式Trans中定要注意设置下参数(XACT_ABORT)语法SET XACT_ABORT ON | OFF 注释 当 SET XACT_ABORT 为 ON 时，如果 Transact-

8、SQL 语句产运时错误，整个事务将终并回滚。为 OFF 时，只回滚产错误的Transact-SQL 语句，事务将继续进处理。编译错误（如语法错误）不受 SET XACT_ABORT 的影响。对于多数 OLE DB 提供程序（包括 SQL Server），隐性或显式事务中的数据修改语句必须将 XACT_ABORT 设置为 ON。SET XACT_ABORT 的设置是在执或运时设置，不是在分析时设置。例 下例导致在含有其它 Transact-SQL 语句的事务中发违反外键错误。在第个语句集中产错误，但其它语句均成功执且事务成功提交。在第个语句集中，SET XACT_ABORT 设置为 ON。这导致

9、语句错误使批处理终，并使事务回滚。 CREATE TABLE t1 (a int PRIMARY KEY)CREATE TABLE t2 (a int REFERENCES t1(a)GOINSERT INTO t1 VALUES (1)INSERT INTO t1 VALUES (3)INSERT INTO t1 VALUES (4)INSERT INTO t1 VALUES (6)GOSET XACT_ABORT OFFGOBEGIN TRANINSERT INTO t2 VALUES (1)INSERT INTO t2 VALUES (2) /* Foreign key error */

10、INSERT INTO t2 VALUES (3)COMMIT TRANGOSET XACT_ABORT ONGOBEGIN TRANINSERT INTO t2 VALUES (4)INSERT INTO t2 VALUES (5) /* Foreign key error */INSERT INTO t2 VALUES (6)COMMIT TRANGOSAVE TRANSACTION在事务内设置保存点。语法 SAVE TRAN SACTION savepoint_name | savepoint_variable 参数 savepoint_name是指派给保存点的名称。保存点名称必须符合标

11、识符规则，但只使前 32 个字符。savepoint_variable是户定义的、含有有效保存点名称的变量的名称。必须 char、varchar、nchar 或 nvarchar 数据类型声明该变量。 注释户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的部分，事 务可以返回的位置。如果将事务回滚到保存点，则必须（如果需要，使更多的 Transact-SQL 语句和 COMMIT TRANSACTION 语句）继续完成事务，或者必须（通过将事务回滚到其起始点）完全取消事务。若要取消整个事务，请使 ROLLBACK TRANSACTION transaction_name 格式。这

12、将撤消事务的所 有语句和过程。Note：1： 在由 BEGIN DISTRIBUTED TRANSACTION 显式启动或从本地事务升级来的分布式事务中，不持 SAVETRANSACTION。2：当事务开始时，将直控制事务中所使的资源直到事务完成（也就是锁定）。当将事务的部分回滚到保存点时，将继续控制资源直到事务完成（或者回滚全部事务）。例：begin transactionsave transaction Ainsert into demo values(BB,B term)rollback TRANSACTION Acreate table demo2(name varchar(10),a

13、ge int)insert into demo2(name,age) values(lis,1)commit transactionROLLBACK TRANSACTION 将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。语法ROLLBACK TRAN SACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable 参数transaction_name是给 BEGIN TRANSACTION 上的事务指派的名称。transaction_name 必须符合标识符规则，但只使事务名称的

14、前 32 个字符。嵌套事务时，transaction_name 必须是来最远的 BEGIN TRANSACTION 语句的名称。tran_name_variable是户定义的、含有有效事务名称的变量的名称。必须 char、varchar、nchar 或 nvarchar 数据类型声明该变量。savepoint_name是来 SAVE TRANSACTION 语句的 savepoint_name。savepoint_name 必须符合标识符规则。当条件回滚只影响事务的部分时使 savepoint_name。savepoint_variable是户定义的、含有有效保存点名称的变量的名称。必须 ch

15、ar、varchar、nchar 或 nvarchar 数据类型声明该变量。注释 ROLLBACK TRANSACTION 清除事务的起点或到某个保存点所做的所有数据修改。ROLLBACK 还释放由事务控制的资源。不带 savepoint_name 和 transaction_name 的 ROLLBACK TRANSACTION 回滚到事务的起点。嵌套事务时，该语句将所有内层事务回滚到 最远的 BEGIN TRANSACTION 语句。在这两种情况下，ROLLBACK TRANSACTION 均将 TRANCOUNT 系统函数减为0。ROLLBACKTRANSACTION savepoint

16、_name 不减少 TRANCOUNT。Note:ROLLBACK TRANSACTION 语句若指定 savepoint_name 则不释放任何锁。在由 BEGIN DISTRIBUTED TRANSACTION 显式启动或从本地事务升级来的分布式事务中，ROLLBACK TRANSACTION 不能引savepoint_name。在执 COMMIT TRANSACTION 语句后不能回滚事务。在事务内允许有重复的保存点名称，但 ROLLBACK TRANSACTION 若使重复的保存点名称，则只回滚到最近的使该保存点名称的SAVE TRANSACTION。在存储过程中，不带 savepoi

17、nt_name 和 transaction_name 的 ROLLBACK TRANSACTION 语句将所有语句回滚到最远的BEGINTRANSACTION。在存储过程中，ROLLBACK TRANSACTION 语句使 TRANCOUNT 在触发器完成时的值不同于调该存储过程时的TRANCOUNT 值，并且成个信息。该信息不影响后的处理。如果在触发器中发出 ROLLBACK TRANSACTION：将回滚对当前事务中的那点所做的所有数据修改，包括触发器所做的修改。触发器继续执 ROLLBACK 语句之后的所有其余语句。如果这些语句中的任意语句修改数据，则不回滚这些修改。执其余的语句不会激发

18、嵌套触发器。在批处理中，不执所有位于激发触发器的语句之后的语句。每次进触发器，TRANCOUNT 就增加 1，即使在动提交模式下也是如此。（系统将触发器视作隐性嵌套事务。）在存储过程中，ROLLBACK TRANSACTION 语句不影响调该过程的批处理中的后续语句；将执批处理中的后续语句。在触发器中，ROLLBACK TRANSACTION 语句终含有激发触发器的语句的批处理；不执批处理中的后续语句。ROLLBACK TRANSACTION 语句不成显给户的信息。如果在存储过程或触发器中需要警告，请使 RAISERROR 或 PRINT 语句。RAISERROR 是于指出错误的选语句。ROL

19、LBACK 对游标的影响由下三个规则定义：当 CURSOR_CLOSE_ON_COMMIT 设置为 ON 时，ROLLBACK 关闭但不释放所有打开的游标。当 CURSOR_CLOSE_ON_COMMIT 设置为 OFF 时，ROLLBACK 不影响任何打开的同步 STATIC 或 INSENSITIVE 游标不影响已完全填充的异步 STATIC 游标。将关闭但不释放任何其它类型的打开的游标。对于导致终批处理并成内部回滚的错误，将释放在含有该错误语句的批处理内声明的所有游标。不论游标的类型或 CURSOR_CLOSE_ON_COMMIT 的设置，所有游标均将被释放，其中包括在该错误批处理所调的

20、存储过程内声明的游标。在该错误批处理之前的批处理内声明的游标以规则 1 和 2 为准。死锁错误就属于这类错误。在触发器中发出的 ROLLBACK 语句也动成这类错误。 权限ROLLBACK TRANSACTION 权限默认授予任何有效户。例：begin transactionsave transaction Ainsert into demo values(BB,B term)rollback TRANSACTION A- select * into demo2 from demo1create table demo2(name varchar(10),age int)insert into

21、demo2(name,age) values(lis,1)rollback transactionCOMMIT TRANSACTION标志个成功的隐性事务或户定义事务的结束。如果 TRANCOUNT 为 1，COMMITTRANSACTION 使得从事务开始以来所执的 所有数据修改成为数据库的永久部分，释放连接占的资源，并将 TRANCOUNT 减少到 0。如果TRANCOUNT 于 1，则COMMITTRANSACTION 使 TRANCOUNT 按 1 递减。只有当事务所引的所有数据的逻辑都正确时，发出 COMMIT TRANSACTION 命令。COMMIT WORK标志事务的结束。语法

22、COMMIT WORK 注释此语句的功能与 COMMIT TRANSACTION 相同，但 COMMIT TRANSACTION 接受户定义的事务名称。这个指定或没有指定可选关键字WORK 的 COMMIT 语法与 SQL-92 兼容例：begin transaction ainsert into demo values(BB,B term)commit TRANSACTION A隐性事务当连接以隐性事务模式进操作时，SQL Server将在提交或回滚当前事务后动启动新事务。须描述事务的开始，只需提交或回滚每个事务。隐性事务模式成连续的事务链。在为连接将隐性事务模式设置为打开之后，当 SQL

23、Server 次执下列任何语句时，都会动启动个事务：INSERTOPENREVOKESELECTFETCHTRUNCATE TABLEUPDATEGRANT在发出 COMMIT 或 ROLLBACK 语句之前，该事务将直保持有效。在第个事务被提交或回滚之后，下次当连接执这些语句中的任何语句时，SQL Server 都将动启动个新事务。SQL Server 将不断地成个隐性事务链， 直到隐性事务模式关闭为例：begin transactionsave transaction Ainsert into demo values(BB,B term)rollback TRANSACTION Acrea

24、te table demo2(name varchar(10),age int)insert into demo2(name,age) values(lis,1)rollback transaction- 在 Create table demo2 时 SQL Server 已经隐式创建个Trans,知道提交或回滚嵌套事务处理:1： Trans 嵌套，将内部的trans 合并到外部并形成个Trans.begin tran t1-In the first trans .Insert into demo2(name,age) values(lis,1)-Second Trans begin tran

25、saction t2insert into demo values(BB,B term)commit transaction t2-In the first trans .Insert into demo2(name,age) values(lis,2)rollback transaction t1Note:在系列嵌套的事务中个事务名给多个事务命名对该事务没有什么影响。系统仅登记第个（最外部的）事务名。回滚到其它任何名字（有效的保存点名除外）都会产错误。事实上，任何在回滚之前执的语句都没有在错误发时回滚。这语句仅当外层的事务回滚时才会进回滚。例：内部事务回滚SQL server 报错。begi

26、n tran t1Insert into demo2(name,age) values(lis,1)-Second Trans-Server: Msg 6401, Level 16, State 1, Line 6-Cannot roll back t2. No transaction or savepoint of that name was found.begin transaction t2insert into demo values(BB,B term)rollback transaction t2-In the first trans .Insert into demo2(name,age) values(lis,2)commit transaction t1例： 内部事务提交SQL server 不会报错。 begin tran t1Insert into demo2(name,age) values(lis,1