SQL - Transaction & Transaction Lock | 馬久里的部落格 - 點部落

2015-04-16

SQL - Transaction & Transaction Lock

5776
0
SQL
2015-05-12

資料的不完整的後續修補動作付出的effort遠比開發來的更花時間，銀行的舉例只是兩個不同的資料表，萬一涉及更多個表，光補資料就費掉很多effort，把時間花在正確的地方才是程式設計師真正的價值。

由於派駐到它公司，因此展開了我的SQL人生，但是卻發現，資料匯入的Strore Procedure居然

沒有Transaction的防範，由於這個SP (Store Procedure)，是整個資料的主要建立來源，因此

可想而知，Tracnscation在這是辦法如何重要防護角色，我這樣說好了，如果以銀行為例子，

如果有筆資料是關聯好幾個Table，比方說

TableA：記錄使用者轉帳資訊

TableB：紀錄使用餘額

假設我在insert TableA(轉帳)的資訊之後，然後Update TableB使用者餘額，

這當中如果insert指令成功，但是在Update的時候失敗，這個時候就出大包了，

User在轉帳號，餘額居然沒有變化，這個時候我們要想到，當兩筆資料都確定

沒有失敗後，這筆交易才成立(才執行)，這就是這次要說的 Transaction，因此這裡我

做個實例如下

Table Structure

SQL Script

Begin try
	begin transaction
	   --Success
	   insert into _test (ID,comment) values (1,1)	
	   
	   --可以由此看出Type mismatch Error 
	   insert into _test (ID,comment) values ('hello','')
	   	   
	commit transaction
End try   
Begin catch
	print 'When sql error'
	if @@TRANCOUNT > 0	Rollback transaction
	print 'exec rollback or u can log in here'
end catch

Display as below

由此可看出當在第二筆insert產生錯誤時，就跳至 Begin catch執行Rollback，

如果你想的話還可以順便收集錯誤紀錄，是哪隻Store Procedure錯誤。

實際上Table並沒有資料匯入

另外分享一些個人的經驗談，由於在交易執行中時，確了確保整筆資料的正確性，

因此在交易過程中的Table會做類似Lock的動作，確保整筆交易完成後，和此

Table相關的操作才可以使用，舉個例子，如果在交易的過程中，出現錯誤，但是

又沒有跑到Catch 去執行Rollback，這種Error就會造成在交易過程中某些已經執行

到的SQL關聯到的Table被Lock住，或許你會很有疑問，如果有錯誤不就會跑到Catch

的區段執行RollBack嗎 ? 正常來說的確如此，但如果在Transaction中操作的是非實體

的Table(temp table)出現錯誤的時候，這時候就會產生例外，我直接用以上例子做個小

修改舉例。

Begin try
	begin transaction
	   --Success
	   insert into _test (ID,comment) values (1,1)
	   --根本不存在的Temp Table	   
	   select * from #temp
	   	   
	commit transaction
End try   
Begin catch
	print 'When sql error'
	if @@TRANCOUNT > 0	Rollback transaction
	print 'exec rollback or u can log in here'
end catch

以上執行，照之前的觀念都會認為會跳至Catch執行Rollback，執行時畫面確出現如下

OK，看起來不就是個找不到#temp的錯誤而已馬，有啥大不了，但是這個時候因為

Transcation還沒有結束，因此_test就Lock住了(連Select都沒辦法)。因此只要系統關聯

到_test的頁面也就全部hang住了(真可怕)。

因此也要提供另一帖藥去偵測那些Talbe 被Lock住。

方法一：直接執行Rollback transaction將交易回覆並釋放。

方法二(卡在權限上，未實測過)：

select  
    object_name(p.object_id) as TableName, 
    resource_type, resource_description
from
    sys.dm_tran_locks l
    join sys.partitions p on l.resource_associated_entity_id = p.hobt_id

回首頁