Question How To Find Out Which Line Of Code Is Creating Lock

[MissBee]

Hi all!

I'd need some help with OE11.5 (and 11.6 at some instances). We have unfortunately some faulty transaction scopes and share locks created in our application. I'd need to trace which line of code and which program is generating these locks. Any tips for this - I remember we could do it somehow "easily" with OE11.5 but tried to google and it didn't quite succeed. So any tip how to do this easily is more than welcome !

And yes, our db & batch programs are running in Unix, I've used also "kill -USR1" for these processes but it's not enough, need something else now - easier way.

 

[RealHeavyDude]

In order to find out which to which blocks transactions and buffers are scoped you can compile the procedures/classes with the listing option. The compiler will produce an output file where you can see transaction and buffer scopes.

Usually unwanted share locks are caused by the buffer scope being larger than the transaction scope. If that is the case, at the end of the transaction scope the lock on the buffer is downgraded to a share lock until the buffer goes out of scope. Mostly you will see this behavior when you use the default buffer for a database table or temp-table which is always scoped to the procedure. A good practice to prevent this is to use named buffers and strong scope them to the transaction scope.

Example ( coded in internet explorer IDE ... ):

define buffer myBuffer for myTable.

do for myBuffer transaction:
  find myBuffer exclusive-lock.
  assign myBuffer.myField = "xyz"

end.

Heavy Regards, RealHeavyDude.

 

[Cringer]

Client logging has an option for tracing transactions through the code at runtime. Check the documentation for the exact syntax.

 

[MissBee]

Thanks for your help both !
Ok, so basicly my issue is this that I can see we have share locks in the db and I'd like my report not just to tell the share locks but also report which code and line of code is doing that. I've done some steps in the background to improve the new code going live but the old "trash" is then causing headache. So what would be easiest way to get my report to report that too ?

 

[Cringer]

How much code is there? It's going to be a tricky one to track down.
ABL2DB - Databasing ABL information for Analysis | The OpenEdge Hive might be of use, although it's not the simplest to set up and get going. Once you're going though it's very useful in terms of what it tells you. Bit of a hammer to crack a nut though?
The thing is, once you've detected a share lock it's already too late to work out what caused it.

 

[TomBascom]

There is no simple answer. You can use the client statement cache to get a stack trace at run time and that would give you some candidate lines of code -- but there is nothing about that which tells you that that code is *the* code that raised a lock.

Often times the issue with old code or badly written code isn't the statements that actually lock the records -- it is the manner of controlling transaction and lock scope or, more likely, the lack of any thought at all being given to the matter. It's not really "which line is doing that", it is more along the lines of "what is the list of missed opportunities to have avoided this?" (and that list will be quite long).

Probably the simplest thing that you can do is to compile with the LISTING option and parse the output looking for transactions that are scoped to the entire procedure. If you hunt those down and fix them properly you'll cure a lot of SHARE-LOCK ills. You could prioritize such procedures by identifying those that show up in the stack traces that you get from the client statement cache or kill -USR1.