User sessions working on Updateable Secondary getting errors -211 / -107 due to locks on systables

Technote (troubleshooting)


Problem(Abstract)

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.


Rate this page:

(0 users)Average rating

Document information


More support for:

Informix Servers

Software version:

11.5, 11.7, 11.70, 12.1

Operating system(s):

AIX, HP-UX, Linux, Mac OS X, Solaris, Windows

Software edition:

Enterprise, Growth, Ultimate, Workgroup

Reference #:

1644785

Modified date:

2013-08-08

Translate my page

Machine Translation

Content navigation