"Index" entries in the lock table? - Forum - OpenEdge RDBMS - Progress Community

"Index" entries in the lock table?

 Forum

"Index" entries in the lock table?

This question is not answered

11.6.0 64-bit
CentOS 6.9

I have been testing Update Statistics recently to investigate a problem at a client site.  In 11.6.0 it seems to use excessive locks.  I have a case open with TS for that.

In my testing I tried to understand better what is happening under the covers, including what is being locked, so I can better predict whether it will run at a client site without a lock table overflow.

As I understand it, Update Statistics updates the following:

PUB._Syscolstat
PUB._Sysdatestat
PUB._Sysidxstat
PUB._Sysintstat
PUB._Sysnumstat
PUB._Sysnvarcharstat
PUB._Systblstat

While it was running on a test DB, I queried the lock table:

for each _lock no-lock:
  find _file no-lock where _file-number = _lock-table no-error.
  display
    _file-name when available( _file )
    _lock-table format "->>>>9"
    _lock-type
    _lock-recid
    _lock-flags
    _lock-transid
    _lock-usr
    _lock-name
    no-error.
end.

I ran the query repeatedly and mostly got nothing.  Occasionally I saw data, like this:

File-Name          Table         Type           RECID      Flags          Trans Id          Usr          Name
                    1057          REC          259355          X          932453851          24          sysprogres
                    1057          REC          260210          X          932453851          24          sysprogres
_Sysdatestat        -109          REC          258843          X          932453851          24          sysprogres
_Sysdatestat        -109          REC          259279          X          932453851          24          sysprogres
_Sysnumstat         -108          REC          262960          X          932453851          24          sysprogres
_Sysintstat         -107          REC          259440          X          932453851          24          sysprogres
_Syscolstat         -104          REC          261108          X          932453851          24          sysprogres
                    1047          REC          261392          X          932453851          24          sysprogres
_Sysintstat         -107          REC          260464          X          932453851          24          sysprogres
_Sysnvarcharstat    -119          REC          261871          X          932453851          24          sysprogres
_Sysintstat         -107          REC          260432          X          932453851          24          sysprogres
                    1048          REC          259188          X          932453851          24          sysprogres
_Syscolstat         -104          REC          258979          X          932453851          24          sysprogres
_Sysnumstat         -108          REC          263367          X          932453851          24          sysprogres
_Sysnumstat         -108          REC          258946          X          932453851          24          sysprogres
                    1050          REC          259009          X          932453851          24          sysprogres
_Sysidxstat          -94          REC          258401          X          932453851          24          sysprogres
                    1057          REC          260299          X          932453851          24          sysprogres
_Syscolstat         -104          REC          260889          X          932453851          24          sysprogres
_Sysnumstat         -108          REC          259008          X          932453851          24          sysprogres

Most of this looks as I expect, but note that there are several records where "find _file" failed and the file name is blank and the file number > 1000. Those table numbers (_lock-table) don't exist, but there are _index records whose index numbers match, and they are indexes on the tables being updated:

_idx-num _index-name
1044     _Idxsysidxstat
1047     _Idxsysdatestat
1048     _Idxsysintstat
1050     _Idxsysnumstat
1057     _Idxsysnvarcharstat

What's more, for the "index" line items, the values in the RECID column are valid recids for the associated tables.

Can anyone explain these records that have what appear to be index numbers in the _lock-table column?  OE bug? Phantom promon data? A side-effect of _lock records changing so fast that the AVM can't do consistent reads?

All Replies
  • But recids of those locks are NOT dbkey of index blocks (they are not devided by 32 or 64).

    Use dbrpr to dump the blocks. You will see their type. If they are data blocks then you will see the tables the recids belong to.

  • > But recids of those locks are NOT dbkey of index blocks (they are not devided by 32 or 64).

    That's right, they are RM blocks.  My point was that the recid is valid for the table that has that index; there is a correlation between the two values.  They aren't just random garbage values.

    The confusion is about index numbers appearing in the _lock-table column.  What are these lock table entries?  Are they valid records?  Are they artifacts of dirty reads?

  • > there is a correlation between the two values.

    You can also display _Lock-Chain and check a correlation with this third value:

    REC/RGET: Hash# = (Table# + Recid) modulo (-lkhash)

    TAB: Hash# = Table# modulo (-lkhash)

    PURG: Hash# = User# modulo (-lkhash)

    > They aren't just random garbage values.

    Indeed!

  • rob,

    there are tables such that _file-number matches some index numbers and also _file-numbers that match index numbers for the same table. easy to get them mixed up.

    there should not be any lock table entries for indexes.

    anyhow, the update statistics function will update index statistics as well as table statistics, depending on what options you used.

  • Thanks George; interesting info.

    But this doesn't answer my underlying question. ;)  If _lock contains record locks, table locks, schema locks; what are these additional records?

  • Thanks Gus.  My confusion is that, in this database, there are no _file-numbers > 1000.  I don't /think/ I'm mixed up.

    In this case the SQL statements were generated from code like this:

    put unformatted "update table statistics and index statistics and all column statistics for PUB." quoter( _file._file-name )";" skip.

    put unformatted "commit work;" skip.

  • possibly there is a bug such that sql is erroneosly taking out locks such that the lock id is the same as an index number. or not, i can’t tell from this distance.

  • I can't be sure at this time but I suspect this is a bug that has been fixed.  Running this SQL script against sports in 11.6.0, I get a lock table HWM of 2034.  If I break it into separate statements/transactions for TABLE, INDEX, and ALL COLUMN, it goes down to 1944; still very high.  

    In 11.6.3, the same tests give HWMs of 90 and 86; a significant difference.

  • Sorry, my numbers were a little off.  90 and 86 was for an empty sports DB in 11.6.0.  The numbers for sports on 11.6.3 were 77 and 58.