如何处理数据库死锁问题?

2022-03-26 DB DB, 优化, 死锁评论
仅SQL Server 2012及以上版本可用.
执行前请确保路径正确！
慢SQL定义：执行时间超过0.5秒即定义为慢SQL，会被捕获。
SSMS中新建查询窗口，将下面代码贴上后执行。
该代码会新建一个[YX_Monitor]库，库中包含[DeadlockDetail]、[SlowSqlDetail]、[SlowSqlReadLog]三个表
DeadlockDetail：死锁明细记录数据，可查看死锁相关信息。
SlowSqlDetail：慢SQL明细数据，可查看所有慢SQL的执行情况。
SlowSqlReadLog：监控读取记录表，仅用于监控识别数据读取。
-----------------------------------------------------------------------------------------
------------执行前请确保路径 D:\TraceFile  存在，如要更改路径请先更改后再执行------------
-----------------------------------------------------------------------------------------
/***
如不能远程到服务器，使用xp_cmdshell创建文件夹路径
-- 开启
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go
 
exec sys.xp_cmdshell 'dir D:\TraceFile' --查看文件夹
exec sys.xp_cmdshell 'mkdir D:\TraceFile' --新建文件夹
exec sys.xp_cmdshell 'rd D:\TraceFile' --删除文件夹
 
--关闭
exec sp_configure 'xp_cmdshell',0
go
reconfigure
go
exec sp_configure 'show advanced options',0
go
reconfigure
go
 
***/
 
----1.建库
use master
go
if(select name from sys.databases where name='YX_Monitor') is null
begin
    CREATE DATABASE YX_Monitor
 
 
    ALTER DATABASE [YX_Monitor] MODIFY FILE ( NAME = N'YX_Monitor', SIZE = 65536KB , FILEGROWTH = 65536KB )
    ALTER DATABASE [YX_Monitor] MODIFY FILE ( NAME = N'YX_Monitor_log', SIZE = 65536KB , FILEGROWTH = 65536KB )
end
go
 
----2.建表
use YX_Monitor
go
 
if object_id('YX_Monitor.dbo.SlowSqlReadLog') is null
BEGIN
    CREATE TABLE [dbo].[SlowSqlReadLog](
        [TransactionNumber] [bigint] IDENTITY(1,1) NOT NULL,
        [LogServer] [nvarchar](100) NULL,
        [LogTime] [datetime] NULL CONSTRAINT [DF_SlowSqlReadLog_LogTime]  DEFAULT (getdate()),
        [Last_Event_Time] [datetime2](7) NULL,
    CONSTRAINT [PK_SlowSqlReadLog] PRIMARY KEY CLUSTERED
    (
        [TransactionNumber] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
end
 
 
if object_id('YX_Monitor.dbo.SlowSqlDetail') is null
BEGIN
     
    CREATE TABLE [dbo].[SlowSqlDetail](
        [TransactionNumber] [bigint] NULL,
        [LogServer] [nvarchar](100) NULL,
        [EventTime] [datetime2](7) NULL,
        [EventName] [nvarchar](128) NULL,
        [statement] [nvarchar](max) NULL,
        [Sql_Text] [nvarchar](max) NULL,
        [Cpu] [bigint] NULL,
        [Logical_Reads] [bigint] NULL,
        [Physical_reads] [bigint] NULL,
        [Writes] [bigint] NULL,
        [Duration_ms] [bigint] NULL,
        [username] [nvarchar](128) NULL,
        [DatabaseName] [nvarchar](128) NULL,
        [ClientHostName] [nvarchar](128) NULL,
        [ClientAppName] [nvarchar](128) NULL,
        [SessionId] [int] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
    create clustered index CIX_SlowSqlDetail_TransactionNumber on SlowSqlDetail(TransactionNumber,EventTime)
end
 
if object_id('yx_monitor.dbo.DeadlockDetail') is null
begin
    CREATE TABLE [dbo].[DeadlockDetail](
        [EventTime] [datetime2](7) NULL,
        [LogServer] [varchar](30) NULL,
        [InputBuffer] [nvarchar](max) NULL,
        [lockMode] [varchar](10) NULL,
        [spid] [int] NULL,
        [hostname] [varchar](50) NULL,
        [clientapp] [varchar](100) NULL,
        [transactionname] [varchar](50) NULL,
        [status] [varchar](20) NULL,
        [waitresource] [varchar](200) NULL
    )
 
    create clustered index CIX_DeadlockDetail_EventTime on DeadlockDetail([EventTime],[LogServer])
end
 
go
 
 
----3.扩展事件慢SQL会话
DECLARE @sessionName NVARCHAR(100),@createAndBegin BIT,@fileLocation NVARCHAR(100),@sql NVARCHAR(max)
    ,@cpuFilter NVARCHAR(100),@durationFilter_s decimal(4,2),@durationFilter_us NVARCHAR(100),@maxSizeMB NVARCHAR(10),@rolloverNum NVARCHAR(10)
    ,@rpc_completed TINYINT,@sp_statement_completed TINYINT,@sql_batch_completed TINYINT,@sql_statement_completed TINYINT
 
 
SELECT @sessionName=N'DB_SlowSql'
    ,@fileLocation=N'D:\TraceFile\'+@sessionName+'.xel'
    ,@cpuFilter=1000
    ,@durationFilter_s=0.5
    ,@maxSizeMB=3
    ,@rolloverNum=1
    ,@createAndBegin=1
    ,@rpc_completed=1
    ,@sp_statement_completed=0
    ,@sql_batch_completed=1
    ,@sql_statement_completed=0
    ,@durationFilter_us=cast(@durationFilter_s*1000*1000 as int)
 
IF(@rpc_completed+@sp_statement_completed+@sql_batch_completed+@sql_statement_completed)=0
BEGIN
    RAISERROR('至少选择一种跟踪事件!',16,3)
    RETURN
END
 
IF exists(SELECT * FROM sys.server_event_sessions WHERE name=@SessionName)
BEGIN
    RAISERROR('警告：扩展事件会话 %s 已存在，无法创建重命名会话！该步骤已跳过！',10,1,@SessionName)
    RETURN
END
ELSE
BEGIN
 
SET @sql=N'
CREATE EVENT SESSION ['+@SessionName+'] ON SERVER
'
 
IF(@rpc_completed=1)
BEGIN
    SET @sql=@sql+'ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.request_id
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([cpu_time]>='+@cpuFilter+' and duration>='+@durationFilter_us+')),'
END
IF(@sp_statement_completed=1)
BEGIN
    SET @sql=@sql+'ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.request_id
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([cpu_time]>='+@cpuFilter+' and duration>='+@durationFilter_us+')),'
END
IF(@sql_batch_completed=1)
BEGIN
    SET @sql=@sql+'ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.request_id
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([cpu_time]>='+@cpuFilter+' and duration>='+@durationFilter_us+')),'
END
IF(@sql_statement_completed=1)
BEGIN
    SET @sql=@sql+'ADD EVENT sqlserver.sql_statement_completed (
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.request_id
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([cpu_time]>='+@cpuFilter+' and duration>='+@durationFilter_us+')),'
END
 
--去除最后的逗号
SELECT @sql=left(@sql,LEN(@sql)-1)
 
SET @sql=@sql+'ADD TARGET package0.event_file(SET filename=N'''+@fileLocation+''',max_file_size=('+@maxSizeMB+'),max_rollover_files=('+@rolloverNum+'))'
set @sql=@sql+'with(STARTUP_STATE=ON)'
 
 
IF(@CreateAndBegin=1)
    SELECT @sql=@sql+'
ALTER EVENT SESSION ['+@SessionName+'] ON SERVER STATE = start;'
 
PRINT @sql
EXEC(@sql)
 
end
 
GO
 
 
----4.扩展事件死锁会话
DECLARE @sessionName NVARCHAR(100),@createAndBegin BIT,@fileLocation NVARCHAR(100),@sql NVARCHAR(max)
    ,@maxSizeMB NVARCHAR(10),@rolloverNum NVARCHAR(10)
     
SELECT @sessionName=N'DB_Deadlock'
    ,@fileLocation=N'D:\TraceFile\'+@sessionName+'.xel'
    ,@maxSizeMB=3
    ,@rolloverNum=1
    ,@createAndBegin=1
 
 
 
IF exists(SELECT * FROM sys.server_event_sessions WHERE name=@SessionName)
BEGIN
    RAISERROR('警告：扩展事件会话 %s 已存在，无法创建重命名会话！该步骤已跳过！',10,1,@SessionName)
    RETURN
END
ELSE
BEGIN
 
SET @sql=N'
CREATE EVENT SESSION ['+@SessionName+'] ON SERVER
'
 
SET @sql=@sql+'ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.request_id
    ,sqlserver.session_id
    ,sqlserver.sql_text
    ,sqlserver.username))
'
SET @sql=@sql+'ADD TARGET package0.event_file(SET filename=N'''+@fileLocation+''',max_file_size=('+@maxSizeMB+'),max_rollover_files=('+@rolloverNum+'))'
set @sql=@sql+'with(STARTUP_STATE=ON)'
 
 
IF(@CreateAndBegin=1)
    SELECT @sql=@sql+'
ALTER EVENT SESSION ['+@SessionName+'] ON SERVER STATE = start;'
 
PRINT @sql
EXEC(@sql)
 
end
GO
 
----5.创建JOB -- 为确保作业正确，会删除老JOB然后新建JOB
USE [msdb]
GO
 
if(select count(1) from msdb.dbo.sysjobs where name='(每90秒) 读取监控文件')>0
begin
    declare @job_id uniqueidentifier
    select @job_id = job_id from msdb.dbo.sysjobs where name='(每90秒) 读取监控文件'
 
    EXEC msdb.dbo.sp_delete_job @job_id=@job_id, @delete_unused_schedule=1
end
go
 
/****** Object:  Job [(每90秒) 读取监控文件]    Script Date: 2022-04-13 10:56:51 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2022-04-13 10:56:51 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'(每90秒) 读取监控文件',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'(每90秒) 读取监控文件',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [slowsql]    Script Date: 2022-04-13 10:56:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'slowsql',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'use YX_Monitor
go
--pepare data
declare @fileLocation nvarchar(128)
 
select @fileLocation=cast(esf.value as nvarchar(128)) from sys.server_event_sessions es
inner join sys.server_event_session_fields esf
on es.event_session_id=esf.event_session_id
where esf.name=''filename''
and es.name =''DB_SlowSql''
select @fileLocation=left(@fileLocation,LEN(@fileLocation)-4)+''*.xel''
 
set QUOTED_IDENTIFIER on
 
if object_id(''tempdb..#tmp'') is not null
    drop table #tmp
 
;WITH events_cte AS (
    SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), xevents.event_data.value(''(event/@timestamp)[1]'', ''datetime2'')) AS [EventTime]
        ,xevents.event_data.value(''(event/@name)[1]'', ''nvarchar(128)'') AS [EventName]
        , xevents.event_data.value(''(event/data[@name="statement"]/value)[1]'', ''nvarchar(max)'') AS [statement]
        , xevents.event_data.value(''(event/action[@name="sql_text"]/value)[1]'', ''nvarchar(max)'') AS [Sql_Text]
        , xevents.event_data.value(''(event/data[@name="cpu_time"]/value)[1]'', ''bigint'') / 1000 AS [CPU]
        , xevents.event_data.value(''(event/data[@name="logical_reads"]/value)[1]'', ''bigint'') AS [Logical_Reads]
        , xevents.event_data.value(''(event/data[@name="physical_reads"]/value)[1]'', ''bigint'') AS [Physical_reads]
        , xevents.event_data.value(''(event/data[@name="writes"]/value)[1]'', ''bigint'') AS [Writes]
        , xevents.event_data.value(''(event/data[@name="duration"]/value)[1]'', ''bigint'') / 1000 AS [Duration_ms]
        , xevents.event_data.value(''(event/action[@name="username"]/value)[1]'', ''nvarchar(128)'') AS [username]
        , xevents.event_data.value(''(event/action[@name="database_name"]/value)[1]'', ''nvarchar(128)'') AS [DatabaseName]
        , xevents.event_data.value(''(event/action[@name="client_hostname"]/value)[1]'', ''nvarchar(128)'') AS [ClientHostName]
        , xevents.event_data.value(''(event/action[@name="client_app_name"]/value)[1]'', ''nvarchar(128)'') AS [ClientAppName]
        , xevents.event_data.value(''(event/action[@name="session_id"]/value)[1]'', ''nvarchar(128)'') AS [SessionId]
    FROM sys.fn_xe_file_target_read_file( @fileLocation, NULL, NULL, NULL)
    CROSS APPLY ( SELECT CAST(event_data AS XML ) AS event_data) xevents
)
SELECT *
into #tmp
FROM events_cte
ORDER BY [EventTime] DESC;
 
 
declare @LogSrever nvarchar(128),@last_Event_Time datetime2,@transactionNumber bigint,@before_last_Event_Time datetime2
 
select @before_last_Event_Time=(select top 1 last_Event_Time
    from SlowSqlReadLog
    where LogServer=@@SERVERNAME
    order by LogTime desc)
 
select @LogSrever=@@SERVERNAME,@last_Event_Time=(select top 1 EventTime 
    from #tmp
    ORDER BY [EventTime] DESC)
 
if (isnull(@before_last_Event_Time,'''')<@Last_event_time)
begin
    --SlowSqlReadLog
    insert into SlowSqlReadLog(LogServer,Last_event_time)
    select @LogSrever,@Last_event_time
 
    --SlowSqlDetail
    select @transactionNumber=SCOPE_IDENTITY()
 
    insert into SlowSqlDetail(
    TransactionNumber
    ,LogServer
    ,EventTime
    ,EventName
    ,statement
    ,Sql_Text
    ,Cpu
    ,Logical_Reads
    ,Physical_reads
    ,Writes
    ,Duration_ms
    ,username
    ,DatabaseName
    ,ClientHostName
    ,ClientAppName
    ,SessionId)
    select @transactionNumber
    ,@LogSrever
    ,EventTime
    ,EventName
    ,statement
    ,Sql_Text
    ,Cpu
    ,Logical_Reads
    ,Physical_reads
    ,Writes
    ,Duration_ms
    ,username
    ,DatabaseName
    ,ClientHostName
    ,ClientAppName
    ,SessionId
    from #tmp
    where EventTime between isnull(@before_last_Event_Time,'''') and @last_Event_Time
end
 
',
        @database_name=N'YX_Monitor',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [deadlock]    Script Date: 2022-04-13 10:56:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'deadlock',
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'use YX_Monitor
go
--pepare data
declare @fileLocation nvarchar(128)
 
select @fileLocation=cast(esf.value as nvarchar(128)) from sys.server_event_sessions es
inner join sys.server_event_session_fields esf
on es.event_session_id=esf.event_session_id
where esf.name=''filename''
and es.name =''DB_Deadlock''
select @fileLocation=left(@fileLocation,LEN(@fileLocation)-4)+''*.xel''
 
set QUOTED_IDENTIFIER on
 
if object_id(''tempdb..#tmpDeadLock'') is not null
    drop table #tmpDeadLock
 
select CAST(event_data AS XML ) AS event_data
into #tmpDeadLock
from  sys.fn_xe_file_target_read_file( @fileLocation, NULL, NULL, NULL)
 
--select * from #tmpDeadLock
 
declare @eventXML xml,@maxTime datetime2
 
select @maxTime=isnull(max(EventTime),'''')
from DeadlockDetail
 
DECLARE XML_Cursor CURSOR
LOCAL STATIC FORWARD_ONLY
READ_ONLY
FOR
SELECT event_data from #tmpDeadLock
OPEN XML_Cursor
FETCH NEXT FROM XML_Cursor INTO @eventXML
WHILE @@fetch_status = 0
BEGIN
    declare @process xml,@resource xml,@eventTime datetime2
 
    select @eventTime=DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), t1.c1.value(''@timestamp'', ''datetime2''))
    from @eventXML.nodes(''/event'') as T1(C1)
 
 
    if(@eventTime>@maxTime)
    begin
        select
        @process=t1.c1.query(''./process-list'')
        from @eventXML.nodes(''/event/data/value/deadlock'') as T1(C1)
 
        insert into DeadlockDetail
        select @eventTime AS [EventTime]
        ,@@SERVERNAME as [LogServer]
        ,T1.C1.value(''(./inputbuf)[1]'',''varchar(max)'') as inputbuffer
        ,T1.C1.value(''@lockMode'',''varchar(100)'') as lockMode
        ,T1.C1.value(''@spid'',''varchar(100)'') as spid
        ,T1.C1.value(''@hostname'',''varchar(100)'') as hostname
        ,T1.C1.value(''@clientapp'',''varchar(100)'') as clientapp
        ,T1.C1.value(''@transactionname'',''varchar(100)'') as transactionname
        ,T1.C1.value(''@status'',''varchar(100)'') as status
        ,T1.C1.value(''@waitresource'',''varchar(100)'') as waitresource
        from @process.nodes(''/process-list/process'') as T1(C1)
    end
 
    FETCH NEXT FROM XML_Cursor INTO @eventXML
END
CLOSE XML_Cursor
DEALLOCATE XML_Cursor
 
',
        @database_name=N'YX_Monitor',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'1',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=2,
        @freq_subday_interval=90,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20220101,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959,
        @schedule_uid=N'a53b9137-2256-4675-b5d1-fa13902cdc4f'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
本文链接： https://junjun.fun/22_03_26_数据库死锁问题跟踪/

版权声明： 本博客所有文章除特别声明外，均采用 CC BY 4.0 CN协议许可协议。转载请注明出处！
赵无敌Developer & Designer

苟利国家生死以,岂因祸福避趋之。