RDS SQL Server死锁(Deadlock)系列之三自动部署Profiler捕获死锁

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: # 问题引入 系列SQL Server死锁系列文章之二,讲的是如何手动部署Profiler来捕获死锁以及对死锁发时场景重现,这篇文章是将这个手动部署的过程自动化话,实现一键部署,既快捷方便,又简单适用。上一篇文章,参见:[使用Profiler捕获死锁](https://yq.aliyun.com/articles/73951?spm=5176.8091938.0.0.oDXHeW)。 # 自动

问题引入

系列SQL Server死锁系列文章之二,讲的是如何手动部署Profiler来捕获死锁以及对死锁发时场景重现,这篇文章是将这个手动部署的过程自动化话,实现一键部署,既快捷方便,又简单适用。上一篇文章,参见:使用Profiler捕获死锁

自动部署Profiler

废话不多说,直接上代码,只需要初始化下面的Profiler停止时间@stop_time即可:

use master
GO

set nocount on

declare 
    @trace_folder nvarchar(256)
    ,@trace_file nvarchar(256) 
    ,@max_files_size bigint
    
    ,@stop_time datetime
    ,@file_count int

    ,@int_filter_cpu int
    ,@int_filter_duration bigint
    ,@int_filter_spid int
    ,@set_trace_status int
;

select 
    @trace_folder=N'C:\Temp\perfmon'
    
    ,@max_files_size = 500            --max file size for each trace file
    ,@file_count = 10                --max file count
    
    ,@stop_time = '04/21/2017 22:10'    --null: stop trace manully; specify time (stop at the specify time)
    ,@int_filter_cpu = NULL                -- >= @int_filter_cpu ms will be traced. or else, skipped.
                                        --NULL: ignore this filter

    ,@int_filter_duration = NULL        --execution duration filter: millisecond
                                        --NULL: ignore this filter

    ,@int_filter_spid = NULL            --integer: specify a spid to trace
                                        --NULL: ignore this filter                
                                        
    ,@set_trace_status = 1    --0: Stops the specified trace.; 
                            --1: Starts the specified trace.;
                            --2: Closes the specified trace and deletes its definition from the server.;
;

/*

select * from sys.traces

*/
--private variables
declare
    @trace_id int
    ,@do int
    ,@loop int
    ,@trace_event_id int
    ,@trace_column_id int
    ,@return_code tinyint
    ,@return_decription varchar(200)
    ,@field_separator char(1)

;    
select
    @field_separator = ','            --trace columns list separator
;

IF right(ltrim(rtrim(@trace_folder)), 1 ) <> '\'
BEGIN
    SELECT 
        @trace_folder = ltrim(rtrim(@trace_folder)) + N'\' 
    ;
    exec sys.xp_create_subdir @trace_folder
END
;

select
    @trace_file = @trace_folder + REPLACE(@@SERVERNAME, N'\', N'')
;

IF @int_filter_spid IS NOT NULL
BEGIN
    select
        @trace_file = @trace_file + cast(@int_filter_spid as varchar)
    ;
END

--select @trace_file

select top 1
    @trace_id = id
from sys.traces
where path like @trace_file + N'%'

if @trace_id is not null
begin
    
    -- Start Trace (status 1 = start)
    EXEC sys.sp_trace_setstatus @trace_id, @set_trace_status

    return
end

if OBJECT_ID('tempdb..#trace_event','u') is not null
    drop table #trace_event
create table #trace_event
(
    id int identity(1,1) not null primary key
    ,trace_event_id int not null
    ,trace_column_id int not null
    ,event_name sysname null
    ,trace_column_name sysname null
)

;with trace_event
as
(        --select * from sys.trace_events where name like '%lock%' order by trace_event_id

    select 
        is_trace = 1 , event_name = 'Deadlock graph'
        ,trace_column_list = 'TextData,SPID,LoginName,StartTime,'
),
trace_column
as(
    select 
        *
        ,trace_column_list_xml = 
                                CAST(
                                        '<V><![CDATA[' 
                                                    + REPLACE(
                                                        REPLACE(
                                                                REPLACE(
                                                                            trace_column_list,CHAR(10),']]></V><V><![CDATA['
                                                                        ),@field_separator,']]></V><V><![CDATA['
                                                                ),CHAR(13),']]></V><V><![CDATA['
                                                            ) 
                                        + ']]></V>'
                                    as xml
                                )
    from trace_event
    where is_trace = 1
)
,data
as(
    select 
        trace_column = T.C.value('(./text())[1]','sysname')
        ,event_name
    from trace_column AS a
        CROSS APPLY trace_column_list_xml.nodes('./V') AS T(C)
)
INSERT INTO #trace_event
select 
    trace_event_id = ev.trace_event_id
    ,trace_column_id = col.trace_column_id
    ,a.event_name
    ,trace_column_name = a.trace_column
from data as a
    inner join sys.trace_columns as col
    on a.trace_column = col.name
    inner join sys.trace_events as ev
    on a.event_name = ev.name
where col.trace_column_id is not null
order by ev.trace_event_id
;

--select * from #trace_event

---private variables
select 
    @trace_id = 0
    ,@do = 1
    ,@loop = @@ROWCOUNT
    ,@trace_event_id = 0
    ,@trace_column_id = 0
    ,@return_code = 0
    ,@return_decription = ''
;

--create trace
exec @return_code = sys.sp_trace_create @traceid = @trace_id OUTPUT 
                                        , @options = 2  
                                        , @tracefile =  @trace_file
                                        , @maxfilesize = @max_files_size
                                        , @stoptime = @stop_time
                                        , @filecount =  @file_count
;

select 
    trace_id = @trace_id
    ,[current_time] = getdate()
    ,[stop_time] = @stop_time
;

set
    @return_decription = case @return_code
                                when 0 then 'No error.'
                                when 1 then 'Unknown error.'
                                when 10 then 'Invalid options. Returned when options specified are incompatible.'
                                when 12 then 'File not created.'
                                when 13 then 'Out of memory. Returned when there is not enough memory to perform the specified action.'
                                when 14 then 'Invalid stop time. Returned when the stop time specified has already happened.'
                                when 15 then 'Invalid parameters. Returned when the user supplied incompatible parameters.'
                            else ''
                            end
;

raiserror('Trace create with:
%s',10,1,@return_decription) with nowait

--loop set trace event & event column
while @do <= @loop
begin
    select top 1
        @trace_event_id = trace_event_id
        ,@trace_column_id = trace_column_id
    from #trace_event
    where id = @do
    ;
    
    --set trace event
    exec sys.sp_trace_setevent @trace_id, @trace_event_id, @trace_column_id, 1
    raiserror('exec sys.sp_trace_setevent @trace_id, %d, %d, 1',10,1,@trace_event_id,@trace_column_id) with nowait
    
    set @do = @do + 1;
end

-- Set any filter. 
--CPU >= 500/ cpu columnid = 18
IF @int_filter_cpu IS NOT NULL
    EXEC sys.sp_trace_setfilter @trace_id, 18, 0, 4, @int_filter_cpu

--duration filter/ duration columnid=13
IF @int_filter_duration IS NOT NULL
    EXEC sys.sp_trace_setfilter @trace_id, 13, 0, 4, @int_filter_duration

--spid filter/ spid columnid=12
IF @int_filter_spid IS NOT NULL
    exec sys.sp_trace_setfilter @trace_id, 12, 0, 0, @int_filter_spid


--applicationName not like 'SQL Server Profiler%'
EXEC sys.sp_trace_setfilter @trace_id, 10, 0, 7, N'SQL Server Profiler%'

-- Start Trace (status 1 = start)
EXEC sys.sp_trace_setstatus @trace_id, @set_trace_status
GO

--EXEC sys.sp_trace_setstatus 2, 0
--GO

select * from sys.traces

死锁测试的方法和过程参见文章RDS SQL Server死锁(Deadlock)系列之一使用DBCC捕获死锁中死锁测试部分,在此不再累述。测试完毕后,死锁信息会一键部署完毕的Profiler进程监控抓取到,并存放在@trace_folder目录下的一个以.trc结尾的文件中,比如:C:TempperfmonCHERISH-PC.trc。

分析trc文件

接下来的工作就是分析这个trc文件,直观Deadlock Graph图分析方法和前一篇文章非常类似,只需要双击打开这个文件即可,详情参见:使用Profiler捕获死锁
打开.trc文件,截图如下所示:
01.png

这里提供一种更为简单便捷,自动化分析方法,代码如下:

use master
go

-- declare variables.
declare 
        @file nvarchar(256)
;
select 
        @file = N'C:\Temp\perfmon\CHERISH-PC.trc'
;

WITH DATA
AS
(
    -- Analysis deadlock when saving into Trace File
    SELECT 
            --[TraceID] = @trace_id , 
            RowNumber = row_number() OVER (ORDER BY StartTime), -- assign a row number to each deadlock
            [DeadlockGraph]=case when TextData like '<deadlock-list%' then convert(xml, TextData) else null end, 
            * 
    from ::fn_trace_gettable(@file, default)
    where TextData like '<deadlock-list%'
)
,
deadlock
AS
(
        SELECT 
                        RowNumber
                        ,OwnerID = T.C.value('@id', 'varchar(50)')
                        ,SPid = T.C.value('(./@spid)[1]','int')
                        ,status = T.C.value('(./@status)[1]','varchar(10)')
                        ,Victim = case when T.C.value('@id', 'varchar(50)') = T.C.value('./../../@victim','varchar(50)') then 1 else 0 end
                        ,LockMode = T.C.value('@lockMode', 'varchar(20)')
                        ,DeadlockGraph
                        ,Inputbuf = T.C.value('(./inputbuf/text())[1]','varchar(max)')
                        ,Code = T.C.value('(./executionStack/frame/text())[1]','VARCHAR(max)')
                        ,SPName = T.C.value('(./executionStack/frame/@procname)[1]','sysname')
                        ,Hostname = T.C.value('(./@hostname)[1]','sysname')
                        ,Clientapp = T.C.value('(./@clientapp)[1]','varchar(max)')
                        ,LoginName = T.C.value('@loginname', 'varchar(20)')
                        ,Action = T.C.value('(./@transactionname)[1]','varchar(max)')
                        --,StartTime
                        ,TransactionTime = T.C.value('@lasttranstarted', 'datetime')
                        --,* 
        FROM DATA AS A
                CROSS APPLY DeadlockGraph.nodes('deadlock-list/deadlock/process-list/process') AS T(C)
)
,
keylock
AS
(
        SELECT
                OwnerID = T.C.value('./owner[1]/@id', 'varchar(50)')
                ,KeylockObject = T.C.value('./../@objectname', 'varchar(200)')
                ,Indexname = T.C.value('./../@indexname', 'varchar(200)')
                ,IndexLockMode = T.C.value('./../@mode', 'varchar(20)')
                --,owner = T.C.query('.')
        FROM DATA AS A
                CROSS APPLY DeadlockGraph.nodes('deadlock-list/deadlock/resource-list/keylock/owner-list') AS T(C)
)
SELECT 
        --A.OwnerID
        A.SPid
        ,is_Vitim = A.Victim
        --,A.DeadlockGraph
        ,A.SPName
        ,A.Code
        ,A.LockMode
        --,A.StartTime
        ,B.Indexname
        ,B.KeylockObject
        ,B.IndexLockMode
        ,A.Inputbuf
        ,A.Hostname
        ,A.LoginName
        ,A.Clientapp
        ,A.Action
        ,status
        ,A.TransactionTime
FROM deadlock AS A
        LEFT JOIN keylock AS B
        ON A.OwnerID = B.OwnerID
ORDER BY A.RowNumber,A.Victim

执行的结果如下,非常清晰的展示了死锁的信息:
02.png

最后总结

这篇文章展示的方法相对于前一篇文章,具备了一键部署,自动分析,快速结果的强大之处,是解决死锁问题的利器,希望能够帮助大家用好,用爽,快速彻底解决SQL Server Deadlock问题。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2天前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
|
20天前
|
SQL 存储 缓存
MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
本文详细介绍了MySQL中update SQL执行过程涉及的undoLog、redoLog和binLog三种日志的作用及其工作原理,包括它们如何确保数据的一致性和完整性,以及在事务提交过程中各自的角色。同时,文章还探讨了这些日志在故障恢复中的重要性,强调了合理配置相关参数对于提高系统稳定性的必要性。
|
18天前
|
SQL 关系型数据库 MySQL
MySQL 高级(进阶) SQL 语句
MySQL 提供了丰富的高级 SQL 语句功能,能够处理复杂的数据查询和管理需求。通过掌握窗口函数、子查询、联合查询、复杂连接操作和事务处理等高级技术,能够大幅提升数据库操作的效率和灵活性。在实际应用中,合理使用这些高级功能,可以更高效地管理和查询数据,满足多样化的业务需求。
56 3
|
21天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
23天前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
24天前
|
SQL Oracle 关系型数据库
SQL(MySQL)
SQL语言是指结构化查询语言,是一门ANSI的标准计算机语言,用来访问和操作数据库。 数据库包括SQL server,MySQL和Oracle。(语法大致相同) 创建数据库指令:CRATE DATABASE websecurity; 查看数据库:show datebase; 切换数据库:USE websecurity; 删除数据库:DROP DATABASE websecurity;
|
1月前
|
SQL 开发框架 .NET
突破T-SQL限制:利用CLR集成扩展RDS SQL Server的功能边界
CLR集成为SQL Server提供了强大的扩展能力,突破了T-SQL的限制,极大地拓展了SQL 的应用场景,如:复杂字符串处理、高性能计算、图像处理、机器学习集成、自定义加密解密等,使开发人员能够利用 .NET Framework的丰富功能来处理复杂的数据库任务。
|
2月前
|
SQL 关系型数据库 MySQL
|
2月前
|
SQL 运维 关系型数据库
MySQL 运维 SQL 备忘
MySQL 运维 SQL 备忘录
52 1
|
2月前
|
SQL 存储 关系型数据库
SQL文件导入MySQL数据库的详细指南
数据库中的数据转移是一项常规任务,无论是在数据迁移过程中,还是在数据备份、还原场景中,导入导出SQL文件显得尤为重要。特别是在使用MySQL数据库时,如何将SQL文件导入数据库是一项基本技能。本文将详细介绍如何将SQL文件导入MySQL数据库,并提供一个清晰、完整的步骤指南。这篇文章的内容字数大约在
373 1