transaction

SQLServer数据库事务处理详解TRANSACTION

务定义：

事务是单个的⼯作单元。如果某⼀事务成功，则在该事务中进⾏的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错

误且必须取消或回滚，则所有数据更改均被清除。事务三种运⾏模式：

⾃动提交事务每条单独的语句都是⼀个事务。显式事务每个事务均以BEGINTRANSACTION徒步运动 语句显式开始，以COMMIT或

ROLLBACK语句显式结束。隐性事务在前⼀个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。

事务操作的语法：BEGINTRANSACTION

BEGINDISTRIBUTEDTRANSACTION

COMMITTRANSACTION

COMMITWORK

ROLLBACKWORK

SAVETRANSACTION

BEGINTRANSACTIONBEGINTRANSACTION

标记⼀个显式本地事务的起始点。

BEGINTRANSACTION将@@TRANCOUNT加1。

BEGINTRANSACTION代表⼀点，由连接引⽤的数据在该点是逻辑和物理上都⼀致的。如果遇上错误，在BEGINTRANSACTION之后

的所有数据改动都能进⾏回滚，以将数据返回到已知的⼀致状态。每个事务继续执⾏直到它⽆误地完成并且⽤COMMITTRANSACTION

对数据库作永久的改动，或者遇上错误并且⽤ROLLBACKTRANSACTION语句擦除所有改动语法

BEGINTRAN[SACTION][transaction_name|@tran_name_variable[WITHMARK['description']]]例⼦：

BEGINTRANT1

--nesttransactionM2

BEGINTRANM2WITHMARK

SELECT*fromtable1

COMMITTRANM2

BEGINDISTRIBUTEDTRANSACTION

指定⼀个由Microsoft分布式事务处理协调器(MSDTC)管理的Transact-SQL分布式事务的起始。语法

BEGINDISTRIBUTEDTRAN[SACTION]

[transaction_name|@tran_name_variable]参数

transaction_name

是⽤户定义的事务名，⽤于跟踪MSDTC实⽤⼯具中的分布式事务。transaction_name必须符合标识符规则，但是仅使⽤头32个字符@tran_name_variable

是⽤户定义的⼀个变量名，它含有⼀个事务名，该事务名⽤于跟踪MSDTC实⽤⼯具中的分布式事务。必须⽤char、varchar、nchar或nvarchar数据类型声明该变量。

注释

执⾏BEGINDISTRIBUTEDTRANSACTION语句的服务器是事务创建⼈，并且控制事务的完成

当连接发出后续COMMITTRANSACTION或ROLLBACKTRANSACTION语句时，

主控服务器请求MSDTC在所涉及的服务器间管理分布式事务的完成。有两个⽅法可将远程SQL服务器登记在⼀个分布式事务中：

分布式事务中已登记的连接执⾏⼀个远程存储过程调⽤，该调⽤引⽤⼀个远程服务器。

分布式事务中已登记的连接执⾏⼀个分布式查询，该查询引⽤⼀个远程服务器。⽰例

本例在本地和远程数据库上更新作者的姓。本地和远程数据库将同时提交或同时回滚本事务。说明

当前的SQLServer上必须安装MSDTC.

USEpubsGO

BEGINDISTRIBUTEDTRANSACTIONUPDATEauthors

SETau_lname='McDonald'WHEREau_id='409-56-7008'

EXECUTElink_Server_auth_lname'409-56-7008','McDonald'

COMMITTRANGONote:

如果需要连接远程DB,如果是linkServer⽅式连接的话，⼀定要修该linkServer的RPC选项置为True。SETXACT_ABORT

指定当Transact-SQL语句产⽣运⾏时错误时，Microsoft?SQLServer?是否⾃动回滚当前事务。

(可以⽐较简单的理解，如果中间有任何⼀句SQL出错，所有SQL全部回滚.特别适⽤于Procedure中间调⽤Procedure，如果第⼀个

ProcedureOk，被调⽤的Procedure中间有错误，如果SETXACT_ABORT=fal，则出错的部分回滚，其他部分提交，当然外部Procedure也提交。）.

---在分布式Trans中⼀定要注意设置下⾯参数(XACT_ABORT)语法SETXACT_ABORT{ON|OFF}

注释当SETXACT_ABORT为ON时，如果Transact-SQL语句产⽣运⾏时错误，整个事务将终⽌并回滚。为OFF时，只回滚产⽣错交流的英语 误的Transact-SQL语句，⽽事务将继续进⾏处理。编译错误（如语法错误）不受SETXACT_ABORT的影响。

对于⼤多数OLEDB提供程序（包括SQLServer），隐性或显式事务中的数据修改语句必须将XACT_ABORT设置为ON。SETXACT_ABORT的设置是在执⾏或运⾏时设置，⽽不是在分析时设置。

⽰例下例导致在含有其它Transact-SQL语句的事务中发⽣违反外键错误。在第⼀个语句集中产⽣错误，但其它语句均成功执⾏且事务成功

提交。在第⼆个语句集中，SETXACT_ABORT设置为ON。这导致语句错误使批处理终⽌，并使军医军衔图解 事务回滚。

CREATETABLEt1(aintPRIMARYKEY)

CREATETABLEt2(aintREFERENCESt1(a))GO

INSERTINTOt1VALUES(1)

INSERTINTOt1VALUES(3)

INSERTINTOt1VALUES(4)

INSERTINTOt1VALUES(6)GO

SETXACT_ABORTOFFGO

BEGINTRAN

INSERTINTOt2VALUES(1)

INSERTINTOt2VALUES(2)/*Foreignkeyerror*/

INSERTINTOt2VALUES(3)

COMMITTRANGO

SETXACT_ABORTONGO

BEGINTRAN

INSERTINTOt2VALUES(4)

INSERTINTOt2VALUES(5)/*Foreignkeyerror*/

INSERTINTOt2VALUES什么水果助消化 (6)

COMMITTRANGO

SAVETRANSACTION在事务内设置保存点。

语法SAVETRAN[SACTION]{savepoint_name|@savepoint_variable}参数savepoint_name

是指派给保存点的名称。保存点名称必须符合标识符规则，但只使⽤前32个字符。@savepoint_variable

是⽤户定义的、含有有效保存点名称的变量的名称。

必须⽤char、varchar、nchar或nvarchar数据类型声明该变量。注释

⽤户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的⼀部分，事务可以返回的位置。如果将事务回滚到保存点，则

必须（如果需要，使⽤更多的Transact-SQL语句和COMMITTRANSACTION语句）继续完成事务，或者必须（通过将事务回滚到其起

始点）完全取消事务。若要取消整个事务，请使⽤ROLLBACKTRANSACTIONtransaction_name格式。这将撤消事务的所有语句和过程。

Note：1：在由BEGINDISTRIBUTEDTRANSACTION显式启动或从本地事务升级⽽来的元宵灯 分布式事务中，不⽀持SAVETRANSACTION。

2：当事务开始时，将⼀直控制事务中所使⽤的资源直到事务完成（也就是锁定）。当将事务的⼀部分回滚到保存点时，将继续控制资源直

到事务完成（或者回滚全部事务）。

例⼦：begintransactionsavetransactionA

inrtintodemovalues('BB','Bterm')rollbackTRANSACTIONA

createtabledemo2(namevarchar(10),ageint)

inrtintodemo2(name,age)values('lis',1)

committransactionROLLBACKTRANSACTION

将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。语法

ROLLBACK[TRAN[SACTION]

[transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable]]参数

transaction_name

是给BEGINTRANSACTION上的事务指派的名称。transaction_name必须符合标识符规则，但只使⽤事务名称的前32个字符。嵌套

事务时，transaction_name必须是来⾃最远的BEGINTRANSACTION语句的名称。@tran_name_variable

是⽤户定义的、含有有效事务名称的变量的名称。必须⽤char、varchar、nchar或男生纹身 nvarchar数据类型声明该变量。savepoint_name

是来⾃SAVETRANSACTION语句的savepoint_name。savepoint_name必须符合标识符规则。当条件回滚只影响事务的⼀部分时使

⽤savepoint_name。@savepoint_variable

是⽤户定义的、含有有效保存点名称的变量的名称。必须⽤char、varchar、nchar或nvarchar数据类型声明该变量。

注释ROLLBACKTRANSACTION清除⾃事务的起点或到某个保存点所做的所有数据修改。ROLLBACK还释放由事务控制的资源。

不带savepoint_name和transaction_name的ROLLBACKTRANSACTION回滚到事务的起点。嵌套事务时，该语句将所有内层事务

回滚到最远的BEGINTRANSACTION语句。在这两种情况下，ROLLBACKTRANSACTION均将@@TRANCOUNT系统函数减为0。ROLLBACK

TRANSACTIONsavepoint_name不减少@@TRANCOUNT。Note:

ROLLBACKTRANSACTION语句若指定savepoint_name则不释放任何锁。

在由BEGINDISTRIBUTEDTRANSACTION显式启动或从本地事务升级⽽来的分布式事务中，ROLLBACKTRANSACTION不能引⽤savepoint_name。在执⾏COMMITTRANSACTION语句后不能回滚事务。

在事务内允许有重复的保存点名称，但ROLLBACKTRANSACTION若使⽤重复的保存点名称，则只回滚到最近的使⽤该保存点名称的SAVETRANSACTION。

在存储过程中，不带savepoint_name和transaction_name的ROLLBACKTRANSACTION语句将所有语句回滚到最远的

BEGINTRANSACTION。在存储过程中，ROLLBACKTRANSACTION语句使@@TRANCOUNT在触发器完成时的值不同于调⽤该存储过程时的@@TRANCOUNT值，并且⽣成⼀个信息。该信息不影响后⾯的处理。

如果在触发器中发出ROLLBACKTRANSACTION：将回滚对当前事务中的那⼀点所做的所有数据修改，包括触发器所做的修改。

触发器继续执⾏ROLLBACK语句之后的所有其余语句。如果这些语句中的任意语句修改数据，则不回滚这些修改。执⾏其余的语句不会

激发嵌套触发器。在批处理中，不执⾏所有位于激发触发器的语句之后的语句。每次进⼊触发器，@@TRANCOUNT就增加1，即使在⾃动提交模式下也是如此。（系统将触发器视作隐性嵌套事务。）

在存储过程中，ROLLBACKTRANSACTION语句不影响调⽤该过程的批处理中的后续语句；

将执⾏批处理中的后续语句。在触发器中，ROLLBACKTRANSACTION语句终⽌含有激发触发器的语句的批处理；不执⾏批处理中的后续语句。

ROLLBACKTRANSACTION语句不⽣成显⽰给⽤户的信息。如果在存储过程或触发器中需要警告，请使⽤RAISERROR或PRINT语

句。RAISERROR是⽤于指出错误的⾸选语句。ROLLBACK对游标的影响由下⾯三个规则定义：

当CURSOR_CLOSE_ON_COMMIT设置为ON时，ROLLBACK关闭但不释放所有打开的游标。

当CURSOR_CLOSE_ON_COMMIT设置为OFF时，ROLLBACK不影响任何打开的同步STATIC或INSENSITIVE游标不影响已完全填充的异步STATIC游标。将关闭但不释放任何其它类型的打开的游标。

对于导致终⽌批处理并⽣成内部回滚的错误，将释放在含有该错误语句的批处理内声明的所有游标。

不论游标的类型或CURSOR_CLOSE_ON_COMMIT的设置，所有游标均将被释放，其中包括在该错误批处理所调⽤的存储过程内声明的

游标。在该错误批处理之前的批处理内声明的游标以规则1和2为准。死锁错误就属于这类错误。在触发器中发出的ROLLBACK语句也⾃动⽣成这类错误。

权限

ROLLBACKTRANSACT商务谈判技巧 ION权限默认授予任何有效⽤户。例⼦：

begintransactionsavetransactionA

inrtintodemovalues('BB','Bterm')rollbackTRANSACTIONA

--lect*intodemo2fromdemo1

createtabledemo2(namevarchar(10),ageint)

inrtintodemo2(name,age)values('lis',1)

rollbacktransactionCOMMITTRANSACTION

标志⼀个成功的隐性事务或⽤户定义事务的结束。如果@@TRANCOUNT为1，COMMIT

TRANSACTION使得⾃从事务开始以来所执⾏的所有数据修改成为数据库的永久部分，释放连接

占⽤的资源，并将@@TRANCOUNT减少到0。如果@@TRANCOUNT⼤于1，则COMMITTRANSACTION使@@TRANCOUNT按1递减。

只有当事务所引⽤的所有数据的逻辑都正确时，发出COMMITTRANSACTION命令。

COMMITWORK

标志事务的结束。语法

COMMIT[WORK]注释

此语句的功能与COMMITTRANSACTION相同，但COMMITTRANSACTION接受⽤户定义的事务

名称。这个指定或没有指定可选关键字WORK的COMMIT语法与SQL-92兼容例⼦：

begintransactiona

inrtintodemovalues('BB','Bterm')

commitTRANSACTIONA隐性事务

当连接以隐性事务模式进⾏操作时，SQLServer将在提交或回滚当前事务后⾃动启动新事务。⽆须描述事务的开始，只需提交或回滚每个事务。隐性事务模式⽣成连续的事务链。

在为连接将隐性事务模式设置为打开之后，当SQLServer⾸次执⾏下列任何语句时，都会⾃动启动⼀个事务：

ALTERTABLE

CREATE

DELETE

DROP

FETCHGRANT

INSERT

OPEN

REVOKESELECT

TRUNCATETABLEUPDATE

在发出COMMIT或ROLLBACK语句之前，该事务将⼀直保持有效。在第⼀个事务被提交或回滚之后，下次当连接执⾏这些语句中的任何语句时，SQLServer都将⾃动启动⼀个新事务。SQLServer将不断地⽣成⼀个隐性事务链，

直到隐性事务模式关闭为⽌例⼦：

begintransactionsavetransactionA

inrtintodemovalues('BB','Bterm')rollbackTRANSACTIONA

createtabledemo2(namevarchar(10),ageint)

inrtintodemo2(name,ag乡村风光 e)values('lis',1)rollbacktransaction

--在Createtabledemo2时SQLServer已经隐式创建⼀个Trans,知道提交或回滚嵌套事务处理:

1：Trans嵌套，将内部的trans合并到外部并形成⼀个Trans.

begintrant1----Inthefirsttrans.

Inrtintodemo2(name,age)values('lis',1)

---SecondTransbegintransactiont2

inrtintodemovalues('BB','Bterm')

committransactiont2----Inthefirsttrans.

Inrtintodemo2(name,age)values('lis',2)

rollbacktransactiont1Note:

在⼀系列嵌套的事务中⽤⼀个事务名给多个事务命名对该事务没有什么影响。系统仅登记第⼀个（最外部的）事务名。回滚到其它任何名字（有效的保存点名除外）都会产⽣错误。

事实上，任何在回滚之前执⾏的语句都没有在错误发⽣时回滚。这语句仅当外层的事务回滚时才会进⾏回滚。

例：内部事务回滚SQLrver报错。begintrant1

Inrtintodemo2(name,age)values('lis',1)---SecondTrans

--Server:Msg6401,Level16,State1,Line6

---ransactiont2

inrtintodemovalues('BB','Bterm')

rollbacktransactiont2----Inthefirsttrans.

Inrtintodemo2(name,age)values('lis',2)committransactiont1

例：内部事务提交SQLrver不会报错。

begintrant1

Inrtintodemo2(name,age)values('lis',1)

---SecondTransnoerrorbegintransactiont2

inrtintodemovalues('BB','Bterm')

committransactiont2----Inthefirsttrans.

Inrtintodemo2(name,age)values('lis',2)

committransactiont1

SQLServer的隔离级别：

1:设置TimeOut参数

SetLock_TimeOut5000

被锁超时5秒将⾃动解锁SetLock_TimeOut0

产⽴即解锁，返回Error默认为-1，⽆限等待2：

(SETTRANSACTIONISOLATIONLEVEL

{READCOMMITTED|READUNCOMMITTED

|REPEATABLEREAD|SERIALIZABLE}）READCOMMITTED

指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从⽽产⽣不可重复读取或幻像数据。该选项是Scad圆弧怎么画 QLServer的默认值。

避免脏读，并在其他ssion在事务中不能对已有数据进⾏修改。共享锁。READUNCOMMITTED

执⾏脏读或0级隔离锁定，这表⽰不发出共享锁，也不接受排它锁。当设置该选项时，可以对数

据执⾏未提交读或脏读；在事务结束前可以更改数据内的数值，⾏也可以出现在数据集中或从数据

集消失。该选项的作⽤与在事务内所有语句中的所有表上设置NOLOCK相同。这是四个隔离级别中

限制最⼩的级别。REPEATABLEREAD

锁定查询中使⽤的所有数据以防⽌其他⽤户更新数据，但是其他⽤户可以将新的幻像⾏插⼊数据

集，且幻像⾏包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使

⽤该选项。SERIALIZABLE

在数据集上放置⼀个范围锁，以防⽌其他⽤户在事务完成之前更新数据集或将⾏插⼊数据集内。这

是四个隔离级别中限制最⼤的级别。因为并发级别较低，所以应只在必要时才使⽤该选项。该选项的作⽤与在事务内所有SELECT语句中的所有表上设置HOLDLOCK相同