sql server查看锁表及解锁方法(示例详解)
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">sql server查看锁表以及解锁方法</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1.查看sqlserver被锁的表：</a></li><li><a href="#_lab2_0_1">2.解锁：@spid为锁表进程</a></li></ul><li><a href="#_label1">SQL Server 查看锁表情况</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>sql server查看锁表以及解锁方法</h2><p class="maodian"><a name="_lab2_0_0"></a></p><h3>1.查看sqlserver被锁的表：</h3>
<p>select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableNamefrom sys.dm_tran_locks where resource_type='OBJECT'</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202507/2025072510490451.png" /></p>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2.解锁：@spid为锁表进程</h3>
<p>declare @spid int</p>
<p>Set @spid = 141</p>
<p>declare @sql varchar(1000)set @sql='kill '+cast(@spid as varchar)</p>
<p>exec(@sql)</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202507/2025072510490469.png" /></p>
<p>补充：SQL Server 查看锁表情况</p>
<p class="maodian"><a name="_label1"></a></p><h2>SQL Server 查看锁表情况</h2>
<div class="jb51code"><pre class="brush:sql;">查看锁表数据
SELECT
request_session_id spid, -- 请求会话ID
OBJECT_NAME( resource_associated_entity_id ) tableName-- 表名
FROM
sys.dm_tran_locks
WHERE
resource_type = 'OBJECT'</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202507/2025072510512289.png" /></p>
<div class="jb51code"><pre class="brush:sql;">-- 杀掉锁表进程
DECLARE
@spid INT
SET @spid = 123 --锁表进程
DECLARE
@SQL VARCHAR ( 1000 )
SET @SQL = 'kill ' + CAST ( @spid AS VARCHAR ) EXEC ( @SQL )</pre></div>
頁:
[1]