sql server dead lock

来源：互联网发布：imei号对应省份数据库编辑：程序博客网时间：2024/06/05 09:20

--sql server dead lock (学习与研究) sql server 2008 r2
--http://mahuidong0222.blog.163.com/blog/static/1044585200911225318604/(引用)
exec sp_configure
DBCC TRACEON (1222,-1)

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 4;
GO
RECONFIGURE WITH OVERRIDE;
GO

--1 create test table
CREATE TABLE [dbo].[LockTest](
[ID] [int] NULL,
[Name] [nvarchar](10) NULL
) ON [PRIMARY]

GO
--2 ecec proc
DBCC TRACEon (1222,-1)

--3 run sql on two windows
set transaction isolation level serializable
declare @count int
declare @tranname varchar(10)
set @count =1
while(@count <10)
begin
set @tranname='a'+convert(varchar,@count)
begin tran @tranname
    update dbo.LockTest set Name ='aaaa' where ID =1;
    waitfor delay '00:00:02'
commit tran @tranname
set @count =@count +1
end

set transaction isolation level serializable
declare @count int
declare @tranname varchar(10)
set @count =1
while(@count <10)
begin
set @tranname='B'+convert(varchar,@count)
begin tran @tranname
    update dbo.LockTest set Name ='BBBB' where ID =2;
    waitfor delay '00:00:02'
commit tran @tranname
set @count =@count +1
end

--4 check log and sql server profil

--5 trace off
DBCC TRACEoff (1222,-1)
--============================================================
DBCC TRACESTATUS
exec sp_who_lock

--每秒死锁数量---------------------------------------------------

SELECT *
FROM    sys.dm_os_performance_counters
WHERE   counter_name LIKE 'Number of Deadlo%';

--查询当前阻塞---------------------------------------------------
WITH    CTE_SID ( BSID, SID, sql_handle )
          AS ( SELECT   blocking_session_id ,
                        session_id ,
                        sql_handle
               FROM     sys.dm_exec_requests
               WHERE    blocking_session_id <> 0
               UNION ALL
               SELECT   A.blocking_session_id ,
                        A.session_id ,
                        A.sql_handle
               FROM     sys.dm_exec_requests A
                        JOIN CTE_SID B ON A.SESSION_ID = B.BSID
             )
    SELECT C.BSID ,
            C.SID ,
            S.login_name ,
            S.host_name ,
            S.status ,
            S.cpu_time ,
            S.memory_usage ,
            S.last_request_start_time ,
            S.last_request_end_time ,
            S.logical_reads ,
            S.row_count ,
            q.text
    FROM    CTE_SID C
            JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
    ORDER BY sid


--历史死锁查询(确定开启才可以)
SELECT REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
                     '<victim-list>',
                     '<deadlock><victim-list>'),
             '<process-list>',
             '</victim-list><process-list>')
FROM (select CAST(target_data as xml) as TargetData
        from sys.dm_xe_session_targets st
        join sys.dm_xe_sessions s
          on s.address = st.event_session_address
       where name = 'system_health' ) AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

--查询所有的死锁信息，当然如果出现内存瓶颈的时候能保存多久我不确定，如果死锁太多你无法顺利的找到，你想把结果减少一点，可以在每次查询死锁后使用：
ALTER EVENT SESSION system_health ON SERVER STATE = stop
go
ALTER EVENT SESSION system_health ON SERVER STATE = start

--保存成csv文件
<deadlock-list>
<deadlock><victim-list>
<victimProcess id="process5c3b708"/>
</victim-list><process-list>
   <process id="process5c3b708" taskpriority="0" logused="0" waitresource="OBJECT: 18:1663344990:0 " waittime="4929" ownerId="7825618" transactionname="B3" lasttranstarted="2013-09-26T09:29:33.557" XDES="0xd77dd950" lockMode="X" schedulerid="6" kpid="5340" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-09-26T09:29:29.533" lastbatchcompleted="2013-09-26T09:22:09.440" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="6O4NFQJ7YA5LWIQ" hostpid="13612" loginname="sa" isolationlevel="serializable (4)" xactid="7825618" currentdb="18" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
    <executionStack>
     <frame procname="" line="9" stmtstart="58" sqlhandle="0x02000000d09e0e03e6960a6056b81deeff2192c0efe3b1da">
     </frame>
     <frame procname="" line="9" stmtstart="420" stmtend="528" sqlhandle="0x020000004059cc11f483bbf741897850dcf590a2b292ddf4">
     </frame>
    </executionStack>
    <inputbuf>
  set transaction isolation level serializable
  declare @count int
  declare @tranname varchar(10)
  set @count =1
  while(@count <10)
  begin
  set @tranname='B'+convert(varchar @count)
  begin tran @tranname
   update dbo.LockTest set Name ='BBBB' where ID =2;
   waitfor delay '00:00:02'
  commit tran @tranname
   set @count =@count +1
  end     </inputbuf>
   </process>
   <process id="process5c1d708" taskpriority="0" logused="0" waitresource="OBJECT: 18:1663344990:0 " waittime="4929" ownerId="7825587" transactionname="a1" lasttranstarted="2013-09-26T09:29:31.883" XDES="0x802f1250" lockMode="X" schedulerid="3" kpid="5384" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-09-26T09:29:31.883" lastbatchcompleted="2013-09-26T08:54:14.807" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="6O4NFQJ7YA5LWIQ" hostpid="13612" loginname="sa" isolationlevel="serializable (4)" xactid="7825587" currentdb="18" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
    <executionStack>
     <frame procname="" line="9" stmtstart="58" sqlhandle="0x02000000d09e0e03e6960a6056b81deeff2192c0efe3b1da">
     </frame>
     <frame procname="" line="9" stmtstart="420" stmtend="528" sqlhandle="0x0200000039181934c682aaaf76581b314c058e1adc6716c7">
     </frame>
    </executionStack>
    <inputbuf>
  set transaction isolation level serializable
  declare @count int
  declare @tranname varchar(10)
  set @count =1
  while(@count <10)
  begin
  set @tranname='a'+convert(varchar @count)
  begin tran @tranname
   update dbo.LockTest set Name ='aaaa' where ID =1;
   waitfor delay '00:00:02'
  commit tran @tranname
   set @count =@count +1
  end     </inputbuf>
   </process>
</process-list>
<resource-list>
   <objectlock lockPartition="0" objid="1663344990" subresource="FULL" dbid="18" objectname="" id="lock1d17d8680" mode="IX" associatedObjectId="1663344990">
    <owner-list>
     <owner id="process5c1d708" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process5c3b708" mode="X" requestType="convert"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="1663344990" subresource="FULL" dbid="18" objectname="" id="lock1d17d8680" mode="IX" associatedObjectId="1663344990">
    <owner-list>
     <owner id="process5c3b708" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process5c1d708" mode="X" requestType="convert"/>
    </waiter-list>
   </objectlock>
</resource-list>
</deadlock>
</deadlock-list>
--http://www.111cn.net/database/mssqlserver/43563.htm(学习与分析死锁)

--语句深入与学习
--1
SELECT REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
                     '<victim-list>',
                     '<deadlock><victim-list>'),
             '<process-list>',
             '</victim-list><process-list>')
FROM (select CAST(target_data as xml) as TargetData
        from sys.dm_xe_session_targets st
        join sys.dm_xe_sessions s
          on s.address = st.event_session_address
       where name = 'system_health' ) AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
--2
select *
    from sys.dm_xe_session_targets st
    join sys.dm_xe_sessions s
      on s.address = st.event_session_address
-- where name = 'system_health

--http://blog.csdn.net/kevinsqlserver/article/details/7978036[SQL Server Extended Events使用1：查询运行时间久的SQL语句]
SELECT*FROM sys.server_event_sessions

--首先我们来看一下目前数据库系统所有请求情况======================================常用语句总结
select s.session_id, s.status,db_name(r.database_id) as database_name,
s.login_name,s.login_time, s.host_name,
c.client_net_address,c.client_tcp_port,s.program_name,
r.cpu_time, r.reads, r.writes,c.num_reads,c.num_writes,
s.client_interface_name,
s.last_request_start_time, s.last_request_end_time,
c.connect_time, c.net_transport, c.net_packet_size,
r.start_time, r.status, r.command,
r.blocking_session_id, r.wait_type,
r.wait_time, r.last_wait_type, r.wait_resource, r.open_transaction_count,
r.percent_complete,r.granted_query_memory
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
order by s.session_id
2. 哪个用户连接数最

--request info by user
select login_name,COUNT(0) user_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by login_name
order by 2 desc
3. 哪台机器发起到数据库的连接数最多：

--request info by hostname
select s.host_name,c.client_net_address,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by host_name,client_net_address
order by 3 desc
4. 这些连接在访问哪个库

--request info by databases
select db_name(r.database_id) as database_name,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by r.database_id
order by 2 desc

select s.status,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by s.status
order by 2 desc

--1. 查看数据库阻塞情况==================================================================
----------------------------------------Blocked Info----------------------------------
--记录当前阻塞信息
select t1.resource_type as [lock type] ,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req]                          -- lock requested
,t1.request_session_id as [waiter sid]                      -- spid of waiter
,t2.wait_duration_ms as [wait time]
,(select text from sys.dm_exec_requests as r with(nolock)                 --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r with(nolock)
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt    --- statement executing now
,t2.blocking_session_id as [blocker sid]                --- spid of blocker
,(select text from sys.sysprocesses as p with(nolock)    --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address;

--2. 查看阻塞其他进程的进程（阻塞源头）：====================================================

--阻塞其他session的session
select t2.blocking_session_id,COUNT(0) counts
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address
group by blocking_session_id
order by 2;
--3. 被阻塞时间最长的进程：==================================================================
--被阻塞时间最长的session
select top 10 t1.resource_type as [lock type] ,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req]                          -- lock requested
,t1.request_session_id as [waiter sid]                      -- spid of waiter
,t2.wait_duration_ms as [wait time]
,(select text from sys.dm_exec_requests as r with(nolock)                 --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r with(nolock)
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt    --- statement executing now
,t2.blocking_session_id as [blocker sid]                --- spid of blocker
,(select text from sys.sysprocesses as p with(nolock)    --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address
order by t2.wait_duration_ms desc;

SELECT sessions.nameAS SessionName,sevents.packageasPackageName,
sevents.name AS EventName,
sevents.predicate, sactions.nameAS ActionName, stargets.nameAS TargetName
FROM sys.server_event_sessionssessions
INNER JOIN sys.server_event_session_eventssevents
ON sessions.event_session_id= sevents.event_session_id
INNER JOIN sys.server_event_session_actionssactions
ON sessions.event_session_id= sactions.event_session_id
INNER JOIN sys.server_event_session_targetsstargets
ON sessions.event_session_id= stargets.event_session_id

--低效的sql
select
    highest_cpu_queries.plan_handle,
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
from
    (select top 50
        qs.plan_handle,
        qs.total_worker_time
    from
        sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) as highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

--其他网址
http://www.cnblogs.com/bhtfg538/archive/2011/01/21/1939706.html
http://msdn.microsoft.com/zh-cn/library/ms186265.aspx 跟踪的含义