查找出SQLServer的死锁和阻塞的源头 --查找出SQLServer死锁和阻塞的源头
use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0
select '引起数据库死锁的是:
'+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '
进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur
查看当前进程,或死锁进程,并能自动杀掉死进程 --查看当前进程,或死锁进程,并能自动杀掉死进程
--因为是针对死的,所以如果有死锁进程,只能查看死锁进程。当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程。
create proc p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程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,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
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 master..sysprocesses
set @count=@@rowcount
end
if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),
b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@标志 varchar(10)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')'
from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go
日期: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 GRANT
58 DATABASE Gepro 0 S GRANT
57 DATABASE Gepro 0 S GRANT
56 DATABASE Gepro 0 S GRANT
58 PAGE Gepro 1:1904 72057594039435264 IS GRANT
57 PAGE Gepro 1:1904 72057594039435264 IX GRANT
58 OBJECT Gepro 853578079 IS GRANT
57 OBJECT Gepro 853578079 IX GRANT
57 KEY Gepro (020068e8b274) 72057594039435264 X GRANT
58 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.text
FROM master..sysprocesses a CROSS APPLY
sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.blocked IN ( SELECT blocked
FROM master..sysprocesses )
-- and blocked <> 0
ORDER BY STR(spid, 4)
--Result
/*
进程ID 进程ID 状态 死锁进程ID 工作站名称 执行命令的用户 数据库名 应用程序名 正在执行的命令 登录名 执行语句
---- ---------- ------ ---------- ---------- ---------- ---------- ---------------- ---------------------------------------------------------------------- -------------------------
56 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442\Administrator SET STATISTICS XML OFF
57 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442\Administrator SET STATISTICS XML OFF
58 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 DISTINCT
60 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、5
select connect_time,last_read,last_write,most_recent_sql_handle
from sys.dm_exec_connections where session_id in(57,5
--查看会话信息
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,5
--查看阻塞正在执行的请求
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,5
/*
session_id text
----------- -----------------------------------------------
57 SET STATISTICS XML OFF
58 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 ta
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_indid
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid
1. use AdventureWorks
2. go
3. SET NOCOUNT ON
4. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
5. WHILE 1=1
6. BEGIN
7. BEGIN TRAN
8. UPDATE Purchasing.Vendor
9. SET CreditRating = 1
10. WHERE VendorID = 90
11. UPDATE Purchasing.Vendor
12. SET CreditRating = 2
13. WHERE VendorID = 91
14. COMMIT TRAN
15. END
use AdventureWorks go SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE WHILE 1=1 BEGIN BEGIN TRAN UPDATE Purchasing.Vendor SET CreditRating = 1 WHERE VendorID = 90 UPDATE Purchasing.Vendor SET CreditRating = 2 WHERE VendorID = 91 COMMIT TRAN END
在第二个查询窗口执行:
view plaincopy to clipboardprint?
1. use AdventureWorks
2. go
3. SET NOCOUNT ON
4. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
5. WHILE 1=1
6. BEGIN
7. BEGIN TRAN
8. UPDATE Purchasing.Vendor
9. SET CreditRating = 2
10. WHERE VendorID = 91
11. UPDATE Purchasing.Vendor
12. SET CreditRating = 1
13. WHERE VendorID = 90
14. COMMIT TRAN
15. END
use AdventureWorks go SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE WHILE 1=1 BEGIN BEGIN TRAN UPDATE Purchasing.Vendor SET CreditRating = 2 WHERE VendorID = 91 UPDATE Purchasing.Vendor SET CreditRating = 1 WHERE VendorID = 90 COMMIT TRAN END
等待几秒后,其中一个查询窗口会提示:
/*
Msg 1205, Level 13, State 51, Line 9
Transaction (Process ID 52) was deadlocked on lock resources with another process and
has been chosen as the deadlock victim. Rerun the transaction.
*/
此时,查看,SQL Server Management Studio的SQL 日志,发现死锁事件没有被记录。
打开第三个查询窗口,执行:
view plaincopy to clipboardprint?
1. DBCC TRACEON (1222, -1)
2. GO
3. DBCC TRACESTATUS
1. SET NOCOUNT ON
2. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
3. SET DEADLOCK_PRIORITY LOW
4. BEGIN TRAN
5. UPDATE Purchasing.Vendor
6. SET CreditRating = 1
7. WHERE VendorID = 2
8. UPDATE Purchasing.Vendor
9. SET CreditRating = 2
10. WHERE VendorID = 1
11. COMMIT TRAN
SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET DEADLOCK_PRIORITY LOW BEGIN TRAN UPDATE Purchasing.Vendor SET CreditRating = 1 WHERE VendorID = 2 UPDATE Purchasing.Vendor SET CreditRating = 2 WHERE VendorID = 1 COMMIT TRAN
意向锁
意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如,放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能,因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。
意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。
锁模式 描述
意向共享 (IS) 通过在各资源上放置 S 锁,表明事务的意向是读取层次结构中的部分(而不是全部)底层资源。
意向排它 (IX) 通过在各资源上放置 X 锁,表明事务的意向是修改层次结构中的部分(而不是全部)底层资源。IX 是 IS 的超集。
与意向排它共享 (SIX) 通过在各资源上放置 IX 锁,表明事务的意向是读取层次结构中的全部底层资源并修改部分(而不是全部)底层资源。允许顶层资源上的并发 IS 锁。例如,表的 SIX 锁在表上放置一个 SIX 锁(允许并发 IS 锁),在当前所修改页上放置 IX 锁(在已修改行上放置 X 锁)。虽然每个资源在一段时间内只能有一个 SIX 锁,以防止其它事务对资源进行更新,但是其它事务可以通过获取表级的 IS 锁来读取层次结构中的底层资源。
加锁语句:
sybase:
update 表 set col1=col1 where 1=0 ;
MSSQL:
select col1 from 表 (tablockx) where 1=0 ;
oracle:
LOCK TABLE 表 IN EXCLUSIVE MODE ;
加锁后其它人不可操作,直到加锁用户解锁,用commit或rollback解锁
几个例子帮助大家加深印象
设table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
1)排它锁
新建两个连接
在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
在第二个连接中执行以下语句
begin tran
select * from table1
where B='b2'
commit tran
若同时执行上述两个语句,则select查询必须等待update执行完毕才能执行即要等待30秒
2)共享锁
在第一个连接中执行以下语句
begin tran
select * from table1 holdlock -holdlock人为加锁
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
在第二个连接中执行以下语句
begin tran
select A,C from table1
where B='b2'
update table1
set A='aa'
where B='b2'
commit tran
3)死锁
增设table2(D,E)
D E
d1 e1
d2 e2
在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30'
update table2
set D='d5'
where E='e1'
commit tran
在第二个连接中执行以下语句
begin tran
update table2
set D='d5'
where E='e1'
waitfor delay '00:00:10'
update table1
set A='aa'
where B='b2'
commit tran