SQL 死結–執行預存程序訪問表時出現

本人在SQL2005中做執行計畫任務時，try出來：事務(進程 ID 86)與另一個進程被死結在 鎖 資源上，並且已被選作死結犧牲品。請重新運行該事務。

在網上找到資料後才發現，原來是我同時執行的計劃任務中時，第一個訪問表的時候已經把此表關閉了，第二個也去訪問，這時出現如此的錯誤，所以後來想到計劃任務中的步驟，第一步驟執行一個預存程序，設定成功或失敗都執行第二步驟的預存程序。這樣解決了現有問題，資料剛處理時，資料量估計有10萬到20萬，下面還有從五分鐘到小時到天到月，通過需要的欄位計算方式觸發器觸發到需要的各個表中。

     當然，網上還有其他方式，在此摘錄以下以便自己日後查詢和翻閱：

===================================================================================

其實所有的死結最深層的原因就是一個:資源競爭 表現一:
    一個使用者A 訪問表A(鎖住了表A),然後又訪問表B
    另一個使用者B 訪問表B(鎖住了表B),然後企圖訪問表A

    這時使用者A由於使用者B已經鎖住表B，它必須等待使用者B釋放表B,才能繼續，好了他老人家就只好老老實實在這等了
    同樣使用者B要等使用者A釋放表A才能繼續這就死結了
解決方案：
    這種死結是由於你的程式的BUG產生的，除了調整你的程式的邏輯別無他法
    仔細分析你程式的邏輯，
    1：盡量避免同時鎖定兩個資源
    2: 必須同時鎖定兩個資源時，要保證在任何時刻都應該按照相同的順序來鎖定資源.
 
表現二:
    使用者A讀一條紀錄，然後修改該條紀錄
    這是使用者B修改該條紀錄
    這裡使用者A的事務裡鎖的性質由共用鎖定企圖上升到獨佔鎖(for update),而使用者B裡的獨佔鎖由於A有共用鎖定存在所以必須等A釋
放掉共用鎖定，而A由於B的獨佔鎖而無法上升的獨佔鎖也就不可能釋放共用鎖定，於是出現了死結。
    這種死結比較隱蔽，但其實在稍大點的項目中經常發生。
解決方案:
    讓使用者A的事務（即先讀後寫類型的操作),在select 時就是用Update lock
    文法如下：
    select * from table1 with(updlock) where ....

==========================

在聯機交易處理(OLTP)的資料庫應用系統中，多使用者、多任務的並發性是系統最重要的技術指標之一。為了提高並發性，目前大部分RDBMS都採用加鎖技術。然而由於現實環境的複雜性，使用加鎖技術又不可避免地產生了死結問題。因此如何合理有效地使用加鎖技術，最小化死結是開發聯機交易處理系統的關鍵。   
  死結產生的原因   
  在聯機交易處理系統中，造成死機主要有兩方面原因。一方面，由於多使用者、多任務的並發性和事務的完整性要求，當多個交易處理對多個資源同時訪問時，若雙方鎖定一部分資源但也都需要對方鎖定的資源時，無法在有限的時間內完全獲得所需的資源，就會處於無限的等待狀態，從而造成其對資源需求的死結。   
  另一方面，資料庫本身加鎖機制的實現方法不同，各資料庫系統也會產生其特殊的死結情況。如在Sybase  SQL  Server  11中，最小鎖為2K一頁的加鎖方法，而非行級鎖。如果某張表的記錄數少且記錄的長度較短(即記錄密度高，如應用系統中的系統配置表或系統參數表就屬於此類表)，被訪問的頻率高，就容易在該頁上產生死結。   
  幾種死結情況及解決方案   
  清算應用系統中，容易發生死結的幾種情況如下:     
  ●  不同的預存程序、觸發器、動態SQL語句段按照不同的順序同時訪問多張表;     
  ●  在交換期間添加記錄頻繁的表，但在該表上使用了非群集索引(non-clustered);     
  ●  表中的記錄少，且單條記錄較短，被訪問的頻率較高；   
  ●  整張表被訪問的頻率高(如代碼對照表的查詢等)。   
  以上死結情況的對應處理方法如下：   
  ●  在系統實現時應規定所有預存程序、觸發器、動態SQL語句段中，對多張表的操作總是使用同一順序。如：有兩個預存程序proc1、proc2，都需要訪問三張表zltab、z2tab和z3tab，如果proc1按照zltab、z2tab和z3tab的順序進行訪問，那麼，proc2也應該按照以上順序訪問這三張表。   
  ●  對在交換期間添加記錄頻繁的表，使用群集索引(clustered)，以減少多個使用者添加記錄到該表的最後一頁上，在表尾產生熱點，造成死結。這類表多為往來賬的流水表，其特點是在交換期間需要在表尾追加大量的記錄，並且對已添加的記錄不做或較少做刪除操作。   
  ●  對單張表中記錄數不太多，且在交換期間select或updata較頻繁的表可使用設定每頁最大行的辦法，減少資料在表中存放的密度，類比行級鎖，減少在該表上死結情況的發生。這類表多為資訊繁雜且記錄條數少的表。   
  如：系統配置表或系統參數表。在定義該表時添加如下語句：   
  with  max_rows_per_page=1   
  ●  在預存程序、觸發器、動態SQL語句段中，若對某些整張表select操作較頻繁，則可能在該表上與其他訪問該表的使用者產生死結。對於檢查帳號是否存在，但被檢查的欄位在檢查期間不會被更新等非關鍵語句，可以採用在select命令中使用at  isolation  read  uncommitted子句的方法解決。該方法實際上降低了select語句對整張表的鎖層級，提高了其他使用者對該表操作的並發性。在系統高負荷運行時，該方法的效果尤為顯著。   
  例如：   
  select*from  titles  at  isolation  read  uncommitted   
  ●  對流水號一類的順序數產生器欄位，可以先執行updata流水號欄位+1，然後再執行select擷取流水號的方法進行操作。   
  小結   
  筆者對同城清算系統進行壓力測試時，分別對採用上述最佳化方法和不採用最佳化方法的兩套系統進行測試。在其他條件相同的情況下，相同業務筆數、相同時間內，死結發生的情況如下：   
  採用最佳化方法的系統:  0次/萬筆業務;     
  不採用最佳化方法的系統：50～200次/萬筆業務。   
  所以，使用上述最佳化方法後，特別是在系統高負荷運行時效果尤為顯著。總之，在設計、開發資料庫應用系統，尤其是OLTP系統時，應該根據應用系統的具體情況，依據上述原則對系統分別最佳化，為開發一套高效、可靠的應用系統打下良好的基礎。   

============
--轉 
  if  exists  (select  *  from  dbo.sysobjects  where  id  =  object_id(N [dbo].[sp_who_lock] )  and  OBJECTPROPERTY(id,  N IsProcedure )  =  1) 
  drop  procedure  [dbo].[sp_who_lock] 
  GO 
  /*************************************************************************** 
  //    建立  :  fengyu    郵件  :  maggiefengyu@tom.com    日期  :2004-04-30 
  //    修改  :  從http://www.csdn.net/develop/Read_Article.asp?id=26566學習到並改寫     
  //    說明  :  查看資料庫裡阻塞和死結情況 
  ***************************************************************************/ 
  use  master 
  go 
  create  procedure  sp_who_lock 
  as 
  begin 
  declare  @spid  int,@bl  int, 
  @intTransactionCountOnEntry  int, 
                  @intRowcount  int, 
                  @intCountProperties  int, 
                  @intCounter  int 
   
  create  table  #tmp_lock_who  ( 
  id  int  identity(1,1), 
  spid  smallint, 
  bl  smallint) 
   
  IF  @@ERROR<>0  RETURN  @@ERROR 
   
  insert  into  #tmp_lock_who(spid,bl)  select    0  ,blocked 
      from  (select  *  from  sysprocesses  where    blocked>0  )  a   
      where  not  exists(select  *  from  (select  *  from  sysprocesses  where    blocked>0  )  b   
      where  a.blocked=spid) 
      union  select  spid,blocked  from  sysprocesses  where    blocked>0 
   
  IF  @@ERROR<>0  RETURN  @@ERROR 
     
  --  找到暫存資料表的記錄數 
  select  @intCountProperties  =  Count(*),@intCounter  =  1 
  from  #tmp_lock_who 
   
  IF  @@ERROR<>0  RETURN  @@ERROR 
   
  if @intCountProperties=0 
  select    現在沒有阻塞和死結資訊    as  message 
   
  --  迴圈開始 
  while  @intCounter  <=  @intCountProperties 
  begin 
  --  取第一條記錄 
  select  @spid  =  spid,@bl  =  bl 
  from  #tmp_lock_who  where  Id  =  @intCounter   
  begin 
    if  @spid  =0   
                          select    引起資料庫死結的是:    +  CAST(@bl  AS  VARCHAR(10))  +    進程號,其執行的SQL文法如下   
    else 
                          select    進程號SPID： +  CAST(@spid  AS  VARCHAR(10))+    被    +    進程號SPID： +  CAST(@bl  AS  VARCHAR(10))  + 阻塞,其當前進程執行的SQL文法如下   
    DBCC  INPUTBUFFER  (@bl  ) 
  end 
   
  --  迴圈指標下移 
  set  @intCounter  =  @intCounter  +  1 
  end 
   
   
  drop  table  #tmp_lock_who 
   
  return  0 
  end 
 
==========================
呵呵，解決死結，光查出來沒有多大用處，我原來也是用這個預存程序來清理死結的 
  我解決死結的方式主要用了： 
  1  最佳化索引 
  2  對所有的報表，非事務性的select  語句  在from  後都加了  with  (nolock)  語句 
  3  對所有的事務性更新盡量使用相同的更新順序來執行 
  現在已解決了死結的問題，希望能對你有協助

with  (nolock)的用法很靈活  可以說只要有  from的地方都可以加  with  (nolock)  標記來取消產生意象鎖，這裡  可以用在  delete  update,select  以及  inner  join  後面的from裡，對整個系統的效能提高都很有協助

==========================
use master --必須在master資料庫中建立
go

if exists (select * from dbo.sysobjects where id = object_id(N [dbo].[p_lockinfo] ) and OBJECTPROPERTY(id, N IsProcedure ) = 1)
drop procedure [dbo].[p_lockinfo]
GO

/*--處理死結

查看當前進程,或死結進程,並能自動殺掉死進程

因為是針對死的,所以如果有死結進程,只能查看死結進程
當然,你可以通過參數控制,不管有沒有死結,都只查看死結進程

感謝: caiyunxia,jiangopen 兩位提供的參考資訊

--鄒建 2004.4--*/

/*--調用樣本

exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1,  --是否殺掉死結的進程,1 殺掉, 0 僅顯示
@show_spid_if_nolock bit=1 --如果沒有死結的進程,是否顯示正常進程資訊,1 顯示,0 不顯示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),標誌,
進程ID=spid,線程ID=kpid,塊進程ID=blocked,資料庫ID=dbid,
資料庫名=db_name(dbid),使用者ID=uid,使用者名稱=loginame,累計CPU時間=cpu,
登陸時間=login_time,開啟事務數=open_tran, 進程狀態=status,
工作站名=hostname,應用程式名稱=program_name,工作站進程ID=hostprocess,
網域名稱=nt_domain,網卡地址=net_address
into #t from(
select 標誌= 死結的進程 ,
  spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
  status,hostname,program_name,hostprocess,nt_domain,net_address,
  s1=a.spid,s2=0
from master..sysprocesses a join (
  select blocked from master..sysprocesses group by blocked
  )b on a.spid=b.blocked where a.blocked=0
union all
select  |_犧牲品_> ,
  spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
  status,hostname,program_name,hostprocess,nt_domain,net_address,
  s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2

select @count=@@rowcount,@i=1

if @count=0 and @show_spid_if_nolock=1
begin
insert #t
select 標誌= 正常的進程 ,
  spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
  open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
end

if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
  declare @spid varchar(10),@標誌 varchar(10)
  while @i<=@count
  begin
  select @spid=進程ID,@標誌=標誌 from #t where id=@i
  insert #t1 exec( dbcc inputbuffer( +@spid+ ) )
  if @標誌= 死結的進程  exec( kill  +@spid)
  set @i=@i+1
  end
end
else
  while @i<=@count
  begin
  select @s= dbcc inputbuffer( +cast(進程ID as varchar)+ )  from #t where id=@i
  insert #t1 exec(@s)
  set @i=@i+1
  end
select a.*,進程的SQL語句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go