Question Index entries in the lock table?

Rob Fitzpatrick Sponsor
I'm investigating an issue with excessive locks while running Update Statistics, which I posted about here. (BTW it looks like an issue in 11.6.0 that was fixed in or before 11.6.3; a case is open with TS.) This thread isn't about Update Statistics specifically; bear with me. :)

I was able to reproduce the client's issue in-house in a sports DB and in an application DB. One of my questions for TS was: what exactly is being locked when I run Update Statistics? Based on my testing (at least with sports), Update Statistics updates the following tables:

While reproducing the issue on a larger DB, which took a while, I saw a chance to try to answer my own question: what is being locked? So I ran this:

for each _lock no-lock:
  find _file no-lock where _file-number = _lock-table no-error.
    _file-name when available( _file )
    _lock-table format "->>>>9"

Disclaimer: this was on a test box; please don't run this code on prod! ;)

In what looks like a timing issue, I ran it repeatedly and got nothing, despite seeing thousands of locks per second in promon. Then I got lucky and saw this data:

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 fine, 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. So these look like "index locks" (if there was such a thing).

I thought, "that's wacky, there are no index locks in the lock table (right?)". I've always understood that the lock table was for three classes of locks: record locks, table locks, and schema locks. No mention of "index locks". I then realized this is an area of DB internals I've never thought about in depth.

An index "update" (delete/create) or an index delete is a side-effect of a record update (on one or more fields that are index components) or a record delete. Since an exclusive record lock for user A prevents user B from updating or deleting that record, they are also inherently prevented from needing to delete the corresponding index key(s). So there is no need for separate lock table entries for index keys, so there is no such thing as an "index lock". Correct?

Then what is this odd data I am seeing in _lock? OE bug? Phantom promon data? A side-effect of _lock records changing so fast that the AVM can't do consistent reads? :confused: