Rob Fitzpatrick
ProgressTalk.com 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:
PUB._Syscolstat
PUB._Sysdatestat
PUB._Sysidxstat
PUB._Sysintstat
PUB._Sysnumstat
PUB._Sysnvarcharstat
PUB._Systblstat
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:
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:
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:
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?
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:
PUB._Syscolstat
PUB._Sysdatestat
PUB._Sysidxstat
PUB._Sysintstat
PUB._Sysnumstat
PUB._Sysnvarcharstat
PUB._Systblstat
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:
Code:
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.
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:
Code:
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:
Code:
_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?