其實所有的死鎖最深層的原因就是一個:資源競爭
表現(xiàn)一:
一個用戶A 訪問表A(鎖住了表A),然后又訪問表B
另一個用戶B 訪問表B(鎖住了表B),然后企圖訪問表A
這時用戶A由于用戶B已經(jīng)鎖住表B，它必須等待用戶B釋放表B,才能繼續(xù)，好了他老人家就只好老老實實在這等了
同樣用戶B要等用戶A釋放表A才能繼續(xù)這就死鎖了
解決方法：
這種死鎖是由于你的程序的BUG產生的，除了調整你的程序的邏輯別無他法
仔細分析你程序的邏輯，
1：盡量避免同時鎖定兩個資源
2: 必須同時鎖定兩個資源時，要保證在任何時刻都應該按照相同的順序來鎖定資源.
表現(xiàn)二:
用戶A讀一條紀錄，然后修改該條紀錄
這是用戶B修改該條紀錄
這里用戶A的事務里鎖的性質由共享鎖企圖上升到獨占鎖(for update),而用戶B里的獨占鎖由于A有共享鎖存在所以必須等A釋
放掉共享鎖，而A由于B的獨占鎖而無法上升的獨占鎖也就不可能釋放共享鎖，于是出現(xiàn)了死鎖。
這種死鎖比較隱蔽，但其實在稍大點的項目中經(jīng)常發(fā)生。
解決方法:
讓用戶A的事務（即先讀后寫類型的操作),在select 時就是用Update lock
語法如下：
select * from table1 with(updlock) where ....
==========================
在聯(lián)機事務處理(OLTP)的數(shù)據(jù)庫應用系統(tǒng)中，多用戶、多任務的并發(fā)性是系統(tǒng)最重要的技術指標之一。為了提高并發(fā)性，目前大部分RDBMS都采用加鎖技術。然而由于現(xiàn)實環(huán)境的復雜性，使用加鎖技術又不可避免地產生了死鎖問題。因此如何合理有效地使用加鎖技術，最小化死鎖是開發(fā)聯(lián)機事務處理系統(tǒng)的關鍵。
死鎖產生的原因
在聯(lián)機事務處理系統(tǒng)中，造成死機主要有兩方面原因。一方面，由于多用戶、多任務的并發(fā)性和事務的完整性要求，當多個事務處理對多個資源同時訪問時，若雙方已鎖定一部分資源但也都需要對方已鎖定的資源時，無法在有限的時間內完全獲得所需的資源，就會處于無限的等待狀態(tài)，從而造成其對資源需求的死鎖。
另一方面，數(shù)據(jù)庫本身加鎖機制的實現(xiàn)方法不同，各數(shù)據(jù)庫系統(tǒng)也會產生其特殊的死鎖情況。如在Sybase SQL Server 11中，最小鎖為2K一頁的加鎖方法，而非行級鎖。如果某張表的記錄數(shù)少且記錄的長度較短(即記錄密度高，如應用系統(tǒng)中的系統(tǒng)配置表或系統(tǒng)參數(shù)表就屬于此類表)，被訪問的頻率高，就容易在該頁上產生死鎖。
幾種死鎖情況及解決方法
清算應用系統(tǒng)中，容易發(fā)生死鎖的幾種情況如下:
● 不同的存儲過程、觸發(fā)器、動態(tài)SQL語句段按照不同的順序同時訪問多張表;
● 在交換期間添加記錄頻繁的表，但在該表上使用了非群集索引(non-clustered);
● 表中的記錄少，且單條記錄較短，被訪問的頻率較高；
● 整張表被訪問的頻率高(如代碼對照表的查詢等)。
以上死鎖情況的對應處理方法如下：
● 在系統(tǒng)實現(xiàn)時應規(guī)定所有存儲過程、觸發(fā)器、動態(tài)SQL語句段中，對多張表的操作總是使用同一順序。如：有兩個存儲過程proc1、proc2，都需要訪問三張表zltab、z2tab和z3tab，如果proc1按照zltab、z2tab和z3tab的順序進行訪問，那么，proc2也應該按照以上順序訪問這三張表。
● 對在交換期間添加記錄頻繁的表，使用群集索引(clustered)，以減少多個用戶添加記錄到該表的最后一頁上，在表尾產生熱點，造成死鎖。這類表多為往來賬的流水表，其特點是在交換期間需要在表尾追加大量的記錄，并且對已添加的記錄不做或較少做刪除操作。
● 對單張表中記錄數(shù)不太多，且在交換期間select或updata較頻繁的表可使用設置每頁最大行的辦法，減少數(shù)據(jù)在表中存放的密度，模擬行級鎖，減少在該表上死鎖情況的發(fā)生。這類表多為信息繁雜且記錄條數(shù)少的表。
如：系統(tǒng)配置表或系統(tǒng)參數(shù)表。在定義該表時添加如下語句：
with max_rows_per_page=1
● 在存儲過程、觸發(fā)器、動態(tài)SQL語句段中，若對某些整張表select操作較頻繁，則可能在該表上與其他訪問該表的用戶產生死鎖。對于檢查賬號是否存在，但被檢查的字段在檢查期間不會被更新等非關鍵語句，可以采用在select命令中使用at isolation read uncommitted子句的方法解決。該方法實際上降低了select語句對整張表的鎖級別，提高了其他用戶對該表操作的并發(fā)性。在系統(tǒng)高負荷運行時，該方法的效果尤為顯著。
例如：
select*from titles at isolation read uncommitted
● 對流水號一類的順序數(shù)生成器字段，可以先執(zhí)行updata流水號字段+1，然后再執(zhí)行select獲取流水號的方法進行操作。
小結
筆者對同城清算系統(tǒng)進行壓力測試時，分別對采用上述優(yōu)化方法和不采用優(yōu)化方法的兩套系統(tǒng)進行測試。在其他條件相同的情況下，相同業(yè)務筆數(shù)、相同時間內，死鎖發(fā)生的情況如下：
采用優(yōu)化方法的系統(tǒng): 0次/萬筆業(yè)務;
不采用優(yōu)化方法的系統(tǒng)：50～200次/萬筆業(yè)務。
所以，使用上述優(yōu)化方法后，特別是在系統(tǒng)高負荷運行時效果尤為顯著。總之，在設計、開發(fā)數(shù)據(jù)庫應用系統(tǒng)，尤其是OLTP系統(tǒng)時，應該根據(jù)應用系統(tǒng)的具體情況，依據(jù)上述原則對系統(tǒng)分別優(yōu)化，為開發(fā)一套高效、可靠的應用系統(tǒng)打下良好的基礎。
============
--轉
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
/***************************************************************************
// 創(chuàng)建 : fengyu 郵件 : maggiefengyu@tom.com 日期 :2004-04-30
// 修改 : 從http://www.csdn.net/develop/Read_Article.asp?id=26566學習到并改寫
// 說明 : 查看數(shù)據(jù)庫里阻塞和死鎖情況
***************************************************************************/
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
-- 找到臨時表的記錄數(shù)
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '現(xiàn)在沒有阻塞和死鎖信息' as message
-- 循環(huán)開始
while @intCounter <= @intCountProperties
begin
-- 取第一條記錄
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起數(shù)據(jù)庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '進程號,其執(zhí)行的SQL語法如下'
else
select '進程號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '進程號SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當前進程執(zhí)行的SQL語法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循環(huán)指針下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end
==========================
呵呵，解決死鎖，光查出來沒有多大用處，我原來也是用這個存儲過程來清理死鎖的
我解決死鎖的方式主要用了：
1 優(yōu)化索引
2 對所有的報表，非事務性的select 語句 在from 后都加了 with (nolock) 語句
3 對所有的事務性更新盡量使用相同的更新順序來執(zhí)行
現(xiàn)在已解決了死鎖的問題，希望能對你有幫助
with (nolock)的用法很靈活 可以說只要有 from的地方都可以加 with (nolock) 標記來取消產生意象鎖，這里 可以用在 delete update,select 以及 inner join 后面的from里，對整個系統(tǒng)的性能提高都很有幫助
==========================
use master --必須在master數(shù)據(jù)庫中創(chuàng)建
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
/*--處理死鎖
查看當前進程,或死鎖進程,并能自動殺掉死進程
因為是針對死的,所以如果有死鎖進程,只能查看死鎖進程
當然,你可以通過參數(shù)控制,不管有沒有死鎖,都只查看死鎖進程
感謝: 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,數(shù)據(jù)庫ID=dbid,
數(shù)據(jù)庫名=db_name(dbid),用戶ID=uid,用戶名=loginame,累計CPU時間=cpu,
登陸時間=login_time,打開事務數(shù)=open_tran, 進程狀態(tài)=status,
工作站名=hostname,應用程序名=program_name,工作站進程ID=hostprocess,
域名=nt_domain,網(wǎng)卡地址=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
本文來自CSDN博客，轉載請標明出處：http://blog.csdn.net/shaily/archive/2009/04/02/4043892.aspx