Kbase P111964: More locks on UPDATE STATISTICS with OpenEdge 10 than with Progress 9
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Verified
FACT(s) (Environment):
OpenEdge 10.x
SYMPTOM(s):
More locks on UPDATE STATISTICS with OpenEdge 10 than with Progress 9
Getting error 7870 when performing an UPDATE STATISTICS
Lock table is full. (7870)
CAUSE:
In OE10, the definition of the schema table which contains SQL statistics was changed. The new definitions are extensible and can contain more detailed and powerful statistics.
The Progress 9 tables contain 10 statistics "numbers" per row. The OE10 tables contain 1 statistics "number" per row.
You can validly see up to 10 times the number of locks in OE10, where the locks are for rows being written to the statistics tables.
FIX:
One workaround can be to first lock the statistics table as per the following statements (make sure auto-commit is turned off):
LOCK TABLE pub."_Systblstat",pub."_Sysidxstat",pub."_Syscolstat",pub."_Sysvarcharstat",pub."_Syssmintstat",pub."_Sysintstat",pub."_Sysnumstat",
pub."_Sysdatestat",pub."_Sysfloatstat",pub."_Systimestat",pub."_Syscharstat",pub."_Sysrealstat",pub."_Systsstat",pub."_Systinyintstat",
pub."_Sysbigintstat",pub."_Sysncharstat",pub."_Sysnvarcharstat",pub."_Syststzstat"
IN EXCLUSIVE MODE;
UPDATE STATISTICS;
COMMIT;
Putting a lock on the statistics table is not causing any problem to the other users.
Getting the locks might be prevented by any SQL92 user being within a transaction in which the statistics tables were read.