SQL 中的事务

事物

     1、什么是事物？

事物的概念：事物是一种机制，它包含了一组数据库操作命令，而且将所有的命令作为一个整体一起向数据库提交或撤销。这组命令要么都执行，要么都不执行，所以事物是一个不可分割的逻辑工作单元。在数据库系统上执行并操作时事物是作为最下的控制单元来使用的。使用于多用户同时操作的数据库系统，一方面这些系统同时操作的用户多，另一方面对数据安全性和完整性的要求更高。

事物的特点

 原子性、一致性、隔离性、持久性。简称ACID

1）原子性（Atomicity）：事物是一个完整性的操作，事物的各个元素是不可分割的，要么全部成功执行，要么就撤销所有操作，事物的单元不可能出现部分成功的情况。
2）一致性（Consistency）：事物完成前后，数据必须保持完全一致的状态。既在事物开始前，数据库中存储的数据保持一致的状态。当事物成功完成时，数据也必须保持一致状态。事物不能使数据处于不稳定状态。这样就保证了数据的一致性，不会出现改变了一部分数据既部分语句执行成功了，而另一部分语句由于出错没执行的情况！
3）隔离性（Isolation）：事物是相对独立的，一个书屋对数据进行修改时，其他事物是不能修改的。如果其他事物要访问这个事物操作的数据，要么在这个事物开始 之前访问，要么在事物完成之后访问。当事物修改数据时，如果其他任何进程也正在使用相同的数据，则只到该事物成功提交之后，其他进程对数据的修改才能生效。隔离性是DBMS针对并发事物之间的冲突提供的安全保证。DBMS可以通过加锁在并发执行的事物间提供不同级别的分离。加入并发交叉执行的事物没有任何控制，操纵相同的共享对象的多个并发事物的执行可能引起异常情况。

4）持久性（Durability）：事物完成后，它对于系统带的影响是永久性的，即一个事物 提交成功，DBMS保证它对数据库中数据的改变是永久性的，经受得住任何系统故障。持久性通过数据库备份和恢复来保证。持久性意味着当系统或介质发生故障时，确保已经提交的事物的更新不会丢失。

事物的隔离级别：

1）事物的4个特性中，隔离性用来解决多用户操作中的并发冲突问题。ISO SQL定义标准事物之间的隔离级别为：未提交读（READ  UNCOMMITTED）、已提交读（READ  COMMITTED）、重复读（REPEATABLE  READ）、可串行化（SERIALIZABLE）。
a）未提交读：事物之间最低的隔离级别。该级别只能保证不会读取损坏的数据，即事物之间没有什么隔离，事物能够读取其他事物正在修改并未提交的数据。这种隔离级别无法确保数据的正确性。
b）已提交读：SQL  Server默认的隔离级别。该隔离级别能确保其他事物不能读取当前事物正在修改但未提交的记录。
c）重复读：这种隔离级别比较高，能确保其他事物不能修改当前事物中正在读取但未提交的数据。
d）可串行化：最高的隔离级别。事物之间完全隔离，事物之间完全隔离，事物之间按串行化的方式执行，所以在这种级别的隔离下不存在并行化的操作。要访问其他事物操作的数据，一定要等其他事物完成提交后才可以进行。

语法：SET  TRANSACTION  ISOLATION  LEVEL  READ  UNCOMMITTED|READ  COMMITTED|REPEATABLE  READ|SERIALIZABLE

如何在T-SQL编程中使用事物

1）开始事物：BEGIN  TRANSACTION。
2）提交事物：COMMIT  TRANSACTION。

3）回滚（撤销）事物：ROLLBACK  TRANSACTION。

常用事物有一下三种形式：显示事物、隐式事物、自动提交事物。
1）显示事物：用BEGIN  TRANSACTION 语句明确指定事物的开始。
2）隐式事物：通过设置SET  IMPLICIT_TRANSACTIONS  ON语句，将隐式事物模式设置为打开。当以隐式事物模式操作时，SQL  Server将在提交和回滚事物后自动启动新的事物。这种事物无法描述事物的开始，只提交或回滚每个事物。
3）自动提交事务：这是SQL  Server默认的事物模式，它将每个T-SQL语句都视为一个事物。如果成功执行，则自动提交；如果出现错误，则自动回滚。
在实际开发中使用最多的是显示事物，因为自动提交事物无法将多条语句作为一个独立的逻辑单元来处理。隐式事物无法明确控制事物的开始位置。

示例：

--购买5张“NBA篮球卡”
user CardSale
go
declare @cnt int
select @cnt=Count(*) from CardInf where CardTypeId=(Select id from CardType where CardTypeName='NBA篮球卡')
and CardStateId=(Select id from CardState where State='未售出')
if(@cnt>=5)
begin
    declare @balance money,@requiremoney money
    --用户账户余额
    select @balance =balance from UserInf where UserName='jack123'
    --购买点卡需要的金额
    select @requiremoney=5*price from CardType where Id=(select id from CardType where CardTypeName='NBA篮球卡')
    --折扣后实际需要的金额
    select @requiremoney =@requiremoney*(select discount from RoleInf where id=(select UserRoleId from UserInf where UserName='jack123'))
    if(@balance>=@requiremoney)
    begin
        --开始购卡
        begin transaction
        --5张卡号后面还需要使用，所以保存到变量中
        declare @temptable table (id int identity(1,1),CardNo nvarchar(20) insert into @temptable Select Top 5 cardno from CardInf where CardStateId =(select id from CardState where State='未售出') and CardTypeId=(select id from cardtype where CardTypeName='NBA篮球卡')）
        if(@@ERROR=0)
        begin
            declare @id int=1
            --循环购卡
            while(@id=<5)
            begin
                insert into ShopHistory values('jack123',(select CardNo from @temptable where id=@id),getdate())
                set @id+=1
            end
            if(@@ERROR=0)
            begin
                --将售出信息保存在购物车表“ShoppingCart”中
                insert into ShoppingCart values('jack123',(select id from CardType where CardTypeName='NBA篮球卡')，5)
                update CardInf set CardStateId=2 where CardNo in (select CardNo from @temptable)
                if(@@ERROR=0)
                begin
                    update UserInf set Balance-=@requiremoney where UserName='jack123'
                    print 'OK'
                end
            end
            if(@@ERROR=0)
            begin
                --提交事物
                commit transaction
            end
            else
            begin
                print '操作失误，事物回滚'
                rollback transaction
            end
        end
    end
end
go