（4.19）sql server中的事务模式（隐式事务，显式事务，自动提交事务）

1.概念：隐式事务，显式事务，自动提交事务

2.操作：如何设置事务模式

3.存储过程中的事务 XACT_ABORT

1.概念

　　【1】隐式事务：即每个窗口系统会自动加上begin tran，但是不会自动提交，需要手动使用 commit 提交。

　　【2】显示事务：需要begin tran commit tran/ rollback tran 配合使用

　　【3】自动提交事务：每一个DML、DDL都是独立的事务，并且一旦执行就自动提交/错误也会自动回滚。

事务的基本控制语句

BEGIN TRANSACTION 表示本地事务的开始
BEGIN DISTRIBUTED TRANSACTION表示分布式事务起始
COMMIT TRANSACTION表示事务的提交
ROLLBACK TRANSACTION表示事务的回滚
COMMIT事务的提交与COMMIT TRANSACTION功能相同
ROLLBACK 事务的回滚与ROLLBACK TRANSACTION功能相同
SAVE TRANSACTION设置事务保存点

2.操作

　　【1】隐式事务

　　　　SET IMPLICIT_TRANSACTIONS ON 语句启动隐性事务模式
　　　　SET IMPLICIT_TRANSACTIONS OFF 语句关闭隐性事务模式

　　【2】显式事务

　　　　通过发出 BEGIN TRANSACTION 语句显式启动事务。

　　【3】自动提交事务，默认为此项，无需设置；

　　　　只要隐式事务和显式事务没有开启，就是自动提交事务。

　　SSMS操作，右击实例-》属性-》连接

3.存储过程中的事务 XACT_ABORT

　　　　　　SET XACT_ABORT ON是设置事务回滚的！默认为OFF

　　　　　　当为ON时，如果你存储中的某个地方出了问题，整个事务中的语句都会回滚；且不会再执行该事务内的所有语句了；
　　　　　　为OFF时，只回滚错误的地方。事务内剩余的语句都会执行；

　　　　　　举例：一个过程里有10行dml，执行到第5行出错。

　　　　　　　　【1】如果为on，则直接回滚整个事务，并且报错

　　　　　　　　【2】如果为off，则只会回滚出错的第5行，其余1-4,6-10可以正常执行并且提交保存；

【最佳实践】事务的概念

从提交方式：自动提交事务、手动提交事务

从开启方式：显式事务、隐式事务

其他：批范围事务、分布式事务

（1）显式事务

通过API函数或者发布T-SQL begin transaction、commit transaction、commit work、rollback transaction、rollback work 、save transaction等明确定义事务的开始和结束。

这里简要说明commit、 save transaction 、rollback 、 xact_abort。

COMMIT

commit，提交最近一次未提交事务，这里注意，commit transaction = commit work = commit tran [name]。每次commit，都需要把当前的@@trancount减去1。

 1 begin tran yu1
 2 select @@TRANCOUNT
 3        begin tran yu2
 4        insert into tbxin(name,age) select \'第2层tran\',100;
 5        select @@TRANCOUNT
 6              
 7                   begin tran yu3
 8                      insert into tbxin(name,age) select \'第3层tran\',100;
 9                      select @@TRANCOUNT
10                      commit tran --等同于 commit tran anyname
11                      select @@TRANCOUNT
12                      commit tran --等同于 commit tran anyname
13                      select @@TRANCOUNT
14                      commit tran --等同于 commit tran anyname
15                      select @@TRANCOUNT

ROLLBACK

rollback，回滚事务，有2中语法：

第一个，回滚当前所有未结束事务

rollback = rollback tran = rollback transaction = rollback work
无论嵌套了多少事务，@@trancount为多少，执行 rollback则直接回滚所有嵌套事务，设置@@trancount为0
常见错误案例：EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 1，当前计数 = 0。

第二个，回滚到某个保存点的位置

rollback tran savepoint_name
不影响@@trancount计算，回滚到某个 save tran savepoint_name的位置

错误案例:

 1 CREATE PROC p_count
 2 AS
 3 begin transaction
 4 insert into tbxin(name,age) select \'第一层tran\',200;
 5 rollback transaction
 6 GO
 7 
 8 BEGIN TRAN
 9 EXEC p_count
10 select @@TRANCOUNT
11 
12 消息 266，级别 16，状态 2，过程 p_count，第 0 行
13 EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 1，当前计数 = 0。

SAVE TRANSACTION

save transaction，定义在按条件取消某个事务的一部分后，该事务可以返回的一个位置。如果将事务回滚到保存点，则根据需要必须完成其他剩余的 Transact-SQL 语句和 COMMIT TRANSACTION 语句，或者必须通过将事务回滚到起始点完全取消事务。若要取消整个事务，使用窗体 ROLLBACK TRANSACTION transaction_name。这将撤消事务的所有语句和过程。

save transaction [savepoint_name] 提供用户在事务内设置保存点或标记，所以 save transaction 只能在事务内部执行；
save transaction [savepoint_nmae] 不影响 @@trancount 计数；
注意 save transaction [savepoint_name] 只有对应 rollback transaction [savepoint_name]，并没有对应 commit transaction [savepoint_name]，强调下：对应的rollback transaction [savepoint_name] 是有带保存点名字的，如果没有带名字，则会回滚整个最外部的事务；
save transaction 对应的 rollback transaction [savepoint_name]并不需要一一对应，可以多个save tran 对应0到多个rollback tran；
事务内，可以有多个 save transaction [savepoint_name]，可使用 rollback transaction [savepoint_name] 回滚到任意一个保存点；
支持savepoint_name重复命名，但是不建议。在事务中允许有重复的保存点名称，但指定保存点名称的 ROLLBACK TRANSACTION savepoint_name 语句只将事务回滚到使用该名称的最近的 SAVE TRANSACTION savepoint_name；
在使用 BEGIN DISTRIBUTED TRANSACTION 显式启动或从本地事务升级的分布式事务中，不支持 SAVE TRANSACTION。

 1 begin tran
 2 select @@TRANCOUNT
 3 
 4        save tran yu1
 5        insert into tbxin(name,age) select \'第1层save\',100;
 6        select @@TRANCOUNT
 7              
 8                   save tran yu2
 9                      insert into tbxin(name,age) select \'第2层save\',100;
10                      select @@TRANCOUNT
11                          
12                             save tran yu3
13                           insert into tbxin(name,age) select \'第3层save\',100;
14                           select @@TRANCOUNT
15 
16                                  save tran yu4
17                                insert into tbxin(name,age) select \'第4层save\',100;
18                                select @@TRANCOUNT
19 
20                             rollback tran yu3
21                             select @@TRANCOUNT
22 
23               commit tran yu2
24         select @@TRANCOUNT
25 
26 rollback tran

XACT_ABORT

xact_abort用于设置环境属性，默认为关闭状态。在关闭的状态下，嵌套事务中，若某个嵌套事务异常，不影响整个事务的进行，需手动写明错误后的处理方式（commit or rollback）；启动状态下，当某个嵌套事务异常，回滚整个嵌套事务。

（2）隐式事务

为连接将隐性事务模式设置为打开之后，当数据库引擎实例首次执行下列任何语句时，都会自动启动一个事务：当连接以隐式事务模式进行操作时，数据库引擎实例将在提交或回滚当前事务后自动启动新事务。无须描述事务的开始，只需提交或回滚每个事务。隐性事务模式生成连续的事务链。通过 API 函数或 Transact-SQL SET IMPLICIT_TRANSACTIONS ON 语句，将隐性事务模式设置为打开。

ALTER TABLE
CREATE
DROP
OPEN
FETCH
GRANT
REVOKE
SELECT
UPDATE
DELETE
INSERT
TRUNCATE TABLE

（3）自动提交事务

--例子
INSERT INTO ...
--数据库默认的事务管理模式，在没有被显式事务及隐式事务覆盖的情况下，自动在每个Tsql完成时，提交或者回滚

（4）手动提交事务

--例子
BEGIN TRAN
 
INSERT INTO ...
 
COMMIT / ROLLBACK
 
--数据库默认的事务管理模式，显式事务在完成时，手动指定SQL，说明提交或者回滚。

（5）批范围事务

只能应用于多个活动结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显式或隐式事务变为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动由 SQL Server 进行回滚。

（6）分布式事务

分布式事务跨越两个或多个称为资源管理器的服务器，有同构分布式及异构分布式。在MSSQL中，可以通过 BEGIN DISTRIBUTED TRANSACTION 命令开启分布式事务。

这里有一点需要注意一下：当事务内部操作跨越了多个服务器，但是并没有使用 BEGIN DISTRIBUTED TRANSACTION 命令开头的事务，也会自动转化为分布式事务！

假设A服务器上的数据库 Orders 用于记录订单，B服务器上的 Stock 用于记录库存（不考虑程序建立两个DB连接，按照仅建立一个 DB链接到 A 上）。当 Stock有库存，并减去一个库存后，Orders 正常可以下一个订单，则这个下单事务内，操作了两个服务器，属于分布式事务，简要操作如下：

BEGIN DISTRIBUTED TRANSACTION
SELECT ... FROM Stock... WHERE ...
UPDATE Stock ...
INSERT INTO ORDERS ...
COMMIT

在SQL SERVER中，如果没有配置服务器的DTC服务，使用分布式事务的时候，会报错如下：

(1 行受影响)
消息 8501，级别 16，状态 3，第 4 行
服务器 \'XINYSU\MSSQL\' 上的 MSDTC 不可用。
 
(1 行受影响)
链接服务器"test_xinysu"的 OLE DB 访问接口 "SQLNCLI11" 返回了消息 "该伙伴事务管理器已经禁止了它对远程/网络事务的支持。"。
消息 7391，级别 16，状态 2，第 4 行
无法执行该操作，因为链接服务器 "test_xinysu" 的 OLE DB 访问接口 "SQLNCLI11" 无法启动分布式事务。

如果实例需要支持分布式事务，则需要在双方的服务器其上开启DTC服务，运行XA事务。这里注意一点，如果链接服务器是MySQL数据库，因为mysql的odbc不支持 XA事务，所以，会报错无法启动分布式事务。官网解释如下：

MySQL OLE DB driver does not support MSDTC because it doesn’t support auto-enlistment in the ambient COM+ transaction. If you really want to, you can write your own XA.DLL to wrap MySQL OLEDB driver in an XA transaction.

windows启动DTC服务，配置如下：

"管理工具" -> "组件服务" ，按照下图操作，打开本地的DTC配置，启动网络DTC，启动XA事务，如下图。

配置结束后点击应用，则会提示MSDTC服务会被重启，同时，依赖DSDTC的应用程序可能需要重启才能使用新的配置，这个时候就需要重启 SQL SERVER的服务了。

如果是正在跑的数据库，建议先从库配置，切换主从，主库配置的顺序来启用。最好是在搭建服务器一开始，就了解程序层面是否会使用到这个功能。正常情况下，都是在程序中配置多个DB连接，由程序来控制分布式事务。

这里多说一个日常需要注意的事项，在使用链接服务器对另外一个服务器上的DB做增删改操作的时候，都是一行一行传递过去做操作的。比如：

1 INSERT INTO mssql_xinysu.dbname.dbo.tbid(id) select id from sys.sysobjects
2 或者
3 INSERT INTO openquery([mysql_xinysu],\'select id from tbid\') select id from sys.sysobjects

假设 select id from sys.sysobjects 的结果有2k行，则在第一个SQL的 ODBC 是这么处理的：

分为2000个INSERT 语句
(@Param000004 int)INSERT [dbname].[dbo].[tbid]([id]) VALUES(@Param000004)
一条一条INSERT

第二个SQL的ODBC是这么处理的：

分为2000个INSERT 语句
INSERT INTO tbid ([id]) VALUES(单个的ID值)
一条条INSERT

通过这个操作说明，可想而知，连接服务器操作是非常慢的过程，不建议在有性能要求的业务上进行这样的使用操作。

回到顶部(go to top)

【事务的ACID特性】

（1）Atomicity原子性

简称为A，事务的原子性。要求在同个事务中，单个SQL或者多个SQL对数据进行的修改操作，要么一起执行提交，要么全部回滚不提交。存储引擎中，通过undo log 来实现事务的原子性。

例子：

（2）Consistency一致性

简称为 C，事务的一致性。事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。

一致性，可以分为两个层次来说明。一个是存储引擎层，一个是业务层。

在存储引擎层，当某个表格发现了数据修改，那么修改的行数据应该符合表格的约束条件、外键条件，涉及到索引页数据也要做相应修改，保证数据修改后的完整性。

在业务层，事务的一致性更多的在于程序设计，比如在一个事务内，账号A给账号B转账100元，那么这个事务结束后，A的账号需要少100元，B的账号需要增加100元。

（3）Isolation隔离性

简称为 I，事务的隔离性。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。在SQL SERVER中，通过设置隔离级别来保证。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。

根据业务的实际情况和需求，可以对不同的业务选择不同的隔离级别来控制该事务与其他并发事务之间的隔离关系，隔离级别越高，并发能力就相应越低。

（4）Durability持久性

简称为D，事务的持久性。完成完全持久的事务之后，它的影响将永久存在于系统中。该修改即使出现系统故障也将一直保持。存储引擎中，通过redo log 来实现事务的持久性。

回到顶部(go to top)

【协议】

在事务内，除了保持ACID的特性外，在MSSQL中，会遵循相应的两阶段锁跟XA协议。

（1） 2PL

两阶段锁2PL，这个在之前的博文 http://www.cnblogs.com/xinysu/p/7260227.html 中有所提及。

2-PL，也就是两阶段锁，锁的操作分为两个阶段：加锁、解锁。先加锁，后解锁，不相交。加锁时，读操作会申请并占用S锁，写操作会申请并占用X锁，如果对所在记录加锁有冲突，那么会处于等待状态，知道加锁成功才惊醒下一步操作。解锁时，也就是事务提交或者回滚的时候，这个阶段会释放该事务中所有的加锁情况，进行一一释放锁。

假设事务对记录A和记录B都有操作，那么，其加锁解锁按照逐行加锁解锁顺序，如下：

BEGIN
LOCK A
READ A
A:A+100
WRITE A
UNLOCK A
LOCK B
READ B
UNLOCK B
COMMIT

两阶段锁还有几种特殊情况：conservative（保守）、strict（严格）、strong strict（强严格），这三种类型在加锁和释放锁的处理有些不一样。

conservative

在事务开始的时候，获取需要的记录的锁，避免在操作期间逐个申请锁可能造成的锁等待，conservative 2PL 可以避免死锁

strict

仅在事务结束的时候（commit or rollback），才释放所有 write lock，read lock 则正常释放

strong strict

仅在事务结束的时候（commit or rollback），才释放所有锁，包括write lock 跟 read lock 都是结束后才释放。

（2）XA

说XA协议，不得不提2PC、3PC协议，而提这两个协议，又不得不说说CAP理论。

（3）CAP理论

CAP分别是Consitency（一致性）、Availability（可用性）、Partition tolerance（分区容错性），在分布式存储系统里边，最多只能同时满足其中两者。

Consitency

一致性，在分布式存储系统中，对于每一次的读操作，对于读到的数据，要么都是最新的，要么则返回一个错误
这里的CAP的C跟ACID的C虽然是一个单词，但是含义不一样哦，记得区分
在关系型数据库里边，通常优先考虑到是一致性

Availability

可用性，保证每次请求都正常，但不要求返回的结果是最新的数据

Partition tolerance

分区容错性，当各个分区之间因为网络发生消息丢失或者延迟是，分布式存储系统仍能正常运行。
则是在操作涉及多个服务器的事务过程中，

（4）2PC，两阶段提交

两阶段提交，全程是 Two Phase Commitment Protocol。也就是将分布式事务分为了两个阶段：Prepare 跟 Commit/Rollback。

在分布式系统中，如果其中一个服务器上的操作失败，则其他服务器上的操作也需要回滚，即在一个事务内，多个跨服务器的操作中，要么所有都成功，要么所有都失败。但是呢，实际上每个节点都可以对自身的操作做控制，但是却不能控制同个分布式系统的其他节点的操作，这也就导致了，同个事务中，如果A节点操作成功，但是B节点操作失败，如果没有相应的处理方式，则会出现，某些操作成功，某些操作失败，造成数据不一致的情况。

如何处理这个问题呢？这就需要引入一个第三方组件来统一接收各个节点的操作情况，然后再根据各个操作的结果来判断各个节点的操作是否提交或者回滚。这个就是 2PC的雏形了。

2PC的简要处理说明如下：

Prepare

事务协调器coordinator 对涉及到的节点发起操作申请；
各个节点获取到操作后，直接在数据库中执行，并存放相关的日志到redo / undo log中，注意注意，这里仅是操作，并没有提交或者回滚该操作；
各节点将处理日志写入磁盘；
返回信息给coordinnator

如果节点可以正常执行，则返回 Ready 通知 coordinator；
如果节点不可以正常执行，则该节点本地回滚该操作，并返回 Not Ready 通知 coordinator

Comiit/Rollback

coordinator 根据各个节点的反馈信息，来决定该事务操作的结果
coordinator将操作结果记录到日志中
反馈操作结果给各个节点

如果出现一个或者一个以上的节点反馈回来 Not Ready的通知，则coordinator会通知正常执行操作的节点回滚事务
如果没有出现 Not Ready 的反馈，则coordinator会通知所有节点 COMMIT 操作。

2PC如何处理异常？

事务协调器宕机

这里需要引入一个新角色：coordinator watchdog，事务协调器看门狗
无论是coordinator 还是分布式系统的各个节点，在操作过程中，都会记录当前操作的状态日志。当出现异常或者恢复时，可以通过日志来判断当前的情况。
当 coordinator 发起提议后宕机，而此时各个节点开始操作，然后反馈给 coordinator，但是迟迟没有接收到 coordinator 的回应，那么各个节点的操作就无法回滚或者提交，处于堵塞情况。而 coordinator watchdog 则可以解决这个堵塞现象，当coordinator宕机一定时间后，看门狗会自动担任 coordinator 的工作，接收各个节点的反馈情况，然后再根据反馈结果传递 COMMIT/ROLLBAK给各个节点。

节点宕机

prepare阶段宕机，则coordinator接收到事务后发送给各个节点需要做的操作时，节点发生宕机，这个时候，则该节点无法返回 Ready 的消息，coordinator则默认接受该节点发出的 abort 信息，coordinator通知其他各个节点 Rollback 操作；
Comiit/Rollback阶段宕机，由于各个节点及coordinator都有日志记录，coordinator会记录这个事务是会提交还是回滚，当节点宕机后，其他节点根据coordinator的通知执行ROLLBACK或者COMMIT，而宕机节点本地会记录该事务操作未执行提交或者回滚，节点恢复后，会从 coordinator 日志中读取日志，重新处理该操作。

（5）XA协议

XA协议是 X/Open DTP Group 定义的两阶段提交协议，规定了事务管理器跟资源管理器的接口。

事务管理器指的是二阶段协议中的 coordinator ，而资源管理器则指的是各个数据库系统。

一般情况下，各个数据库系统并不知道彼此之间做了什么，这个时候，就需要一个第三方来做信息的接受跟传达，由它通知和协调相关数据库的提交或回滚。XA就是用来定义这个第三方的协议，详细定义了交易中间件与数据库之间的接口规范（即接口函数），交易中间件用它来通知数据库事务的开始、结束以及提交、回滚等。

XA接口函数由数据库厂商提供。通常情况下，交易中间件与数据库通过XA 接口规范，使用两阶段提交来完成一个全局事务，XA规范的基础是两阶段提交协议。注意，XA事务的性能相对较差。

参考文档：

http://www.infoq.com/cn/minibooks/JTDS

http://blog.jobbole.com/95632/

https://en.wikipedia.org/wiki/CAP_theorem

http://blog.nahurst.com/visual-guide-to-nosql-systems

秒客网

（4.19）sql server中的事务模式（隐式事务，显式事务，自动提交事务）

1.概念

2.操作

3.存储过程中的事务 XACT_ABORT

【最佳实践】事务的概念

（1）显式事务

（2）隐式事务

（3）自动提交事务

（4）手动提交事务

（5）批范围事务

（6）分布式事务

【事务的ACID特性】

（1）Atomicity原子性

（2）Consistency一致性

（3）Isolation隔离性

（4）Durability持久性

【协议】

（1） 2PL

（2）XA

（3）CAP理论

（4）2PC，两阶段提交

（5）XA协议

相关文章

（4.19）sql server中的事务模式（隐式事务，显式事务，自动提交事务）

1.概念

2.操作

3.存储过程中的事务 XACT_ABORT

【最佳实践】事务的概念

（1）显式事务

（2）隐式事务

（3）自动提交事务

（4） 手动提交事务

（5）批范围事务

（6）分布式事务

【事务的ACID特性】

（1）Atomicity原子性

（2）Consistency一致性

（3）Isolation隔离性

（4）Durability持久性

【协议】

（1） 2PL

（2）XA

（3）CAP理论

（4）2PC，两阶段提交

（5）XA协议

相关文章

（4）手动提交事务