ROWID when a lock is on the run

BobyIsProgress · Jun 20, 2018

Hi,
recently I have many locks that block all the systems.

With the system tables _Lock and _File I manage to find the user, the machine and the tables that are locked (exclusive and shares) but I don't find the rowid of each tables. Is there a way to found out ?

Best Regards,

- A newcomer -

RealHeavyDude · Jun 20, 2018

You get the RECID - which, in a nutshell, is the predecessor of ROWID. Although one is urged to use ROWID and forget RECID, it is still used in many places in the meta-schema, VSTs and other system tables.

One of the disadvantages of RECID - amongst lots of others which are more relevant to not use it anymore - is, that for dynamic buffers you need a FIND-BY-RECID function. Therefore you need to build a full-blown dynamic query if you want to dynamically fetch the related record.

TomBascom · Jun 20, 2018

Working with _LOCK is almost certainly a mistake.

BobyIsProgress · Jun 20, 2018

Ok I will go on the rowid.

My main issue is that with the lock on the run I can't connect it with the Trans VTS because my Lock VTS don't have a field _Lock-TransID.
Also
Do you know a way to connect the two of them ?

Also I'm sure there is a way to connect the recid and the rowid, but I don't know how to it without writind directly something like that:

Code:

 Find "my table name" where RECID("my table name") = _Lock._Lock-recid no-lock.
            mRowid = "my table name":rowid().

And this is my actual code for lock search

Code:

 FOR EACH _Lock WHERE _Lock._Lock-ID < 200
                            AND   _Lock._Lock-Table <> ? 
                            AND  (_Lock._Lock-Flags BEGINS "X"
                                   OR _Lock._Lock-Flags BEGINS "S") NO-LOCK:
        CREATE TT1 .             
        BUFFER-COPY _Lock TO TT1 .
        FIND FIRST _File WHERE _File._File-Number = _Lock._Lock-table NO-LOCK NO-ERROR .
        TT1.NomTable = _File._File-Name .

        FIND FIRST _Connect WHERE _Connect._Connect-pid  <> ?
                                     AND   _Connect._Connect-pid  <> 0
                                     AND   _Connect._Connect-Type = "REMC"
                                     AND   _Connect._Connect-Usr  = _Lock._Lock-Usr NO-LOCK NO-ERROR .
        IF AVAILABLE _Connect THEN ASSIGN
            TT1.Machine = _Connect._Connect-Device
            TT1.PID     = _Connect._Connect-PID .

    END.

BobyIsProgress · Jun 20, 2018

TomBascom said:
Working with _LOCK is almost certainly a mistake.

I'm afraid that the lock are not always in TRANSACTION procedure ...

TomBascom · Jun 20, 2018

Don't focus on the locks. Focus on the blocked connections.

Code:

for each _connect no-lock where _connect-wait <> " -- ":  /* or where _connect-wait1 = "REC" if you only want to know about record locks */
  display _connect.   /* you probably want to only show a few columns... */
    /* if _connect-wait = "REC" then _connect-wait1 happens to contain the RECID of the record being waited on
     * depending on your db design that *might* be enough to identify the table (but RECIDs are only unique within a storage area) 
     */
end.

This is a much better starting point. It focuses you on the sessions that are actually blocked, and why, from the top down rather than trying to back into it from the lock info.

TomBascom · Jun 20, 2018

BobyIsProgress said:
I'm afraid that the lock are not always in TRANSACTION procedure ...

That doesn't mean you are not making a mistake.

BobyIsProgress · Jun 20, 2018

I understand. I'm going on this way.

Do you have any tips for finding a rowid corresponding to the recid while working with the VTS table ?

BobyIsProgress · Jun 20, 2018

TomBascom said:
Don't focus on the locks. Focus on the blocked connections.

Code:

for each _connect no-lock where _connect-wait <> " -- ": /* or where _connect-wait1 = "REC" if you only want to know about record locks */ display _connect. /* you probably want to only show a few columns... */ /* if _connect-wait = "REC" then _connect-wait1 happens to contain the RECID of the record being waited on * depending on your db design that *might* be enough to identify the table (but RECIDs are only unique within a storage area) */ end.

This is a much better starting point. It focuses you on the sessions that are actually blocked, and why, from the top down rather than trying to back into it from the lock info.

but it seems that _connect-wait1 is an integer and not a character .

TomBascom · Jun 20, 2018

Why do you think that is a problem?

Rob Fitzpatrick · Jun 20, 2018

What is your version of OpenEdge?

BobyIsProgress · Jun 21, 2018

TomBascom said:
Why do you think that is a problem?

I didn't read it right, i'm sorry. I'm quite a new with this technologie .
I will change my question. What is the meaning of _Connect-wait ? what triggers him ?

Rob Fitzpatrick said:
What is your version of OpenEdge?

And I'm in 10.2b 0.5

TomBascom · Jun 21, 2018

_connect-wait is the reason that the connection is waiting. If it equals "REC" then the connection is waiting for a record lock.

If the connection is waiting for a record lock then the _connect-wait1 field will hold the RECID of the record it is waiting for.

TomBascom · Jun 21, 2018

OpenEdge 10 is ancient and obsolete. You should upgrade.

OE10 is an especially bad version to be attempting to read _LOCK.

BobyIsProgress · Jun 21, 2018

Ok I understant, it's planne for us to do it but not right now.

My second interogation is with the _File._File-name and the recid ow can I found the rowid ?

RealHeavyDude · Jun 21, 2018

If you need the ROWID of a record for which you only know the RECID then you have basically 1 1/2 options:

For understanding the difference between RECID and ROWID you may want to have a look @ Progress KB - FAQ: Recid and Rowid.

You fetch the record with the RECID ( bufferName no-lock where recid ( bufferName ) = theRecidYouKnow ). As soon as you have the record in the buffer you can then query the ROWID ( rowidYouNeed = ROWID ( bufferName ) ).
Really 1/2: One can calculate the ROWID from a RECID and vice versa. BUT: This doesn't hold water under all circumstances - hence the 1/2 option ( Progress KB - 4GL. How to convert a ROWID to a RECID and Vice Versa ) ...

BobyIsProgress · Jun 21, 2018

RealHeavyDude said:
If you need the ROWID of a record for which you only know the RECID then you have basically 1 1/2 options:

For understanding the difference between RECID and ROWID you may want to have a look @ Progress KB - FAQ: Recid and Rowid.

You fetch the record with the RECID ( bufferName no-lock where recid ( bufferName ) = theRecidYouKnow ). As soon as you have the record in the buffer you can then query the ROWID ( rowidYouNeed = ROWID ( bufferName ) ).

Really 1/2: One can calculate the ROWID from a RECID and vice versa. BUT: This doesn't hold water under all circumstances - hence the 1/2 option ( Progress KB - 4GL. How to convert a ROWID to a RECID and Vice Versa ) ...

Thank you for the tips, in fact I already was able to do it with the buffer name. But I wasn't with the VST tables.

Rob Fitzpatrick · Jun 21, 2018

I think it's likely more important for you to understand the specific code involved in this record contention than to know the specific records involved. Data may change and rowids/recids will change along with them, but it is the code that is causing this contention in the first place. If these record locks "block all the systems" then the code needs to be fixed.

You have said you know which users are involved. If you know that then the next step is to find out what those users are doing to cause this record contention. This presentation should help you. It is from the Progress Revolution 2011 conference.

What's That User Doing? The Progress OpenEdge Client Request Statement Cache
Gus Björklund, Progress Software
http://download.psdn.com/media/revolution_2011/oe11/OE1111.wmv

BobyIsProgress · Jun 21, 2018

Do you have any documentation about the VST tables ?

BobyIsProgress · Jun 21, 2018

Rob Fitzpatrick said:
I think it's likely more important for you to understand the specific code involved in this record contention than to know the specific records involved. Data may change and rowids/recids will change along with them, but it is the code that is causing this contention in the first place. If these record locks "block all the systems" then the code needs to be fixed.

You have said you know which users are involved. If you know that then the next step is to find out what those users are doing to cause this record contention. This presentation should help you. It is from the Progress Revolution 2011 conference.

What's That User Doing? The Progress OpenEdge Client Request Statement Cache
Gus Björklund, Progress Software
http://download.psdn.com/media/revolution_2011/oe11/OE1111.wmv

Thanks I will work on this way too

ROWID when a lock is on the run

Member

Well-Known Member

Curmudgeon

Member

Member

Curmudgeon

Curmudgeon

Member

Member

Curmudgeon

ProgressTalk.com Sponsor

Member

Curmudgeon

Curmudgeon

Member

Well-Known Member

Member

ProgressTalk.com Sponsor

Member

Member

Similar threads