Re: Question on File locking

I also need the records to be written in the sequence they came in (thru
the data queue).

Here is a scenario where the records may not be in sequence:

Let us say the Audit file already has seq 99998 for ID ABC.

Then there are 3 instances of the main file update for ID ABC one after the
other in the data queue, which get picked up by three different instances
of the program in question.

The first instance of the program gets the first record, and goes into the
loop to determine the lowest sequence (which is 99998 as of now) and write
a new record (which would be 99997).

BEFORE the 99997 record is written by the first instance of the program,
the second instance of the program gets the second record, and goes into
the loop to determine the lowest sequence (which is still 99998) and write
a new record (which would be 99997).

The first instance of the program now completes it loop and writes the
99997 record successfully.

The second instance is still in it's loop.

Now, the third instance of the program gets the third record, and goes into
the loop to determine the lowest sequence (which is 99997 now) and write a
new record (which would be 99996).

The second instance now completes it write of 99997, finds an error, and
goes back in the loop. Now, it finds 99997 as the last record and intends
to write 99996.

The third instance completes its loop and writes 99996 successfully.

The second instance encounters one more error on write, and in the next
loop writes 99995.

Here, I have all 3 records written successfully, but are out of sequence.




On Tue, Aug 12, 2014 at 10:12 PM, Booth Martin <booth@xxxxxxxxxxxx> wrote:

There is no reason I can imagine why a loop that decrements the counter
and writes the record until the write is successful wouldn't answer the
problem.




On 8/12/2014 5:45 PM, Vinay Gavankar wrote:

Thanks everyone.

To use Buck's approach of writing to a data queue and having another
program process from the data queue, would mean that only one instance of
the data queue processing program can be running otherwise we could have
the same situation.

This is the full scenario:

File A is unique by Id, and has a trigger program for *INSERT & UPDATE
(records are not being deleted from the file). The trigger program writes
the ID and Before & After Images to a data queue.

File A is being updated by multiple batch as well as interactive programs,
and it is "possible" that same Id gets updated in rapid succession (batch
programs process hundreds of records per second).

The program I am working on reads the data queue and I want to capture an
audit trail by writing the Id, Seq# and the Before Image of the record.
There are multiple instances of this program running, reading from the
same
data queue.

I think Ken's approach of locking a "different" file (with only Id as the
key) would work, if I make another control file with only Id as the field
and key, as only one instance of the program will then be in the "get
lowest sequence - subtract 1 - write new record" loop for a given Id. The
question then would be, while one record is being written, say 99997, if 2
other instances of the program try to lock the control file for the same
Id, then which one will get it first? The one which requested it first or
it could be either one (unpredictable).

It is also important that the records are written in the sequence they
were
updated, that is why I wanted to avoid the trapping for error on Write and
looping, in the first place.

Adding this logic in the trigger program will of course solve the problem,
but that is out of question, as it will slow down all of the batch
programs.

I am not familiar with using SQL, but are you all saying that if this file
were to be defined in DDL, then system will take care of inserting the
records in correct sequence, even if more than one additional request was
made while the first one was still being completed? If so, will that
command also return the Sequence number just inserted, so that I can use
it
for some other purpose?



On Tue, Aug 12, 2014 at 3:33 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 11-Aug-2014 16:56 -0500, Vinay Gavankar wrote:

It is an existing file defined with a DDS and not an SQL TABLE.

I generally try to dissuade anyone from changing from DDS to DDL

for no
reason [or per some supposed but unsubstantiated perception of some
benefit
to making the change], but this scenario certainly qualifies for having a
good reason to make the switch.


Let me explain what my question was.

Let us say the lowest sequence in the file for Id ABC is 99998.

Now the first instance of the program, wants to write another record
for Id ABC, so locks this record, and goes on to write a record with
seq 99997.

Before the record with 99997 is actually written, another instance
of the program tries to find the lowest sequence for Id ABC. At this
point, it is still 99998, so it goes in LCKW state.

Now the first instance completes the write of 99997 and releases the
lock on 99998.

Wouldn't the second instance get the Seq 99998 record, on which it
was waiting for the lock to be released and try to write 99997 seq#?


I think I did understood that to be the scenario originally. But the

specific example given above, by contrast to mentions of /that record/ in
the OP, clarifies greatly.

A problem with the proposed scenario is that the signal established
that
informs the other updater(s) about their ability to proceed, is somewhat
flawed for the chosen mutex. While any row suffices as a resource for a
mutex, the use of a variable record rather than a static record has a
potential issue; a traffic signal-light is a good signal, but one must be
referencing the signal for their own lane, not the signal over another
lane
of traffic. Basically the currently-is-last row is a variable resource
rather than a static resource, and thus can be confusing, much like a
signal-light over the next lane of traffic can accidentally be seen as a
signal for the current lane. The initial row with the specific Id, or
that
specific Id from the parent file, can be a fixed\static resource with
regard to the new child row(s).

With the variable resource of currently-last-row, after the lock is
obtained on the last-row, the next step in the process [missing from the
described scenario] would require determining if there is a new last-row
that was since-added, added while the newly obtained last-row was locked
by
a concurrent request to insert. Because no requester of the last-row
knows
whether they were waiting, every time a last-row is found, that newly
obtained\located last-row must be allocated as a mutex to the work of
_both_ looking for a new last-row and to add a new row if no new last-row
existed; but if a new\different last-row existed when checked, then the
process must be repeated [loop]. The /repeat/ is in effect a
contradiction
with the OP asking that the code should not effect /looping/ to get the
new
row inserted; albeit there would be no "trap for error on write" for
which
that looping would be required. That is why I proposed that, if
possible,
a static resource [if the initial row could be established as such] might
be used to resolve the presumed issue. The looping instead on a
duplicate
key error, would probably be better than looping on a
different-new-last-row-found condition, because the work to identify the
condition is deferred to the database.


--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.