怎么知道是什么锁着我的桌子?

sql sql-server sql-server-2008-r2 locking query-optimization 匿名 | 2020-02-27 02:24:34


我有一个SQL表,除非在末尾包含
with (nolock)
,否则它突然无法返回数据,这表示表上还有某种锁。
我已经用sys.dm_tran_锁做了一些试验,以确定表上实际上有许多锁,但是我如何确定锁定它们的是什么(即sys.dm_tran_锁的请求元素)?
编辑:我知道SQL 2005之前版本的sp_lock,但现在该sp已被弃用,所以正确的方法是使用sys.dm_tran_locks。我正在使用SQL Server 2008 R2。





3 答案



查看以下系统存储过程,您可以在SQLServer Management Studio(SSMS)中运行这些过程:
sp_who
sp_lock
此外,在SSMS中,您可以以不同的方式查看锁和进程:
不同版本的SSMS将活动监视器放在不同的位置。例如,当您右键单击服务器节点时,SSMS 2008和2012会将其放在上下文菜单中。

2020-02-27 02:24:42
匿名


为了直接了解“谁被阻止/阻止”,我将sp}who和sp}u组合在一个查询中,该查询很好地概括了谁将什么对象锁定到了什么级别。
--Create Procedure WhoLock
--AS
set nocount on
if object_id('tempdb..#locksummary') is not null Drop table #locksummary
if object_id('tempdb..#lock') is not null Drop table #lock
create table #lock ( spid int, dbid int, objId int, indId int, Type char(4), resource nchar(32), Mode char(8), status char(6))
Insert into #lock exec sp_lock
if object_id('tempdb..#who') is not null Drop table #who
create table #who ( spid int, ecid int, status char(30),
loginame char(128), hostname char(128),
blk char(5), dbname char(128), cmd char(16)
--
, request_id INT --Needed for SQL 2008 onwards
--
)
Insert into #who exec sp_who
Print '-----------------------------------------'
Print 'Lock Summary for ' + @@servername + ' (excluding tempdb):'
Print '-----------------------------------------' + Char(10)
Select left(loginame, 28) as loginame,
left(db_name(dbid),128) as DB,
left(object_name(objID),30) as object,
max(mode) as [ToLevel],
Count(*) as [How Many],
Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],
l.spid, hostname
into #LockSummary
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and l.status='GRANT'
group by dbID, objID, l.spid, hostname, loginame
Select * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, object
Print '--------'
Print 'Who is blocking:'
Print '--------' + char(10)
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, p.loginame
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
WHERE EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid )
Print '--------'
Print 'Details:'
Print '--------' + char(10)
Select left(loginame, 30) as loginame, l.spid,
left(db_name(dbid),15) as DB,
left(object_name(objID),40) as object,
mode ,
blk,
l.status
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and blk <>0
Order by mode desc, blk, loginame, dbID, objID, l.status

(对于锁定级别缩写的含义,例如,请参见https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx)
复制自:sp_who lock–一个将sp_who和sp_lock组合在一起的T-sql存储过程…
NB[e Xclusive lock for command]列可能会引起误解--它显示该spid的当前命令;但X锁可能是由事务中较早的命令触发的。

2020-02-27 02:24:54
匿名


我有一个存储过程,我把它放在一起,不仅处理锁和阻塞,还查看服务器上运行的内容。
我把它放在master中。
我将与您共享它,代码如下:
USE [master]
go

CREATE PROCEDURE [dbo].[sp_radhe]
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- the current_processes
-- marcelo miorelli
-- CCHQ
-- 04 MAR 2013 Wednesday
SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, '') AS login_name
,COALESCE(es.host_name,'') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
CASE es.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END
,COALESCE(es.cpu_time,0)
+ COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0)
+ COALESCE(es.writes,0)
+ COALESCE(er.reads,0)
+ COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
where es.is_user_process = 1
and es.session_id <> @@spid
and es.status = 'running'
ORDER BY es.session_id
end
GO

这一过程在过去几年中对我非常有用。
只需键入spòradhe
关于将spòradhe放入主数据库
我使用以下代码并使其成为系统存储过程
exec sys.sp_MS_marksystemobject 'sp_radhe'

正如您在下面的链接中看到的那样
创建自己的SQL Server系统存储过程
关于事务隔离级别
有关T-SQL事务隔离的问题你太害羞了,不敢问Jonathan Kehayias一旦你改变了事务隔离级别,它只会在过程结束或返回调用时作用域退出时改变,或者如果使用SET TRANSACTION ISOLATION LEVEL再次显式地更改它。
此外,事务隔离级别仅限于存储过程,因此可以有多个嵌套存储过程在其特定的隔离级别上执行。

2020-02-27 02:25:06
匿名


World is powered by solitude
备案号:湘ICP备19012068号