Re: Question on File locking

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.