SQL Server死鎖問題

liyihongcug

* 要：SQL Server死鎖問題是我們經(jīng)常會遇到的，下文就針對死鎖問題進(jìn)行了實例測試，供您參考學(xué)習(xí)之用。
    * 標(biāo)簽：SQL Server死鎖
*

SQL Server死鎖問題很值得我們?nèi)パ芯�，下面就作了一個相關(guān)方面的測試，看看究竟什么時候會出現(xiàn)SQL Server死鎖問題。

SQL Server死鎖測試
--增設(shè) 帳戶表_2
CREATE TABLE 帳戶表_2
(
帳號 CHAR(4),
余額 INT
)
GO
INSERT 帳戶表_2
SELECT 'C',100
UNION ALL
SELECT 'D',200

--在第一個連接中執(zhí)行以下語句
BEGIN TRAN
UPDATE 帳戶表 SET 余額=3 WHERE 帳號='A'
WAITFOR DELAY '00:00:10'
UPDATE 帳戶表_2 SET 余額=3 WHERE 帳號='C'
COMMIT TRAN

--在第二個連接中執(zhí)行以下語句
BEGIN TRAN
UPDATE 帳戶表_2 SET 余額=4 WHERE 帳號='C'
WAITFOR DELAY '00:00:10'
UPDATE 帳戶表 SET 余額=4 WHERE 帳號='A'
COMMIT TRAN

--刪除測試表
DROP TABLE 帳戶表,帳戶表_2

--同時執(zhí)行，系統(tǒng)會檢測出死鎖，第一個連接的事務(wù)可能正常執(zhí)行，SQL Server 終止第二個連接的事務(wù)(不涉及超時)。
--如果沒有出現(xiàn)死鎖，則在其它事務(wù)釋放鎖之前，請求鎖的事務(wù)被阻塞。
--LOCK_TIMEOUT 設(shè)置允許應(yīng)用程序設(shè)置語句等待阻塞資源的最長時間。

4、更新數(shù)據(jù)時候允許進(jìn)行插入
5、插入數(shù)據(jù)時不允許更新\讀取


SQL Server死鎖監(jiān)控的語句寫法
http://database.51cto.com  2010-11-09 16:20  佚名  互聯(lián)網(wǎng)  我要評論(0)

    * 摘要：多用戶同時操作時，可能會造成數(shù)據(jù)庫死鎖和阻塞，下文就教您SQL Server死鎖監(jiān)控的語句寫法，希望對您能有些許的幫助。
    * 標(biāo)簽：SQL Server死鎖
*

如果想要查出SQL Server死鎖的原因，下面就教您SQL Server死鎖監(jiān)控的語句寫法，如果您對此方面感興趣的話，不妨一看。

下面的SQL語句運行之后，便可以查找出SQLServer死鎖和阻塞的源頭。

查找出SQLServer的死鎖和阻塞的源頭 --查找出SQLServer死鎖和阻塞的源頭
use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
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
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0
select '引起數(shù)據(jù)庫死鎖的是:
'+ CAST(@bl AS VARCHAR(10)) + '進(jìn)程號,其執(zhí)行的SQL語法如下'
else
select '進(jìn)程號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '
進(jìn)程號SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當(dāng)前進(jìn)程執(zhí)行的SQL語法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur

查看當(dāng)前進(jìn)程,或死鎖進(jìn)程,并能自動殺掉死進(jìn)程 --查看當(dāng)前進(jìn)程,或死鎖進(jìn)程,并能自動殺掉死進(jìn)程
--因為是針對死的,所以如果有死鎖進(jìn)程,只能查看死鎖進(jìn)程。當(dāng)然,你可以通過參數(shù)控制,不管有沒有死鎖,都只查看死鎖進(jìn)程。
create proc p_lockinfo
@kill_lock_spid bit=1, --是否殺掉死鎖的進(jìn)程,1 殺掉, 0 僅顯示
@show_spid_if_nolock bit=1 --如果沒有死鎖的進(jìn)程,是否顯示正常進(jìn)程信息,1 顯示,0 不顯示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),標(biāo)志,
進(jìn)程ID=spid,線程ID=kpid,塊進(jìn)程ID=blocked,數(shù)據(jù)庫ID=dbid,
數(shù)據(jù)庫名=db_name(dbid),用戶ID=uid,用戶名=loginame,累計CPU時間=cpu,
登陸時間=login_time,打開事務(wù)數(shù)=open_tran, 進(jìn)程狀態(tài)=status,
工作站名=hostname,應(yīng)用程序名=program_name,工作站進(jìn)程ID=hostprocess,
域名=nt_domain,網(wǎng)卡地址=net_address
into #t from(
select 標(biāo)志='死鎖的進(jìn)程',
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 標(biāo)志='正常的進(jìn)程',
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),@標(biāo)志 varchar(10)
while @i<=@count
begin
select @spid=進(jìn)程ID,@標(biāo)志=標(biāo)志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @標(biāo)志='死鎖的進(jìn)程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(進(jìn)程ID as varchar)+')'
from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,進(jìn)程的SQL語句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go

exec p_lockinfo

unixandoracle

好東西，友情支持！

liyihongcug

工作中數(shù)據(jù)庫經(jīng)常出錯死鎖，并且還要要求解決當(dāng)前的死鎖，問題多多；

參照CSDN，中國風(fēng)(Roy)一篇死鎖文章并改進(jìn)了下；

/***********************************************************************************************************************

整理人：黑木崖上的蝸牛(lenolotus) 日期:2009.04.28

************************************************************************************************************************/

/***********************************************************************************************************************
阻塞：其中一個事務(wù)阻塞，其它事務(wù)等待對方釋放它們的鎖,同時會導(dǎo)致死鎖問題。 整理人：中國風(fēng)(Roy) 參照Roy_88的博客

http://blog.csdn.net/roy_88/archive/2008/07/21/2682044.aspx

日期:2008.07.20
************************************************************************************************************************/
--生成測試表Ta
if not object_id('Ta') is null
drop table Ta
go
create table Ta(ID int Primary key,Col1 int,Col2 nvarchar(10))
insert Ta
select 1,101,'A' union all
select 2,102,'B' union all
select 3,103,'C'
go
生成數(shù)據(jù):
/*
表Ta
ID Col1 Col2
----------- ----------- ----------
1 101 A
2 102 B
3 103 C (3 行受影響) */
1、將處理阻塞減到最少:
2、不要在事務(wù)中請求用戶輸入
3、在讀數(shù)據(jù)考慮便用行版本管理
4、在事務(wù)中盡量訪問最少量的數(shù)據(jù)
5、盡可能地使用低的事務(wù)隔離級別
阻塞1(事務(wù)):
--測試單表
-----------------------連接窗口1(update\insert\delete)------------------------------
begin tran
--update
update ta set col2='BB' where ID=2
--或insert
--begin tran
-- insert Ta values(4,104,'D')
--或delete
--begin tran
-- delete ta where ID=1

--rollback tran
-------------------------連接窗口2（查詢表）---------------------------------------------
begin tran
select * from ta
--rollback tran
--- --分析--------------------------------------------------
-->SQL SERVER 2005查詢死鎖進(jìn)程
select
request_session_id as spid,
resource_type,
db_name(resource_database_id) as dbName,
resource_description,
resource_associated_entity_id,
request_mode as mode,
request_status as Status
from
sys.dm_tran_locks
--Result:
/*
進(jìn)程ID 資源類型 數(shù)據(jù)庫 資源描述 資源關(guān)鏈ID 鎖類型 進(jìn)程狀態(tài)
----------- ------------- ------ -------------------- ----------------------------- ----- ------
59 DATABASE Gepro 0 S GRANT
58 DATABASE Gepro 0 S GRANT
57 DATABASE Gepro 0 S GRANT
56 DATABASE Gepro 0 S GRANT
58 PAGE Gepro 1:1904 72057594039435264 IS GRANT
57 PAGE Gepro 1:1904 72057594039435264 IX GRANT
58 OBJECT              Gepro 853578079 IS GRANT
57 OBJECT Gepro 853578079 IX GRANT
57 KEY Gepro (020068e8b274) 72057594039435264     X      GRANT
58 KEY Gepro (020068e8b274) 72057594039435264 S      WAIT
(9 行受影響)
*/
-->SQL SERVER 2000查詢死鎖進(jìn)程
SELECT DISTINCT
'進(jìn)程ID' = STR(a.spid, 4)
, '進(jìn)程ID狀態(tài)' = CONVERT(CHAR(10), a.status)
, '死鎖進(jìn)程ID' = STR(a.blocked, 2)
, '工作站名稱' = CONVERT(CHAR(10), a.hostname)
, '執(zhí)行命令的用戶' = CONVERT(CHAR(10), SUSER_NAME(a.uid))
, '數(shù)據(jù)庫名' = CONVERT(CHAR(10), DB_NAME(a.dbid))
, '應(yīng)用程序名' = CONVERT(CHAR(10), a.program_name)
, '正在執(zhí)行的命令' = CONVERT(CHAR(16), a.cmd)
, '登錄名' = a.loginame
, '執(zhí)行語句' = b.text
FROM master..sysprocesses a CROSS APPLY
sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.blocked IN ( SELECT blocked
FROM master..sysprocesses )
-- and blocked <> 0
ORDER BY STR(spid, 4)
--Result
/*
進(jìn)程ID 進(jìn)程ID   狀態(tài) 死鎖進(jìn)程ID 工作站名稱 執(zhí)行命令的用戶 數(shù)據(jù)庫名 應(yīng)用程序名 正在執(zhí)行的命令 登錄名 執(zhí)行語句
---- ---------- ------ ---------- ---------- ---------- ---------- ---------------- ---------------------------------------------------------------------- -------------------------
56 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442\Administrator SET STATISTICS XML OFF
57 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442\Administrator SET STATISTICS XML OFF
58 suspended 57 DC91229126 sa Gepro Microsoft SELECT DC91229126FF442\Administrator begin tran select * from ta
59 runnable 0 DC91229126 sa Gepro Microsoft SELECT DC91229126FF442\Administrator SELECT DISTINCT
60 sleeping 0 DC91229126 sa Gepro Toad for S AWAITING COMMAND DC91229126FF442\Administrator SET FMTONLY OFF;
62 sleeping 0 DC91229126 sa Gepro Toad for S AWAITING COMMAND DC91229126FF442\Administrator
*/
--查連接住信息(spid:57、5
select connect_time,last_read,last_write,most_recent_sql_handle
from sys.dm_exec_connections where session_id in(57,5
--查看會話信息
select login_time,host_name,program_name,login_name,last_request_start_time,last_request_end_time
from sys.dm_exec_sessions where session_id in(57,5
--查看阻塞正在執(zhí)行的請求
select
session_id,blocking_session_id,wait_type,wait_time,wait_resource
from
sys.dm_exec_requests
where
blocking_session_id>0--正在阻塞請求的會話的 ID。如果此列是 NULL，則不會阻塞請求
/*
session_id,blocking_session_id,wait_type,wait_time,wait_resource
58 57 LCK_M_S 2116437 KEY: 6:72057594039435264 (020068e8b274)
*/
--查看正在執(zhí)行的SQL語句
select
a.session_id,sql.text,a.most_recent_sql_handle
from
sys.dm_exec_connections a
cross apply
sys.dm_exec_sql_text(a.most_recent_sql_handle) as SQL --也可用函數(shù)fn_get_sql通過most_recent_sql_handle得到執(zhí)行語句
where
a.Session_id in(57,5
/*
session_id text
----------- -----------------------------------------------
57 SET STATISTICS XML OFF
58 begin tran select * from ta
*/

處理方法:
法一：
--連接窗口2
begin tran
select * from ta with (nolock)--用nolock:業(yè)務(wù)數(shù)據(jù)不斷變化中,如銷售查看當(dāng)月時可用。
法二：
阻塞2(索引):
處理方法: 加索引
create index IX_Ta_Col1 on Ta(Col1)--用COl1列上創(chuàng)索引,當(dāng)更新時條件:COl1=102會用到索引IX_Ta_Col1上得到一個排它鍵的范圍鎖
----------------------------連接窗口1 -------------------------------------------------
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--針對會話設(shè)置了 TRANSACTION ISOLATION LEVEL
--SERIALIZABLE 幻影讀、不可重復(fù)讀和臟讀都不允許
begin tran
update ta set col2='BB' where COl1=102
--rollback tran
-----------------------------連接窗口2------------------------------------------------
begin tran
select * from ta
法三：設(shè)置當(dāng)前查詢隔離級別
-----------------------------連接窗口2------------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ COMMITTED --設(shè)置會話已提交讀:指定語句不能讀取已由其他事務(wù)修改但尚未提交的數(shù)據(jù)
begin tran
select * from ta

1、事務(wù)要盡量短


--查看死鎖犧牲品

SELECT '進(jìn)程ID[SPID]' = STR(a.spid, 4)
, '進(jìn)程狀態(tài)' = CONVERT(CHAR(10), a.status)
, '分塊進(jìn)程ID' = STR(a.blocked, 2)
, '服務(wù)器名稱' = CONVERT(CHAR(10), a.hostname)
, '執(zhí)行用戶' = CONVERT(CHAR(10), SUSER_NAME(a.uid))
, '數(shù)據(jù)庫名' = CONVERT(CHAR(10), DB_NAME(a.dbid))
, '應(yīng)用程序名' = CONVERT(CHAR(10), a.program_name)
, '正在執(zhí)行的命令' = CONVERT(CHAR(16), a.cmd)
, '累計CPU時間' = STR(a.cpu, 7)
, 'IO' = STR(a.physical_io, 7)
, '登錄名' = a.loginame
, '執(zhí)行sql' = b.text
FROM    master..sysprocesses a CROSS APPLY
    sys.dm_exec_sql_text(a.sql_handle) b
WHERE   blocked <> 0
ORDER BY spid


--查看進(jìn)程運行狀況

   SELECT '進(jìn)程ID' = STR(spid, 4)
      , '進(jìn)程ID狀態(tài)' = CONVERT(CHAR(10), status)
      , '分塊進(jìn)程ID' = STR(blocked, 2)
      , '工作站名稱' = CONVERT(CHAR(10), hostname)
      , '執(zhí)行用戶' = CONVERT(CHAR(10), SUSER_NAME(uid))
      , '數(shù)據(jù)庫名' = CONVERT(CHAR(10), DB_NAME(dbid))
      , '應(yīng)用程序名' = CONVERT(CHAR(10), program_name)
      , '正在執(zhí)行的命令' = CONVERT(CHAR(16), cmd)
      , '累計CPU時間' = STR(cpu, 7)
      , 'IO' = STR(physical_io, 7)
      , '登錄名' = loginame
FROM    master..sysprocesses
    --where blocked = 0
ORDER BY spid


--blocked = 0表示沒有阻塞的進(jìn)程ID；

--查詢鎖類型

select 進(jìn)程id=a.req_spid
,數(shù)據(jù)庫=db_name(rsc_dbid)
,類型=case rsc_type when 1 then 'NULL 資源（未使用）'
when 2 then '數(shù)據(jù)庫'
when 3 then '文件'
when 4 then '索引'
when 5 then '表'
when 6 then '頁'
when 7 then '鍵'
when 8 then '擴展盤區(qū)'
when 9 then 'RID（行 ID)'
when 10 then '應(yīng)用程序'
end
,對象id=rsc_objid
,對象名=b.obj_name
,rsc_indid
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid

本文來自CSDN博客，轉(zhuǎn)載請標(biāo)明出處：http://blog.csdn.net/you_tube/archive/2009/07/18/4132850.aspx

liyihongcug

QL Server 2008中SQL應(yīng)用之－“死鎖(Deadlocking)” 收藏

此文于2011-03-01被推薦到CSDN首頁
此文于2011-02-28被推薦到CSDN首頁
如何被推薦？

當(dāng)一個用戶會話（會話1）已經(jīng)落定了一個資源，而另一個會話（會話2）想要修改該資源，并且會話2也鎖定了會話1想要修改的資源時，就會出現(xiàn)“死鎖”(deadlocking) 。在另一方釋放資源前，會話1和會話2都不可能繼續(xù)。所以，SQL Server會選擇死鎖中的一個會話作為“死鎖犧牲品”。

注意：死鎖犧牲品的會話會被殺死，事務(wù)會被回滾。

注意：死鎖 與正常的阻塞 是兩個經(jīng)常被混淆的概念。

發(fā)生死鎖的一些原因：

1、應(yīng)用程序以不同的次序訪問表。例如會話1先更新了客戶然后更新了訂單，而會話2先更新了訂單然后更新了客戶。這就增加了死鎖的可能性。

2、應(yīng)用程序使用了長時間的事務(wù)，在一個事務(wù)中更新很多行或很多表。這樣增加了行的“表面積”，從而導(dǎo)致死鎖沖突。

3、在一些情況下，SQL Server發(fā)出了一些行鎖，之后它又決定將其升級為表鎖。如果這些行在相同的數(shù)據(jù)頁面中，并且兩個會話希望同時在相同的頁面升級鎖粒度，就會產(chǎn)生死鎖。

一、 使用 SQL Server Profiler 分析死鎖  

http://msdn.microsoft.com/zh-cn/library/ms188246.aspx

二、使用跟蹤標(biāo)志位找出死鎖

本文主要介紹使用DBCC TRACEON、DBCC TRACEOFF和DBCC TRACESTATUS命令來確保死鎖被正確記錄到SQL Server Management Studio SQL日志中。這些命令用來啟用、關(guān)閉、和檢查跟蹤標(biāo)志位的狀態(tài)。

■   DBCC TRACEON， 啟用跟蹤標(biāo)志位。用法：DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]

詳細(xì)參看 MSDN:http://msdn.microsoft.com/zh-cn/library/ms187329.aspx

■   DBCC TRACESTATUS， 檢查跟蹤標(biāo)志位狀態(tài)。用法：DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] ) [ WITH NO_INFOMSGS ]

詳細(xì)參看 MSDN:http://msdn.microsoft.com/zh-cn/library/ms187809.aspx

■   DBCC TRACEOFF， 關(guān)閉跟蹤標(biāo)志位。用法：DBCC TRACEOFF (trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]

詳細(xì)參看 MSDN:http://msdn.microsoft.com/en-us/library/ms174401.aspx

下面我們模擬一個死鎖：

在第一個SQL查詢窗口執(zhí)行：

view plaincopy to clipboardprint?

   1. use AdventureWorks  
   2. go  
   3. SET NOCOUNT ON  
   4. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
   5. WHILE 1=1  
   6. BEGIN  
   7. BEGIN TRAN  
   8. UPDATE Purchasing.Vendor  
   9. SET CreditRating = 1  
  10. WHERE VendorID = 90  
  11. UPDATE Purchasing.Vendor  
  12. SET CreditRating = 2  
  13. WHERE VendorID = 91  
  14. COMMIT TRAN  
  15. END  

use AdventureWorks go SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE WHILE 1=1 BEGIN BEGIN TRAN UPDATE Purchasing.Vendor SET CreditRating = 1 WHERE VendorID = 90 UPDATE Purchasing.Vendor SET CreditRating = 2 WHERE VendorID = 91 COMMIT TRAN END

在第二個查詢窗口執(zhí)行：

view plaincopy to clipboardprint?

   1. use AdventureWorks  
   2. go  
   3. SET NOCOUNT ON  
   4. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
   5. WHILE 1=1  
   6. BEGIN  
   7. BEGIN TRAN  
   8. UPDATE Purchasing.Vendor  
   9. SET CreditRating = 2  
  10. WHERE VendorID = 91  
  11. UPDATE Purchasing.Vendor  
  12. SET CreditRating = 1  
  13. WHERE VendorID = 90  
  14. COMMIT TRAN  
  15. END  

use AdventureWorks go SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE WHILE 1=1 BEGIN BEGIN TRAN UPDATE Purchasing.Vendor SET CreditRating = 2 WHERE VendorID = 91 UPDATE Purchasing.Vendor SET CreditRating = 1 WHERE VendorID = 90 COMMIT TRAN END

等待幾秒后，其中一個查詢窗口會提示：
/*

Msg 1205, Level 13, State 51, Line 9
Transaction (Process ID 52) was deadlocked on lock resources with another process and
has been chosen as the deadlock victim. Rerun the transaction.
*/

此時，查看，SQL Server Management Studio的SQL 日志，發(fā)現(xiàn)死鎖事件沒有被記錄。

打開第三個查詢窗口，執(zhí)行：

view plaincopy to clipboardprint?

   1. DBCC TRACEON (1222, -1)  
   2. GO  
   3. DBCC TRACESTATUS  

DBCC TRACEON (1222, -1) GO DBCC TRACESTATUS

為了模擬另一個死鎖，將重啟動“勝利”的那個連接查詢（沒有被殺死的那個），然后重啟死鎖丟失的會話，幾秒后又出現(xiàn)另一個死鎖了。

死鎖發(fā)生后，停止另一個執(zhí)行的查詢。現(xiàn)在，SQL Server Management Studio的SQL 日志中包含了死鎖事件的詳細(xì)錯誤信息。包括相關(guān)的數(shù)據(jù)庫和對象、鎖定模式以及死鎖中的SQL語句。
邀月工作室

在檢查完畢后，關(guān)閉跟蹤標(biāo)志位：

view plaincopy to clipboardprint?

   1. DBCC TRACEON (1222, -1)  
   2. GO  
   3. DBCC TRACESTATUS  

DBCC TRACEON (1222, -1) GO DBCC TRACESTATUS

解析：

在本例中，我們使用跟蹤標(biāo)志位1222。跟蹤標(biāo)志位1222能把詳細(xì)的死鎖信息返回到SQL日志中，標(biāo)志位－1表示跟蹤標(biāo)志位1222應(yīng)該對所有SQL Server連接在全局中啟用。

三、設(shè)置死鎖優(yōu)先級

我們也可以使用SET DEADLOCK_PRIORITY命令來增加一個查詢會話被選為死鎖犧牲品的可能性。此命令的語法如下：

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }

http://msdn.microsoft.com/en-us/library/ms186736.aspx

例如，上例中，第一個查詢窗口如果使用以下的死鎖優(yōu)先級命令，幾乎可以肯定會被選為死鎖犧牲品。（正常情況下，SQL Server會把它認(rèn)為取消或回滾代價最小的連接作為默認(rèn)的死鎖犧牲品）：

view plaincopy to clipboardprint?

   1. SET NOCOUNT ON  
   2. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
   3. SET DEADLOCK_PRIORITY LOW  
   4. BEGIN TRAN  
   5. UPDATE Purchasing.Vendor  
   6. SET CreditRating = 1  
   7. WHERE VendorID = 2  
   8. UPDATE Purchasing.Vendor  
   9. SET CreditRating = 2  
  10. WHERE VendorID = 1  
  11. COMMIT TRAN  

SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET DEADLOCK_PRIORITY LOW BEGIN TRAN UPDATE Purchasing.Vendor SET CreditRating = 1 WHERE VendorID = 2 UPDATE Purchasing.Vendor SET CreditRating = 2 WHERE VendorID = 1 COMMIT TRAN

解析：可以將優(yōu)先級設(shè)為High或Normal,High 表示除非另一個會話有相同的優(yōu)先級，否則它不會被選為犧牲品。Norma l是默認(rèn)行為，如果另一個會話是High,它可能會被選中。如果另一個是Low,則它可以安全地不被選中。如果兩個會話有相同的優(yōu)先級，則回滾代價最小的事務(wù)會被選中。

關(guān)于死鎖的其他資源，可能會有補充：

happyhippy 的SQL Server死鎖總結(jié)，也總結(jié)的不錯。

http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html

liyihongcug

鎖的概述

一. 為什么要引入鎖

多個用戶同時對數(shù)據(jù)庫的并發(fā)操作時會帶來以下數(shù)據(jù)不一致的問題:

丟失更新
A,B兩個用戶讀同一數(shù)據(jù)并進(jìn)行修改,其中一個用戶的修改結(jié)果破壞了另一個修改的結(jié)果,比如訂票系統(tǒng)

臟讀
A用戶修改了數(shù)據(jù),隨后B用戶又讀出該數(shù)據(jù),但A用戶因為某些原因取消了對數(shù)據(jù)的修改,數(shù)據(jù)恢復(fù)原值,此時B得到的數(shù)據(jù)就與數(shù)據(jù)庫內(nèi)的數(shù)據(jù)產(chǎn)生了不一致

不可重復(fù)讀
A用戶讀取數(shù)據(jù),隨后B用戶讀出該數(shù)據(jù)并修改,此時A用戶再讀取數(shù)據(jù)時發(fā)現(xiàn)前后兩次的值不一致

并發(fā)控制的主要方法是封鎖,鎖就是在一段時間內(nèi)禁止用戶做某些操作以避免產(chǎn)生數(shù)據(jù)不一致

二 鎖的分類

鎖的類別有兩種分法：

1. 從數(shù)據(jù)庫系統(tǒng)的角度來看：分為獨占鎖（即排它鎖），共享鎖和更新鎖

MS-SQL Server 使用以下資源鎖模式。

鎖模式 描述
共享 (S) 用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如 SELECT 語句。
更新 (U) 用于可更新的資源中。防止當(dāng)多個會話在讀取、鎖定以及隨后可能進(jìn)行的資源更新時發(fā)生常見形式的死鎖。
排它 (X) 用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會同時同一資源進(jìn)行多重更新。
意向鎖 用于建立鎖的層次結(jié)構(gòu)。意向鎖的類型為：意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。
架構(gòu)鎖 在執(zhí)行依賴于表架構(gòu)的操作時使用。架構(gòu)鎖的類型為：架構(gòu)修改 (Sch-M) 和架構(gòu)穩(wěn)定性 (Sch-S)。
大容量更新 (BU) 向表中大容量復(fù)制數(shù)據(jù)并指定了 TABLOCK 提示時使用。

共享鎖
共享 (S) 鎖允許并發(fā)事務(wù)讀取 (SELECT) 一個資源。資源上存在共享 (S) 鎖時，任何其它事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)讀取數(shù)據(jù)，便立即釋放資源上的共享 (S) 鎖，除非將事務(wù)隔離級別設(shè)置為可重復(fù)讀或更高級別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享 (S) 鎖。

更新鎖
更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個事務(wù)組成，此事務(wù)讀取記錄，獲取資源（頁或行）的共享 (S) 鎖，然后修改行，此操作要求鎖轉(zhuǎn)換為排它 (X) 鎖。如果兩個事務(wù)獲得了資源上的共享模式鎖，然后試圖同時更新數(shù)據(jù)，則一個事務(wù)嘗試將鎖轉(zhuǎn)換為排它 (X) 鎖。共享模式到排它鎖的轉(zhuǎn)換必須等待一段時間，因為一個事務(wù)的排它鎖與其它事務(wù)的共享模式鎖不兼容；發(fā)生鎖等待。第二個事務(wù)試圖獲取排它 (X) 鎖以進(jìn)行更新。由于兩個事務(wù)都要轉(zhuǎn)換為排它 (X) 鎖，并且每個事務(wù)都等待另一個事務(wù)釋放共享模式鎖，因此發(fā)生死鎖。

若要避免這種潛在的死鎖問題，請使用更新 (U) 鎖。一次只有一個事務(wù)可以獲得資源的更新 (U) 鎖。如果事務(wù)修改資源，則更新 (U) 鎖轉(zhuǎn)換為排它 (X) 鎖。否則，鎖轉(zhuǎn)換為共享鎖。

排它鎖
排它 (X) 鎖可以防止并發(fā)事務(wù)對資源進(jìn)行訪問。其它事務(wù)不能讀取或修改排它 (X) 鎖鎖定的數(shù)據(jù)。

意向鎖
意向鎖表示 SQL Server 需要在層次結(jié)構(gòu)中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如，放置在表級的共享意向鎖表示事務(wù)打算在表中的頁或行上放置共享 (S) 鎖。在表級設(shè)置意向鎖可防止另一個事務(wù)隨后在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能，因為 SQL Server 僅在表級檢查意向鎖來確定事務(wù)是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務(wù)是否可以鎖定整個表。

意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

鎖模式 描述
意向共享 (IS) 通過在各資源上放置 S 鎖，表明事務(wù)的意向是讀取層次結(jié)構(gòu)中的部分（而不是全部）底層資源。
意向排它 (IX) 通過在各資源上放置 X 鎖，表明事務(wù)的意向是修改層次結(jié)構(gòu)中的部分（而不是全部）底層資源。IX 是 IS 的超集。
與意向排它共享 (SIX) 通過在各資源上放置 IX 鎖，表明事務(wù)的意向是讀取層次結(jié)構(gòu)中的全部底層資源并修改部分（而不是全部）底層資源。允許頂層資源上的并發(fā) IS 鎖。例如，表的 SIX 鎖在表上放置一個 SIX 鎖（允許并發(fā) IS 鎖），在當(dāng)前所修改頁上放置 IX 鎖（在已修改行上放置 X 鎖）。雖然每個資源在一段時間內(nèi)只能有一個 SIX 鎖，以防止其它事務(wù)對資源進(jìn)行更新，但是其它事務(wù)可以通過獲取表級的 IS 鎖來讀取層次結(jié)構(gòu)中的底層資源。

獨占鎖：只允許進(jìn)行鎖定操作的程序使用，其他任何對他的操作均不會被接受。執(zhí)行數(shù)據(jù)更新命令時，SQL Server會自動使用獨占鎖。當(dāng)對象上有其他鎖存在時，無法對其加獨占鎖。
共享鎖：共享鎖鎖定的資源可以被其他用戶讀取，但其他用戶無法修改它，在執(zhí)行Select時，SQL Server會對對象加共享鎖。
更新鎖：當(dāng)SQL Server準(zhǔn)備更新數(shù)據(jù)時，它首先對數(shù)據(jù)對象作更新鎖鎖定，這樣數(shù)據(jù)將不能被修改，但可以讀取。等到SQL Server確定要進(jìn)行更新數(shù)據(jù)操作時，他會自動將更新鎖換為獨占鎖，當(dāng)對象上有其他鎖存在時，無法對其加更新鎖。

2. 從程序員的角度看：分為樂觀鎖和悲觀鎖。
樂觀鎖：完全依靠數(shù)據(jù)庫來管理鎖的工作。
悲觀鎖：程序員自己管理數(shù)據(jù)或?qū)ο笊系逆i處理。

MS-SQLSERVER 使用鎖在多個同時在數(shù)據(jù)庫內(nèi)執(zhí)行修改的用戶間實現(xiàn)悲觀并發(fā)控制

三 鎖的粒度
鎖粒度是被封鎖目標(biāo)的大小,封鎖粒度小則并發(fā)性高,但開銷大,封鎖粒度大則并發(fā)性低但開銷小

SQL Server支持的鎖粒度可以分為為行、頁、鍵、鍵范圍、索引、表或數(shù)據(jù)庫獲取鎖

資源 描述
RID 行標(biāo)識符。用于單獨鎖定表中的一行。
鍵 索引中的行鎖。用于保護可串行事務(wù)中的鍵范圍。
頁 8 千字節(jié) (KB) 的數(shù)據(jù)頁或索引頁。
擴展盤區(qū) 相鄰的八個數(shù)據(jù)頁或索引頁構(gòu)成的一組。
表 包括所有數(shù)據(jù)和索引在內(nèi)的整個表。
DB 數(shù)據(jù)庫。

四 鎖定時間的長短

鎖保持的時間長度為保護所請求級別上的資源所需的時間長度。

用于保護讀取操作的共享鎖的保持時間取決于事務(wù)隔離級別。采用 READ COMMITTED 的默認(rèn)事務(wù)隔離級別時，只在讀取頁的期間內(nèi)控制共享鎖。在掃描中，直到在掃描內(nèi)的下一頁上獲取鎖時才釋放鎖。如果指定 HOLDLOCK 提示或者將事務(wù)隔離級別設(shè)置為 REPEATABLE READ 或 SERIALIZABLE，則直到事務(wù)結(jié)束才釋放鎖。

根據(jù)為游標(biāo)設(shè)置的并發(fā)選項，游標(biāo)可以獲取共享模式的滾動鎖以保護提取。當(dāng)需要滾動鎖時，直到下一次提取或關(guān)閉游標(biāo)（以先發(fā)生者為準(zhǔn)）時才釋放滾動鎖。但是，如果指定 HOLDLOCK，則直到事務(wù)結(jié)束才釋放滾動鎖。

用于保護更新的排它鎖將直到事務(wù)結(jié)束才釋放。
如果一個連接試圖獲取一個鎖，而該鎖與另一個連接所控制的鎖沖突，則試圖獲取鎖的連接將一直阻塞到：

將沖突鎖釋放而且連接獲取了所請求的鎖。

連接的超時間隔已到期。默認(rèn)情況下沒有超時間隔，但是一些應(yīng)用程序設(shè)置超時間隔以防止無限期等待

五 SQL Server 中鎖的自定義

1 處理死鎖和設(shè)置死鎖優(yōu)先級

死鎖就是多個用戶申請不同封鎖,由于申請者均擁有一部分封鎖權(quán)而又等待其他用戶擁有的部分封鎖而引起的無休止的等待

可以使用SET DEADLOCK_PRIORITY控制在發(fā)生死鎖情況時會話的反應(yīng)方式。如果兩個進(jìn)程都鎖定數(shù)據(jù)，并且直到其它進(jìn)程釋放自己的鎖時，每個進(jìn)程才能釋放自己的鎖，即發(fā)生死鎖情況。

2 處理超時和設(shè)置鎖超時持續(xù)時間。

@@LOCK_TIMEOUT 返回當(dāng)前會話的當(dāng)前鎖超時設(shè)置，單位為毫秒

SET LOCK_TIMEOUT 設(shè)置允許應(yīng)用程序設(shè)置語句等待阻塞資源的最長時間。當(dāng)語句等待的時間大于 LOCK_TIMEOUT 設(shè)置時，系統(tǒng)將自動取消阻塞的語句，并給應(yīng)用程序返回"已超過了鎖請求超時時段"的 1222 號錯誤信息

示例
下例將鎖超時期限設(shè)置為 1,800 毫秒。
SET LOCK_TIMEOUT 1800

3) 設(shè)置事務(wù)隔離級別。

4 ) 對 SELECT、INSERT、UPDATE 和 DELETE 語句使用表級鎖定提示。

5) 配置索引的鎖定粒度
可以使用 sp_indexoption 系統(tǒng)存儲過程來設(shè)置用于索引的鎖定粒度

六 查看鎖的信息

1 執(zhí)行 EXEC SP_LOCK 報告有關(guān)鎖的信息
2 查詢分析器中按Ctrl+2可以看到鎖的信息

七 使用注意事項

如何避免死鎖
1 使用事務(wù)時，盡量縮短事務(wù)的邏輯處理過程，及早提交或回滾事務(wù)；
2 設(shè)置死鎖超時參數(shù)為合理范圍，如：3分鐘-10分種；超過時間，自動放棄本次操作，避免進(jìn)程懸掛；
3 優(yōu)化程序，檢查并避免死鎖現(xiàn)象出現(xiàn)；
4 .對所有的腳本和SP都要仔細(xì)測試，在正是版本之前。
5 所有的SP都要有錯誤處理（通過@error）
6 一般不要修改SQL SERVER事務(wù)的默認(rèn)級別。不推薦強行加鎖

解決問題 如何對行 表 數(shù)據(jù)庫加鎖

八 幾個有關(guān)鎖的問題

1 如何鎖一個表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

2 鎖定數(shù)據(jù)庫的一個表

SELECT * FROM table WITH (HOLDLOCK)

加鎖語句：
sybase:
update 表 set col1=col1 where 1=0 ;
MSSQL:
select col1 from 表 (tablockx) where 1=0 ;
oracle:
LOCK TABLE 表 IN EXCLUSIVE MODE ；
加鎖后其它人不可操作，直到加鎖用戶解鎖，用commit或rollback解鎖


幾個例子幫助大家加深印象
設(shè)table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3

1）排它鎖
新建兩個連接
在第一個連接中執(zhí)行以下語句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
在第二個連接中執(zhí)行以下語句
begin tran
select * from table1
where B='b2'
commit tran

若同時執(zhí)行上述兩個語句，則select查詢必須等待update執(zhí)行完畢才能執(zhí)行即要等待30秒

2）共享鎖
在第一個連接中執(zhí)行以下語句
begin tran
select * from table1 holdlock -holdlock人為加鎖
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran

在第二個連接中執(zhí)行以下語句
begin tran
select A,C from table1
where B='b2'
update table1
set A='aa'
where B='b2'
commit tran

若同時執(zhí)行上述兩個語句，則第二個連接中的select查詢可以執(zhí)行
而update必須等待第一個事務(wù)釋放共享鎖轉(zhuǎn)為排它鎖后才能執(zhí)行 即要等待30秒

3）死鎖
增設(shè)table2(D,E)
D E
d1 e1
d2 e2
在第一個連接中執(zhí)行以下語句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30'
update table2
set D='d5'
where E='e1'
commit tran

在第二個連接中執(zhí)行以下語句
begin tran
update table2
set D='d5'
where E='e1'
waitfor delay '00:00:10'
update table1
set A='aa'
where B='b2'
commit tran

同時執(zhí)行，系統(tǒng)會檢測出死鎖，并中止進(jìn)程

補充一點:
Sql Server2000支持的表級鎖定提示

HOLDLOCK 持有共享鎖，直到整個事務(wù)完成，應(yīng)該在被鎖對象不需要時立即釋放，等于SERIALIZABLE事務(wù)隔離級別

NOLOCK 語句執(zhí)行時不發(fā)出共享鎖，允許臟讀 ，等于 READ UNCOMMITTED事務(wù)隔離級別

PAGLOCK 在使用一個表鎖的地方用多個頁鎖

READPAST 讓sql server跳過任何鎖定行，執(zhí)行事務(wù)，適用于READ UNCOMMITTED事務(wù)隔離級別只跳過RID鎖，不跳過頁，區(qū)域和表鎖

ROWLOCK 強制使用行鎖

TABLOCKX 強制使用獨占表級鎖，這個鎖在事務(wù)期間阻止任何其他事務(wù)使用這個表

UPLOCK 強制在讀表時使用更新而不用共享鎖

應(yīng)用程序鎖:
應(yīng)用程序鎖就是客戶端代碼生成的鎖，而不是sql server本身生成的鎖

處理應(yīng)用程序鎖的兩個過程

sp_getapplock 鎖定應(yīng)用程序資源

sp_releaseapplock 為應(yīng)用程序資源解鎖

注意: 鎖定數(shù)據(jù)庫的一個表的區(qū)別

SELECT * FROM table WITH (HOLDLOCK) 其他事務(wù)可以讀取表，但不能更新刪除

SELECT * FROM table WITH (TABLOCKX) 其他事務(wù)不能讀取表,更新和刪除