Member Avatar for jcarbillon

Hi Guys.

Is it possible to trap this error in Classic ASP, and redirect to another page instead of displaying this error?


Microsoft OLE DB Provider for SQL Server error '80004005'

Transaction (Process ID 104) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

It happens when a lot of people accessing my intranet site all at the same time.

Thanks in advance!

  • 2 Contributors
  • 1 Reply
  • 270 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Golden-Boy

Dani AI

Generated

This can be handled more robustly than just using a blanket On Error Resume Next and redirect. As noted you can catch errors in ASP, but two important points are: (1) the COM Err.Number you see in ASP is a generic HRESULT and is not the SQL Server error code; check the ADO Errors collection and its NativeError to detect SQL Server deadlock (1205); (2) best practice is to retry a deadlock victim a small number of times with backoff and to log the failure — redirecting hides the root cause and leaves you blind to recurring problems.

Example server-side pattern (Classic ASP / ADO): detect native SQL error 1205 and retry (limit retries).

' ExecuteWithRetry(conn, sql, maxRetries) - returns recordset or Nothing
Function ExecuteWithRetry(conn, sql, maxRetries)
  Dim attempt, rs, aErr, isDeadlock
  attempt = 0
  Do
    attempt = attempt + 1
    On Error Resume Next
    Set rs = conn.Execute(sql)
    If Err.Number = 0 Then
      On Error GoTo 0
      ExecuteWithRetry = rs
      Exit Function
    End If
    isDeadlock = False
    For Each aErr In conn.Errors
      If aErr.NativeError = 1205 Then
        isDeadlock = True
        Exit For
      End If
    Next
    Err.Clear
    On Error GoTo 0
    If Not isDeadlock Or attempt >= maxRetries Then Exit Do
    ' optional: small backoff before retry (use caution with server-side delays)
  Loop
  ExecuteWithRetry = Nothing
End Function

If you can change server code, implement retry in the database (stored proc) using TRY/CATCH and only retry when ERROR_NUMBER() = 1205. Example sketch:

DECLARE @retries INT = 3;
WHILE @retries > 0
BEGIN
  BEGIN TRY
    BEGIN TRAN;
      -- DML here
    COMMIT TRAN;
    BREAK;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRAN;
    IF ERROR_NUMBER() = 1205
    BEGIN
      SET @retries = @retries - 1;
      IF @retries > 0 WAITFOR DELAY '00:00:01';
      ELSE RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE());
    END
    ELSE RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE());
  END CATCH
END

Finally, address root causes: shorten transactions, keep transactions to a single quick statement when possible, add appropriate indexes to avoid scans, consider READ_COMMITTED_SNAPSHOT if suitable, and set a lower deadlock priority for noncritical work. Log deadlocks (server-side table or SQL trace/extended events) before redirecting so the problem can be diagnosed rather than just hidden.

Member Avatar for Golden-Boy

Yes it is possible. On the top of the main ASP page, put <% On Error Resume Next %> At the end of your page, put an email system if you want, where in body part of the mail, you make it response the error and send you

Example sending the email using CDO.Message:

<%
If Err.Number <> 0 Then

[INDENT] [B]
'CREATE THE MAIL OBJECT[/B] 
Set objMail = Server.CreateObject("CDO.Message")
objMail.Configuration.Fields.Item ("") = 2 
objMail.Configuration.Fields.Item ("") = application("SMTP")
objMail.Configuration.Fields.Item ("") = 25
objMail.Configuration.Fields.Item ("") = False 
objMail.Configuration.Fields.Item ("") = 60
			
objMail.Configuration.Fields.Item ("") = 1 
objMail.Configuration.Fields.Item ("")= [B]SMTP USERNAME[/B]
objMail.Configuration.Fields.Item ("")= [B]SMTP PASSWORD[/B]
									
objMail.Configuration.Fields.Update
objMail.MimeFormatted=false


'[B]SET KEY PROPERTIES[/B]
objMail.From = "[B]MYSELF@MYDOMAIN.COM[/B]"
objMail.To = "[B]SENDER@SENDERDOMAIN.COM[/B]"
objMail.Subject= "ASP ERROR"
objMail.HtmlBody = "Description = " & Err.Description & "<br>Error Number:" & Err.Number & "<br>"Error Line : " & Err.Line

'SEND THE EMAIL
objMail.Send

'CLEAN-UP MAIL OBJECT
Set objMail = Nothing
[/INDENT]

'---- [B]REDIRECT USER TO ANOTHER PAGE[/B] -----

Response.Redirect("secondarypage.asp")

End If

On Error GoTo 0
%>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.