浅谈SQL Transaction在请求中断后的行锁表锁

最近在维护Web Service接口时，由于数据数据量达到千万级别，接口调用不时出现错误让人不胜烦恼，经过性能测试查出瓶颈在数据库数据处理上，可着实忙了一番。相信众多程序猿和DBA都会头痛性能的问题，尤其是应用程序池的超时和假死造成的后台数据处理中断，带来的数据维护带来不小的麻烦。

 

在此背景下追查后台处理数据时请求中断成为目前棘手的问题，被吊打之余正努力想办法解决根本问题，小弟在解决防止数据处理时请求中断上，想到了Transaction的可行性，于是有了这篇随笔出来，废话比较多，下面开始吧

 

首先做点准备工作，建库建表

create database tranTest
go

use TranTest
go

create table TranTest(
ID int identity (1,1),
CreateTime datetime,
Value int
)
go

 

带transaction的 Proc ，不才sql小学级别，将就着看，大意是1秒钟插一条记录到TranTest表中，持续30秒

create proc [dbo].[testTrans]
as 
begin
declare @timeSpan datetime
declare @timeStart datetime
declare @val int = 1
    begin try 
        begin transaction 
            set @timeSpan = GETDATE();set @timeStart=getdate();
            while(DATEDIFF(second,@timeStart,getdate())<=30)
            begin
                while(DATEDIFF(SECOND,@timeSpan,getdate())=1)
                begin
                    set @timeSpan = GETDATE()
                    insert into TranTest values (getdate(),@val);
                    --update trantest set value=@val,createtime=getdate() where id=（select max(id) from trantest）
                    --set @val = @val +1
                end
            end
        commit transaction 
    end try 
    begin catch 
    select ERROR_NUMBER() as errornumber 
    rollback transaction 
    end catch
end

 

有了材料咱开始吧，首先要验证是transaction默认Level设置，在中断时的锁是行锁还是表锁

场景设置： 先运行3秒，强制中断，查询表中的数据，查看锁情况

exec  testTrans  走起，执行3秒强制结束查询分析器的执行

 

查看数据及锁的情况，带脏读查看表数据如下，3秒钟插入了3条，由于强制中止了执行，造成5条锁数据，其中重点关注id为54的锁(database级锁本文不做扩展)，此时不带脏读是查不出数据的(去掉with(nolock))，由于只有3条数据都被加锁，无法确定是否为

上表使用的sql如下，新开查询分析页使用exec ('kill 54')解除锁，再来查询表数据，会发现表中没有数据，此时sql server回滚了数据操作

select * from TranTest with(nolock)

SELECT request_session_id sessionid,
resource_type type,
resource_database_id dbid,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) objectname,
request_mode rmode,
request_status rstatus
FROM sys.dm_tran_locks
WHERE resource_type IN ('DATABASE', 'OBJECT')

解除锁
exec ('kill 54')

 

修改下Proc中的30，将30改为3运行到结束，先正常插入3条数据为后面测试做准备。再将proc更新成如下代码，单独修改单条数据

alter proc [dbo].[testTrans]
as 
begin
declare @timeSpan datetime
declare @timeStart datetime
declare @val int = 1
    begin try 
        begin transaction 
            set @timeSpan = GETDATE();set @timeStart=getdate();
            while(DATEDIFF(second,@timeStart,getdate())<=30)
            begin
                while(DATEDIFF(SECOND,@timeSpan,getdate())=1)
                begin
                    set @timeSpan = GETDATE()
                    --insert into TranTest values (getdate(),@val);
                    update trantest set value=@val,createtime=getdate() where id=（select max(id) from trantest）
                    set @val = @val +1
                end
            end
        commit transaction 
    end try 
    begin catch 
    select ERROR_NUMBER() as errornumber 
    rollback transaction 
    end catch
end

 

运行更新后的proc，3秒时强制终止执行，看下表值的前后变化，依然新开查询分析页，查询表中的值

 

我们使用脏读方式看到了ID为3的数据被更新了，再来看看锁的情况，此时的锁默认为行锁，验证方法很简单，使用select * from TranTest查询时查询被hang住了，使用select * from TranTest where id=1 时，数据就出来了，如果将 where 的条件改成 id=3查询依然无法返回，此时说明ID=3的这条数据被锁

 

下面继续看看回滚方面的验证，上图已经查到行锁ID为54，使用exec('kill 54')解除行锁， 再来查看表数据，ID=3的数据恢复到初始状态，回滚被执行了

 

有小伙伴可能会问，IIS的应用程序池连接中断和查询分析器强制终止是相同行为吗，要验证很简单，建一个空网站，调用上面的proc,设置执行超时时间为3秒，行为与查询分析器中一样，大家可以去验证一下，关于锁的Level问题和Database级的锁后面继续探索