| Detect SQL timeout from ASP & Issue RollbackTrans | ||
| 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. 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 ThenadoConn.CommitTrans End If adoConn.Close Set adoConn = Nothing ------------------------------------------------------- |
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。