Strange problem with locking of records
Hi All,
My program reads many records one by one, and will update the OLD KEY with NEW KEY in more than 100 files. If any run-time error/record
locking the pgm will roll back the partial updates and continue with the
next record.
So, example take 5 records... in which I locked the 3rd record in
another session. So, I'm expecting the rest 4 should go through and the
3rd will be rolledback.
Main Batch File
===========
Acc1 AAA . . ..
Acc2 BBB ... ..
Acc3 CCC .. .. .
Acc4 DDD .. .
Acc5 EEE ....
#File1 for update
===========
Acc3 A1 B1...
Acc3 A2 B2....
Acc3 A3 B3....
This file is not having any records belong to Acc1, Acc2, Acc4 & Acc5.
My Program
OLD_KEY SETLL #File1
OLD_KEY READE #File1
LOOP
UPDATE NEW_KEY in #File1
OLD_KEY READE #File1
ENDLOOP
When the program is executing first record Acc1 (and there is no such related record in #File1)
When it encounters the READ/READE of #File1 >> *PSSR will catch it and error will be "RECORD 77888 IN USE BY SESSION"
I wonder because, RRN 77888 record belongs to Acc3 but not Acc1 .....
Summary:
I really locked the RRN 12345, and my program is executing some other record... and the *PSSR catches the error as "RECORD 12345 IS IN USE BY XXXXXX".... this is happening when I use SETLL + READ/READE... Instead if I used CHAIN as a work around.. and its working fine" but for me I want to know why READ/READE is not supporting, as I cannot modify the code for many programs....!
Work around Code is:
OLD_KEY CHAIN #File1
LOOP
UPDATE NEW_KEY in #File1
OLD_KEY CHAIN #File1
ENDLOOP
(CHAIN LOOP will not go to infinite loop, as the OLD_KEY will be updated to NEW_KEY for every read)
For record locking, I created a dummy pgm to lock it, and also using SQL locking some times.... This problem I have seen in both types of test locking....
I need the reason of this strange behaviour, and also better solution rather than modifying many lines of code.
Software/Hardware used:
Iseries
Software/Hardware used:
Iseries
ASKED:
August 29, 2011 2:45 PM
UPDATED:
March 31, 2012 5:27 PM
Answer Wiki:
Here you can suggest me, to check the record existence using SETLL, if record exists then only perform READ/READE... however this case will also not work.. if the record really exists and when the pgm tries to execute READ/READE is returns with error "RECORD IN USE" even though that's not the actual record being locked.
++++
It looks like you have no conditional checking once you get in the loop.
Try this:
My Program
OLD_KEY SETLL #File1
OLD_KEY READE #File1
LOOP
DoW NOT %EOF;
UPDATE NEW_KEY in #File1
OLD_KEY READE #File1
EndDo;
ENDLOOP
To see all answers submitted to the Answer Wiki: View Answer History.
No, but to do the UPDATE, I need to READ… and I’m encountering the record lock problem at READ statement itself.. I even cannot go further. as the *PSSR already handled by that time..
my program is simple, just UserOpen Files…
STRCMTCTL
OPEN FILES
BEGIN PROCESS
CLOSE FILES
ENDCMTCTL
and my loop is
SETLL OLD_KEY
READE OLD_KEY
DO FOR ALL
UPDATE NEW_KEY
READE OLD_KEY
ENDDO
error occurs whenever READE encountered…!
Is it the 1st READE or the 2nd READE?
What exacttly is your “DO FOR ALL” statement?
Setll moves you to the desired record
but it it doesn’t exit it moves you to the next record in the index!
Ater a setll you need to check the %EQUAL condition to determine if an exact match occurred.
Phil
Hi All!
No, still you didn’t get what I mean…..! Forget about SETLL, READ every thing.. just see the below points.
I locked Acc3 record belongs to #File1 and I called my program.
- Case 1:
The Acc1 is the KEY under process. After SETLL, when READ encounters, the program says “Acc3 RECORD LOCKED BY XXXX”
#File1 for update
===========
Acc1 X2 Y2
Acc2 X1 Y1…
Acc3 A1 B1…
Acc3 A2 B2….
Acc3 A3 B3….
- Case 2:
Take Acc2 as the key field, and also the same problem.
- Case 3:
Assume Acc3 as the key field, here *PSSR will handle anyway, as the Acc3 record is really locked. So Case 1 and Case 2 were affected?
1) As per my example stated before,
how come the READE has been failed though the key passed is irrelevant to the record that was locked?
2) Why CHAIN is working, Why not READ/READE… any problem with sequential access in case of record locks?
Hi CharlieBrown
As I specified before, problem with the first and as well as second READ
Hi CharlieBrown,
My program has more than 100 loops, and the purpose is to update the OLD_NUMBER with NEW_NUMBER…
So, all LOOP’s just READE with OLD_NUMBER, and UPDATE to NEW_NUMBER.
When READE or READ encounters, my program is facing the RECORD LOCKING problem…. irrespective of the ACCOUNT NUMBER that I have locked.
However instead of READ, when I use CHAIN my code is working fine as expected. But I cannot change all the LOOPS, and all the PROGRAMS now…
1) So, please advise me, if there is any wise way to handle this issue?
2) Why only CHAIN working, Why not READ?
Hope you understood this time.
Two things.
1. We need to see the code or the compile listing. Can you either post it here or email it to me at charlieb@themembersgroup.com
2. Have you ever considered doing this with SQL statements?
I appears that the reade works like this
- Can I get a lock on the record
– no, error
- Yes, does the key match the key
So your critical READE’s need to be preceeded with
- SETLL
- IF %equals
- READE
or replace them with a CHAIN
Phil
Are these actual “keys” associated with indexes?
How are the files defined — MAINT(*IMMED)? Where is the COMMIT in your code? I don’t see any referenced, only rollback.
This would be far better done with SQL than trying to issue an I/O statement for every individual row.
Tom
So
You have a database with 100 files referentially linked by key (account number) and the account number is being altered ?
Then for the duration of the process which alters the account nukmber, all records in the 100 files should be locked by the change process to avoid alteration by any other processes. ?
a sequential locking each record is not the same thing. Or does you commit boundary take this into account?
Did you read the manuals regarding record locks?
a cursory glance into the UDB Programming manual says
Updating database records
The update operation allows you to change an existing database record in a logical or physical file. (The UPDAT statement in the RPG/400 language and the REWRITE statement in the COBOL/400 language are examples of this type operation.) Before you update a database record, the record must first be read and locked. The lock is obtained by specifying the update option on any of the read operations listed under the
“Reading database records using an arrival sequence access path” on page 168 or “Reading database records using a keyed sequence access path” on page 169.
If you issue several read operations with the update option specified, each read operation releases the lock on the previous record before attempting to locate and lock the new record. When you do the update operation, the system assumes that you are updating the currently locked record. Therefore, you do not have to identify the record to be updated on the update operation. After the update operation is done, the
system releases the lock.
Note: The rules for locking are different if your job is running under commitment control. See the Backup and Recovery book for more details.
If the update operation changes a key field in an access path for which immediate maintenance is specified, the access path is updated if the high-level language allows it. (Some high-level languages do not allow changes to the key field in an update operation.)
So the next place to look is the Backup and recovery book.
But the real answer, as stated, is probably to use SQL for such an update.