SQL Server 数据库之事务

1. 概念

事务与存储过程类型，它们是由一系列的逻辑语句组成的工作单元；
事务由非常明确的开始和结束点，用 T-SQL 语句进行 SELECT 、INSERT、UPDATE 和 DELETE 等数据操作都属于隐式事务的一部分；
当系统把这些操作语句当成一个事务时，要么执行所有语句，要么都不执行；
当事务执行时，事务中进行的所有操作都会被写入事务日志中，写入日志的内容分为两种：一种是事务进行数据操作的记录，如对数据进行插入和修改；另一种是对任务的操作记录，如对表中的某一列创建索引；
当取消事务时，系统会根据日志中的记录进行反操作，保证系统的一致性；

事务是一系列 SQL 操作的逻辑工作单元，一个逻辑单元必须有 4 个属性，即原子性（Atomic）、一致性（Consistent）、隔离性（Isolated）、持久性（Durable），简称为 ACID；

• 原子性：对于事务必须是一个整体的工作单元，事务中对数据的操作要么全部执行，要么全部不执行；
• 一致性：事务完成时，所有的数据都必须保持一致状态；在相关数据库中，所以的规则都必须有事务进行修改，以保证所有数据的完整性；当事务结束时，所有的内部数据结构都必须是正确的；
• 隔离性：若多个事务对同一数据进行操作，那么当前事务的操作必须与其他事务进行隔离；事务在识别操作数据时，那么是第一个事务处理之前的状态，要么是第二个事务处理之后的状态，事务不会查看中间状态数据；
• 持久性：当事务提交成功后，事务对数据库中的数据操作会被永久保存下来；

2. 显示事务与隐式事务

SQL Server 数据提供了两种不同的方法处理事务，它们能基于单独到到连接定义，每一节连接都能使用它们需要的事务模式来实现其需求；
这两种事务根据定义方式不同进行分类：

• 显式事务
• 隐式事务

2.1 显示事务

SQL Server 数据库中能定义显式事务，将用户自定义或用户指定的事务称为显式事务；
显式事务的第一个分隔符为 BEGIN TRANSACTION 或 BEGIN DISTRIBUTED TRANSACTION，而结束分隔符必须为 COMMIT TRANSACTION、COMMIT WORK、ROOLBACK TRANSACTION、ROLLBACK WORK、SAVE TRANSACTION 中的一种；
下面是几种常用的事务语句和语法参数；

2.1.1 BEGIN TRANSACTION

BEGIN TRANSACTION 用来标记一个显示本地事务的起点；
语法格式如下：

BEGIN { TRAN | TRANSACTION }
[ {transaction_name | @tran_name_variable}
[ WITH MARK ['description'] ]
]
[;]

参数说明：

• transaction_name：分配给事务的名称，transaction_name 必须符合标识符规则，但标识符所包含的字符数不能大于32个；
• @tran_name_variable：含有有效事务的定义名称，必须用 char、varchar、nchar 或 nvarchar 数据类型声明变量；若传递给该变量的字符多于32个，就仅使用前面的32个字符，其余的字符将被截断；
• WITH MARK['description']：指定在日志中标记事物，description 是描述该标记的字符串；长于 128 个字符的 description 先截断为 128 个字符，然后才存储到 msdb.dbo.logmarkhistory 表中；

2.1.2 COMMIT TRANSACTION

COMMIT TRANSACTION 用来标记一个成功的显示或隐式事务的结束；
语法格式如下：

COMMIT [{ TRAN | TRANSACTION } [transaction_name | @tran_name_variable] ]
[ WITH (DELAYED_DURABILITY = { OFF | ON})]
[;]

参数说明：

• transaction_name：指定由前面的 BEGIN TRANSATCION 分配的事务名称；transaction_name 必须符合标识符规则，但不能超过 32 个字符，transaction_name 通过向程序员指明 COMMIT TRANSTACTION 与哪些 BEGIN TRANSACTION 相关联；
• @tran_name_variable：用户定义的、含有有效事务名称的变量，必须用 char、varchar、nchar 或 nvarchar 数据类型声明变量；若传递给变量的字符数超过 32 个，就只使用 32个字符，其余的字符将被截断；
• DELAYED_DEURABILITY：请求将此事务与延迟持续性一起提交的选项；若已用 DELAYED_DURABILITY=DISABLED 或 DELAYED_DURABILITY=FORCED 更改了数据库，就忽略此请求；

2.1.3 COMMIT WORK

COMMIT WORK 用于标志事务的结束；
语法格式如下：

COMMIT [WORK]
[;]

该语句的功能与 COMMIT TRANSACTION 相同，但 COMMIT TRANSACTION 接受用户定义的事务名称；

2.1.4 ROLLBACK REANSACTION

将显示或隐式事务回滚到事务的起点或事务内的某个保存点，当事务执行时，发生错误可使用 ROLLBACK TRANSACTION 语句撤销对数据库内数据的操作，使其恢复到之前的状态；
语法格式如下：

ROLLBACK {TRAN|TRANSACTION}
[ transation_name | @tran_name_variable | sacepoint_name | @savepoint_variable]
[;]

参数说明：

• transacction_name：指定事务的名称；
• @tran_name_variable：用户定义的、含有有效事务名称的变量的名称，必须用 char、varchar、nchar 或 nvchar 数据类型声明变量；
• savepoint_name：是 SAVE TRANSACTION 语句中的 savepoint_name；savepoint_name 必须符合有关标识符的规则，当条件回滚只影响事务的一部分时，可使用 savepoint_name；
• @savepoint_variable：是用户定义的、包含有效保存点名称的变量的名称，必须用 char、varchar、nchar 或 nvarchar 数据类型声明变量；

注意： 在多个活动的结果集（MARS）会话中，通过 T-SQL BEGIN TRANSACTION 语句启动的显示事务将变成批范围的事务；若批范围的事务在批处理完成时还没有提交或回滚，SQL Server 将自动回滚该事务；

3. 隐式事务

在隐式事务的模式中，SQL Server 在没有事务存在的情况下会开始下一个事务，与自动模式不同的是在隐式事务中不会执行 COMMIT 或 ROLLBACK 语句；

表格中所示的语句在没有事务时隐式开始一个事务；
在打开隐式事务开关时，执行下一条语句时会自行开启一个新的事务，并且没关闭一个事务，执行下一条语句时又会启动一个新事务，直到关闭了隐式事务的设置开关；
在执行 COMMIT 或 ROLLBACK 语句之前，事务一直保持有效；在第一个事务被提交或回滚之后，下次当连接执行这些语句中的任何语句时，SQL Server 都将自动启动一个新事务；
SQL Server 将不断生成一个隐式事务链，直到隐式事务关闭为止；

例子 1：隐式事务的创建

begin transaction
save transaction A
insert into demo values('BB','B term')
rollback TRANSACTION A
create table demo2(name varchar(10),age int)
insert into  demo2(name,age) varlues('lis',1)
rollback transaction

执行到 create table demo2 语句时，SQL Server 已经隐式创建一个事务，直到事务提交回滚；

4. API 中控制隐式事务

在 SQL Server 中使用 ODBC 和 OLE DB 来设置隐式事务，能使用 ODBC 提供的 SQL SetConnectAtter 函数用来启动隐式事务模式，只需将 SQL_ATTR_AUTOCOMMIT 中 ValuePtr 的值设置为 SQL_AUTOCOMMIT，ValuePtr 设置为 SQL_AUTOCOMMIT_ON；
调用 SQL EndTran 函数提交或回滚每个事务，其中 CompletionType 设置为 SQL_COMMIT 或 SQL_ROLLBACK

5. 事务的 COMMIT 和 ROLLBACK

事务执行结束后得到两种状态，即“事务提交成功”和“事务失败回滚”；
在 SQL Server 中，使用 T-SQL 语句中的 COMMIT 和 ROLLBACK 来处理事务结束后的工作；

1. COMMIT
   在事务执行成功的时候使用 COMMIT 来提交事务，在使用 COMMIT 语句的情况下能保证事务中的所有数据操作都有效，同时将释放事务执行时所使用到的资源，如使用事务的所；
2. ROLLBACK
   在事务执行失败时使用 ROLLBACK 将对隐式事务和显式事务进行回滚，回滚到事务执行前的状态或事务所设定的某个保存点内；