Re: SQL updating qtemp table record lock trouble . -- MIDRANGE-L

On 2/18/11 7:54 AM, Schutte, Michael D wrote:

I'm stumped...

I have an SQLRPGLE program that is trying to update a QTEMP table
but it's producing an error saying that a record in QTEMP/RCTDTL is
in use. The problem is that it says that it's coming from an
interactive job from another user. I know that QTEMP is unique to
each user, so I am certain that the user doesn't have the record
locked in QTEMP/RCTDTL. This user is responsible for maintaining
IMSPITM so it's likely that they have a record locked in this file
because they have it up in the maintenance program. I have verified
that they are in the maintenance program.

UPDATE QTEMP/RCTDTL
SET (ITMTYP, ITMMFR, ITMITG, ITMSFX,
ITMPUM, ITMIUM, ITMRUM, ITMPCF, ITMICF) =
(SELECT ITMTYP, ITMMFR, ITMITG, ITMSFX,
ITMPUM, ITMIUM, ITMRUM, ITMPCF, ITMICF
FROM IMSPITM
WHERE ECDCONO = ITMCONO
AND ECDCPY = ITMCPY
AND ECDITM = ITMITM)

How can I tell SQL not to gain update access to IMSPITM?

In the job log I do have a message saying "Subselects processed as
join query". I'd imagine that this is what is causing the update
access to IMSPITM.

Here's the actual error message in the job log.
Record 46927 in use by job 251535/USER/JOB
Cause . . . . . : You cannot get record number 46927 record format
FORMAT0001 member number 2 in member RCTDTL file RCTDTL in
library QTEMP because record number 46927 is being used by job
251535/USER/JOB. The error code is 2. If the error code is 1,
then this error occurred when record number 46927 was locked by
job 251535/USER/JOB which was waiting on a lock held by this
job. If the error code is 2, then job 251535/USER/JOB was not
waiting on a lock held by this job.

I also verify that the record number in the error message happens to
be the record that the user has up in the maintenance program.

Any ideas?

An UPDATE-lock held by A job will prevent a READ-lock by another job, but will not prevent a "dirty read" which is effectively a request to read with no-lock requested. I believe an SQL UPDATE request running WITH NC [commit level *NONE] isolation requires the READ-lock on row data [from a subselect] being used for updating a row against which an UPDATE-lock would I expect would already be held. That is to say, I believe the subselect query is implicitly [already] for-read-only rather than for-update, as desired. And that the locking error arises from the requirement for the subselect query to get a READ-lock, not due to the subselect query asking to obtain an UPDATE-lock. That effect should be visible in a DSPRCDLCK of the table while the WAITRCD time is in effect; i.e. while the SQL UPDATE job is in LCKW for the RcdLck.

If the lock in the other job is under isolation, then request the SQL UPDATE to have WITH UR [or WITH CHG] to run under isolation versus what was presumably done WITH NC [as typical for files in QTEMP to avoid journaling]. When running with isolation level *CHG, the "uncommitted changes in other jobs can be seen" so that should allow the subquery of the SQL UPDATE to access the data from the other TABLE without obtaining a READ-lock; i.e. enable the dirty read, thus no lock conflict.

Having suggested that, I took a quick peek at some documentation for row locks for commitment control. That confirms the requirement for the READ-lock for "Subqueries (update or delete capable cursor or UPDATE or DELETE non-cursor)" requests, but seems not to allude to the ability of two *CHG jobs not conflicting for their locks; UPDATE-lock held by one, and a READ-lock requested by another for its subquery. I tested on v5r3 both the UPDATE ... WITH CHG and the UPDATE ... WITH NC, the former UPDATE completed with dirty data [the effect of a prior UPDATE in another job having been performed but not committed being visible to the subselect query], and the latter UPDATE terminated with the lock wait timeout CPF5027 and SQL0913 [or -913].

http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafydicomm.htm

Regards, Chuck