sql locks¶

sp_who2
kill 123

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)

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

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);

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;