SQL之事务管理

事务的由来

        我们在使用DELETE命令或UPDATE命令对数据库进行更新时，一次只能操作一个表，这会带来数据库不一致的问题。例如如果提高班的所有信息都放在一个数据库中，每个部门、每期的信息都放在不同的表中。如果博客委员会的一名成员退出提高班，我们不仅仅要在博客委员会表中此条记录，同时我们还得在提高班表中删除此人信息（提高班表中的成员与博客委员会的委员相对应，且提高班表包含此人各项职务此信息）。因此两个表都需要修改，这种修改只能通过delete语句进行。假设此人叫李勇

            第一条DELETE语句修改博客委员会表

            Delete  from  博客委员会  where  委员='李勇'

           第二条DELETE语句修改提高班表

           Delete  from  提高班表  where 成员='李勇'

        在执行第一条DELETE语句后，数据库中的数据已经处于不一致的状态。因为此时已经没有此人，但提高班表中仍然存在此人的相关记录；只有执行了第二条DELETE语句后，数据才重新处于一致状态。但计算机出现状况，无法继续执行第二条DELETE语句，则数据库中的数据将处于永远不一致的状态。因此必须保证两条DELETE语句同时执行。为解决类似问题，数据库系统通常引入事务Transaction的概念。

事务概述

一、概念

        事务指作为单个逻辑工作单元执行的一系列操作，而这些逻辑工作单元需要具有原子性，一致性，隔离性和持久性四个属性，统称ACID特性。

二、特性

        原子性：指事务必须是院子工作单元，即对于事务所进行数据修改，有么全部执行，要么全都不执行

        一致性：事务完成或者撤销后，都应该处于一致的状态。

        隔离性：多个事务同时进行，它们之间应该互不干扰，.应该防止一个事务处理其他事务也要修改的数据时，不合理的存取和不完整的读取数据。

       永久性：事务提交以后，所做的工作就被永久保存下来。

三、类型

 （1）隐式事务：每次执行SQL Server的任何数据修改语句时，它都是一个隐式事务。

   例如：下列SQL语句是一个独立事务：

       Insert table values （1，‘abc’）

      Update  table set col1=5 where col1=1

       Delete from table1 where col1=5

       Go

（2）显示事务：通常在程序中用BEGIN TRANSACTION命令来标识一个事务的开始，COMMIT TRANSACTION命令标识事务结束；这两个命名之间的所有语句被视为一体。只有执行到COMMIT TRANSACTION命令时，事务中对数据库的更新操作才算确认。和BEGIN…END命令类似。这两个命令也可以进行嵌套，即事务可以嵌套执行。

这两个命令的语法如下:

      BEGIN TRAN[transaction_name|@tran_name_variable]

      COMMIT[TRAN[SACTION][transaction_name|@tran_name_variable]]

     其中,BEGIN TRANSACTION可以缩写为BEGINTRAN。COMMIT TRANSACTION可以缩写为COMMITTRAN或COMMIT

    Tiansaction_name：指定事务的名称，只有前32个字符会被系统识别。

    @tran_name_variable：用变量来指定事务的名称变量。只能声明为CHAR VARCHAR NCHAR或NVARCHAR类型。

例：删除委员李勇

   Declare @transaction_name varchar（32）

   Select @transaction_name='my_transaction_delete'

   Begin transaction @transaction_name

   Go

   Use sample

   Go

   Delete  from  博客委员会  where  委员='李勇'

   Go

   Delete  from  提高班表  where 成员='李勇'

   Go

注：

一、不一定要给每一个事务一个变量名

二、第一条语句执行完后，第二条语句正在执行的时候突然状况发生，通过日志可以恢复到一条记录都没有删除的，可以重新做这个事务。

事务回滚

一、概念

       事务回滚（Transaction Rollback）是指当事务中的某一条语句执行失败时，将对数据库的操作恢复到事务执行前或某个指定位置。

   其语法如下：

 ROLLBACK [TRAN[SACTION]   [transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable]

     其中savepoint_name和@savepoint_variable参数用于指定回滚到某一指定位置。

     如果要让事务回滚到指定位置，则需要在事务中设定保存点Save Point

二、事务回滚的条件

注意：savepoint回滚到的位置

1、如果要让事务会滚到指定位置，则需要在事务中设定保存点Save  Point

2、如果没有使用保存点而又使用到了rollback就回滚到没有执行事务的状态。

注：保护点是指定其所在位置之前的事务语句不能回滚的语句，即此语句前面的操作被视为有效。

    其语法如下：

    SAVE  TRAN[SACTION]{savepoint_name|@savepoint_variable}

    各参数说明如下：

    savepoint_name：指定保存点的名称。同事务的名称一样，只有前32个字符会被系统识别。

   @savepoint_variable:用变量来指定保存点的名称变量，只能声明为CHAR、VARCHAR、NCHAR或NVARCHAR类型。

             事务工作机制

   例如：

   Begin tran

   Insert table1 values(1,'abc')

   Update table1 set col1=5 where col1=1

   Delete from table1 where col1=5

  COMMIT tran

 具体过程：

1、当begin tran语句到达数据库时，sql server分析出这是事务的开始，SQL server找到下一个可用的内存日志页面，并给新事物分配一个事务ID

2、接着运行插入语句，新的行被记录到事务日志中，数据页面在内存中进行修改，若所需页面不在内存中，则从磁盘中调出。

3、Update语句类似方式运行

4、当sql server收到commit tran时，日志页面被写到数据库的日志设备上，这样才能保证日志页面可被回复。由于日志变化写入了硬盘，它保证事务是可恢复的，即使掉电了或在数据页写入磁盘时数据库崩溃了，也能进行事务恢复。