SQL - Transaction & Transaction Lock

  • 5778
  • 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