Get Record information from Locking Table

[dlangschied]

I have ran across the need for this before and recall that nothing was possible. But I will try again, as the current situation makes this even more necessary.

I have some record locking issues. I can determine through the VSTs the record that is locked and the table. What I need is to be able to examine the content of the locked record.

I know that I cannot pass the table name as a variable to a FOR EACH

(eg.
FOR EACH _UserLock-Table NO-LOCK:
FOR EACH _File Where _File_Name = _UserLock-Table NO-LOCK:
FOR EACH _File-Name WHERE recid(_UserLock-Recid) NO-LOCK:

_File-Name needs to be expressed as variable because I do not know what it is.
)

OK...
So now what? How can I get the record information?

 

[Osborne]

Are you able to obtain the information by using a dynamic buffer?:

DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.

CREATE BUFFER hBuffer FOR TABLE _File._File-Name.
hBuffer:FIND-UNIQUE("WHERE RECID(" + _File._File-Name + ") = _UserLock._UserLock-Recid[1]", NO-LOCK) NO-ERROR.
/* or
hBuffer:FIND-UNIQUE("WHERE RECID(" + _File._File-Name + ") = _Lock._Lock-RecId", NO-LOCK) NO-ERROR.
*/
MESSAGE hBuffer:AVAILABLE VIEW-AS ALERT-BOX.

 

[dlangschied]

I am getting the message below from the code:

** Missing FOR, FIND or CREATE for a table with _File-Name in current block. (232)
** Could not understand line 3. (196)

I am on OE10.1C; does that make a difference?

 

[Osborne]

The code posted was a basic example for reading a table not known until run-time and was missing the parts that read the locked records. Either put the buffer workings after the "FOR EACH _File ..." line, or run a procedure passing _File-Name as a parameter:

...
FOR EACH _File ... NO-LOCK:
   RUN record_details (_File._File-Name).
END.
...

PROCEDURE record_details:
   DEFINE INPUT PARAMETER pTable AS CHAR NO-UNDO.

   DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.

   CREATE BUFFER hBuffer FOR TABLE pTable.
   hBuffer:FIND-UNIQUE("WHERE RECID(" + pTable + ") = _UserLock._UserLock-Recid[1]", NO-LOCK) NO-ERROR.
   ... /* If buffer available code here to get the full record details */
   DELETE OBJECT hBuffer.
END PROCEDURE.

 

[TomBascom]

Using _LOCK is DANGEROUS.

_UserLock is only slightly less bad.

Both are unreliable.

Access to _LOCK might sort of mostly kind of seem to "work" in a small development environment.

But do not fool yourself. This is not code that you want to be executing in production.

You most especially do NOT want to embed it in code that runs routinely. Lots of people dream up little routines that try to replace the "record X is in use by someone" message. This does not work. Even if it appears to work on your development system it will cause problems under load and it will be unreliable.

What's so bad about _LOCK?

Like all VSTs it is a representation of a structure in shared memory. This particular structure does not naturally map to a table very well and it changes extremely rapidly. Far more rapidly than you can scan it with 4GL code. Queries on _LOCK may *never* complete, there is no guarantee that you will even find the entry that you are looking for, the entries move around and there is no index that you can use to order the entries (by user, by recid etc...) They will also consume a core when they execute. Several users who just happen to fall into code that scans _LOCK entries will bring a system to its knees. Particularly on busy systems with a large -L.

Stay away from _LOCK. _UserLock is a bit better in that there are only 512 entries per user. But it still changes very, very rapidly and there is o guarantee that you will find what you are looking for.

It can occasionally be useful to look at _LOCK in a controlled development environment. But you should never try to use it in production. You might, just maybe, possibly run some special ad-hoc code on rare occasions when your back is up against the wall and you are desperate. Really desperate. But, please, don't make it part of your regular code.

 

[dlangschied]

Thanks!

 

[dlangschied]

You have honed in on the issue and that is, in fact the user locked record. We are not looking to execute against this with anything inside of the database. It was only going to be used as a heads up for a record locked for a long time, like an hour. I was looking to use UserLock, so _Lock not an issue. I do want to use this in production <sigh>. The number of locking issues that we are having are not huge, but when I get woke up at 2:00AM it is a big deal for me.

Argh!

 

[TomBascom]

Scanning _LOCK is not the answer.

You want to monitor active transactions and blocked sessions. Those are important and they are safe to watch for. They will tell you what you need to know at 2am without having to mess around with _LOCK.

 

[RealHeavyDude]

Please excuse me for stepping in late.

I am 100% with Tom. IMHO the _Lock is just looking at the symptom - monitoring long running transactions is more looking at the root cause. Not only do you want to eliminate locks on records that are hold for much longer than they need be, you also want to eliminate long running transactions which can cause all sorts of havoc to your system. While a process or user waiting on a locked record might be a bad thing, blowing the before image because of long running transactions will bring down your system, thus affecting everybody and everything.

You might want to have a look into the _Trans table ( _Trans._Trans-State = "ACTIVE" ) and the _Connect ( _Connect._Connect-Usr = _Trans._Trans-UsrNum ). The _Trans._Trans-Duration field will tell you how long the transaction is active. You might then drill down further when you find transactions that are open for a long time ...

Heavy Regards, RealHeavyDude.