Kbase P109301: Lock table is full error generated by SQL-92 queries
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
08/10/2009 |
|
Status: Verified
SYMPTOM(s):
Lock table is full. (7870)
Executing an SQL-92 Query involving input parameters.
Lock table is full error generated by SQL-92 queries
Using transaction isolation level REPEATABLE READ
Using ODBC with SQL-92 engine
Simple SELECT statement
select count(*) from pub.<table>
FACT(s) (Environment):
Progress 9.1E
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
The Repeatable Read transaction isolation level will hold all locks satisfied by the selection criteria until the end of the transaction. Progress locks all the table's records when parameters are passed as opposed to constant values.
FIX:
If transaction isolation level REPEATABLE READ must be used, then increase the Lock table database startup parameter (-L) accordingly.
Alternatively, consider revising the statement with hard coded constant values in the WHERE clause of the SELECT statement.
Running UPDATE Statistics may improve the query in some cases.
For example:
UPDATE TABLE STATISTICS FOR pub.<table>;
COMMIT WORK;
UPDATE INDEX STATISTICS FOR pub.<table>;
COMMIT WORK;
UPDATE ALL COLUMN STATISTICS FOR pub.<table>;
COMMIT WORK;