目录

事务定义:

事务定义:

  • 1.事务
    • 1.1.事务的ACID属性
    • 1.2.事务分类
      • 1.2.1.系统提供的事务
      • 1.2.2.用户自定义的事务
    • 1.3.管理事务
      • 1.3.1.SAVE
        TRANSACTION
      • 1.3.2.@@TRANCOUNT变量和@@ERROR变量
    • 1.4.SQL
      Server本地事务支持

      • 1.4.1.自动提交事务模式
      • 1.4.2.显式事务模式
      • 1.4.3.隐式事务模式
      • 1.4.4.批范围的事务
    • 1.5.隔离级别
      • 1.5.1.四种隔离级别
      • 1.5.2.设置事务隔离级别
    • 1.6.分布式事务
    • 1.7.高级事务主题
    • 1.8.管理长时间运行的事务
      • 1.8.1.查看长时间运行的事务
      • 1.8.2.停止事务

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

事务是单个的工作单元。如果某一事务成功,则在该事务中进行的所有数据更改均会

事务是单个的工作单元。如果某一事务成功,则在该事务中进行的所有数据更改均会

1.事务

事务在SQL
Server中相当于一个工作单元,可以确保同时发生的行为与数据的有效性不发生冲突,并且维护数据的完整性。在实际应用中,多个用户在同一时刻对同一部分数据进行操作时,可能会由于一个用户的操作使其他用户的操作和数据失效。事务可以很好地解决这一点。事务总是确保数据库的完整性。

BEGIN TRANSACTION
 标记一个显式本地事务的起始点。
 
BEGIN TRANSACTION将 @@TRANCOUNT 加 1。
 
BEGIN TRANSACTION
代表一点,由连接引用的数据在该点是逻辑和物理上都一致的。如果遇上错误,在
BEGIN TRANSACTION
之后的所有数据改动都能进行回滚,以将数据返回到已知的一致状态
。每个事务继续执行直到它无误地完成并且用 COMMIT TRANSACTION
对数据库作永久的改动,或者遇上错误并且用 ROLLBACK TRANSACTION
语句擦除所有改动
 
语法
 BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable
[ WITH MARK [ ‘description’ ] ] ]
 
例子:
 BEGIN TRAN T1
 UPDATE table1 …
 –nest transaction M2
 BEGIN TRAN M2 WITH MARK
 UPDATE table2 …
 SELECT * from table1
 COMMIT TRAN M2
 UPDATE table3 …
 COMMIT TRAN T1
 
BEGIN DISTRIBUTED TRANSACTION
 指定一个由 Microsoft 分布式事务处理协调器 (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 或 nvarchar
数据类型声明该变量。
 
注释
 执行BEGIN DISTRIBUTED TRANSACTION
语句的服务器是事务创建人,并且控制事务的完成
 
当连接发出后续 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句时,
 主控服务器请求 MS DTC 在所涉及的服务器间管理分布式事务的完成。
 有两个方法可将远程 SQL 服务器登记在一个分布式事务中:

提交,成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚,则所有

提交,成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚,则所有

1.1.事务的ACID属性

  • 原子性(Atomicity):事务是工作单元。事务内的所有工作要不全部完成,要不全部没完成,不存在完成一部分的说法。
  • 一致性(Consistency):事务完成时,所有的数据都必须是一致的。事务结束时,所有内部数据结构都必须是正确的。
  • 隔离性(Isolation):由并发事务所做的修改必须与其他并发事务所做的修改隔离。事务识别数据时数据所处的状态,要不是另一并发事务修改前的状态,要不是另一并发事务修改后的状态,不存在中间状态。
  • 持久性(Durability):事务提交后,事务所完成的工作结果会得到永久保存。

示例1:情况如下2个代码

--语句1:
UPDATE student
SET stu_birthday='1993-02-01',
stu_native_place='山西',
stu_phone='15729810290'
WHERE stu_no='20180101'
--语句2:
UPDATE student
SET stu_birthday='1993-02-01'
WHERE stu_no='20180101'
UPDATE student
SET stu_native_place='山西'
WHERE stu_no='20180101'
UPDATE student
SET stu_phone='15729810290'
WHERE stu_no='20180101'

在语句1中,只有一个事务,对列的更新要不全部成功更新,要不全部更新失败。而语句2中,有三个事务,就算其中有某个列更新失败,也不会影响其他列的更新。

分布式事务中已登记的连接执行一个远程存储过程调用,该调用引用一个远程服务器。
分布式事务中已登记的连接执行一个分布式查询,该查询引用一个远程服务器。

数据更改均被清除。

数据更改均被清除。

1.2.事务分类

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

事务三种运行模式:
自动提交事务
每条单独的语句都是一个事务。
显式事务
每个事务均以 BEGIN TRANSACTION 语句显式开始,
以 COMMIT 或 ROLLBACK 语句显式结束。
隐性事务
在前一个事务完成时新事务隐式启动,但每个事务仍以 COMMIT 或 ROLLBACK 语句

事务三种运行模式:
自动提交事务
每条单独的语句都是一个事务。
显式事务
每个事务均以 BEGIN TRANSACTION 语句显式开始,
以 COMMIT 或 ROLLBACK 语句显式结束。
隐性事务
在前一个事务完成时新事务隐式启动,但每个事务仍以 COMMIT 或 ROLLBACK 语句

1.2.1.系统提供的事务

系统提供的事务是指执行某些T-SQL语句时,一条语句段构成了一个事务,如ALTER
TABLE,CREATE,DELETE,DROP,FETCH等。

说明
当前的SQL Server 上必须安装 MS DTC.

显式完成。

显式完成。

1.2.2.用户自定义的事务

实际应用中,经常使用用户自定义的事务。自定义的方法是,以BEGIN
TRANSACTION开始,以COMMIT TRANSACTION或ROLLBACK
TRANSACTION结束。这两个语句之间所有语句都被视为一体。
示例2:自定义事务的应用

BEGIN TRANSACTION
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180013','贾乃亮','1993-01-20','498')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180014','周星星','1993-07-20','532')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180015','雨化田','错误格式数据','570')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180016','周琪','1993-01-20','653')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180017','陈璐','1998-01-20','599')
COMMIT TRANSACTION

在上面的事务中,第三条插入数据是错误数据,无法成功插入,执行上面的语句,发现所有插入语句都没有被执行成功。
还有一种用户自定义事务——分布式事务。如果在比较复杂的环境中,有多台服务器,为了保证服务器中数据的完整性和一致性,就必须定义一个分布式事务。举个例子,有2台服务器,一台存放库存数据,另一台存放订单数据,用户下单的逻辑是,下单前先扣除库存数据,再下单。如果没有分布式事务,容易出现扣除库存数量,单下单却没成功,造成两个数据库数据不一致的情况。

USE pubs
 GO
 BEGIN DISTRIBUTED TRANSACTION
 UPDATE authors
 SET au_lname = ‘McDonald’ WHERE au_id = ‘409-56-7008’
 EXECUTE link_Server_T.pubs.dbo.changeauth_lname
‘409-56-7008′,’McDonald’
 COMMIT TRAN
 GONote:
 如果需要连接远程DB,如果是linkServer
方式连接的话,一定要修该linkServer的 RPC 选项置为 True。
 
SET XACT_ABORT
 指定当 Transact-SQL 语句产生运行时错误时,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-SQL
语句产生运行时错误,整个事务将终止并回滚。为 OFF
时,只回滚产生错误的Transact-SQL
语句,而事务将继续进行处理。编译错误(如语法错误)不受 SET XACT_ABORT
的影响。
 
对于大多数 OLE DB 提供程序(包括 SQL
Server),隐性或显式事务中的数据修改语句必须将 XACT_ABORT 设置为 ON。
 
SET XACT_ABORT 的设置是在执行或运行时设置,而不是在分析时设置。
 
示例 下例导致在含有其它 Transact-SQL
语句的事务中发生违反外键错误。在第一个语句集中产生错误,但其它语句均成功执行且事务成功
 提交。在第二个语句集中,SET XACT_ABORT 设置为
ON。这导致语句错误使批处理终止,并使事务回滚。

事务操作的语法:

事务操作的语法:

1.3.管理事务

主要使用以下4条语句管理事务:BEGIN TRANSACTION,COMMIT
TRANSACTION,ROLLBACK TRANSACTION和SAVE
TRANSACTION。此外还有2个全局变量可以用在事务处理语句中:@@ERROR和@@TRANCOUNT。
BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK TRANSACTION不多说了。

CREATE TABLE table1 (a int PRIMARY KEY)
 CREATE TABLE table2 (a int REFERENCES t1(a))
 GO
 INSERT INTO table1 VALUES (1)
 INSERT INTO table1 VALUES (3)
 INSERT INTO table1 VALUES (4)
 INSERT INTO table1 VALUES (6)
 GO
 SET XACT_ABORT OFF
 GO
 BEGIN TRAN
 INSERT INTO table2 VALUES (1)
 INSERT INTO table2 VALUES (2) /* Foreign key error */
 INSERT INTO table2 VALUES (3)
 COMMIT TRAN
 GO
 
SET XACT_ABORT ON
 GO
 
BEGIN TRAN
 INSERT INTO table2 VALUES (4)
 INSERT INTO table2 VALUES (5) /* Foreign key error */
 INSERT INTO table2 VALUES (6)
 COMMIT TRAN
 GO
 
SAVE TRANSACTION
 在事务内设置保存点。
 
语法 SAVE TRAN [ SACTION ] { savepoint_name | @savepoint_variable
}
 参数 savepoint_name
 是指派给保存点的名称。保存点名称必须符合标识符规则,但只使用前 32
个字符。
 @savepoint_variable
 是用户定义的、含有有效保存点名称的变量的名称。
 必须用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。 注释
 用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分,事
务可以返回的位置。如果将事务回滚到保存点,则必须(如果需要,使用更多的
Transact-SQL 语句和 COMMIT TRANSACTION
语句)继续完成事务,或者必须(通过将事务回滚到其起始点)完全取消事务。若要取消整个事务,请使用
ROLLBACK TRANSACTION transaction_name 格式。这将撤消事务的所
有语句和过程。
 
Note:1: 在由 BEGIN DISTRIBUTED TRANSACTION
显式启动或从本地事务升级而来的分布式事务中,不支持 SAVE TRANSACTION。
 
2:当事务开始时,将一直控制事务中所使用的资源直到事务完成(也就是锁定)。当将事务的一部分回滚到保存点时,将继续控制资源直到事务完成(或者回滚全部事务)。
 
例子:begin transaction
 save transaction A
 
create table demo1(name varchar(20),Sno varchar(12))
 insert into demo1 values(‘testName1′,’029303290320’)
 rollback TRANSACTION A
 create table demo2(name varchar(10),age int)
 insert into demo2(name,age) values(‘ok’,1)
 commit transaction
 
ROLLBACK TRANSACTION
 
将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。
 语法
 ROLLBACK [ TRAN [ SACTION ]
 [ transaction_name | @tran_name_variable | savepoint_name |
@savepoint_variable ] ]
 
参数
 transaction_name
 是给 BEGIN TRANSACTION 上的事务指派的名称。transaction_name
必须符合标识符规则,但只使用事务名称的前 32 个字符。嵌套
 事务时,transaction_name 必须是来自最远的 BEGIN TRANSACTION
语句的名称。
 @tran_name_variable
 是用户定义的、含有有效事务名称的变量的名称。必须用 char、varchar、nchar
或 nvarchar 数据类型声明该变量。
 savepoint_name
 是来自 SAVE TRANSACTION 语句的 savepoint_name。savepoint_name
必须符合标识符规则。当条件回滚只影响事务的一部分时使 用
savepoint_name。
 @savepoint_variable
 是用户定义的、含有有效保存点名称的变量的名称。必须用
char、varchar、nchar 或 nvarchar 数据类型声明该变量。
 
注释 ROLLBACK TRANSACTION
清除自事务的起点或到某个保存点所做的所有数据修改。ROLLBACK
还释放由事务控制的资源。
 不带 savepoint_name 和 transaction_name 的 ROLLBACK TRANSACTION
回滚到事务的起点。嵌套事务时,该语句将所有内层事务回滚到 最远的 BEGIN
TRANSACTION 语句。在这两种情况下,ROLLBACK TRANSACTION 均将 @@TRANCOUNT
系统函数减为 0。ROLLBACK
TRANSACTION savepoint_name 不减少 @@TRANCOUNT。
 
Note:
ROLLBACK TRANSACTION 语句若指定 savepoint_name 则不释放任何锁。
 在由 BEGIN DISTRIBUTED TRANSACTION
显式启动或从本地事务升级而来的分布式事务中,ROLLBACK TRANSACTION 不能
 引用savepoint_name。在执行 COMMIT TRANSACTION 语句后不能回滚事务。
 
在事务内允许有重复的保存点名称,但 ROLLBACK TRANSACTION
若使用重复的保存点名称,则只回滚到最近的使用该保存点名称的SAVE
TRANSACTION。
 
在存储过程中,不带 savepoint_name 和 transaction_name 的 ROLLBACK
TRANSACTION 语句将所有语句回滚到最远的
BEGINTRANSACTION。在存储过程中,ROLLBACK TRANSACTION 语句使 @@TRANCOUNT
在触发器完成时的值不同于调用该存储过程时的@@TRANCOUNT
值,并且生成一个信息。该信息不影响后面的处理。
 
如果在触发器中发出 ROLLBACK
TRANSACTION:将回滚对当前事务中的那一点所做的所有数据修改,包括触发器所做的修改。
触发器继续执行 ROLLBACK
语句之后的所有其余语句。如果这些语句中的任意语句修改数据,则不回滚这些修改。执行其余的语句不会激发嵌套触发器。在批处理中,不执行所有位于激发触发器的语句之后的语句。每次进入触发器,@@TRANCOUNT
就增加
1,即使在自动提交模式下也是如此。(系统将触发器视作隐性嵌套事务。)
 
在存储过程中,ROLLBACK TRANSACTION
语句不影响调用该过程的批处理中的后续语句;
 将执行批处理中的后续语句。在触发器中,ROLLBACK TRANSACTION
语句终止含有激发触发器的语句的批处理;
 不执行批处理中的后续语句。
 
ROLLBACK TRANSACTION
语句不生成显示给用户的信息。如果在存储过程或触发器中需要警告,请使用
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 语句也
自动生成这类错误。

BEGIN TRANSACTION
BEGIN DISTRIBUTED TRANSACTION
COMMIT TRANSACTION
COMMIT WORK
ROLLBACK WORK
SAVE TRANSACTION
BEGIN TRANSACTION

BEGIN TRANSACTION
BEGIN DISTRIBUTED TRANSACTION
COMMIT TRANSACTION
COMMIT WORK
ROLLBACK WORK
SAVE TRANSACTION
BEGIN TRANSACTION

1.3.1.SAVE TRANSACTION

允许部分地提交一个事务,同时仍能回退这个事务的剩余部分。
示例3:BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK
TRANSACTION和SAVE TRANSACTION的结合使用
执行下列语句

BEGIN TRANSACTION changed
INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
VALUES('20180014','谭晶','男','533')
SAVE TRANSACTION saveinsert--设置保存事务点saveinsert
UPDATE student
SET stu_sex='错误数据'
WHERE stu_no='20180014'
ROLLBACK TRANSACTION saveinsert--回滚到保存事务点saveinsert
COMMIT TRANSACTION changed

上述代码完成了一个这样的功能:设置一个事务,事务名changed,该事务的作用是向student表中插入一条记录并更新该记录的stu_sex字段。如果更新失败,则回滚到插入操作,即保证不管更新是否成功,插入操作都能成功。

权限
 ROLLBACK TRANSACTION 权限默认授予任何有效用户。
 例子:
 
begin transaction
 save transaction A
 insert into demo1 values(‘testName2′,’029303290820’)
 rollback TRANSACTION A
 
— select * into demo2 from demo1
 
create table demo2(name varchar(10),age int)
 insert into demo2(name,age) values(‘ok’,1)
 rollback transaction
 
USE pubs
 GO
 DECLARE @del_error int, @ins_error int
 — Start a transaction.
 BEGIN TRAN
 
— Execute the DELETE statement.
 DELETE authors
 WHERE au_id = ‘409-56-7088’
 
— Set a variable to the error value for
— the DELETE statement.
 SELECT @del_error = @@ERROR
 
— Execute the INSERT statement.
 INSERT authors
    VALUES(‘409-56-7008’, ‘Bennet’, ‘Abraham’, ‘415 658-9932’,
    ‘6223 Bateman St.’, ‘Berkeley’, ‘CA’, ‘94705’, 1)
 — Set a variable to the error value for
— the INSERT statement.
 SELECT @ins_error = @@ERROR
 
— Test the error values.
 IF @del_error = 0 AND @ins_error = 0
 BEGIN
    — Success. Commit the transaction.
    PRINT “The author information has been replaced”    
   COMMIT TRAN
 END
 ELSE
 BEGIN
    — An error occurred. Indicate which operation(s) failed
    — and roll back the transaction.
    IF @del_error <> 0
      PRINT “An error occurred during execution of the DELETE
      statement.”

BEGIN TRANSACTION
标记一个显式本地事务的起始点。

BEGIN TRANSACTION
标记一个显式本地事务的起始点。

1.3.2.@@TRANCOUNT变量和@@ERROR变量

@@TRANCOUNT变量报告当前嵌套事务为第几层嵌套,每个BEGIN
TRANSACTION都能使@@TRANCOUNT加一,@@ERROR变量用来保存任何一条T-SQL语句的最新错误号。
示例4:对示例3中代码加上对@@TRANCOUNT和@@ERROR变量的访问
执行下列语句

BEGIN TRANSACTION changed
SELECT @@TRANCOUNT AS trancount
INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
VALUES('20180016','陈甜甜','女','661')
SAVE TRANSACTION saveinsert--设置保存事务点saveinsert
UPDATE student
SET stu_sex='错误数据'
WHERE stu_no='20180016'
SELECT @@ERROR AS error
ROLLBACK TRANSACTION saveinsert--回滚到保存事务点saveinsert
COMMIT TRANSACTION changed
GO

结果如图所示
图片 1
示例5:对@@TRANCOUNT变量的理解
执行下列语句

BEGIN TRANSACTION changed1
SELECT @@TRANCOUNT AS trancount
INSERT INTO class(class_id,class_name,enter_score_level)
VALUES('07','TEST','TEST')
BEGIN TRANSACTION changed2
INSERT INTO class(class_id,class_name,enter_score_level)
VALUES('08','TEST','TEST')
BEGIN TRANSACTION changed3
SELECT @@TRANCOUNT AS trancount
INSERT INTO class(class_id,class_name,enter_score_level)
VALUES('09','TEST','TEST')
COMMIT TRANSACTION changed3
COMMIT TRANSACTION changed2
COMMIT TRANSACTION changed1

我在changed1和changed3中对@@TRANCOUNT变量进行了访问,结果如图所示
图片 2
每个BEGIN TRANSACTION都使@@TRANCOUNT加一。

   IF @ins_error <> 0
       PRINT “An error occurred during execution of the INSERT
      statement.”

BEGIN TRANSACTION将 @@TRANCOUNT 加 1。

BEGIN TRANSACTION将 @@TRANCOUNT 加 1。

1.4.SQL Server本地事务支持

应用程序主要通过设置事务开始时间和事务结束时间来管理事务。这可以通过函数或者应用程序接口(API)实现。默认情况下,事务按连接级别进行处理,使用API函数或者SQL语句,可以将事务作为显式,隐式和自动提交事务来处理。

   ROLLBACK TRAN
 END
 GO
 
COMMIT TRANSACTION
 标志一个成功的隐性事务或用户定义事务的结束。如果 @@TRANCOUNT 为
1,COMMIT

BEGIN
TRANSACTION 代表一点,由连接引用的数据在该点是逻辑和物理上都一致的。如果遇上错误,在 BEGIN
TRANSACTION 之后的所有数据改动都能进行回滚,以将数据返回到已知的一致状态 。每个事务继续执行直到它无误地完成并且用 COMMIT
TRANSACTION 对数据库作永久的改动,或者遇上错误并且用 ROLLBACK
TRANSACTION 语句擦除所有改动

BEGIN TRANSACTION
代表一点,由连接引用的数据在该点是逻辑和物理上都一致的。如果遇上错误,在
BEGIN TRANSACTION
之后的所有数据改动都能进行回滚,以将数据返回到已知的一致状态
。每个事务继续执行直到它无误地完成并且用 COMMIT TRANSACTION
对数据库作永久的改动,或者遇上错误并且用 ROLLBACK TRANSACTION
语句擦除所有改动

1.4.1.自动提交事务模式

自动提交事务模式是SQL
Server默认的事务管理模式,每个SQL语句都是一个事务,在完成时都会被提交或回滚。在自动提交事务模式下,当遇到的错误是编译时错误,会回滚整个批处理,当遇到的错误是运行时错误,不会回滚整个批处理,而是执行部分语句并提交。
示例6:遇到编译时错误和运行时错误时,事务处理方式是不同的
执行下列语句

--编译时错误代码
USE test
GO
CREATE TABLE T1(
id INT NOT NULL,
name VARCHAR(20),
age INT,
CONSTRAINT pk_id PRIMARY KEY(id)
)
GO
INSERT INTO T1(id,name,age)VALUES
('1001','宋佳佳','26')
INSERT INTO T1(id,name,age)VALUES
('1002','陈琦','23')
INSERT INTO T1(id,name,age)VALUE
('1003','卢哲','27')--语法错误,回滚整个批处理
GO
SELECT * FROM T1

结果可以看到,T1表虽然被创建了,但是三条数据都没有插入成功。可见编译时错误会回滚整个批处理。
删除T1表后执行下列语句

--运行时错误代码
USE test
GO
CREATE TABLE T1(
id INT NOT NULL,
name VARCHAR(20),
age INT,
CONSTRAINT pk_id PRIMARY KEY(id)
)
GO
INSERT INTO T1(id,name,age)VALUES
('1001','宋佳佳','26')
INSERT INTO T1(id,name,age)VALUES
('1002','陈琦','23')
INSERT INTO T1(id,name,age)VALUES
('1001','卢哲','27')--主键重复错误,仅该语句不执行
GO
SELECT * FROM T1

结果如图所示
图片 3
仅错误的INSERT语句不执行,而整个批处理并没有回滚。可见运行时错误不会导致整个批处理被回滚,仅仅只是中断执行。

TRANSACTION 使得自从事务开始以来所执行的
所有数据修改成为数据库的永久部分,释放连接
 
占用的资源,并将 @@TRANCOUNT 减少到 0。如果@@TRANCOUNT 大于
1,则COMMIT

语法
BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable [
WITH MARK [ ‘description’ ] ] ]

语法
BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable [
WITH MARK [ ‘description’ ] ] ]

1.4.2.显式事务模式

有明显使用BEGIN
TRANSACTION语句定义一个事务的就是显式事务模式。示例2,3,4,5都是显式事务模式。

TRANSACTION 使 @@TRANCOUNT 按 1 递减。
 
只有当事务所引用的所有数据的逻辑都正确时,发出 COMMIT TRANSACTION
命令。
 COMMIT WORK
 标志事务的结束。
 语法
 COMMIT [ WORK ]
 
注释
 此语句的功能与 COMMIT TRANSACTION 相同,但 COMMIT TRANSACTION
接受用户定义的事务
 
名称。这个指定或没有指定可选关键字WORK 的 COMMIT 语法与 SQL-92 兼容
 
例子:
 begin transaction a
 insert into demo1 values(‘testName3′,’029303290320’)
 commit TRANSACTION A
 
隐性事务
 当连接以隐性事务模式进行操作时,SQL
Server将在提交或回滚当前事务后自动启动新事务。无须描述事务的开始,只需提交或
 
回滚每个事务。隐性事务模式生成连续的事务链。
 
在为连接将隐性事务模式设置为打开之后,当 SQL Server
首次执行下列任何语句时,都会自动启动一个事务:
 

例子:
BEGIN TRAN T1
UPDATE table1 …
–nest transaction M2
BEGIN TRAN M2 WITH MARK
UPDATE table2 …
SELECT * from table1
COMMIT TRAN M2
UPDATE table3 …
COMMIT TRAN T1

例子:
BEGIN TRAN T1
UPDATE table1 …
–nest transaction M2
BEGIN TRAN M2 WITH MARK
UPDATE table2 …
SELECT * from table1
COMMIT TRAN M2
UPDATE table3 …
COMMIT TRAN T1

1.4.3.隐式事务模式

隐式事务模式是一种连接选项,在该选项下每个连接执行的SQL语句都被视为单独的事务。当连接以隐式事务模式进行操作时,SQL
Server将在事务提交或事务回滚后自动开始新事务。隐式事务模式无需BEGIN
TRANSACTION这种语句来进行定义。

 
 

BEGIN DISTRIBUTED TRANSACTION
指定一个由 Microsoft 分布式事务处理协调器 (MS
DTC) 管理的 Transact-SQL 分布式事务的起始。

BEGIN DISTRIBUTED TRANSACTION
指定一个由 Microsoft 分布式事务处理协调器 (MS DTC) 管理的 Transact-SQL
分布式事务的起始。

1.4.3.1.通过SET IMPLICIT_TRANSACTIONS ON语句设置隐式事务模式

显式事务模式模式会在有大量DDL和DML语句执行时自动开始,并一直保持到用户明确提交为止。也就是说,如果设置了隐式事务模式,而SQL语句中又有事务没有明确提交,即使用COMMIT
TRANSACTION语句提交,那么用户断开连接,或者关闭数据库时,系统会询问有未提交的事务,是否提交,如果选择否,那么未提交的事务将会被回滚,下次连接时就不存在了。
示例7:执行下列语句

SET IMPLICIT_TRANSACTIONS ON
GO

USE test
CREATE TABLE T1(
id INT NOT NULL,
name VARCHAR(20),
age INT,
CONSTRAINT pk_id PRIMARY KEY(id)
)
INSERT INTO T1(id,name,age)VALUES
('1001','宋佳佳','26')
COMMIT TRANSACTION
INSERT INTO T1(id,name,age)VALUES
('1002','陈琦','23')
INSERT INTO T1(id,name,age)VALUES
('1003','卢哲','27')
SELECT * FROM T1

结果如图所示
图片 4
然后断开连接,出现如下提示
图片 5
如果选择否的话,再次连接成功后SELECT T1表,结果如图所示
图片 6
会发现1002和1003的记录都被回滚了,那是因为在插入的时候,这两条语句的事务没有COMMIT,只有第一条插入语句被提交了。这就是隐式事务模式。

ALTER TABLE
 

语法
BEGIN DISTRIBUTED TRAN [ SACTION ]
[ transaction_name | @tran_name_variable ]

语法
BEGIN DISTRIBUTED TRAN [ SACTION ]
[ transaction_name | @tran_name_variable ]

1.4.3.2.调用API函数来设置隐式事务模式

用来设置隐式事务模式的API机制是ODBC和OLE DB(不能理解,不多说了)

INSERT
 

参数
transaction_name
是用户定义的事务名,用于跟踪 MS
DTC 实用工具中的分布式事务。 transaction_name 必须符合标识符规则,但是仅使用头 32 个字符

参数
transaction_name
是用户定义的事务名,用于跟踪 MS DTC 实用工具中的分布式事务。
transaction_name 必须符合标识符规则,但是仅使用头 32 个字符

1.4.4.批范围的事务

该事务只适用于多个活动的结果集。在MARS会话中启动的SQL显式或隐式事务,将变成批范围事务,当批处理完成时,如果批范围事务还没有被提交或回滚,SQL
Server将自动对其进行回滚。

 
 

@tran_name_variable
是用户定义的一个变量名,它含有一个事务名,该事务名用于跟踪 MS
DTC 实用工具中的分布式事务。必须用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。

@tran_name_variable
是用户定义的一个变量名,它含有一个事务名,该事务名用于跟踪 MS DTC
实用工具中的分布式事务。必须用 char、varchar、nchar 或 nvarchar
数据类型声明该变量。

1.5.隔离级别

当多个线程都开启事务来操作数据库中的数据时,数据库要能进行隔离操作,以确保各个线程获取数据的准确性。如果没有隔离操作,会出现以下几种情况:

  • 脏读:一个事务处理过程里读取了另一个未提交的事务中的数据。

例如:A转100块钱给B,SQL语句如下

UPDATE acount
SET cash=cash+100
WHERE name='B'--此时A通知B
UPDATE acount
SET cash=cash-100
WHERE name='A'

执行完第一条语句时,A通知B,让B确认是否到账,B确认钱到账(此时发生了脏读),而后无论第二条SQL语句是否执行,只要事务没有提交,所有操作都将回滚,B第二次查看时发现钱没有到账。

  • 不可重复读:一个事务范围内多次查询某个数据,返回不同的值,这是因为该数据被另一个事务修改并提交了。脏读和不可重复读的区别在于,脏读是读取了另一个事务还未提交的数据,不可重复都是读取了反复读取了前一个事务提交了的数据
  • 幻读:比如事务T1将表中某一列数据从1修改成2,同时T2事务插入一条数据,该列值仍然是1,那么用户查询时就会发现该表还有1列数据为1,未被T1事务修改。

CREATE
 

注释
执行BEGIN DISTRIBUTED
TRANSACTION 语句的服务器是事务创建人,并且控制事务的完成

注释
执行BEGIN DISTRIBUTED TRANSACTION
语句的服务器是事务创建人,并且控制事务的完成

1.5.1.四种隔离级别

  • 未提交读(READ
    UNCOMMITTED):事务隔离的最低级别,可执行未提交读和脏读,任何情况都无法保证
  • 提交读(READ
    COMMITTED):在读取数据时控制共享锁,避免脏读,但无法避免不可重复读和幻读。它是SQL
    Server 2008的默认值。
  • 可重复读(REPEATABLE
    READ):锁定查询过程中所有数据,防止用户更新数据,避免了脏读和不可重复读的发生,无法避免幻读。
  • 可串行读(SERIALZABLE):在数据集上放置一个范围锁,防止其他用户在事务完成之前更新数据或插入行,是事务隔离的最大限制级别,避免了脏读,不可重复读和幻读的发生。

事务隔离级别越高,越能保证数据的一致性和完整性。

OPEN
 

当连接发出后续 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句时,
主控服务器请求 MS DTC 在所涉及的服务器间管理分布式事务的完成。
有两个方法可将远程 SQL 服务器登记在一个分布式事务中:

当连接发出后续 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句时,
主控服务器请求 MS DTC 在所涉及的服务器间管理分布式事务的完成。
有两个方法可将远程 SQL 服务器登记在一个分布式事务中:

1.5.2.设置事务隔离级别

默认情况下,SQL Server 2008的事务隔离级别为提交读。可通过SET TRANSACTION
ISOLATION LEVEL来设置事务隔离级别。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 
 

分布式事务中已登记的连接执行一个远程存储过程调用,该调用引用一个远程服务器。 
分布式事务中已登记的连接执行一个分布式查询,该查询引用一个远程服务器。

分布式事务中已登记的连接执行一个远程存储过程调用,该调用引用一个远程服务器。
分布式事务中已登记的连接执行一个分布式查询,该查询引用一个远程服务器。

1.6.分布式事务

对多个数据库中的数据进行修改的事务,是分布式事务。这些数据库可以是本地数据库,也可以是其他链接服务器上的数据库。
分布式事务由一个分布式事务协调程序(DTC)来控制,若想使用分布式事务,必须先启动该服务。在分布式事务中用COMMIT
TRANSACTION提交事务,数据库会自动调用一个两步提交协议:1.通知每个数据库核实它们能够提交该事务并保留资源。2.当每个相关数据库通知SQL
Server 2008可以随时提交该事务后,SQL Server
2008通知相关数据库提交该事务。如果有一个数据库不能成功提交该事务,则SQL
Server 2008会通知所有相关数据库回滚该事务。

DELETE
 

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

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

1.7.高级事务主题

  • 嵌套事务:显式事务可以嵌套在存储过程中
  • 事务保存点:提供了一种可以部分回滚事务的机制
  • 绑定会话:有利于在一个服务器上的多个会话之间的协调操作,允许一个或多个会话共享事务和锁,并且可以使用同一个数据,不会有锁的冲突

REVOKE
 

说明 
当前的SQL Server 上必须安装 MS DTC.

说明
当前的SQL Server 上必须安装 MS DTC.

1.8.管理长时间运行的事务

 
 

USE pubs
GO
BEGIN DISTRIBUTED TRANSACTION
UPDATE authors
SET au_lname = ‘McDonald’ WHERE au_id = ‘409-56-7008’
EXECUTE link_Server_T.pubs.dbo.changeauth_lname
‘409-56-7008′,’McDonald’
COMMIT TRAN
GONote:
如果需要连接远程DB,如果是linkServer 方式连接的话,一定要修该linkServer的 RPC 选项置为 True。

USE pubs
GO
BEGIN DISTRIBUTED TRANSACTION
UPDATE authors
SET au_lname = ‘McDonald’ WHERE au_id = ‘409-56-7008’
EXECUTE link_Server_T.pubs.dbo.changeauth_lname
‘409-56-7008′,’McDonald’
COMMIT TRAN
GONote:
如果需要连接远程DB,如果是linkServer 方式连接的话,一定要修该linkServer的
RPC 选项置为 True。

1.8.1.查看长时间运行的事务

执行下列语句

SELECT * FROM sys.dm_tran_database_transactions

结果如图所示
图片 7

DROP
 

SET XACT_ABORT
指定当 Transact-SQL 语句产生运行时错误时,Microsoft? SQL
Server? 是否自动回滚当前事务。

SET XACT_ABORT
指定当 Transact-SQL 语句产生运行时错误时,Microsoft? SQL Server?
是否自动回滚当前事务。

1.8.2.停止事务

停止事务可能必须运行KILL语句,使用该语句时要小心,特别是在运行重要的进程时。

SELECT
 

( 可以比较简单的理解,如果中间有任何一句SQL 出错,所有SQL全部回滚.特别适用于 Procedure 中间调用Procedure ,如果第一个Procedure
Ok,被调用的Procedure 中间有错误,如果SET
XACT_ABORT=false,则出错的部分回滚,其他部分提交,当然外部Procedure 也提交。).

( 可以比较简单的理解,如果中间有任何一句SQL
出错,所有SQL全部回滚.特别适用于 Procedure 中间调用Procedure
,如果第一个Procedure Ok,被调用的Procedure 中间有错误,如果SET
XACT_ABORT=false,则出错的部分回滚,其他部分提交,当然外部Procedure
也提交。).

 
 

—在分布式Trans中一定要注意设置下面参数(XACT_ABORT)

—在分布式Trans中一定要注意设置下面参数(XACT_ABORT)

FETCH
 

语法SET XACT_ABORT { ON | OFF }

语法SET XACT_ABORT { ON | OFF }

TRUNCATE TABLE
 

注释 当 SET
XACT_ABORT 为 ON 时,如果 Transact-SQL 语句产生运行时错误,整个事务将终止并回滚。为 OFF 时,只回滚产生错误的Transact-SQL语句,而事务将继续进行处理。编译错误(如语法错误)不受 SET
XACT_ABORT 的影响。

注释 当 SET XACT_ABORT 为 ON 时,如果 Transact-SQL
语句产生运行时错误,整个事务将终止并回滚。为 OFF
时,只回滚产生错误的Transact-SQL
语句,而事务将继续进行处理。编译错误(如语法错误)不受 SET XACT_ABORT
的影响。

 
 

对于大多数 OLE DB 提供程序(包括 SQL
Server),隐性或显式事务中的数据修改语句必须将 XACT_ABORT 设置为 ON。

对于大多数 OLE DB 提供程序(包括 SQL
Server),隐性或显式事务中的数据修改语句必须将 XACT_ABORT 设置为 ON。

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注