Troubleshooting
Problem
You have client applications working on Updateable Secondary. From time to time the corresponding sessions are getting errors -211 / -107 (Cannot read system catalog systables / Record is locked).
Symptom
The output of 'onstat -k' implies that locks on systables were placed by UPDATE STATISTICS process:
Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
11ec889c0 0 7b76e1738 12a677040 HDR+X 4005ed c05 0 U
11ec999c0 0 7b76e1738 11fb89140 HDR+X 4005ed 30b 0 U
11ed43b40 0 7b76e1738 134787240 HDR+X 4005ed 1d06 0 U
11f039b40 0 7b76e1738 133e1f840 HDR+X 4005ed d307 0 U
11f304e40 0 7b76e1738 134a474c0 HDR+X 4005ed 2504 0 U
11f4340c0 0 7b76e1738 1239cf540 HDR+X 4005ed 1209 0 U
<...>
Cause
During execution of UPDATE STATISTICS on the Primary systables is locked exclusively. These locks are then propagated to the Updateable Secondary which interferes with the applications processing.
Diagnosing The Problem
1) Identify partnum of systables table
2) On the secondary use 'onmode -I 211' to capture the assert failure file for error -211: Cannot read system catalog.
3) Open the assert failure file and search for systables partnum in the 'onstat -k' output saved in the file.
4) For identified locks check if U flag is set.
Resolving The Problem
- Use LOCK MODE WAIT for the client applications.
- Enable SQL_FEAT_CTRL 0x00000020 in the $ONCONFIG file of the Primary. Having it set should reduce the number of locking issue during processing of UPDATE STATISTICS LOW. Note that this feature was introduced in Informix v11.50.FC9W1 & in v11.70.FC4.
- Reschedule UPDATE STATISTICS operations so they would not interfere with the running applications.
Was this topic helpful?
Document Information
Modified date:
03 June 2021
UID
swg21644785