Question Lock wait timeout of 1800 seconds expired (8812)

Hi,

One of our clients is using Progress Schema Holder and maps to an MS SQL Server. Application is hanged for a long time. When we checked the asbroker log and found the below error.

Can anyone help me sort out this issue? Is this raised due to the locking by SQL DB? Or shall we look into the Progress code base? I am asking this because no other Progress DB customers are reporting this issue. Any more log files I can refer in the SQL side? Can we give a utility to this customer when it hangs which user is locking these records (a kind of debug utility).

Kindly suggest which are the area I need to focus on so that customers should not report this issue.

[20/05/29@23:06:57.669-0400] P-020192 T-014932 1 AS -- (Procedure: 'STOCK-1 APPSERVER\STOCK\STCK0001-A.P' Line:3821) Lock wait timeout of 1800 seconds expired (8812)
[20/05/29@23:06:57.669-0400] P-020192 T-014932 1 AS -- (Procedure: 'STOCK1 APPSERVER\STOCK\STCK0001-A.P' Line:3821) Lock wait timeout of 1800 seconds expired (8812)
[20/05/29@23:06:57.773-0400] P-000532 T-011312 1 AS -- (Procedure: 'DEL1 appserver\stOck\stck002-a.p' Line:893) Lock wait timeout of 1800 seconds expired (8812)

Thanks
-Philip-
 

TomBascom

Curmudgeon
The error message is providing you with the DEBUG-LIST line number of the code responsible for the problem. I would start by looking at that code.
 

Cringer

ProgressTalk.com Moderator
Staff member
I think the question is more "Is the ABL locking because the SQL is blocking it? And how can I identify this?"
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Is this raised due to the locking by SQL DB?
I don't know your application topology. If your OE App Servers are the only database clients then they are responsible for any record locks on the SQL side. If there are other SQL database clients that may be locking records that your App Server agents are attempting to lock then your scope of investigation is broader.

I agree with Tom that you need to look at the code indicated by the error messages.

Any more log files I can refer in the SQL side? Can we give a utility to this customer when it hangs which user is locking these records (a kind of debug utility).
I don't know. That question is probably best directed to a SQL Server forum. But first ask yourself, what do you want to know? You should be able to determine which tables are involved by looking at your compile debug-list output. Would it help you to know which specific records were involved in the lock contention? If so, you'd want to look at the SQL Server equivalent of the _lock table at the time of the contention (i.e. immediately after the error 8812 appears in the log).
 
Thank you. My doubt is SQL performance is a bit lower than the Progress DB So that until creating / Writing in SQL completes, corresponding tables will be locked in Progress Db as well?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
My doubt is SQL performance is a bit lower than the Progress DB So that until creating / Writing in SQL completes, corresponding tables will be locked in Progress Db as well?
The schema holder has no data. The only locks I would expect in it would be if you were modifying the schema. I don't think data-access code would be doing anything with the schema holder, other than compiling dynamic queries. Certainly no "table locks".
 

TomBascom

Curmudgeon
I don't see what the point is in getting worked up about hypothetical SQL Server issues before you look at the code to see what it is trying to do, identify which table is involved, and check for all of the usual record locking faux pas.

At that point you might be able to ask a sensible question about why SQL Server might be doing something different than your usual expectations. But until then you're just stumbling around in the dark making stuff up.
 
Thank you so much for the replies.
I have one more clarification required.
Below is the current appserver log message.
[20/05/29@23:06:57.669-0400] P-020192 T-014932 1 AS -- (Procedure: 'STOCK-1 APPSERVER\STOCK\STCK0001-A.P' Line:3821) Lock wait timeout of 1800 seconds expired (8812)
It would be great if I get a message like
[20/05/29@23:06:57.669-0400] P-020192 T-014932 1 AS -- (Procedure: 'STOCK-16 APPSERVER\STOCK\STCK0005-b.P' Line:3321) Already have locked the record - (Procedure: 'STOCK-1 APPSERVER\STOCK\STCK0001-A.P' Line:3821) Lock wait timeout of 1800 seconds expired (8812)
So that I can debug the program which is already locked that record.
How can I build a utility to show the program name which locked the record? KB Progress KB - How to find who is locking what table(s) using ABL is very much helpful to build a utility to show lock info but it lacks one column Program name.

Thanks
-Philip-
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
You won't necessarily find out directly which procedure locked a record (more on that below). But you can follow a process to find out.

The first step is to identify, as quickly as possible, that a client is blocked (i.e. queued, waiting on a record lock). For example you could use the Blocked Sessions display in ProTop (disclaimer: I work on this product).

Second, identify which client session is blocking them (e.g. via ProTop or promon).

Third, identify the resource (e.g. rowid & table) that is the point of contention (again, via ProTop or promon).

Fourth, identify what the blocking session is doing right now. What is their ABL call stack?

You can get a call stack manually via the proGetStack command, with the blocking client's PID, provided you have sufficient permission. Or you can enable the Client Database Request Statement Cache feature (10.1C or later) to have the client send all or part of its call stack to the database when it accesses the database, and then read that information from the client's _Connect record.

You can enable/disable this feature in promon and write ABL code to read the data. Or you can have ProTop do it for you: set the user number and then look at the user info.

There was a great session on the Client Database Request Statement Cache feature (aka Client Statement Cache) feature by Gus Björklund at the 2011 Progress Revolution conference: http://download.psdn.com/media/revolution_2011/oe11/OE1111.wmv.

One last note, explaining the first sentence: you might get lucky and find that the line of code at the top of the call stack of the blocking client is very close to the point where it took the record lock that caused the contention you are investigating. But it also might be the case that they started their transaction an hour ago and took that lock 20 minutes and 10 procedures ago. You won't know until you look. But if you get their full call stack, you will have a list of candidate procedures to look at. You can inspect the code and its includes, or you can do a compile xref. Either way you should be able to find where and why it takes this lock, and address the problem.
 
Top