I developed an application that worked fine in development and single user testing, but when multiple users starting using the application, I ran into some cases when users got timeout errors from ASP while the code was trying to execute a long SQL stored procedure. This long stored procedure uses a transaction because it involves a lot of updates, inserts, and deletes, and if any statement fails, then all of the statements must be rolled back to the state that they were in before the transaction began. When the timeout was occurring from ASP, the data in my database was getting messed up because some of the statements in the transaction were being executed but then the transaction was just stopped in the middle of execution without being rolled back.
I wanted to find out a way to detect when a timeout occurs so that I could then manually tell the stored procedure to rollback the transaction. I started searching for how to do this, and I found that I couldn't find much information on this topic.
I started out trying to use the LOCK_TIMEOUT value in the SQL Server stored procedure on all of my SQL statements and to then detect if this was exceeded. However, I learned that if the LOCK_TIEMOUT value is exceeded, your transaction skips the blocked operation and receives trappable error number 1222 with the message "Lock request time out exceeded." In this situation, the blocked statement is cancelled and the rest of the transaction continues as soon as a locking conflict occurs. A transaction isn't rolled back if you exceed the lock timeout. Exceeding the timeout skips the blocked operation, but any remaining SQL statements in the transaction still execute. You need to trap for error 1222 and take appropriate action if you want to roll back any user-defined transactions. This was unacceptable to me because it is too much of a hassle to write your own error-detection code after each statement in your stored procedure and then to write the code to rollback the transaction.
Well, I finally was able to figure out how to detect the timeout and to rollback the transaction from the ASP page in the case that a timeout does occur. It is kind of a roundabout way of doing things, but it works. I still declare a transaction inside my stored procedure and use the normal method of detecting errors and conditions where I need to rollback the transaction from within the stored procedure. However, I also begin a transaction from within the ASP page by calling adoConn.BeginTrans in my ASP code. Then I set a CommandTimeout value for the maximum time I want to allow for this stored procedure to execute, and if the ADO command takes longer than this specified time, I check for the timeout error and if it exists, then I rollback the transaction from the ASP code with RollbackTrans. Else, I go ahead and commit the transaction with CommitTrans.
It took me a lot of research and testing to figure this out, but it now works perfectly! Just thought I would pass along what I learned. Here is a code excerpt.
-----------------------------------------------------
Dim colErrs, objError, timeout, adoConn, adoCmd
timeout=False
Set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.CursorLocation = adUseClient
adoConn.Open Session("UserConnStr")
adoConn.IsolationLevel = adXactReadUncommitted
'The next line starts a transaction
adoConn.BeginTrans
Set adoCmd = Server.CreateObject("ADODB.Command")
'I added this to give the command 45 seconds to execute.
adoCmd.CommandTimeout = 45
adoCmd.ActiveConnection = adoConn
adoCmd.CommandText = "sproc_RecalculateQuantities"
adoCmd.CommandType = adCmdStoredProc
adoCmd.Execute
Set colErrs=adoConn.Errors
If adoConn.Errors.Count <> 0 then
For Each objError In colErrs
'This is the error number for a timeout.
If objError.Number=-2147217871 Then
adoConn.RollbackTrans
Response.Write "The query timed out before finishing. Please try again."
timeout=True
adoConn.Errors.Clear
Exit For
End If
Next
End If
Set adoCmd = Nothing
If Not timeout Then
adoConn.CommitTrans
End If
adoConn.Close
Set adoConn = Nothing
-------------------------------------------------------
Hope that this helps you. I know that I will be using this code a lot from now on for my most complex stored procedures involving transactions.
|
