<

Resolving blocking conflicts in SQL Server in user-friendly manner

Published on
14,190 Points
3,390 Views
3 Endorsements
Last Modified:
Awarded
Community Pick
We describe the solution, a combination of SQL code, COM object, and active directory query, that allows cooperative resolution of blocking user conflicts when working with SQL Server.

One of the main responsibilities of the database administrator is watching for the users blocking each other while working with the database. When that happens, the user's application gets locked; to the user this appears as hanging, and is usually followed either by calling support (better-case scenario), or an attempt to kill the application or reset the computer. Unfortunately, the two latter actions don't remove the block because the database server does not know what the user has done, and is assuming that he is still waiting. All this quickly involves more users, and often results in the necessity to restart the database server.

The proposed solution aims to resolve this problem in user-friendly manner, promptly, and with minimum involvement of the administrator.


A Stored Procedure to Detect Locks

The solution is to create a stored procedure that will frequently check for the locking conflicts in the database.  When a conflict is detected for a long enough time, inform both parties about the situation and offer them the way to resolve it.  We will be using SQL Server 2000.  Here's the code of the stored procedure.
ALTER           procedure BlockWatchDog as
/* net send to the blocker */
/* this s.p. will only work with current d.b. where the lock is. (because of OBJECT_NAME) */

declare @LockingMAC varchar(500), @LockedMAC Varchar(500), @LockingApp varchar(500),@LockedApp varchar(500),
@LockingHost varchar(50), @LockedHost varchar(50),
@Resource varchar(500), @dbid int, @v varchar(500), @rc int, @o int,
@BlockedSQL varchar(80),@Blockingsql varchar(80),
@Blockingsqlhandle binary(20), @Blockedsqlhandle binary(20),
@BlockingUserName varchar(50), @BlockedUserName varchar(50),
@BlockingUser varchar(50), @BlockedUser varchar(50)

declare c cursor local fast_forward for

select locking.nt_username,locked.nt_username,
locking.net_address,locked.net_address,locking.program_name,locked.program_name,
locking.hostname, locked.hostname, rsc_dbid, rsc_objid, locking.sql_handle, locked.sql_handle
from master..sysprocesses locked
,master..sysprocesses locking
, master..syslockinfo li
where locked.blocked<>0
and locking.spid=locked.blocked
and locked.spid=li.req_spid
and li.req_status in (2,3)
and locked.waittime between 10000 and 99000000

Open in new window

The above statement is the core query that actually finds the situations of users blocking each other.

The status in (2,3) part of the WHERE clause filters only the locks that are in state “waiting” or “converting”.  Why “converting”?  because it’s still a wait to upgrade the lock (source) . We also filter only those situations where the locked party has been waiting for at least 10 seconds (locked.waittime between 10000 and 99000000), so we ignore trivial waits that self-resolve quickly.


Informing the Affected Users

Now that we have identified the locking and locked party, we are going to inform them about the situation. We will tell them what happened, and what to do in order to remove the lock. We will tell the blocking party that their application X has blocked someone else, and ask to complete their operation.  We will tell the blocked party that they were blocked by someone else, and suggest talking to that party.  This way, the blocked party won’t panic and won’t hit “reset”.

As we saw above, we have retrieved from the query the names of both workstations, so we will be sending them each a message, for example via NET SEND.  That seems easy enough, except one nuance.  In practice, these names of workstations will often be wrong, and they will be representing the name of the workstation of the developer of the application, rather than workstation of the user.

Why is that?  This stems from Microsoft practice to hardcode workstation name in the ADO connection string of various database-oriented development tools.  If the developer is using the wizard to create the connection string, the wizard will usually include workstation name in it. Once the application is deployed to the users, and it connects to the database, the hardcoded (developer's) workstation name in the connection string will override the real workstation name of the user. The developer can adjust the connection string and remove the hardcoded workstation name, but few will do that, and in some tools, notable Microsoft Access Project (ADP), this is actually impossible to do at all.

This is bad news for us, because now we can’t rely on the workstation name fetched from our query. The good news is that besides the workstation name, we have also learned MAC address of both workstations, and it’s possible to determine the true workstation name from MAC by asking the network’s DHCP server.


Using the MAC Address to Identify the Workstations and the Users

In order to resolve MAC into the workstation name, we are using COM object vrSQLHelper.Network written in VB6.  The code is in the Appendix A (at the bottom of this article).

Here's the code to use that COM object in our task:
exec @rc=sp_oacreate 'vrSQLHelper.Network', @o OUT

open c
fetch next from c into @LockingMac , @LockedMac , @LockingApp ,@LockedApp , @LockingHost, @LockedHost, @dbid,@resource, @Blockingsqlhandle, @Blockedsqlhandle

WHILE @@FETCH_STATUS = 0
begin
select @blockingsql='  blocking with: ' + left(cast(text as varchar(5000)),80) from ::fn_get_sql(@blockingsqlhandle)
select @blockedsql ='  blocked code: '  + left(cast(text as varchar(5000)),80) from ::fn_get_sql(@blockedsqlhandle)
if @LockingApp not like 'SQLAgent%' and @LockedApp not like 'SQLAgent%'  begin


set @Lockingapp=rtrim(isnull(@lockingapp,''))
set @Lockedapp=rtrim(isnull(@lockedapp,''))
set @lockingHost=NULL

if @lockingApp = '' set @Lockingapp = 'unidentified application'
if @lockedApp  = '' set @Lockedapp = 'unidentified application'

set @LockedHost=NULL
if @rc=0 and @LockingMAC<>'' exec sp_oagetproperty @o,'HOSTfrommac',@LockingHost OUT,@LockingMAC
if @rc=0 and @LockedMAC <>'' exec sp_oagetproperty @o,'HOSTfrommac',@LockedHost OUT,@LockedMAC

Open in new window

Now that we know the true workstation name, we can proceed with NET SEND. However, in order to make the messages helpful, we will want to include contact information of the both parties, so they know how to contact each other, and, hopefully, do it without bothering the busy admin (possibly, you :-).  We will extract this information from the Active Directory basing on the username.

Here is the code to obtain contact information for the affected users:
if rtrim(@BlockingUserName)<>''
    select @BlockingUser=cn + ', ' + telephonenumber from  
OPENROWSET('ADsDSOObject','Integrated Security=SSPI',
'SELECT  cn,telephonenumber, sAMAccountName
from ''LDAP://DC=my,dc=domain,dc=com''    
WHERE objectCategory = ''Person'' AND  objectClass = ''user'' ')
where sAMAccountName=@BlockingUserName

if rtrim(@BlockedUserName)<>''
    select @BlockedUser=cn + ', ' + telephonenumber from  
OPENROWSET('ADsDSOObject',  'Integrated Security=SSPI',
'SELECT  cn,telephonenumber,sAMAccountName
from ''LDAP://DC=my,dc=domain,dc=com''    
WHERE objectCategory = ''Person'' AND  objectClass = ''user'' ')
where sAMAccountName=@BlockedUserName

Open in new window

In the above, dc=my,dc=domain,dc=com represents the DNS domain.

Now that we know all pieces, we need to send the messages.  We will compose it and send. I leave composition part to the developer since it probably will reflect the specifics of the organization.  You will have to take care of the scenarios where username is not known, only the host name is; decide whether to inform the administrator as well; and so forth. Once the message(s) are complete, issue
     exec master..xp_cmdshell <machine>, no_output
and also maybe
     exec master..xp_logevent 50002,<sql-server-log record>,WARNING

Here's an example of using NET SEND to notify the affected parties:
if @LockingHost is not null 
    begin
    if @BlockedUser is not null
        set @v = 'NET SEND ' + @LockingHost + ' Your ' + @LockingApp + ' is blocking ' + @BlockedUser + '. Please complete your operation.'
    else
        set @v = 'NET SEND ' + @LockingHost + ' Your ' + @LockingApp + ' is blocking ' + isnull('machine ' + upper(@lockedHost), 'another machine') + '. Please complete your operation.'
    exec master..xp_cmdshell @v, no_output
    end
    if @LockedHost is not null 
        begin
        if @BlockingUser is not null
            set @v = 'NET SEND ' + @LockingHost + ' You are blocked by ' + @BlockingUser + '. I notified them, so if you are not unblocked soon, call them and ask to complete their transaction.'
        else begin
        if @lockingHost is null
            set @v = 'NET SEND ' + @LockingHost + ' You are blocked by another machine that we can''t determine. Database Administrator has been notified.'
        else
            set @v = 'NET SEND ' + @LockedHost + ' You are blocked by machine ' + upper(@lockinghost) + '. That machine and database administrator have been notified.'
        end
        exec master..xp_cmdshell @v, no_output
    end

    if @LockingApp = 'unidentified application' set @Lockingapp = 'Unidentified application'

        set @v = @LockingApp + ' on ' + upper(@lockingHost) + ' is blocking ' + @LockedApp + ' on ' + upper(@LockedHost) + ' ; resource: ' + @tname + isnull(@blockingsql,'') + isnull(@blockedsql,'')
        exec master..xp_logevent 50002,@v,WARNING

Open in new window

NOTE:
In order to use NET SEND, Windows service "Messenger" must be running on both sender's and receiver's workstations.  In Windows XP SP3, it was made disabled by default, following series of spam attacks exploiting this feature. The remedy is to use group policy to set service's startup type to Automatic for everyone.
Starting from Vista, NET SEND does not exist anymore, so you will need to choose your own method of notifying the user. For corporate users who have their email client open in all times and usually receive instant notification of new messages, xp_sendmail is probably the best alternative.

From the experience of several years of running this solution, it has allowed countless users to promptly make informed decisions about the situation when they were blocked by another user, and in most cases they were able to resolve their blocking conflict without involving the database administrator.

Appendix A
This the a VB6 COM object that obtains a workstation name, given a MAC address.
Option Explicit

Private Const MAX_ADAPTER_NAME_LENGTH         As Long = 256
Private Const MAX_ADAPTER_DESCRIPTION_LENGTH  As Long = 128
Private Const MAX_ADAPTER_ADDRESS_LENGTH      As Long = 8
Private Const ERROR_SUCCESS  As Long = 0

Private Const MAXLEN_PHYSADDR = 8
Private Type MIB_IPNETROW
    dwIndex As Long
    dwPhysAddrLen As Long
    bPhysAddr(0 To MAXLEN_PHYSADDR - 1) As Byte
    dwAddr As Long
    dwType As Long
End Type

Private Type IP_ADDRESS_STRING
    IpAddr(0 To 15)  As Byte
End Type

Private Type IP_MASK_STRING
    IpMask(0 To 15)  As Byte
End Type

Private Type IP_ADDR_STRING
    dwNext     As Long
    IpAddress  As IP_ADDRESS_STRING
    IpMask     As IP_MASK_STRING
    dwContext  As Long
End Type


Private Type IP_ADAPTER_INFO
  dwNext                As Long
  ComboIndex            As Long  'reserved
  sAdapterName(0 To (MAX_ADAPTER_NAME_LENGTH + 3))        As Byte
  sDescription(0 To (MAX_ADAPTER_DESCRIPTION_LENGTH + 3)) As Byte
  dwAddressLength       As Long
  sMAC(0 To (MAX_ADAPTER_ADDRESS_LENGTH - 1))        As Byte
  dwIndex               As Long
  uType                 As Long
  uDhcpEnabled          As Long
  CurrentIpAddress      As Long
  IpAddressList         As IP_ADDR_STRING
  GatewayList           As IP_ADDR_STRING
  DhcpServer            As IP_ADDR_STRING
  bHaveWins             As Long
  PrimaryWinsServer     As IP_ADDR_STRING
  SecondaryWinsServer   As IP_ADDR_STRING
  LeaseObtained         As Long
  LeaseExpires          As Long
End Type

Private Declare Function GetIpNetTable Lib "Iphlpapi" (pIpNetTable As Byte, pdwSize As Long, ByVal bOrder As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)
Private Declare Function GetAdaptersInfo Lib "Iphlpapi" _
(AdapterAddresses As Any, BK As Long) As Long

Public Property Get IPfromMAC(ByVal Mac As String) As Variant
    On Error GoTo ex
    IPfromMAC = Null

    If Len(Mac) <> 12 And Len(Mac) <> 17 Then
        Exit Property
    End If
    Mac = UCase(Mac)
    Dim Mac0 As String: Mac0 = Mac
    
    If InStr(Mac, "-") = 0 Then
        Mac = CInt("&H" & Mid(Mac, 1, 2)) & "." & CInt("&H" & Mid(Mac, 3, 2)) & "." & CInt("&H" & Mid(Mac, 5, 2)) & _
        "." & CInt("&H" & Mid(Mac, 7, 2)) & "." & CInt("&H" & Mid(Mac, 9, 2)) & "." & CInt("&H" & Mid(Mac, 11, 2))
        Else
        Mac = CInt("&H" & Mid(Mac, 1, 2)) & "." & CInt("&H" & Mid(Mac, 4, 2)) & "." & CInt("&H" & Mid(Mac, 7, 2)) & _
        "." & CInt("&H" & Mid(Mac, 10, 2)) & "." & CInt("&H" & Mid(Mac, 13, 2)) & "." & CLng("&H" & Mid(Mac, 16, 2))
    End If
    IPfromMAC = GetIPfromARP(UCase$(Mac))
    
    ' The previous ARP lookup does not include our own addresses. Check them now
    If VarType(IPfromMAC) = vbNull Then
        IPfromMAC = IPfromOurMAC(Mac0)
    End If
    Exit Property
        
ex:
End Property

Public Property Get HostfromMAC(ByVal Mac As String) As Variant
    Dim vIP As Variant
    Mac = UCase(Mac)
    vIP = IPfromMAC(Mac)
    HostfromMAC = Null
    If VarType(vIP) <> vbNull Then
        HostfromMAC = GetHostNameFromIP(vIP)
        If VarType(HostfromMAC) <> vbNull Then
            If InStr(HostfromMAC, ".") <> 0 Then HostfromMAC = Split(HostfromMAC, ".")(0)
        End If
    End If
End Property


Private Function GetIPfromARP(Mac) As Variant
    GetIPfromARP = Null
    On Error GoTo ex
    'KPD-Team 2001
    'URL: http://www.allapi.net/
    'E-Mail: KPDTeam@Allapi.net
    Dim Listing() As MIB_IPNETROW, Ret As Long, cnt As Long
    Dim bBytes() As Byte, bTemp(0 To 3) As Byte
    'set the graphics mode of this form to persistent
    'call the`` function to retrieve how many bytes are needed
    GetIpNetTable ByVal 0&, Ret, False
    'if it failed, exit the sub
    If Ret <= 0 Then Exit Function
    'redimension our buffer
    ReDim bBytes(0 To Ret - 1) As Byte
    'retireve the data
    GetIpNetTable bBytes(0), Ret, False
    'copy the number of entries to the 'Ret' variable
    CopyMemory Ret, bBytes(0), 4
    'redimension the Listing
    If Ret > 0 Then ReDim Listing(0 To Ret - 1) As MIB_IPNETROW
    'show the data
    For cnt = 0 To Ret - 1
        Dim PA As String
        CopyMemory Listing(cnt), bBytes(4 + 24 * cnt), 24
        CopyMemory bTemp(0), Listing(cnt).dwAddr, 4
        PA = UCase$(ConvertAddressToString(Listing(cnt).bPhysAddr, Listing(cnt).dwPhysAddrLen))
        If PA = Mac Then
            GetIPfromARP = ConvertAddressToString(bTemp(), 4)
            Exit Function
        End If
    Next cnt
ex:
End Function
'converts a byte array to a string
Private Function ConvertAddressToString(bArray() As Byte, lLength As Long) As String
    Dim cnt As Long
    For cnt = 0 To lLength - 1
        ConvertAddressToString = ConvertAddressToString + CStr(bArray(cnt)) + "."
    Next cnt
    ConvertAddressToString = Left$(ConvertAddressToString, Len(ConvertAddressToString) - 1)
End Function

Private Function IPfromOurMAC(ByVal Mac As String) As Variant
    
    IPfromOurMAC = Null
    If Len(Mac) <> 17 And Len(Mac) <> 12 Then Err.Raise 555, , "Invalid MAC supplied"
    If Len(Mac) = 17 And Mid(Mac, 3, 1) = "-" Then ' remove dashes
        Mac = Mid(Mac, 1, 2) & Mid(Mac, 4, 2) & Mid(Mac, 7, 2) & Mid(Mac, 10, 2) & Mid(Mac, 13, 2) & Mid(Mac, 16, 2)
    End If

    Dim rc As Long, rLen As Long, bArr() As Byte, Found As Boolean, Ptr1 As Long
    Dim Adapter As IP_ADAPTER_INFO
    rc = GetAdaptersInfo(ByVal 0&, rLen)
    If rc <> 111 Then Err.Raise 555, , "Failed to get adapters info"
    ReDim bArr(0 To rLen - 1)
    rc = GetAdaptersInfo(bArr(0), rLen)
    If rc <> 0 Then Err.Raise 555, , "Failed to get adapters info"
    
     'get a pointer to the data stored in buff()
    Ptr1 = VarPtr(bArr(0))
    Do While (Ptr1 <> 0)
        'copy the data from the pointer to the
        'first adapter into the IP_ADAPTER_INFO type
         CopyMemory Adapter, ByVal Ptr1, LenB(Adapter)
         With Adapter
            Dim CurrentMAC As String, i As Integer, NextDigit As String
            CurrentMAC = ""
            For i = 0 To 5
                NextDigit = Hex(.sMAC(i))
                If Len(NextDigit) = 1 Then NextDigit = "0" & NextDigit
                CurrentMAC = CurrentMAC & NextDigit
            Next i
            If CurrentMAC = Mac Then
                IPfromOurMAC = TrimNull(StrConv(.IpAddressList.IpAddress.IpAddr, vbUnicode))
                Exit Function
            End If
            Ptr1 = .dwNext
         End With  'With Adapter
            'ptr1 is 0 when (no more adapters)
    Loop  'Do While (ptr1 <> 0)


End Function


Private Function TrimNull(item As String)
    Dim pos As Integer
    pos = InStr(item, Chr$(0))
    If pos Then
          TrimNull = Left$(item, pos - 1)
    Else: TrimNull = item
    End If
End Function

Open in new window

3
Comment
Author:Vadim Rapp
0 Comments

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month