Salesforce

IDXCHECK online terminates with Lock wait timeout error 8812

« Go Back

Information

 
TitleIDXCHECK online terminates with Lock wait timeout error 8812
URL Name000035193
Article Number000158674
EnvironmentProduct: OpenEdge
Version: 10.1C, 10.2x, 11.x
OS: All supported platforms
Question/Problem Description
IDXCHECK online terminates with "Index check completed successfully" and Lock wait timeout error 8812.
Online IDXCHECK run with the default locking options does not complete.
Steps to Reproduce
Clarifying Information
IDXCHECK is not being run with the Option: L - Lock tables during the check

IDXCHECK is run with the default online Options :

* 1 - Validate physical consistency of index blocks
* 2 - Validate keys for each record
* 4 - Validate key orderr
Error MessageIndex check completed successfully.
1090 indexes, 820256 blocks, 134768942 keys checked.
Lock wait timeout of 1800 seconds expired (8812)
Index check aborted. (2808)

Lock tables during the validation (14464)
Validate physical consistency of index blocks (14460)
Validate keys for each record (14461)
Validate key order (14463)
Defect Number
Enhancement Number
Cause
IDXCHECK didn't complete running against all indexes.

The last index referenced prior to the Lock wait timeout 8812 message was the last index checked:
Index <index-num> (PUB.<table-name>, <index-name): <num> keys.

Where IDXCHECK stopped after reaching the lkwtimeout on a record that was locked against this table, since the default online IDXCHECK locking is to run SHARE-LOCK:
Lock wait timeout of 1800 seconds expired (8812)
Resolution
Since all indexes with idxnum > the 'index-num' referenced didn't get checked, the operation will need to be re-run. This is the 'downside' of online utilities, balancing accurate information against time, options are:

1. Add -NL to IDXCHECK, if any errors are reported, then re-run an offline IDXCHECK against a copy of the online database or more specifically, a limited IDXCHECK (iow: for that specific table.index where the error is raised).

2. Use the "L (lock tables during check)" IDXCHECK Option, which will cause the check to wait on any other process locking a record or the schema on a given table and take longer to run online as a consequence.

3. Add the -lkwtmo parameter to IDXCHECK to specify how long you're prepared to wait for a lock, the default is 30 minutes (1800 seconds). While the default of 30 minutes should be more than long enough if not too long, setting it any longer could retard the whole operation. This could highlight an underlying application locking problem, where the question "why was some process locking a record for so long in the first place anyway?" needs to be answered.

For further details of the online IDXCHECK locking protocols, refer to Article: What are the implications of the online IDXCHECK locking options?   
Workaround
Notes
Keyword Phrase
Last Modified Date11/20/2020 7:36 AM

Powered by