In order to assist continuous and effective monitoring of lock status, SQL Server offers the system stored procedure SP_LOCK, which can provide several useful pieces of information on how locks are set and removed.
However, using this information - such as process ID, database ID, and object ID - to determine the actual resources involved in the blocking transaction is much more difficult. The solution I illustrate here displays not only all the locked resources, but also the login and user name, and the name of the application, the process ID, and even the MAC address of the network card mounted on the machine where the host app which is "to blame" or which is the "victim" of the lock. Let's more in detail how this information is retrieved.
The sp_ExBlockingLocks procedure illustrated below is an extension of an example contained in the book Inside MS-SQLServer 7.0 by Ron Soukup e Kalen Delany, improved to display all the extra data mentioned previously. This stored procedure retrieves the name of all the databases involved in the lock from the syslockinfo table (which contains all the most important data related to system locks) and from the sysdatabases table in the Master database. It retrieves the name of the involved table from the sysobjects table in current database. Finally, the following information is retrieved from the sysprocesses table in the master database:
hostname: La macchina su cui gira l'applicazione bloccante oppure bloccata.
program_name: Il nome della applicazione.
hostprocess: Rappresenta il process ID della applicazione in questione.
nt_domain: Il dominio da cui proviene la connessione.
nt_username: Il nome utente con cui è stato effettuato il login.
net_address: Il MAC Address della macchina da cui è stato effettuato il login.
loginname: Il nome di login della connessione.
Here is the complete source code of the sp_ExBlockingLocks procedure:
/* Start of sp_ExBlockingLocks.sql stored procedure*/
/* Original version: Ron Soukup, Kalen Delany */
use master
go
IF EXISTS( SELECT name FROM sysobjects WHERE name = 'sp_ExBlockingLocks' AND type = 'P')
drop proc sp_ExBlockingLocks
go
create procedure sp_ExBlockingLocks
as
set nocount on
select DISTINCT
sp.hostname HostName,
sp.program_name "Prg Name",
sp.hostprocess "PID",
sp.nt_domain "DOMAIN",
sp.nt_username "Usr Name",
sp.net_address "Net Addr",
sp.loginame "Login",
convert (smallint, l1.req_spid) As spid,
l1.rsc_dbid As dbid,
(SELECT name from master.dbo.sysdatabases (nolock) where dbid = l1.rsc_dbid) As Db,
l1.rsc_objid As ObjId,
(select name from sysobjects (nolock) where id = l1.rsc_objid) As Obj,
l1.rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (l1.rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo l1,
master.dbo.syslockinfo l2,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysprocesses sp
where
l1.rsc_type = v.number
and v.type = 'LR'
and l1.req_status = x.number
and x.type = 'LS'
and l1.req_mode + 1 = u.number
and u.type = 'L'
and l1.rsc_type <>2 /* not a DB lock */
and l1.rsc_dbid = l2.rsc_dbid
and l1.rsc_bin = l2.rsc_bin
and l1.rsc_objid = l2.rsc_objid
and l1.rsc_indid = l2.rsc_indid
and l1.req_spid <> l2.req_spid
and l1.req_status <> l2.req_status
and sp.spid = l1.req_spid
order by
substring (l1.rsc_text, 1, 16),
substring (x.name, 1, 5)
return (0)
/* End of stored procedure */ |
|