Resolving blocking conflicts in SQL Server in user-friendly manner
Published:
Browse All Articles > Resolving blocking conflicts in SQL Server in user-friendly manner
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.
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:
Here is the code to obtain contact information for the affected users:
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:
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.
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
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
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
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
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.
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.
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
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.
Comments (0)