DB Locking? ASP application issue? We can't tell.... help us please 1

[tw3]

Our app is, under certain conditions, not allowing an insert or update on a table. We must fix this someway, somehow. Here is our scenario.

BTW: We are using ASP Classic, ADO and SQL Server

1. We have a report query running. The recordset returned (in asp classic) in less than a second BUT the app may take a while to iterate through (1-2 minutes). During this time, no inserts or updates can happen.
2. We have tried using many different stored procedures to identify what kind of locking is happening. Obviously, when you see this kind of situation, you think "table lock". However, sp_lock and many other lock identifying stored procedures are not showing table locks. They may show page locks, but not table locks. We cannot confirm that locking is the issue here.
3. The app uses an account with DBO priviliges.
4. I have tried altering the query in the following ways:
-o- I have set the cursor type of the recordset to adOpenForwardOnly
-o- I have set the LockType of the recordset to adLockOptimistic
-o- I have set the table hint NONLOCK (equivalent to READUNCOMMITTED) on the table(s) that are being queried

At this point, I am tempted to say that this issue could be in the application simply because I cannot confirm that it is a DB locking issue. I have searched for any and all information that I can find on locking and it tells me that I am doing the right things but clearly I haven't gone far enough or I am down a rat hole. Dunno....

Can anyone give me a suggestion or investigative path?

Thank you VERY much.

 

[Sheco]

First thing to try is open up Query Analyzer and then, inside QA, open 2 windows...

In the first window try your report query and in the second one test your update and insert.

If that works then it must be the application... suspect that you've hit the connection limit... try closing conections on all pages so they are not left to time out in the connection pool... never put a connection or recordset object into a session variable...

oh, and do a search on this site and google for "Disconnected Recordset" ... if you are just spinning through a recordset processing it somehow then there is no reason it needs to maintain its connection to the db.

 

[Sheco]

app may take a while to iterate through (1-2 minutes)

I assume you mean it takes the ASP 1-2 mins to process the returned recordset.

 

[tw3]

Regarding some of this...

I am not certain I can recreate in QA. I will give this a concerted effort...

I am pretty certain we are not hitting a connection limit. Connection timeout is set pretty low (30 seconds) and it never has a high number of concurrent users (probably max 80 serving a page every 2-3 minutes or so - this would be a very unusually high load and frankly). Isn't the default connection limit somehting like 2 million or something?

In every page that instantiates the standard application connection object we make certain we close the connection as the script ends. This was an early area of investigation and while we found a couple of rarely used pages where we were not doing it, making the change did not change the behavior.

We never put connection of recordset objects in session vars.

I have looked at several posts regarding disconnected RS objects. There is a single function that ALL dynamic SELECT SQL statements go through (without exception) and I have made some changes to that function. Can you look at my code and tell me if I am doing this correctly? You will see lots of error trapping in this function but I can reproduce this inability to insert/update records at will and none of the error trappings engage.

FUNCTION CmdExecute2(oCMD, IgnoreErrors)

Dim Retries, DeadLock, NumErrors, ftr, HasErrors, ErrorDescription
Dim RecordSetObject

HasErrors = False
Retries = 3
DeadLock = True

ON ERROR RESUME NEXT

'NOTE: oConn is a global var set elsewhere in the app....
Set oCMD.ActiveConnection = oConn

If err.number <> 0 Then
<< ... Send and email to me every time this happens ... >>
End If

While (Retries > 0) AND DeadLock
DeadLock = False
HasErrors = False

Set RecordSetObject = Server.CreateObject("ADODB.RecordSet")
RecordSetObject.CursorLocation = adUseClient
RecordSetObject.Open oCMD, , adOpenForwardOnly, adLockBatchOptimistic

if err.Number <> 0 then

ErrorNumber = err.Number
ErrorSource = err.Source
ErrorDescription = err.Description
HasErrors = True

For Each oError in oConn.Errors
If oErrors.Number = 1205 Then
<< ... Send and email to me every time this happens ... >>
DeadLock = True
End If
Next

'Wait 2 Seconds before Retrying A DeadLock
If DeadLock Then
ftr = Now() + (2 / 100000)
While (Now() < ftr)
Wend

Retries = Retries - 1
Else
Retries = 0
End If
Else
Retries = 0
End If
Wend

ON ERROR GOTO 0

If hasErrors Then
Set CmdExecute2 = Nothing
Set RecordSetObject = Nothing

If IgnoreErrors Then
err.Raise ErrorNumber, ErrorSource, ErrorDescription, "", 1
Else
<< ... Send and email to me every time this happens ... >>
End If
Else
Set oCMD.ActiveConnection = Nothing
Set oCMD = Nothing
Set CmdExecute2 = RecordSetObject
Set RecordSetObject = Nothing
End If

END FUNCTION

 

[tw3]

btw: even with the changes to help create this disconnected recordset, I still cannot get the darn thing to accept an insert/update while the recordset is being iterated through.

 

[tw3]

also... changed the following lines of code:

Set oCMD.ActiveConnection = Nothing
Set oCMD = Nothing
Set CmdExecute2 = RecordSetObject
Set RecordSetObject = Nothing


to...

Set oCMD.ActiveConnection = Nothing
Set oCMD = Nothing
Set RecordSetObject.ActiveConnection = Nothing
Set CmdExecute2 = RecordSetObject
Set RecordSetObject = Nothing

Still no luck

 

[Sheco]

I was thinking of the SQL Server connection limit not IIS... I think the developer version of sql server and also

I guess I misunderstood... I thought your query would run in a couple of seconds and then you would USE the recordset object for 1-2 minutes and then close it... but the code

above opens the recordset and then immediately destroys it inside of the function.

There must be something going over my head here, why run a query and not examine the results? Or is the sql statement only an INSERT or UPDATE that doesnt return anything anyway? If so you could just use the Execute() method of the connection object and not even bother with a recordset... something like oConn.Execute "UPDATE Foo SET myfield=1 WHERE mydate < GetDate()"

PS: You probably want an err.clear in there after you trap the error but before you go around the loop again... I know you said that the error trapping doesnt engage for this particular problem but in general you'd want to clear the error before trying again.

 

[Sheco]

ah, that first part was an incomplete sentence... i was thinking that the developer version and MSDE had some connection limitations and was going to verify that youv'e got the standard or enterprise edition.

Also you got the disconnected recordset thing right by setting the recordset object's ActiveConnection property to Nothing.

 

[tw3]

The confusion is around this point... the function is passed a command object that is developed elsewhere in the code. The function passed back the recordset.

Actually, this function is a slightly stripped down version of the real one. I stripped out some of the logic that is used for non-select statements to make it more readable.

To explain, this function can be passed any type of command object (insert, update, delete, select, create table, create index, drop table, etc.) and it will execute it. Only under the circumstance that it is a select command will it pass the recordset back as a disconnected recordset. Other wise it does a:

Set CmdExecute2 = oCMD.Execute 'set the function to the returned result

... and passes whatever result is returned back to the calling code for examination.

Anyway, we are still dealing with guesses. Until I can figure out a way to mimic the app's function inside QA, I cannot diagnose this issue properly...

 

[tw3]

This is actually the full function....



FUNCTION CmdExecute2(oCMD, IgnoreErrors)

Dim Retries, DeadLock, NumErrors, ftr, HasErrors, ErrorDescription
Dim RecordSetObject, DoRecordSetObject
Dim objError, ErrorInfo
Dim MailContent


If LCASE(Left(oCMD.CommandText, 6)) = "select" and oCMD.CommandType = adCmdText Then
DoRecordSetObject = True
Else
DoRecordSetObject = False
End If

HasErrors = False
Retries = 3
DeadLock = True

ON ERROR RESUME NEXT

Set oCMD.ActiveConnection = oConn

If err.number <> 0 Then
ErrorInfo = ReturnSession
SendEmail "!! Connection Error Occurred !!", "HTML", ErrorInfo , ErrorInfo , "Service <myemail>", _
"", "", "Your Friendly Database Error Function <myemail>", "Service <myemail>", "", 0
End If

While (Retries > 0) AND DeadLock
DeadLock = False
HasErrors = False

If DoRecordSetObject Then
Set RecordSetObject = Server.CreateObject("ADODB.RecordSet")
RecordSetObject.CursorLocation = adUseClient
RecordSetObject.Open oCMD, , adOpenForwardOnly, adLockReadOnly
Else
Set CmdExecute2 = oCMD.Execute
End If

if err.Number <> 0 then

' Response.Write err.Number & "<br>"
' Response.Write err.Source & "<br>"
' Response.Write err.Description & "<br>"

ErrorNumber = err.Number
ErrorSource = err.Source
ErrorDescription = err.Description
HasErrors = True

For Each oError in oConn.Errors
If oErrors.Number = 1205 Then
MailContent = "A deadlock occurred" & "<br>" & ReturnSession & "<br>" & "<br>"
SendEmail "!! Handled Error Occurred !!", "HTML", MailContent, MailContent, "Service <myemail>", _
"", "", "Your Friendly Database Error Function <myemail>", "Service <myemail>", "", 0

DeadLock = True
End If
Next

'Wait 2 Seconds before Retrying A DeadLock
If DeadLock Then
ftr = Now() + (2 / 100000)
While (Now() < ftr)
Wend

Retries = Retries - 1
Else
Retries = 0
End If
Else
Retries = 0
End If
Wend

On Error Goto 0

'Response.Write "I am here<br>"
'Response.Write "hasErrors: " & hasErrors & "<br>"
'Response.End

If hasErrors Then
Set CmdExecute2 = Nothing
If DoRecordSetObject Then
Set RecordSetObject = Nothing
End If

If IgnoreErrors Then
err.Raise ErrorNumber, ErrorSource, ErrorDescription, "", 1
Else
'Response.Write "I am here!!!!!!!!<br>"
For Each objError in oConn.Errors
ErrorInfo = ErrorInfo & "Error Description: " & objError.Description & "<br>" &_
"Error Help Context: " & objError.HelpContext & "<br>" &_
"Error Help File: " & objError.HelpFile & "<br>" &_
"Error Native Error: " & objError.NativeError & "<br>" &_
"Error Number: " & objError.Number & "<br>" &_
"Error Source: " & objError.Source & "<br>" &_
"Error SQL State: " & objError.SQLState & "<br><br>"
Next

WriteSQLIncident oCmd, ErrorInfo, ProductionSystem
End If
ElseIf DoRecordSetObject Then
Set oCMD = Nothing
Set RecordSetObject.ActiveConnection = Nothing
Set CmdExecute2 = RecordSetObject
Set RecordSetObject = Nothing
End If

END FUNCTION

 

[Sheco]

OK, so you are saying:

[tt]
'This works:
Set oCMD Server.CreateObject("ADODB.command")
oCMD.CommandText = "<any statement except a SELECT>"
CmdExecute2 oCMD, True
Set oCMD = Nothing


'And this works:
Set oCMD Server.CreateObject("ADODB.command")
oCMD.CommandText = "<a SELECT statement with big resultset>"
Set oRS = CmdExecute2(oCMD, True)
Set oCMD = Nothing


'But THIS will hang up on CmdExecute2 until oRS is closed
Set oCMD Server.CreateObject("ADODB.command")
oCMD.CommandText = "<any SQL statement>"
CmdExecute2 oCMD, True
Set oCMD = Nothing
[/tt]


Is that right?

 

[Sheco]

Can you say if it is hanging up inside the function or outside?

 

[tw3]

just had my first bit of progress in diagnosis in about a week ... be back with you soon...

Tw3

 

[Sheco]

Yuck, a week!?! Now I'm curious to see how it turns out.

 

[tw3]

well, I don't have a conclusion just yet ...

After I made the disconnected record set change you suggested, I logged into the app, ran the report and then simultaneously tried to update a record. No success. BUT, I could tell that others in the office COULD insert/update into the table while the report was running. So, I figure that IIS and/or the application as written will not allow a single user (me) to pull up a report window AND a record editing window separately at the same time. It never occurred to me that the behavior I was seeing was related to the fact that I was the same person. But, for seom reason, it is meaningful.

So, now, I am totally confused. But, I am certain that table locking is not my issue. This is an app level issue of some sort. AND, it may very well have been fixed through your disconnected recordset implementation. We will see. I have made these upgrades to the production system and will watch it carefully over the next day. I have also enhanced how the errors are sent to me to that I can see more information. Unfortunately, Friday is our least active day so we will see.

Hopefully, this will also fix the last big continuing issue that we seem to have - intermittent script timeout issues. I have raised the timeout setting a little and saw a drop off in these errors. I have always thought everything was related but maybe that are not.

I will come back to upate this with whatever I find.

 

[damber]

tw3, seems like a difficult problem, but it sounds like you're on the right track.

As a side note, you might want to consider using GetRows or GetString to speed up your report if you're currently iterating over a recordset object with lots of rows - usually much quicker and also disconnected.

I'd have to agree that it's the app that's your problem, not the DB. And I think it's important to follow Sheco's train of thought on testing exactly where this starts to hang.. use response.write and response.flush liberally to see where you are in the process when it hangs.

Have you tried a different web server outside of your standard configuration (e.g. not QA/Prod) ? Maybe try setting up a fresh Webserver install on a different machine with all bog standard settings, and retesting the problem - there may be some configuration in IIS that is locking access to something.. bit of a long shot, but you never know.

Next thing to try is to take this feature outside of the main application process (don't use common includes/functions/variables etc) and see if it still has issues, if it does, it is probably the web server, otherwise it will most likely be your application design.

Also, if it is inside the function.. try doing it without the function.. if this works, your function is to blame, if not, look at what other components of the process are shared - e.g. session variables, application variables, connections etc - you could find it is waiting to use something that is locked at the session or application variable level by the report process.


Hope that gives you some fresh ideas.

A smile is worth a thousand kind words. So smile, it's easy!

 

[Sheco]

Are there any clues in the web server's Event Log?

Also, if the problem happens for you but not others, how does the web server distinguish between users? Is IIS using something other than plain anonymous access... like integrated windows authentication? Perhaps the web app itself keeps a list of valid users with a database and then uses session variables to keep track of different users once they are logged in?

I agree with damber that it is difficult but there is still more low-hanging-fruit when it comes to narrowing the problem.

 

[tw3]

Well... I made the change to a disconnected recordset and there are no errors in the production environment yet today. This is a freaking miracle at the moment.

About some of the other points made...

GetRows and/or GetString, although used occassionally for some things, cannot work generally in the app especially in reporting. We use the information in the fields object to help us understand the formatting of the report - especially in excel dumps. There are ways around this but it would be a very complex re-write to roughly 5000 lines of highly complex code that exists already. Better, for the moment to fix the issue that we have.

In regards to a different web-server, I would love to. I admit to something almost embarrassing - our development server and prodution server are one in the same and there is not development database. We develop against the production database. As a "seasoned professional" with almost 17 years in IT, I know how stupid that is. But, it is the situation with which this company is currently faced.

We did recreate the feature outside of the main app. I wrote a script to simulate the process and we did encounter the same errors. I used it to play with different locking schemes and for speed comparisons. One really cool thing - it helped me see a way to re-write the reporting process to increase the speed with which the program ran. It always bugged me that a report with only 2000 records would take 3-4 minutes to run - espeically on the horse of a box that we run this thing on - but without encountering errors I had no motivation to fix it when we had a lot of other stuff on our plate. So, with some smart use of arrays, was able to make some of those long reports run in 1/5 the time. This was quite cool to me but, in essence, all it did was shorten the window in which these errors could occurr. That was a step in the right direction though.

The way the app works in its connection to the DB is fairly simple. Any page that needs to access the DB - which is every one - has an include file that makes that connection immediately. That connection is what is used for the life of the page. All of those pages also close the connection as the very last command in that page. The login for all is that same. All sessions use that login.

Overall, the thing that sent me in the wrong directions was this - I totally misinterpreted something I was seeing. When I ran the report query, I knew that I couldn't insert/update a record on that same table. It never occurred to me that maybe I couldn't do the same on another table. In fact, what I found out yesterday was this - while that report is running, IIS will not serve me ANY OTHER PAGE. I have always known that reports affected the error issues I was seeing. I assumed that what I was seeing with me was the recreation of those error issues - but it wasn't. I feel a little dumb about this.

Now I know that we had a situation where reports enhance the possibility of users encountering timeouts or other waits in processing against that table.

Anyway, I am abotu 100% certain that the disconneted recordset fixed the issue. This particular table has about 30 concurrent users on it right now and I just ran its big monster report about 5 times in a row - no timeouts, no errors of any sort. I am excited. Holy smokes I thought my clients were gonna kill me. I will have to continue to monitor this but right now it all looks really, really good.

Last thing... I am a fairly regular member of experts-exchange. THis group was clearly superior