淺談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 [email protected],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級的鎖後面繼續探索

轉載于:https://www.cnblogs.com/johnx/p/4981006.html