SQL 锁表、解锁

最新推荐文章于 2024-04-13 22:07:33 发布

原创最新推荐文章于 2024-04-13 22:07:33 发布 · 943 阅读

3 ·

CC 4.0 BY-SA版权

理论基础专栏收录该内容

12 篇文章

订阅专栏

本文详细介绍了SQL Server中的不同锁类型，包括锁表、锁行和锁部分行的操作，以及如何查看和管理锁状态，提供了实用的SQL脚本用于监控和处理锁冲突。

--锁表（其它事务不能读、更新、删除）
BEGIN TRAN
SELECT * FROM <表名> WITH(TABLOCKX);
WAITFOR delay '00:00:20'
COMMIT TRAN


--锁表（其它事务只能读，不能更新、删除）
BEGIN TRAN
SELECT * FROM <表名> WITH(HOLDLOCK);
WAITFOR delay '00:00:20'
COMMIT TRAN

--锁部分行
BEGIN TRAN
SELECT * FROM <表名>  WITH(XLOCK) WHERE ID IN ('81A2EDF9-D1FD-4037-A17B-1369FD3B169B');
WAITFOR delay '00:01:20'
COMMIT TRAN


-- 查看锁状态
select spid,
       blocked,
       loginame,
       last_batch,
       status,
       cmd,
       hostname,
       program_name
  from sys.sysprocesses
 where spid in (select blocked from sys.sysprocesses where blocked <> 0)
    or (blocked <> 0)
 
-- 终止进程
 declare @spid  int  
Set @spid  = 83 --锁表进程 
declare @sql varchar(1000) 
set @sql='kill '+cast(@spid  as varchar) 
exec(@sql)



---- 查看语句-正在执行
SELECT  [Spid] = session_id ,
            ecid ,
            [Database] = DB_NAME(sp.dbid) ,
            [User] = nt_username ,
            [Status] = er.status ,
            [Wait] = wait_type ,
            [Individual Query] = SUBSTRING(qt.text,
                                           er.statement_start_offset / 2,
                                           ( CASE WHEN er.statement_end_offset = -1
                                                  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                                       * 2
                                                  ELSE er.statement_end_offset
                                             END - er.statement_start_offset )
                                           / 2) ,
            [Parent Query] = qt.text ,
            Program = program_name ,
            hostname ,
            nt_domain ,
            start_time
    FROM    sys.dm_exec_requests er
            INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
    WHERE   session_id > 50 -- Ignore system spids.
            AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.
ORDER BY    1 ,2


-- 查看语句执行过信息
select *
	 from sys.sysprocesses  
     where spid in (select blocked from sys.sysprocesses where blocked <> 0)  
        or (blocked <> 0)  ;	
SELECT TOP 1000 
       ST.text AS '执行的SQL语句',
       QS.execution_count AS '执行次数',
       QS.total_elapsed_time AS '耗时',
       QS.total_logical_reads AS '逻辑读取次数',
       QS.total_logical_writes AS '逻辑写入次数',
       QS.total_physical_reads AS '物理读取次数',       
       QS.creation_time AS '执行时间' ,  
       QS.*
FROM   sys.dm_exec_query_stats QS
       CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE  QS.creation_time BETWEEN '2016-08-05 15:00:00' AND '2016-08-05 17:00:00' 
ORDER BY QS.total_elapsed_time DESC


--kill当前库所有进程

DECLARE @sql NVARCHAR(MAX),@currDbName NVARCHAR(100)
SET @currDbName='DB1'	--如果在当前库上执行，可改为: SET @currDbName = DB_NAME()
WHILE EXISTS( SELECT 1 FROM sys.sysprocesses WHERE dbid=DB_ID(@currDbName) AND spid!=@@SPID )
BEGIN
	SELECT TOP 1 @sql='kill '+CAST(spid AS NVARCHAR(50)) FROM sys.sysprocesses WHERE dbid=DB_ID(@currDbName) AND spid!=@@SPID  
	EXEC (@sql)	
END