SQL Server事务

事务处理是数据库的主要工作，事务由一系列的数据操作组成，是数据库应用程序的基本逻辑单元，用来保证数据的一致性。

SQL Server提供了几种自动的、可以通过编程来完成的机制，包括事务日志、SQL事务控制语句，以及事务处理运行过程中通过锁保证数据完整性的机制。

1. 事务概述

事务和存储过程类似，由一系列T-SQL语句组成，是SQL Server系统的执行单元。

在数据库处理数据的时候，有一些操作是不可分割的整体。

例如，当用银行卡消费的时候首先要在账户扣除资金，然后再添加资金到公司的户头上。

在这个过程中用户所进行的实际操作可以理解成不可分割的，不能只扣除不添加，当然也不能只添加不扣除。

利用事务可以解决上面的问题，即把这些操作放在一个容器里，强制用户执行完所有的操作或者不执行任何一条语句。

事务就是作为单个逻辑工作单元执行的一系列操作、这一系列的操作或者都被执行或者都不被执行。

在SQL Server中，事务要求处理时必须满足ACID原则，即原子性、一致性、隔离性、持久性。

• 原子性：事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全部不执行。
• 一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。
• 隔离性：由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据时，数据所处的状态要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
• 持久性：事务完成之后，它对于系统的影响是永久的。该修改即使出现系统故障也会一直保持。

事务的这种机制保证了一个事务或者成功提交，或者失败回滚，二者必发生一种，因此，事务对数据的修改具有可恢复性，即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。

而使用一般的批处理，则有可能出现有的语句被执行，而另一些语句没有被执行的情况，从而有可能造成数据不一致。

2. 事务的类型

根据事务系统设置和运行模式的不同，SQL Server将事务分为多种类型。

根据系统的设置分类

根据系统的设置，SQL Server将事务分成两种类型：系统事务和用户定义事务。

• 系统事务

系统提供的事务是指在执行某些语句时，一条语句就是一个事务。

但是要明确，一条语句的对象即可能是表中的一行数据，也可能是表中的多行数据，甚至是表中的全部数据。

因此，只有一条语句构成的事务也可能包含了多行数据的处理。

系统提供的事务语句：ALTER、CREATE、DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REVOKE、SELECT、UPDATE、TRUNCATE TABLE，这些语句本身就构成了一个事务。

例子1：使用ALTER TABLE语句修改STUDENT表

ALTER TABLE STUDENT

ALTER COMLUMN NAME NVARCHAR(20) NOT NULL

这条语句就构成了一个事务，要么全部执行，要么都不执行。

• 用户定义事务

在实际应用中，大多数的事务处理采用了用户定义的事务来处理。

在开发应用程序时，可以使用BEGIN TRANSACATION语句来定义明确的用户定义事务。

在使用用户定义的事务时，一定要注意事务必须有明确的结束语句来结束。

如果不使用明确的结束语句来结束，那么系统可能把从事务开始到用户关闭连接之间的全部操作都作为一个事务来对待。

事务的明确结束可以使用如下两个语句：COMMIT语句或ROLLBACK语句。

COMMIT语句是提交语句，将全部完成的语句明确地提交到数据库中。

ROLLBACK语句是取消语句，该语句将事务的操作全部取消，即表示事务操作失败。

根据运行模式分类

根据运行模式的不同，SQL Server将事务分为四种类型：自动提交事务、显式事务、隐式事务和批处理级事务。

• 自动提交事务

自动提交事务是指每条单独的T-SQL语句都是一个事务。

如果没有通过任何T-SQL语句设置事务，一条T-SQL语句就是一个事务，语句执行完事务就结束。

以前使用的每一条T-SQL语句都可以叫作一个自动提交事务。

• 显式事务

显式事务指每个事务均以BEGIN TRANSACTION语句、COMMIT TRANSACTION或ROLLBACK TRANSACTION语句明确地定义了什么时候启动事务、什么时候结束事务。

• 隐式事务

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

• 批处理级事务

批处理级事务是SQL Server的新增功能，该事务只能应用于多个活动结果集(MARS)，在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。

3. 事务处理语句

所有的T-SQL语句本身都是内在的事务。

另外，SQL Server中有专门的事务处理语句，这些语句将SQL语句集合分组后形成单个的逻辑工作单元。

事务处理的T-SQL语句如下：

• 定义一个事务的开始：BEGIN TRANSACATION
• 提交一个事务：COMMIT TRANSACTION
• 在事务内设置保存点：SAVE TRANSACTION
• 回滚事务：ROLLBACK TRANSACTION

BEGIN TRANSACTION代表一个事务的开始位置，每个事务继续执行直到用COMMIT TRANSACTION提交，从而正确地完成对数据库做永久的改动；

或者遇上错误用ROLLBACK TRANSACTION语句撤销所有改动，也就是回滚整个事务，也可以回滚到事务内的某个保存点，它也标志一个事务的结束。

开始事务

开始一个本地事务的语法格式如下：

BEGIN{tran|transaction}

[{transaction_name|@tran_name_variable}
[with mark['description']]]

其中，各参数说明如下：

• transaction_name：分配给事务的名称。transaction_name必须符合标识符规则。
• @tran_name_variable：用户定义的、含有有效事务名称的变量的名称。
• with mark[‘description’]：指定在日志中标记事务。description是描述该标记的字符串。

提交事务

当一个成功的隐式事务或显式事务结束时，需要使用COMMIT TRANSACTION语句提交事务，其语法格式如下：

commit {tran|transaction}[transaction_name|@tran_name_variable]

其中，各参数的含义同开始事务(BEGIN TRANSACTION)。

因为数据已经永久修改，所以在COMMIT TRANSACTION语句后不能回滚事务。

在嵌套事务中使用COMMIT TRANSACTION时，内部事务的提交并不释放资源，也没有执行永久修改，只有在提交了外部事务时，数据修改才具有永久性且资源才会被释放。

设置保存点

可以使用SAVE TRANSACTION语句在事务内部设置保存点，以便回滚事务时回滚到某个保存点，其语法格式如下：

save{tran|transaction}[transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable]

其中，各参数说明如下：

• savepoint_name：分配给保存点的名称。保存点名称必须符合标识符规则。
• @savepoint_variable：包含有效保存点名称的用户定义变量的名称。

回滚事务

当需要将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点时，需要使用ROLLBACK TRANSACTION语句回滚事务，其语法格式如下：

rollback {tran|transaction}
[transaction_name
|@tran_name_variable
|savepoint_name
|@savepoint_variable]

其中，各参数的含义同上面3条语句的相关命令。

对于rollback transaction语句需要注意以下几点：

1. 如果不指定回滚的事务名称或保存点，则rollback transaction命令会将事务回滚到事务的起点。
2. 在嵌套事务时，该语句将所有内层事务回滚到最远的begin transaction语句，transaction_name也只能是来自最远的begin transaction语句的名称。
3. 在执行commit transaction语句后不能回滚事务。
4. 如果在触发器中发出rollback transaction命令，将回滚对当前事务中所做的所有数据修改，包括触发器所作的修改。
5. 事务在执行过程中出现任何错误，SQL Server都将自动回滚事务。

使用事务时应注意以下几点：

• 不是所有的T-SQL语句都能放在事务里，通常，insert、update、delete、select(DML)等可以放在事务里，创建、删除、恢复数据库(DDL)等不能放在事务里。
• 事务要尽量地小，而且一个事务占用的资源越少越好。
• 如果事务在事务中间发生了错误，并不是所有情况都会回滚，只有达到一定的错误级别才会回滚，可以在事务中使用@@err变量查看是否发生了错误。

例子2：定义一个事务，将姓名为ICHI的年龄改为27，并提交该事务。

USE ichi

GO

BEGIN TRANSACTION

UPDATE STUDENT
SET    AGE = 27
WHERE  NAME = 'ICHI'

COMMIT TRANSACTION 

例子3：定义一个事务，向STUDENT表中插入一条记录，并设置保存点。然后删除该条记录，并回滚到事务的保存点，提交该事务。

USE ichi

GO

INSERT INTO STUDENT
VALUES     ('ITACHI',
            '男',
            '60')

SAVE TRANSACTION SAVEPOINT

DELETE FROM STUDENT
WHERE  NAME = 'ITACHI'

ROLLBACK TRANSACTION SAVEPOINT

COMMIT