事务隔离级别与锁

认真的把相关资料分析和整理了下，思路缕清楚了，也把几个问题制造出来，供大家参考下，有问题帮忙指出来！

 

     创建表lock，有记录

                   

A1	A2
10	110

     

   l 脏读

      出现问题的情况

      先运行事务Ⅰ，十秒结束前运行事务Ⅱ，十秒后在运行事务Ⅱ，出现脏读

   --事务Ⅰ

        begin tran

        update lock set A2 = 20 where A1 = 10

        waitfor delay '00:00:10'

        rollback tran

    --事务Ⅱ

        SET TRANSACTION ISOLATION LEVEL READ unCommitted

        select * from lock where A1 = 10

现象：

        原表数据

 

         

        运行事务Ⅰ后10秒内运行事务Ⅱ，结果

        

        十秒后在运行事务Ⅱ

            

     

        用提交读隔离级别解决

    --事务Ⅰ

        SET TRANSACTION ISOLATION LEVEL READ Committed

        begin tran

         select * from  lock WHERE A1=10

        commit tran

    --事务Ⅱ

SET TRANSACTION ISOLATION LEVEL READ Committed

        select * from lock where A1 = 10

现象：

        原表数据

        

        运行事务Ⅰ后10秒内再运行事务Ⅱ，结果

        事务Ⅱ进入等待

        

        直到10秒后出现结果

        

分析：

        1.隔离级别为提交读的事务在每次查询时都会申请获取共享锁，而未提交读则不会申请，而是直接执行语句

        2. 在提交读隔离级别下，事务读取数据时，对数据加上共享锁，查询结束后立刻释放共享锁（共享锁的锁定时间与事务的隔离级别有关，见注1），在其他一个或者多个事务对某数据拥有共享锁期间，无法获得排它锁，即无法对数据进行修改。

        3.当其他事务对数据不拥有共享锁时事务对数据进行更新操作，将获得排它锁，直到事务提交才将锁释放。

        4.若某一个事务获得排他锁时，则其他事务是不能获得共享锁的（即排他锁与共享锁不能兼容）

 

 

所以提交读就保证了不会出现脏读的情况

 

   l 不可重复读

 

出现问题的情况

        T1运行到一半，T2运行完成之后T1才将事务提交

        --事务T1

        begin tran

        select * from lock where A1=10

        waitfor delay '00:00:10'

       select * from lock where A1=10

        commit tran

 

        --事务T2

        begin tran

       update lock set A2 = 70 where A1 = 10

        commit tran

结果为

        

使用可重复读(repeatable read)则可以解决这个问题

    --事务T1

        SET TRANSACTION ISOLATION LEVEL repeatable read

        begin tran

       select * from lock where A1=10

       waitfor delay '00:00:10'

       select * from lock where A1=10

        commit tran

    --事务T2

        begin tran

        update lock set A2 = 70 where A1 = 10

        commit tran

可重复读的上锁情况是

事务在查询时候获取共享锁，直到事务提交才将锁释放，在此期间其他事务无法获得排他锁，所以无法进行更新操作，进入等待，知道其他事务将共享锁释放。这样避免了问题

 

 

l幻读

运行T3后运行在2--10秒内运行事务T4

    --事务T3

        begin tran

       select * from lock

       waitfor delay '00:00:10'

       select * from lock

        commit tran

    --事务T4

        begin tran

        Insert into lock values(99,99)

        commit tran

        

可串行读(serializable)

     仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

 

      总结：隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、虚读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

 

注1：

共享锁的锁定时间与事务的隔离级别有关，如果隔离级别为Read Committed的默认级别，

只在读取(select)的期间保持锁定，即在查询出数据以后就释放了锁；如果隔离级别为

更高的Repeatable read或Serializable，直到事务结束才释放锁。另说明，如果select

语句中指定了HoldLock提示，则也要等到事务结束才释放锁。