SQL SERVER之事务

olop

  在实际对数据库的使用中，会出现多个用户同时对某一张表进行操作，当多个用户在同一时间对同一张数据表进行读取或者修改操作时，若处理不当就有可能发生冲突问题。为了解决这样的问题，就需要使用事务的控制和管理机制。


事务

   单个逻辑工作单元执行操作的集合，也可以看作是多条语句封装的结果。通过事务可以保证数据表中数据的一致性。
事务的特性




原子性

   是指事务中所有的执行操作，要么全部成功，要么不执行。如在商场购物中，管理员同时对用户进行充值操作。

    修改账户A中的现金数。

    修改账户A中的现金数

    如果在执行第一个SQL语句之后，第二个语句之前，突然断电了该如何办？

     

一致性

所谓的事务的一致性，是指一个事务操作执行完周，数据库中的数据必须处于合法一致的状态。例如如果账户A给账户B转账1000元后，那么账户A应该减少1000，账户B应该增加1000，但是两人的钱数总和还是一致的，应该处于合法的状态中。

隔离性

    就是事务看到的数据库中的数据要么是这个事务被修改之前的状态，要么是被修改之后的状态。



持久性

   如果一个事务被成功地修改，其结果在数据库中不会因为软件，硬件等故障而改变，数据会永久的保留下来




控制事务的流程

START TRANSACTION(开始事务)、COMMIT(提交)、ROLLBACK(事务回滚)


显示开始一个事务



使用START TRANSACTION或者BEGIN语句可以显示开始一个新的事务



语法规则：

START TRANSACTION{事务名}

     
隐式开始一个事务



在SQL语句中的第一条语句开始就表示隐式开始了一个新的事务


提交事务



显示提交：COMMIT[事务名]



    start transaction   --开始事务  
    insert into accounttable values('A',5000)  
    update accounttable set cashvalue=cashvalue+1000  
    where accountuser ='A'  
    commit   --提交事务  


（这里把两条语句封装到了一起，一个是插入语句，一个是更新语句）


隐式提交



是指通过使用SQL语句就可以完成事务的提交，如果执行了CREATE TABLE,DROP TABLE等



操作就会自动提交事务。




自动提交

自动提交指通过设置AUTOCOMMIT命令完成事务的提交。



自动提交语句

SET AUTOCOMMIT=1

SET AUTOCOMMIT ON



关闭自动提交方式

SET AUTOCOMMIT=0

SET AUTOCOMMIT OFF




回滚事务

  是表示当事务执行失败时，数据库恢复到该事务操作之前的那一个合法状态中，并撤销对该表的一些操作。同时在数据库中还可以设置保存点，可以当发生意外时，回滚到保存点状态。



    begin try  
    begin transaction  
    --使用try语句进行捕捉错误  
      
    insert into accounttable values(5000,'A')  
    save tran a1  
    insert into accounttable values(5000,'C')  
    commit  
    end try  
      
    begin catch  
    --当发生错误时，进行回滚  
    rollback tran a1  
    end catch  




（这段SQL语句，分别设置了2个保存点，分别是A1,A2,并使用rollback回滚机制）



并发事务的工作流程

    用户user1使用SELECT语句查询到accounttable账户中有5000元，但是由于某些原因，用户user1并没有提取现金

    用户2也通过SELECT语句查询到accounttable账户中有5000元，此时他开始事务，从账户中提取出了1000元，但是并没有提交事务。此时用户user1查到的还是5000元

    在用户user1查询到账户余额为5000后，希望全部提取出来，但是由于user2的事务并没有提交，所以用户1并不能提取

    用户2提取1000后，提交了事务，此时剩余4000元

    用户1终于可以执行提取5000元操作了，可是此时只有4000元，操作将被撤销，回滚到他之前的操作状态

    如果用户1还想全部取出的话，就必须开始一个新的事务

     

通过以上的叙述我们发现事物并发处理中存在的问题



    读脏数据

    是指那些已经更改但还是没有被提交的数据。如用户2取走1000后，用户1提取5000时，会发现账户的余额不足。

    不能重复读

    同一个事物中在多次执行时，由于其他事务对其做的修改或者删除等更新操作时，使得每次查询时返回的数据结果都不相同。如上例中，用户2已经取出1000元，而用户1查询到的仍然是5000元

     

     

    幻想读

    是指读取了其他事务中执行完插入或者更新操作后的错误数据。

     

    ------------------------------------------------------------------------




事务的隔离级别

1.READUNCOMMITTED:未提交读。正如上面叙述的那样，由于用户2没有提交，所以用户1还是读出了5000元。在READ UNCOMMITED隔离级别下，会隔离UPDATE语句，但不隔离SELECT语句。它的隔离级别最低。





2.READ COMMITTED:提交读。给隔离级别在读取数据时对其加共享锁，可以避免读脏数据，但是在READ COMMITTED隔离级别下，事务在结束前更改可以更改数据，因此不能避免不能重复读或者幻想读。



3.REPEATABLE READ:可重复读。在该隔离级别中会将查询中使用的所有数据锁定，防止其他用户对改数据进行操作，可以避免产生不能重复读。

4.SERIALLZABLE :可串行化。该隔离级别在事务提交之前，会锁定整个数据表，防止其他用户对数据进行增加、删除和修改等更新操作。



下面是有关四种隔离级别允许不同的类型的行为

隔离级别	脏读	不可重复读取	幻象
未提交读	是	是	是
提交读	否	是	是
可重复读	否	否	是
可串行读	否	否	否

牛刀小试

下面的例子均以下表为例

Table
Money	Int
Name	Char(4)

脏读操作

由上面的表格可知，脏读操作发生在未提交数据时，正如我们的例子中，用户1查到了5000的情况。如下操作

第一个连接语句

    <span style="font-family:SimSun;font-size:18px;">begin tran  
    update table set money=103 where name='A'  
    waitfor delay '00:00:10' --等待10秒  
      
    update table set money=104 where name='A'  
    commit tran</span>  


接着马上执行第二个连接语句


    <span style="font-family:SimSun;font-size:18px;">set transaction isolation level read uncommitted  
    begin tran  
    select money from table where name='A'  
    commit tran</span>  


最终结果是103，而不是104，这就是脏数据，可知如果我们把第二个连接中的事务隔离级别设置为 READ MOMMITED、REPEATABLE READ或者SERIALLZABLE就可以避免这种情况发生。


非重复读操作

第一个连接语句



    <span style="font-family:SimSun;font-size:18px;">set transaction isolation level read committed  
    --或者是set transaction isolation level read uncommitted  
      
    begin tran  
    select money from table where name='A'  
    waitfor delay '00:00:10'  --等待10秒  
    select money from table where name='A'  
    commit tran  
    </span>  


接着马上执行第二个连接语句




    <span style="font-family:SimSun;font-size:18px;">begin tran  
    update table set money=10 where name='A'  
    commit tran</span>  


我们发现第一个连接中两次返回账号的余额不一样，第一次是100，第二次是10，这就是典型的“非重复读”的问题

根据上表所示，如果把事务的隔离级别设置为REPEATABLE READ或者SERIALLZABLE可以防止此类问题


幻象读

根据上表所示，当事务的隔离级别为READ COMMITTED、READ UNCOMMITTED、REPEATABLE READ时就会发生幻象



先看下面的例子（账户余额为100）

第一个连接语句



    <span style="font-family:SimSun;font-size:18px;">begin tran  
    select * from table  
    waitfor delay '00:00:10'--等待10秒  
    select * from table  
    commit tran</span>  


接着马上执行第二个连接语句




    <span style="font-family:SimSun;font-size:18px;">begin tran  
    insert into table values(300,'a')  
    commit tran</span>  


我们发现两次查询的结果不一样，这就是典型的”幻象读”问题，可知解决方法为把隔离级别设置为SERIALLZABLE即可。





小结：在实际应用的时候，采用何种隔离级别应视具体情况而定。