Sqlserver的事务处理

一、前言

       我们除了可以在开发工具中进行事务处理，例如Pb，C#或者Java等开发工具中进行事务并发处理，我们还可以在数据库中自身进行事务处理，下面我们研究一下Sqlserver数据库自身的事务处理。
       我们在数据库中使用事务，一般会在存储过程中使用，这样，我们在前台应用程序，只需调用这些存储过程，就实现了数据的并发控制，同时也实现了展示层、逻辑层和数据层的分离操作，方便数据逻辑的集中处理和跨前台开发语言。
        例如：我们在存储过程之中写一个入库的存储过程，不仅pb程序可以直接使用，c#程序也可以直接使用，Java程序还是可以直接使用，移动应用也可以通过webservice等方式直接使用存储过程，所以，十分方便。

二、Sqlserver的事务处理机制

        好了，下面开始研究一下Sqlserver的事务和并发处理：

指定和强制事务处理
 

        SQL 程序员要负责启动和结束事务，同时强制保持数据的逻辑一致性。程序员必须定义数据修改的顺序，使数据相对于其组织的业务规则保持一致。然后，程序员将这些修改语句包括到一个事务中，使SQL Server能够强制该事务的物理完整性。

        为了保证事务的正确运行，SQL Server 提供： 

        锁定设备，使事务相互隔离。 

        记录设备，保证事务的持久性。即使服务器硬件、操作系统或 SQL Server 自身出现故障，SQL Server 也可以在重新启动时使用事务日志，将所有未完成的事务自动地回滚到系统出现故障的位置。 

         事务管理特性 ，强制保持事务的原子性和一致性。事务启动之后，就必须成功完成，否则 SQL Server 将撤消该事务启动之后对数据所作的所有修改。 

Sqlserver事务处理模式

        Sqlserver支持常用的三种事务处理模式：显示、隐示和自动提交事务。
       
显式事务可以显式地在其中定义事务的启动和结束。

BEGIN TRANSACTION

为连接标记显式事务的起始点。

COMMIT TRANSACTION 或 COMMIT WORK

如果没有遇到错误，可使用该语句成功地结束事务。该事务中的所有数据修改在数据库中都将永久有效。事务占用的资源将被释放。

ROLLBACK TRANSACTION 或 ROLLBACK WORK

用来清除遇到错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。

自动提交模式是SQL Server的默认事务管理模式。每个 Transact-SQL 语句在完成时，都被提交或回滚。如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。只要自动提交模式没有被显式或隐性事务替代，SQL Server 连接就以该默认模式进行操作。自动提交模式也是 ADO、OLE DB、ODBC 和 DB-Library 的默认模式。

SQL Server 连接在 BEGIN TRANSACTION 语句启动显式事务，或隐性事务模式设置为打开之前，将以自动提交模式进行操作。当提交或回滚显式事务，或者关闭隐性事务模式时，SQL Server 将返回到自动提交模式。

隐性事务用的不多，ADO也不支持，不描述了。

控制事务

        应用程序主要通过指定事务启动和结束的时间来控制事务。这可以使用 Transact-SQL 语句或数据库 API 函数。系统还必须能够正确处理那些在事务完成之前便终止事务的错误。

        事务是在连接层进行管理。当事务在一个连接上启动时，在该连接上执行的所有的 Transact-SQL 语句在该事务结束之前都是该事务的一部分。

启动事务

        在 Microsoft® SQL Server中，可以按显式、自动提交或隐性模式启动事务。

显式事务

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

自动提交事务

这是 SQL Server 的默认模式。每个单独的 Transact-SQL 语句都在其完成后提交。不必指定任何语句控制事务。

隐性事务

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

连接模式在连接层进行管理。如果一个连接从一种事务模式改变到另一种，那么它对任何其它连接的事务模式没有影响。

结束事务
 

        可以使用 COMMIT 或 ROLLBACK 语句结束事务。

COMMIT

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

ROLLBACK

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

指定事务边界

        可以用 Transact-SQL 语句或 API 函数和方法确定 SQL Server 事务启动和结束的时间。

Transact-SQL 语句

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

API 函数和方法

 数据库 API（如 ODBC、OLE DB 和 ADO）包含用来描述事务的函数和方法。它们是 SQL Server 应用程序中用来控制事务的主要机制。

 每个事务都必须只由其中的一种方法管理。在同一事务中使用两种方法可能导致不确定的结果。例如，不应先使用 ODBC API 函数启动一个事务，再使用 Transact-SQL COMMIT 语句完成该事务。这样将无法通知 SQL Server ODBC 驱动程序该事务已被提交。在这种情况下，应使用 ODBC SQLEndTran 函数结束该事务。

事务处理过程中的错误

如果服务器错误使事务无法成功完成，SQL Server 将自动回滚该事务，并释放该事务占用的所有资源。
如果客户端与 SQL Server 的网络连接中断了，那么当网络告知 SQL Server 该中断时，将回滚该连接的所有未完成事务。
如果客户端应用程序失败或客户计算机崩溃或重启，也会中断该连接，而且当网络告知 SQL Server 该中断时，也会回滚所有未完成的连接。
如果客户从该应用程序注销，所有未完成的事务也会被回滚。

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

如果出现运行时错误或编译错误，那么程序员应该编写应用程序代码以便指定正确的操作（COMMIT 或 ROLLBACK）。

 三、测试

测试环境：Sqlserver2000；
表table_test，
字段dbid 自增种子 主键，xm1 varchar（20） 字段设为唯一性，num1 numeric(18,2)

3.1 测试一 自动提交 三条正确的语句

编写存储过程

CREATE PROCEDURE sp_test  AS

    insert into table_test  (xm1,num1) values  ('a1',1);

    insert into table_test  (xm1,num1) values  ('a2',2);

    insert into table_test  (xm1,num1) values  ('a3',3);

GO

在查询分析器中执行。

第一条语句增加成功，第二条语句增加成功，第三条语句增加成功。

3.2 测试二 自动提交 一条正确的语句，一条唯一键重复的语句，一条正确语句

    insert into table_test  (xm1,num1) values  ('a4',4);

    insert into table_test  (xm1,num1) values  ('a4',5);

    insert into table_test  (xm1,num1) values  ('a6',6);

第一条语句增加成功，第二条语句增加失败，第三条语句增加成功。

3.3 测试三 自动提交 一条正确语句，一条正确的语句，一条唯一键重复的语句

    insert into table_test  (xm1,num1) values  ('a7',7);

    insert into table_test  (xm1,num1) values  ('a8',8);

    insert into table_test  (xm1,num1) values  ('a8',9);

第一条语句增加成功，第二条语句增加成功，第三条语句增加失败。
通过上面三个例子可以看出，默认的自动提交模式和power builder 的自动提交模式的概念是一样的，就是一个语句是一个独立的事务，两者之间是完全独立的，互相没有干扰。

下面的几个例子将测试显示事务的各种情况：

  3.4 测试四 显示事务  三条正确的语句

CREATE PROCEDURE sp_test  AS

 begin tran

    insert into table_test  (xm1,num1) values  ('a10',10);

    insert into table_test  (xm1,num1) values  ('a11',11);

    insert into table_test  (xm1,num1) values  ('a12',12);

commit tran

GO

查询分析器执行
第一条语句增加成功，第二条语句增加成功，第三条语句增加成功。

3.5 测试五 显示事务  一条正确的语句，一条唯一键重复的语句，一条正确语句

CREATE PROCEDURE sp_test  AS

 begin tran

    insert into table_test  (xm1,num1) values  ('a13',13);

    insert into table_test  (xm1,num1) values  ('a13',14);

    insert into table_test  (xm1,num1) values  ('a15',15);

commit tran

GO

查询分析器执行
第一条语句增加成功，第二条语句增加失败，第三条语句增加成功。
不是写在一个事务里了吗？
怎么和自动提交的情况是一样的呀？
事务不是有了错误，就自动回滚吗？
原子性的吗？

 查询上文的 事务处理过程中的错误 

 如果批处理中出现运行时语句错误（如违反约束），那么 SQL Server 中默认的行为将是只回滚产生该错误的语句。

 我们下面再测试一下别的错误，不是违法约束，看看Sqlserver 如何处理：

3.6 测试六 显示事务  一条正确的语句，一条类型错误的语句，一条正确语句

CREATE PROCEDURE sp_test  AS

 begin tran

    insert into table_test  (xm1,num1) values  ('a16',16);

    insert into table_test  (xm1,num1) values  ('a17','a');

    insert into table_test  (xm1,num1) values  ('a18',18);

commit tran

GO

执行，
一条语句也没有执行成功。
看来出现类型转换错误的时候，事务中的Sqlserver 自动回滚。
那么哪些错误会自动回滚，哪些错误不自动回滚呀，好像一时也搞不清楚。
省事的做法，就是按照上文的描述，使用 SET XACT_ABORT ON 自动回滚。
再次测试3.5的例子：

3.7 测试七 显示事务  一条正确的语句，一条唯一键重复的语句，一条正确语句

CREATE PROCEDURE sp_test  AS

SET XACT_ABORT ON

 begin tran

    insert into table_test  (xm1,num1) values  ('a13',13);

    insert into table_test  (xm1,num1) values  ('a13',14);

    insert into table_test  (xm1,num1) values  ('a15',15);

commit tran

GO

执行，
一条语句也没有增加成功。
达到了预期的目标。

3.8 测试八 显示事务 完善的写法

编写存储过程：
 

CREATE PROCEDURE sp_test  
AS

 begin tran

    insert into table_test  (xm1,num1) values  ('a13',13);
    if @@error <> 0 
    begin
            rollback tran
            return -1
     end
    insert into table_test  (xm1,num1) values  ('a13',14);
    if @@error <> 0 
    begin
            rollback tran
            return -2
     end
    insert into table_test  (xm1,num1) values  ('a15',15);
    if @@error <> 0 
    begin
            rollback tran
            return -3
     end
    
commit tran
return 0
GO

3.9 测试九 2005以上版本使用try catch进行异常处理

-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_test] 
AS
BEGIN

 SET NOCOUNT ON;
   begin tran
  
   begin try
      insert into xx_yg(name,bm) values  ('001','研发部')
      insert into xx_yg(name,bm) values  ('002','研发部')
   end try
   begin catch
      if @@trancount > 0
         rollback tran
   end catch

   if @@trancount > 0
      commit tran
   
END