sql locks¶
sp_who2
kill 123
https://stackoverflow.com/questions/27040114/how-can-i-set-a-lock-inside-a-stored-procedure
CREATE PROCEDURE ...
AS
BEGIN
BEGIN TRANSACTION
-- lock table "a" till end of transaction
SELECT ...
FROM a
WITH (TABLOCK, HOLDLOCK)
WHERE ...
-- do some other stuff (including inserting/updating table "a")
-- release lock
COMMIT TRANSACTION
END
select top 1 *
from table1
with (tablock, holdlock)
find blocking query¶
select l.request_session_id as spid,
sp.blocked as blockingspid,
sp.status as processstatus,
db_name(l.resource_database_id) as databasename,
o.name as lockedobjectname,
p.object_id as lockedobjectid,
l.resource_type as lockedresource,
l.request_mode as locktype,
st.text as sqlstatementtext,
es.login_name as loginname,
es.host_name as hostname,
cn.auth_scheme as authenticationmethod,
sp.waittime as blockingwaittime,
sp.lastwaittype as lastwaittype,
sp.program_name as program_name
from sys.dm_tran_locks l
join sys.partitions p on p.hobt_id = l.resource_associated_entity_id
join sys.objects o on o.object_id = p.object_id
join sys.dm_exec_sessions es on es.session_id = l.request_session_id
join sys.dm_exec_connections cn on cn.session_id = es.session_id
cross apply sys.dm_exec_sql_text(cn.most_recent_sql_handle) as st
join master.dbo.sysprocesses as sp on l.request_session_id = sp.spid
where resource_database_id = db_id()
order by l.request_session_id
kill connections¶
use [master]
go
declare @kill varchar(8000) = '';
select @kill = @kill + 'kill ' + convert(varchar(5), session_id) + ';'
from sys.dm_exec_sessions
where database_id = db_id('mydb')
exec(@kill);
dynamic view¶
https://www.sqlshack.com/kill-spid-command-in-sql-server/
SELECT *
FROM sys.dm_exec_sessions;
By default, it shows all processes in SQL Server. We might not be interested in the system processes. We can filter the results using the following query.
SELECT *
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;