Re: SQL0901: "SQL system error" and MCH2601: "Lock enforcement rules broken when trying to access object &1"

Dan wrote:

(Cross-posted to Midrange and RPG400 lists.)

We have a real mind-blower on our hands. We have an application which submits several jobs to the job queue, which allows up to three jobs to run simultaneously. The jobs run a program (the
same program using different data) which uses SQL *extensively*.
SQL INSERTS are performed on tables that exist in libraries that
are unique to each job. DECLARE CURSORs are defined with "FOR
READ ONLY". No explicit locking (i.e., ALCOBJ) is used.

If the stars align correctly, we occasionally get an SQL0901,
which is a "SQL system error". The second-level text isn't much
help except to suggest that the job log should be looked at. (At
the end of this post, I've pasted the relevant part of the job
log with "white space" removed to reduce line wrapping.) I am
usually able to replicate the error, and it seems to occur when a
particular segment of the program is being executed
simultaneously by two jobs. But nailing it down seems to be near
impossible, given the lack of information in the error and the
job log. At this point, I am about to add code to test for
SQL0901 after every executable SQL statement, and perform a dump
with an identifying label. The MCH2601 message appears in the job
log just prior to the SQL0901, but I've not been able to find any
information on this message relevant to it being issued as a
result of an SQL error.

I searched the IBM Series i Support site for "MCH2601 SQL0901"
(without the double quotes) and got one hit. Unfortunately, it
requires PartnerWorld access, and it may be a few days before I
can get the required company token to be able to add myself to
the company profile. (If anyone reading this has PartnerWorld
access and can send me the content from the result link, I would
be greatly appreciative.)


MSGID TYPE SEV DATE TIME FROM PGM
LIBRARY INST TO PGM LIBRARY
MCH2601 Escape 40 03/23/10 11:40:11.412336 #cfochkr
000B10 QQQVAP QSYS
To module . . . . . . . . . : QQQVAP
To procedure . . . . . . . :
RESOLVE_SYSTEM_POINTERS
Statement . . . . . . . . . : 4204
Message . . . . : Lock enforcement rules broken when trying to
access object &1.
<<SNIP>>
SQL0901 Diagnostic 50 03/23/10 11:40:11.414624 QSQRUN3
QSYS *STMT QSQRUN3 QSYS
From module . . . . . . . . : QSQINS
From procedure . . . . . . : CLEANUP
Statement . . . . . . . . . : 24225
To module . . . . . . . . . : QSQINS
To procedure . . . . . . . : CLEANUP
Statement . . . . . . . . . : 24225
Message . . . . : SQL system error.
Cause . . . . . : An SQL system error has occurred. The
current SQL statement cannot be completed successfully. The
error will not prevent other SQL statements from being processed.
Previous messages may indicate that there is a problem with the
SQL statement and SQL did not correctly diagnose the error. The
previous message identifier was CPF4204. Internal error type 3107
has occurred. <<SNIP>>

msgMCH2601 F/#cfochkr x/0B10 T/QQQVAP TM/QQQVAP TP/RESOLVE_SYSTEM_POINTERS stmt/4204
The error for QQQVALID is merely the effect of a "resignal exception"; i.e. not much interest.
The msgCPF4204 is as worthless as *FC [aka CPF9999] because that is the generic "query processing failed" for which the prior messages, usually the first escape in a series, is the actual issue; in this case, the first incident of MCH2601 recorded.

The failure is in the general "validate access plan" processing, apparently in the more specific [but unfortunately still too generally, in some] procedure which "resolves system pointers" to some objects. Presumably "resolve system pointer" activity is being performed for various objects named in the query, to which the plan must associate.

The error is bubbled up from there to the SQL processor for an INSERT statement, presented as an abnormal error condition to the requester of the SQL, manifest as the generic failure condition -901 [SQL0901] with return code 3107:
msgSQL0901 rc3107 F/QSQRUN3 f/qsqins FM/QSQINS FP/CLEANUP stmt/24225

When an attempt is made to refer to an object using a method for which the object must [per locking protocol] be allocated to the current or just to no other thread, while that object is already allocated to another thread or process, that is considered to have broken the "Lock enforcement rules" which are applicable "when trying to access" the object. It is the "common function object checker" which signals the LIC exception 1A01 [0x1A=0d26 & 0x01=0d01 combined to make 2601]. That also is not too helpful to determine the specific processing, since as a "common function" its processing is also very general; we just know that another thread holds a lock which conflicts with the activity in the current & failing thread. With an OPM program we would more likely better, determine by inference, the MI instruction name; here we can merely guess that it is the RSLVSP.

By use of debug and\or possibly only by implementing a variation on the message variable definition to represent the &1 data in hex, the name or address of the object can be determined and more inferred about what the object type and possible purpose might be. Since a temporary object would likely be destroyed as part of the cleanup for the first exception, a substitution\replacement variable is often all that is seen; of course if the failing thread did not have the ability to destroy the object due to not holding a lock, and that another thread held the lock, then obviously if the object had been destroyed, it was not by the failing thread. The following can be done to enable seeing the address which includes the object type:
chgmsgd mch2601 msgf(qcpfmsg) FMT((*HEX 16))

Regards, Chuck