本文共 6941 字,大约阅读时间需要 23 分钟。
作中数据库经常出错死锁,并且还要要求解决当前的死锁,问题多多;
参照CSDN,中国风(Roy)一篇死锁文章并改进了下;
/***********************************************************************************************************************
整理人:黑木崖上的蜗牛(lenolotus) 日期:2009.04.28
************************************************************************************************************************/
/***********************************************************************************************************************
阻塞:其中一个事务阻塞,其它事务等待对方释放它们的锁,同时会导致死锁问题。 整理人:中国风(Roy) 参照Roy_88的博客
日期: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 生成数据: /* 表Ta ID Col1 Col2 ----------- ----------- ---------- 1 101 A 2 102 B 3 103 C (3 行受影响) */ 1、将处理阻塞减到最少:2、不要在事务中请求用户输入 3、在读数据考虑便用行版本管理 4、在事务中尽量访问最少量的数据 5、尽可能地使用低的事务隔离级别 阻塞1(事务): --测试单表 -----------------------连接窗口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查询死锁进程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:/* 进程ID 资源类型 数据库 资源描述 资源关链ID 锁类型 进程状态----------- ------------- ------ -------------------- ----------------------------- ----- ------ 59 DATABASE Gepro 0 S GRANT58 DATABASE Gepro 0 S GRANT57 DATABASE Gepro 0 S GRANT56 DATABASE Gepro 0 S GRANT58 PAGE Gepro 1:1904 72057594039435264 IS GRANT57 PAGE Gepro 1:1904 72057594039435264 IX GRANT58 OBJECT Gepro 853578079 IS GRANT57 OBJECT Gepro 853578079 IX GRANT57 KEY Gepro (020068e8b274) 72057594039435264 X GRANT58 KEY Gepro (020068e8b274) 72057594039435264 S WAIT(9 行受影响) */ -->SQL SERVER 2000查询死锁进程SELECT DISTINCT'进程ID' = STR(a.spid, 4), '进程ID状态' = CONVERT(CHAR(10), a.status), '死锁进程ID' = STR(a.blocked, 2), '工作站名称' = CONVERT(CHAR(10), a.hostname), '执行命令的用户' = CONVERT(CHAR(10), SUSER_NAME(a.uid)), '数据库名' = CONVERT(CHAR(10), DB_NAME(a.dbid)), '应用程序名' = CONVERT(CHAR(10), a.program_name), '正在执行的命令' = CONVERT(CHAR(16), a.cmd), '登录名' = a.loginame, '执行语句' = b.textFROM master..sysprocesses a CROSS APPLYsys.dm_exec_sql_text(a.sql_handle) bWHERE a.blocked IN ( SELECT blockedFROM master..sysprocesses )-- and blocked <> 0ORDER BY STR(spid, 4)--Result/*进程ID 进程ID 状态 死锁进程ID 工作站名称 执行命令的用户 数据库名 应用程序名 正在执行的命令 登录名 执行语句---- ---------- ------ ---------- ---------- ---------- ---------- ---------------- ---------------------------------------------------------------------- ------------------------- 56 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442/Administrator SET STATISTICS XML OFF57 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442/Administrator SET STATISTICS XML OFF58 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 DISTINCT60 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、58) select connect_time,last_read,last_write,most_recent_sql_handle from sys.dm_exec_connections where session_id in(57,58) --查看会话信息 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,58) --查看阻塞正在执行的请求 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) */ --查看正在执行的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 --也可用函数fn_get_sql通过most_recent_sql_handle得到执行语句 where a.Session_id in(57,58) /* session_id text ----------- ----------------------------------------------- 57 SET STATISTICS XML OFF58 begin tran select * from ta */处理方法:
法一:--连接窗口2 begin tran select * from ta with (nolock)--用nolock:业务数据不断变化中,如销售查看当月时可用。 法二:阻塞2(索引):处理方法: 加索引create index IX_Ta_Col1 on Ta(Col1)--用COl1列上创索引,当更新时条件:COl1=102会用到索引IX_Ta_Col1上得到一个排它键的范围锁 ----------------------------连接窗口1 ------------------------------------------------- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --针对会话设置了 TRANSACTION ISOLATION LEVEL --SERIALIZABLE 幻影读、不可重复读和脏读都不允许begin tran update ta set col2='BB' where COl1=102 --rollback tran -----------------------------连接窗口2------------------------------------------------begin tran select * from ta 法三:设置当前查询隔离级别-----------------------------连接窗口2------------------------------------------------SET TRANSACTION ISOLATION LEVEL READ COMMITTED --设置会话已提交读:指定语句不能读取已由其他事务修改但尚未提交的数据 begin tran select * from ta1、事务要尽量短
--查看死锁牺牲品
SELECT '进程ID[SPID]' = STR(a.spid, 4)
, '进程状态' = CONVERT(CHAR(10), a.status) , '分块进程ID' = STR(a.blocked, 2) , '服务器名称' = CONVERT(CHAR(10), a.hostname) , '执行用户' = CONVERT(CHAR(10), SUSER_NAME(a.uid)) , '数据库名' = CONVERT(CHAR(10), DB_NAME(a.dbid)) , '应用程序名' = CONVERT(CHAR(10), a.program_name) , '正在执行的命令' = CONVERT(CHAR(16), a.cmd) , '累计CPU时间' = STR(a.cpu, 7) , 'IO' = STR(a.physical_io, 7) , '登录名' = a.loginame , '执行sql' = b.textFROM master..sysprocesses a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) bWHERE blocked <> 0ORDER BY spid--查看进程运行状况
SELECT '进程ID' = STR(spid, 4)
, '进程ID状态' = CONVERT(CHAR(10), status) , '分块进程ID' = STR(blocked, 2) , '工作站名称' = CONVERT(CHAR(10), hostname) , '执行用户' = CONVERT(CHAR(10), SUSER_NAME(uid)) , '数据库名' = CONVERT(CHAR(10), DB_NAME(dbid)) , '应用程序名' = CONVERT(CHAR(10), program_name) , '正在执行的命令' = CONVERT(CHAR(16), cmd) , '累计CPU时间' = STR(cpu, 7) , 'IO' = STR(physical_io, 7) , '登录名' = loginameFROM master..sysprocesses --where blocked = 0ORDER BY spid--blocked = 0表示没有阻塞的进程ID;
--查询锁类型
select 进程id=a.req_spid
,数据库=db_name(rsc_dbid) ,类型=case rsc_type when 1 then 'NULL 资源(未使用)' when 2 then '数据库' when 3 then '文件' when 4 then '索引' when 5 then '表' when 6 then '页' when 7 then '键' when 8 then '扩展盘区' when 9 then 'RID(行 ID)' when 10 then '应用程序' end ,对象id=rsc_objid ,对象名=b.obj_name ,rsc_indidfrom master..syslockinfo a left join #t b on a.req_spid=b.req_spid转载地址:http://bgfmi.baihongyu.com/