数据库中的事务处理

1.事务的开启

使用 API 函数和 SQL 语句，可以在 SQL Server 数据库引擎实例中将事务作为显式、自动提交或隐式事务来启动。

显式事务

通过 API 函数或通过发出 BEGIN TRANSACTION 语句来显式启动事务。

自动提交事务
数据库引擎的默认模式。每个单独的 SQL 语句都在其完成后提交。不必指定任何语句来控制事务。

隐式事务
通过 API 函数或 SET IMPLICIT_TRANSACTIONS ON 语句，将隐性事务模式设置为打开。下一个语句自动启动一个新事务。当该事务完成时，下一个 Transact-SQL 语句又将启动一个新事务。

当隐性事务模式设置为打开之后，当数据库引擎实例首次执行下列任何语句时，都会自动启动一个事务：

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

在发出 COMMIT 或 ROLLBACK 语句之前，该事务将一直保持有效。在第一个事务被提交或回滚之后，下次当连接执行以上任何语句时，数据库引擎实例都将自动启动一个新事务。该实例将不断地生成隐性事务链，直到隐性事务模式关闭为止。

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

事务模式按连接级别进行管理。一个连接的事务模式发生变化对任何其他连接的事务模式没有影响。

2.事务的结束

可以使用 COMMIT 或 ROLLBACK 语句，或者通过 API 函数来结束事务。

COMMIT
如果事务成功，则提交。COMMIT 语句保证事务的所有修改在数据库中都永久有效。COMMIT 语句还释放事务使用的资源（例如，锁）。

ROLLBACK
如果事务中出现错误，或用户决定取消事务，则回滚该事务。ROLLBACK 语句通过将数据返回到它在事务开始时所处的状态，来取消事务中的所有修改。ROLLBACK 还释放事务占用的资源。

3.指定事务边界及错误处理

可以使用 Transact-SQL 语句来确定数据库引擎事务启动和结束的时间。

Transact-SQL 语句
可以使用 BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION、ROLLBACK WORK 和 SET IMPLICIT_TRANSACTIONS 语句来描述事务。这些语句主要用于 DB 库应用程序和 Transact-SQL 脚本（如使用 osql 命令提示实用工具运行的脚本）中。

错误处理

          如果某个错误使事务无法成功完成，SQL Server 会自动回滚该事务，并释放该事务占用的所有资源。如果客户端与数据库引擎实例的网络连接中断了，那么当网络向实例通知该中断后，该连接的所有未完成事务均会被回滚。如果客户端应用程序失败或客户端计算机崩溃或重新启动，也会中断连接，而且当网络向数据库引擎实例通知该中断后，该实例会回滚所有未完成的连接。如果客户端从该应用程序注销，所有未完成的事务也会被回滚。

          如果批中出现运行时语句错误（如违反约束），那么数据库引擎中的默认行为是只回滚产生该错误的语句。可以使用 SET XACT_ABORT 语句更改此行为。在执行 SET XACT_ABORT ON 语句后，任何运行时语句错误都将导致自动回滚当前事务。编译错误（如语法错误）不受 SET XACT_ABORT 的影响。

          出现错误时，纠正操作（COMMIT 或 ROLLBACK）应包括在应用程序代码中。一种处理错误（包括那些事务中的错误）的有效工具是 Transact-SQL TRY...CATCH 构造。

4.高级事务

        显式事务可以嵌套。这主要是为了支持存储过程中的一些事务，这些事务可以从已在事务中的进程调用，也可以从没有活动事务的进程中调用。

        下列示例显示了嵌套事务的用途。TransProc 过程强制执行其事务，而不管执行事务的进程的事务模式。如果在事务活动时调用 TransProc，很可能会忽略 TransProc 中的嵌套事务，而根据对外部事务采取的最终操作提交或回滚其 INSERT 语句。如果由不含未完成事务的进程执行 TransProc，则在该过程结束时，COMMIT TRANSACTION 将有效地提交 INSERT 语句。

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
USE AdventureWorks;
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
               Colb CHAR(3) NOT NULL);
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc;
GO
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
EXECUTE TransProc 3,'bbb';
GO
/* The following SELECT statement shows only rows 3 and 4 are
   still in the table. This indicates that the commit
   of the inner transaction from the first EXECUTE statement of
   TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans;
GO

SQL Server 数据库引擎将忽略内部事务的提交。根据最外部事务结束时采取的操作，将提交或者回滚内部事务。如果提交外部事务，也将提交内部嵌套事务。如果回滚外部事务，也将回滚所有内部事务，不管是否单独提交过内部事务。

对 COMMIT TRANSACTION 或 COMMIT WORK 的每个调用都应用于最后执行的 BEGIN TRANSACTION。如果嵌套 BEGIN TRANSACTION 语句，那么 COMMIT 语句只应用于最后一个嵌套的事务，也就是在最内部的事务。即使嵌套事务内部的 COMMIT TRANSACTION transaction_name 语句引用外部事务的事务名称，该提交也只应用于最内部的事务。

ROLLBACK TRANSACTION 语句的 transaction_name 参数引用一组命名嵌套事务的内部事务是非法的，transaction_name 只能引用最外部事务的事务名称。如果在一组嵌套事务的任意级别执行使用外部事务名称的 ROLLBACK TRANSACTION transaction_name 语句，那么所有嵌套事务都将回滚。如果在一组嵌套事务的任意级别执行没有 transaction_name 参数的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句，那么所有嵌套事务都将回滚，包括最外部事务。

5.事务保存点

         保存点提供了一种机制，用于回滚部分事务。您可以使用 SAVE TRANSACTION savepoint_name 语句创建保存点。然后执行 ROLLBACK TRANSACTION savepoint_name 语句以回滚到保存点，而不是回滚到事务的起点。

在不可能发生错误的情况下，保存点很有用。在很少出现错误的情况下使用保存点回滚部分事务，比让每个事务在更新之前测试更新的有效性更为有效。更新和回滚操作代价很大，因此只有在遇到错误的可能性很小，而且预先检查更新的有效性的代价相对很高的情况下，使用保存点才会非常有效。

         下面的示例说明了保存点在一个订单系统中的使用情况。该系统中存货不足的可能性很小，因为该公司具备有效的供应商和分购点。通常应用程序在尝试更新订单记录时，会先验证目前是否有足够的存货。下面的示例假定由于某种原因，验证目前可用存货量代价相对较大（由于连接到一个低速的调制解调器或广域网上）。可将应用程序编写为只进行更新，而且如果收到错误信息表明库存不足时，将回滚该更新。在这种情况下，在插入之后快速检查 @@ERROR 要比在更新之前验证库存数量的速度要快得多。

        InvCtrl 表有一个 CHECK 约束，如果 QtyInStk 列低于 0，它就会触发 547 号错误。OrderStock 过程将创建一个保存点。如果出现 547 错误，它将回滚到该保存点，并将当前手边有的项目数返回给调用进程。然后调用进程可以针对现有的数量重新下订单。如果 OrderStock 返回 0，这就使调用进程确认当前有足够的存货来满足定购需要。

SET NOCOUNT OFF;
GO
USE AdventureWorks;
GO
CREATE TABLE InvCtrl
    (WhrhousID      int,
    PartNmbr      int,
    QtyInStk      int,
    ReordrPt      int,
    CONSTRAINT InvPK PRIMARY KEY
    (WhrhousID, PartNmbr),
    CONSTRAINT QtyStkCheck CHECK (QtyInStk > 0) );
GO
CREATE PROCEDURE OrderStock
    @WhrhousID int,
    @PartNmbr int,
    @OrderQty int
AS
    DECLARE @ErrorVar int;
    SAVE TRANSACTION StkOrdTrn;
    UPDATE InvCtrl SET QtyInStk = QtyInStk - @OrderQty
        WHERE WhrhousID = @WhrhousID
        AND PartNmbr = @PartNmbr;
    SELECT @ErrorVar = @@error;
    IF (@ErrorVar = 547)
    BEGIN
        ROLLBACK TRANSACTION StkOrdTrn;
        RETURN (SELECT QtyInStk
                FROM InvCtrl
                WHERE WhrhousID = @WhrhousID
                AND PartNmbr = @PartNmbr);
    END
    ELSE
        RETURN 0;
GO

6.事务隔离级别

设置事务隔离级别使程序员面临的风险因某些完整性问题而增加，但好处是可以支持更好的数据并发访问。事务隔离级别有：

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SNAPSHOT
SERIALIZABLE
 

下表显示了不同隔离级别允许的并发副作用。

隔离级别 更新丢失 脏读取 重复读取 幻读
未提交读取 N      Y      Y      Y
提交读取  N       N      Y      Y
可重复读取 N      N      N      Y
快照      N      N      N      N

序列化    N      N       N      N
 
   事务必须至少在可重复读的隔离级别运行，才能防止当两个事务都检索同一行，然后根据原先检索的值更新行时，可能发生丢失更新的情况。如果两个事务使用一个 UPDATE 语句更新行，而且更新并不以先前检索的值为基础，则在默认的已提交读的隔离级别上不会发生丢失更新的情况。

7.事务中允许的SQL 语句
ALTER DATABASE
 DROP DATABASE
 
ALTER FULLTEXT CATALOG
 DROP FULLTEXT CATALOG
 
ALTER FULLTEXT INDEX
 DROP FULLTEXT INDEX
 
BACKUP
 RECONFIGURE
 
CREATE DATABASE
 RESTORE
 
CREATE FULLTEXT CATALOG
 UPDATE STATISTICS
 
CREATE FULLTEXT INDEX
  

也不能使用下列语句：

显式事务中的全文系统存储过程。
sp_dboption 用于设置数据库选项，或在显式事务或隐式事务内部修改 master 数据库的任何系统过程。
8.编写有效的事务

尽可能使事务保持简短很重要。当事务启动后，数据库管理系统 (DBMS) 必须在事务结束之前保留很多资源，以保护事务的原子性、一致性、隔离性和持久性 (ACID) 属性。如果修改数据，则必须用排他锁保护修改过的行，以防止任何其他事务读取这些行，并且必须将排他锁控制到提交或回滚事务时为止。根据事务隔离级别设置，SELECT 语句可以获取必须控制到提交或回滚事务时为止的锁。特别是在有很多用户的系统中，必须尽可能使事务保持简短以减少并发连接间的资源锁定争夺。在有少量用户的系统中，运行时间长、效率低的事务可能不会成为问题，但是在有上千个用户的系统中，将不能忍受这样的事务。

  编码指导原则
以下是编写有效事务的指导原则：

不要在事务处理期间要求用户输入。
在事务启动之前，获得所有需要的用户输入。如果在事务处理期间还需要其他用户输入，则回滚当前事务，并在提供了用户输入之后重新启动该事务。即使用户立即响应，作为人，其反应时间也要比计算机慢得多。事务占用的所有资源都要保留相当长的时间，这有可能会造成阻塞问题。如果用户没有响应，事务仍然会保持活动状态，从而锁定关键资源直到用户响应为止，但是用户可能会几分钟甚至几个小时都不响应。
在浏览数据时，尽量不要打开事务。
在所有预备的数据分析完成之前，不应启动事务。
尽可能使事务保持简短。
在知道要进行的修改之后，启动事务，执行修改语句，然后立即提交或回滚。只有在需要时才打开事务。
若要减少阻塞，请考虑针对只读查询使用基于行版本控制的隔离级别。
灵活地使用更低的事务隔离级别。
可以很容易地编写出许多使用只读事务隔离级别的应用程序。并不是所有事务都要求可序列化的事务隔离级别。
灵活地使用更低的游标并发选项，例如开放式并发选项。
在并发更新的可能性很小的系统中，处理“别人在您读取数据后更改了数据”的偶然错误的开销要比在读取数据时始终锁定行的开销小得多。
在事务中尽量使访问的数据量最小。
这样可以减少锁定的行数，从而减少事务之间的争夺。
  避免并发问题和资源问题
为了防止并发问题和资源问题，应小心管理隐式事务。使用隐式事务时，COMMIT 或 ROLLBACK 后的下一个 Transact-SQL 语句会自动启动一个新事务。这可能会在应用程序浏览数据时（甚至在需要用户输入时）打开一个新事务。在完成保护数据修改所需的最后一个事务之后，应关闭隐性事务，直到再次需要使用事务来保护数据修改。此过程使 SQL Server 数据库引擎 能够在应用程序浏览数据以及获取用户输入时使用自动提交模式。

另外，启用快照隔离级别后，尽管新事务不会控制锁，但是长时间运行的事务将阻止从 tempdb 中删除旧版本。