：點晴OA工作流查詢待辦工作流時，偶爾出現報錯提示：事務（進程ID）與另一個進程已被死鎖在lock通信緩沖區資源上，并且已被選作死鎖犧牲品。請重新運行該事務，如何解決？
QQ圖片20161217144149.png)
出現這個錯誤的原因是一個用戶訪問該頁面，觸發了更改系統相關數據庫表的動作（可能是系統后臺自動刷新用戶已讀狀態、或者用戶提交數據等），為了確保數據庫表更新成功，系統會自動鎖住該表不被其他用戶更改，這時候另外一個用戶也要訪問該頁面進行操作，也要更新數據庫表，此時，因為數據庫表已被同時訪問的用戶鎖定了，就會導致本用戶無法更新，其結果就會報以上錯誤。簡言之，這是系統中該頁面同時訪問用戶太多，產生了并發操作造成的。
其實所有的死鎖最深層的原因就是一個：資源競爭，程序設計開發時對大量用戶并發操作的處理考慮不周。
表現一:
一個用戶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系統時，應該根據應用系統的具體情況，依據上述原則對系統分別優化，為開發一套高效、可靠的應用系統打下良好的基礎。
============
--轉
/********************************************************
// 創建 :
// 日期 :
// 修改 :
//
// 說明 : 查看數據庫里阻塞和死鎖情況
********************************************************/
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
GO
==========================
呵呵，解決死鎖，光查出來沒有多大用處，我原來也是用這個存儲過程來清理死鎖的
我解決死鎖的方式主要用了：
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
/*--處理死鎖
查看當前進程,或死鎖進程,并能自動殺掉死進程
因為是針對死的,所以如果有死鎖進程,只能查看死鎖進程
當然,你可以通過參數控制,不管有沒有死鎖,都只查看死鎖進程
--鄒建 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
該文章在 2016/12/17 14:53:43 編輯過
400 186 1886
