正文
sqlserver死锁牺牲，sql死锁的简单例子
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
如何处理SQL Server死锁问题
死锁，简而言之，两个或者多个trans，同时请求对方正在请求的某个对象，导致双方互相等待。简单的例子如下：
trans1 trans2
------------------------------------------------------------------------
1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransaction
2.update table A 2.update table B
3.update table B 3.update table A
4.IDBConnection.Commit 4.IDBConnection.Commit
那么，很容易看到，如果trans1和trans2，分别到达了step3，那么trans1会请求对于B的X锁，trans2会请求对于A的X锁，而二者的锁在step2上已经被对方分别持有了。由于得不到锁，后面的Commit无法执行，这样双方开始死锁。
好，我们看一个简单的例子，来解释一下，应该如何解决死锁问题。
-- Batch #1
CREATE DATABASE deadlocktest
GO
USE deadlocktest
SET NOCOUNT ON
DBCC TRACEON (1222, -1)
-- 在SQL2005中，增加了一个新的dbcc参数，就是1222，原来在2000下，我们知道，可以执行dbcc
--traceon(1204,3605,-1)看到所有的死锁信息。SqlServer 2005中，对于1204进行了增强，这就是122
相关问答
Q1: 如何分析SQLServer中的deadlocktrace
首先我们来看一个简单的例子，大结构非常简单：
1，process-list显示了两个进程之间发生了死锁process60fb88和processd11902c8。
2，vistim-list显示了process60fb88被选为了牺牲者。
2，后面的resource-list显示了两个进程争取并导致死锁的资源。
[html] view plain copy
deadlock
victim-list
victimProcess id="process60fb88" /
/victim-list
process-list
process id="process60fb88" taskpriority="0" logused="0" waitresource="KEY: 9:72057597664231424 (7506ff9b7b0d)" waittime="4376" ownerId="2656658629" transactionname="SELECT" lasttranstarted="2014-04-09T23:01:35.743" XDES="0x80059940" lockMode="S" schedulerid="4" kpid="10640" status="suspended" spid="80" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-04-09T23:01:35.657" lastbatchcompleted="2014-04-09T23:01:35.657" clientapp=".Net SqlClient Data Provider" hostname="BODCPRODVSQL128" hostpid="10088" loginname="PROD\s-propdata" isolationlevel="read committed (2)" xactid="2656658629" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"
executionStack
frame procname="" line="9" stmtstart="336" stmtend="874" sqlhandle="0x030009003d00da3fa6087c0182a200000100000000000000" /
frame procname="" line="20" stmtstart="1022" stmtend="1206" sqlhandle="0x03000900941f284ed5929e00aba200000100000000000000" /
frame procname="" line="9" stmtstart="464" stmtend="642" sqlhandle="0x03000a006502e0715df5af00aba200000100000000000000" /
frame procname="" line="4" stmtstart="224" stmtend="420" sqlhandle="0x01000a00b6fca934509742900b0000000000000000000000" /
/executionStack
inputbuf
DECLARE @logText NVARCHAR(MAX)
EXEC IntegratedService_ProcessLatestCommand @logText OUTPUT
SELECT @logText /inputbuf
/process
process id="processd11902c8" taskpriority="0" logused="232" waitresource="KEY: 9:72057596808265728 (ed2e944beff9)" waittime="4379" ownerId="2656658630" transactionname="UPDATE" lasttranstarted="2014-04-09T23:01:35.743" XDES="0x80048570" lockMode="X" schedulerid="8" kpid="6620" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-04-09T23:01:34.650" lastbatchcompleted="2014-04-09T23:01:34.650" clientapp=".Net SqlClient Data Provider" hostname="BODCPRODVSQL128" hostpid="10088" loginname="PROD\s-propdata" isolationlevel="read committed (2)" xactid="2656658630" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"
executionStack
frame procname="" line="22" stmtstart="1230" stmtend="1496" sqlhandle="0x030009003d00da3fa6087c0182a200000100000000000000" /
frame procname="" line="20" stmtstart="1022" stmtend="1206" sqlhandle="0x03000900941f284ed5929e00aba200000100000000000000" /
frame procname="" line="9" stmtstart="464" stmtend="642" sqlhandle="0x03000a006502e0715df5af00aba200000100000000000000" /
frame procname="" line="4" stmtstart="224" stmtend="420" sqlhandle="0x01000a00b6fca934509742900b0000000000000000000000" /
/executionStack
inputbuf
DECLARE @logText NVARCHAR(MAX)
EXEC IntegratedService_ProcessLatestCommand @logText OUTPUT
SELECT @logText /inputbuf
/process
/process-list
resource-list
keylock hobtid="72057597664231424" dbid="9" objectname="" indexname="" id="lockc99859500" mode="X" associatedObjectId="72057597664231424"
owner-list
owner id="processd11902c8" mode="X" /
/owner-list
waiter-list
waiter id="process60fb88" mode="S" requestType="wait" /
/waiter-list
/keylock
keylock hobtid="72057596808265728" dbid="9" objectname="" indexname="" id="lock2f4de2d00" mode="S" associatedObjectId="72057596808265728"
owner-list
owner id="process60fb88" mode="S" /
/owner-list
waiter-list
waiter id="processd11902c8" mode="X" requestType="wait" /
/waiter-list
/keylock
/resource-list
/deadlock
下面是详细分析。
1，victim-list没什么可分析的。
2，process-list中关于各个process的详细信息很重要。
waitresource="KEY: 9:72057597664231424 (7506ff9b7b0d)"
当前process正在等待的资源。通常我们在resource-list中可以看到同样的信息。使用下面的sql查询等待的资源是什么：
下面使用的hobtid是heap or b-tree id的缩写。详细见sys.partotions的解释。
[sql] view plain copy
SELECT o.name, i.name
FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.indexes i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE p.hobt_id = 72057597664231
name name
--------------------------------------------------------------------
MatchService PK_Matcher_ID
从结果我们就可以知道，等待的资源是一个表MatchService的主键PK_Matcher_ID。考察另外一个process的waitresource我们可以得知等待的资源是同一个表的另外一个索引。至此我们找到了直接导致死锁的资源是什么。
同时可以看到两个process一个是x lock，一个是s lock。因此可以判定发生在该表上的一个修改语句和一个查询语句之间发生了死锁。
另外，上例中可以清晰的看到是keylock导致的死锁，因此查询partitions可以找到对应的object (sys.partitions contains a row for each partition of all
the tables and most types of indexes in the database.)。但有时是其他类型的资源发生了死锁，例如pagelock, waitresource="PAGE: 9:1:28440841" 。 9是dbid; 1是fileid; 28440841是pageid。对于这种情况，使用下面的语句查询对应的资源：
[sql] view plain copy
DBCC TRACEON(3604)
GO
DBCC PAGE (9, 1, 28440841)
GO
DBCC TRACEOFF(3604)
GO
从返回的Metadata: objectId找到对应的objectid。
3，再看process中的inputbuf。这个tag表明了process正在运行的语句，因此对于定位死锁非常重要。但这里有一个问题，比如
上例中，inputbuf是一个存储过程，其中又嵌套了很多其他的存储过程，但inputbuf是用户直接发出的sql，而我们需要在其中找出直接导致死
锁的语句并优化，从而解决或减少死锁。自此我们已经有的信息是：导致死锁的语句由inputbuf中的语句调用，同时导致死锁的语句必定是对表
MatchService的修改语句。如果存储过程很简单，到此DBA已经能够找到直接导致死锁的sql了，分析过程到此结束。而如果存储过程很复杂，则
需要进一步分析。
4，现在再进一步考察tag, executionStack。executionStack表明了死锁发生时，由inputbuf调用的一系列
sql。上例中有4条sql。同时仔细观察上例可以发生，两个process的executionStack是完全相同的，因此考察一个就可以了。另外，
如果procname不为空则直接得到了sql，但上例中该tag为空。
自此我们希望把executionStack中的所有sql显示出来。使用下面的sql找出sqlhandle对应的在内存中的sql。需要注意的
是，如果deadlock已经过去了一段时间，sqlhandle可能已经被从内存中清除掉了，这时就不可查了。还有sqlhandle是
varbinaryd，所以查询时不可加引号。
另外还有一个有趣的地方：和其他程序语言报错时一样，stack最上的一条是最直接的错误，后面的错误都是该错误的上一层错误(这么解释可能有点
乱，写过代码的同学能理解哈)。因此在上面说的存储过程调用存储过程的情况中，executionStack中第一条是直接导致死锁的sql，第二条是调
用该sql的sql，以此类推，最后一条理论上就是inputbuf中的sql。
[sql] view plain copy
SELECT sql_handle AS Handle,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS Text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where sql_handle = 0x030009003d00da3fa6087c0182a200000100000000000000
order by sql_handle
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x030009003D00DA3FA6087C0182A200000100000000000000SELECT
TOP 1 @matcherQueueID = lhs.MatcherService_MatcherQueue_ID,
@rootOperationUID = Root_Operation_UID FROM
MatcherService_MatcherQueue lhs WHERE lhs.Processing_State
= 'MATCHING' OR lhs.Processing_State = 'MATCHED' ORDER BY
Last_Execution_Date ASC
0x030009003D00DA3FA6087C0182A200000100000000000000
SELECT Top 1 @ticketID = OperationLog_ID FROM GEDemo.dbo.OperationLog
WHERE @rootOperationUID = Root_Operation_UID AND Status = 0
ORDER BY OperationLog_ID ASC
0x030009003D00DA3FA6087C0182A200000100000000000000
UPDATE MatcherService_MatcherQueue SET Last_Execution_Date =
GETDATE() WHERE MatcherService_MatcherQueue_ID = @matcherQueueID
注意看起来一个sql_handle有三条语句，原因是这三条sql是属于同一个存储过程的。
如果一个sql_handle包含的语句很多，比如是一个很长的存储过程，那么我们还可以使用一个有力的信息：executionStack中的
line
tag.这条语句表明了到底是哪一个sql直接导致了死锁。如果一条statement中又包含了很多表，那么还需要和死锁的资源结合起来判断是哪个表或
索引的数据发生了死锁。
Q2: SQLServer死锁的解除方法
SQL Server死锁使我们经常遇到的问题 下面就为您介绍如何查询SQL Server死锁 希望对您学习SQL Server死锁方面能有所帮助
SQL Server死锁的查询方法
exec master dbo p_lockinfo 显示死锁的进程 不显示正常的进程
exec master dbo p_lockinfo 杀死死锁的进程 不显示正常的进程
SQL Server死锁的解除方法
Create proc p_lockinfo
@kill_lock_spid bit= 是否杀掉死锁的进程 杀掉 仅显示
@show_spid_if_nolock bit= 如果没有死锁的进程 是否显示正常进程信息 显示 不显示
as
declare @count int @s nvarchar（ ） @i int
select id=identity（int ） 标志
进程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
s =a spid s =
from mastersysprocesses a join （
select blocked from mastersysprocesses group by blocked
）b on a spid=b blocked where a blocked=
union all
select |_牺牲品_
spid kpid blocked dbid uid loginame cpu login_time open_tran
status hostname program_name hostprocess nt_domain net_address
s =blocked s =
from mastersysprocesses a where blocked
）a order by s s
select @count=@@rowcount @i=
if @count= and @show_spid_if_nolock=
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 mastersysprocesses
set @count=@@rowcount
end
if @count
begin
create table #t （id int identity（ ） a nvarchar（ ） b Int EventInfo nvarchar（ ））
if @kill_lock_spid=
begin
declare @spid varchar（ ） @标志 varchar（ ）
while @i=@count
begin
select @spid=进程ID @标志=标志 from #t whereid=@i
insert #t exec（ dbcc inputbuffer（ +@spid+ ） ）
if @标志= 死锁的进程 exec（ kill +@spid）
set @i=@i+
end
end
else
while @i=@count
begin
select @s= dbcc inputbuffer（ +cast（进程ID as varchar）+ ） from #t whereid=@i
insert #t exec（@s）
set @i=@i+
end
select a * 进程的SQL语句=b EventInfo
from #t a join #t b on a id=b id
lishixinzhi/Article/program/SQLServer/201311/22183
Q3: 为什么在sql server中引入死锁机制?
前面两位兄弟回答的不是死锁，是正常的锁定。
死锁是这样形成的，假设有两个事物A和B
A事物在执行中需要更新两个表，假设为T1，T2，此时A已执行完T1，正在申请使用T2.
B事物也需要更新这两个表，但B事物先执行了T2,正在申请使用T1，
因为T1已被A事物锁定，所以B必须等待A事物执行完后释放锁，但A事物此时正在申请T2，而T2确被B事物先锁定了，需等待B事物完成后释放锁后才可获得T2的锁，此时死锁就发生了，如果没有死锁机制，这两个事物就会一直等下去。
sql
server会定期检查死锁，如果发现死锁，就会权衡两个事物，牺牲掉其中一个执行代价较小的事物，使另一个事物能继续执行。
要避免死锁的发生，有很多需要注意的，如
1.保持事物尽可能的简短。
2。事物更新的顺序尽量一致，如上例中A和B如果更新顺序都为T1，T2或T2，T1的话就不会发生死锁了。
3.可以修改锁的粒度，如页锁改为行锁
Q4: sqlserver怎么清除死锁
1、首先需要判断是哪个用户锁住了哪张表.
查询被锁表
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'
查询后会返回一个包含spid和tableName列的表.
其中spid是进程名,tableName是表名.
2.了解到了究竟是哪个进程锁了哪张表后,需要通过进程找到锁表的主机.
查询主机名
exec sp_who2 'xxx'
xxx就是spid列的进程,检索后会列出很多信息,其中就包含主机名.
3.通过spid列的值进行关闭进程.
关闭进程
declare @spid int
Set @spid = xxx --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)
PS:有些时候强行杀掉进程是比较危险的,所以最好可以找到执行进程的主机,在该机器上关闭进程.
关于sqlserver死锁牺牲和sql死锁的简单例子的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。
