【sql server】锁引起的等待时间过长解决办法

最新推荐文章于 2023-01-28 09:27:24 发布

cuanjiaoxi8230

最新推荐文章于 2023-01-28 09:27:24 发布

阅读量1.5k

点赞数

文章标签：数据库

数据库突然运行很慢，就连简单的查询都要等上好几分钟。可能是sql 中的锁在“捣鬼”。

USE master
EXEC sp_lock --查询被锁的库及状态等信息

spid

smallint

请求锁的进程的数据库引擎进程 ID (SPID) 号。

dbid

smallint

保留锁的数据库的标识号。可以使用 DB_NAME() 函数来标识数据库。

ObjId

int

持有锁的对象的标识号。可以在相关数据库中使用 OBJECT_NAME() 函数来标识对象。值为 99 时是一种特殊情况，表示用于记录数据库中页分配的其中一个系统页的锁。

IndId

smallint

持有锁的索引的索引标识号。

Type

nchar(4)

锁的类型：

RID = 表中单个行的锁，由行标识符 (RID) 标识。

KEY = 索引内保护可串行事务中一系列键的锁。

PAG = 数据页或索引页的锁。

EXT = 区（具有 8 个连续页的单元）的锁。

TAB = 整个表（包括所有数据和索引）的锁。

DB = 数据库的锁。

FIL = 数据库文件的锁。

APP = 指定的应用程序资源的锁。

MD = 元数据或目录信息的锁。

HBT = 堆或 B 树索引的锁。在 SQL Server 2005 中此信息不完整。

AU = 分配单元的锁。在 SQL Server 2005 中此信息不完整。

Resource

nchar(32)

标识被锁定资源的值。值的格式取决于 Type 列标识的资源类型：

Type 值：Resource 值

RID：格式为 fileid:pagenumber:rid 的标识符，其中 fileid 标识包含页的文件，pagenumber 标识包含行的页，rid 标识页上的特定行。fileid 与 sys.database_files 目录视图中的 file_id 列相匹配。

KEY：数据库引擎内部使用的十六进制数。

PAG：格式为 fileid:pagenumber 的数字，其中 fileid 标识包含页的文件，pagenumber 标识页。

EXT：标识区中的第一页的数字。该数字的格式为 fileid:pagenumber。

TAB：没有提供信息，因为已在 ObjId 列中标识了表。

DB：没有提供信息，因为已在 dbid 列中标识了数据库。

FIL：文件的标识符，与 sys.database_files 目录视图中的 file_id 列相匹配。

APP：被锁定的应用程序资源的唯一标识符。格式为 DbPrincipleId:。

MD：随资源类型而变化。有关详细信息，请参阅 sys.dm_tran_locks 中 resource_description 列的说明。

HBT：没有提供任何信息。请改用 sys.dm_tran_locks 动态管理视图。

AU：没有提供任何信息。请改用 sys.dm_tran_locks 动态管理视图。

模式

nvarchar(8)

所请求的锁模式。可以是：

NULL = 不授予对资源的访问权限。用作占位符。

Sch-S = 架构稳定性。确保在任何会话持有对架构元素（例如表或索引）的架构稳定性锁时，不删除该架构元素。

Sch-M = 架构修改。必须由要更改指定资源架构的任何会话持有。确保没有其他会话正在引用所指定的对象。

S = 共享。授予持有锁的会话对资源的共享访问权限。

U = 更新。指示对最终可能更新的资源获取的更新锁。用于防止一种常见的死锁，这种死锁在多个会话锁定资源以便稍后对资源进行更新时发生。

X = 排他。授予持有锁的会话对资源的独占访问权限。

IS = 意向共享。指示有意将 S 锁放置在锁层次结构中的某个从属资源上。

IU = 意向更新。指示有意将 U 锁放置在锁层次结构中的某个从属资源上。

Status

nvarchar

锁的请求状态：

EX：

spid dbid objid indid type resource Mode status

88 12 1698105090 0 RID 1:2916:85 S GRANT

select * from sys.databases --可查看sp_lock中dbid所对应的database_name

select * from sysprocesses where spid = '88' -- 查看进程号88 所对应的主机号

-- 对于等待时间过长的建立解锁脚本

use master --必须在master数据库中创建
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_lockinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_lockinfo]
GO

create proc p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
set nocount on
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(255),b Int,EventInfo nvarchar(1000))
if @kill_lock_spid=1
begin
declare @spid varchar(255),@标志 varchar(255)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @@rowcount=0 insert #t1(a) values(null)
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)
if @@rowcount=0 insert #t1(a) values(null)
set @i=@i+1
end
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
order by 进程ID
end
set nocount off
go