Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Resolving blocking conflicts in SQL Server in user-friendly manner

Vadim Rapp
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.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
                      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 
                          if @BlockedUser is not null
                              set @v = 'NET SEND ' + @LockingHost + ' Your ' + @LockingApp + ' is blocking ' + @BlockedUser + '. Please complete your operation.'
                              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
                          if @LockedHost is not null 
                              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.'
                                  set @v = 'NET SEND ' + @LockedHost + ' You are blocked by machine ' + upper(@lockinghost) + '. That machine and database administrator have been notified.'
                              exec master..xp_cmdshell @v, no_output
                          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

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

Vadim Rapp

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.