Re: Question on File locking

Joep,

This is very much a real life problem. Maybe I started with providing too
little information.

The idea of data queue is not only to serialize things, but it can also be
used to multi-thread processing.

Current Scenario:

There is a Member File, with Member Id as Unique key. The key is not being
changed or records are not being deleted from this file.

This file has a Trigger defined for *INSERT & *UPDATE.

The Trigger program writes a record to a Data Queue. This is optimum as we
don't want to hold up any program making an update to the Member file any
longer than necessary, while the Trigger program does whatever processing
it needs to do. So any Trigger program should do minimum processing.

The program that reads the data queue, does a lot of processing creating
multiple other files. So we need to run multiple instances of this program
to keep up with what is being written to the data queue. Currently, the
files being written are simple "flat" files so running multiple instances
does not pose any issues.

Currently there is no "audit" information on the Member file about which
fields changed and when they changed etc.

Problem:

A new Client (US Federal Government) requires detailed audit information on
the Member file. They assess a penalty of $1 Million for every missed or
incorrect audit information (I don't know if and how they enforce it, but
we are told that is what the contract says).

To be accurate, the audit records need to be in exact sequence of how the
updates to the Member file occurred. (@Buck: If they are not in the update
sequence, then they would be considered out of sequence).

Approach to solving the problem:

The simplest way of capturing Audit information would be to capture Before
and/or After information on every update to the file. As the Member File is
updated from lots of programs, the most logical place would be to use a
Trigger on the file.

Once it was decided to use the trigger, you would always consider using an
existing trigger program (or set of programs), if possible.

That is why I was trying to see if there is a fool-proof way of writing
these audit records in sequence in already existing multi-threaded programs.

I did not "reject" any suggestions. I had asked for a couple of
clarifications in one of my replies:

About Chuck's suggestion for locking record with '99999':

If a record is locked, and 2 other requests are made for the same record,
which one will get the record once it is released? The one which made the
request first or is it "unpredictable"? If it is unpredictable, then this
is not fool-proof to get records in correct sequence. If it is FIFO, then
this would work perfectly.

About Scott's suggestion to use SQL Identity:

/Quote/ 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? /Unquote/

Not knowing the answers to these questions, or if the answers turn out to
be unfavorable, I will probably go with Booth's suggestion:

Modify the trigger program to write to another data queue, and create a
brand new program to process that data queue, running only one instance of
it.

Vinay

On Wed, Aug 13, 2014 at 8:31 AM, Joep Beckeringh <joep.beckeringh@xxxxxxxxxx

wrote:

Vinay,

Are you presenting a logic puzzle just for fun, or is this a real life
situation?

Your problem was that different instances of a program writing to the same
file might try to write the same sequence number to that file.

You were offered three valid solutions:
- Scott suggested to use SQL IDENTITY
- Booth suggested serializing the writes by using a data queue
- Chuck suggested to lock the initial row in a set instead of the last row

For reasons unknown to us you decided to ignore Scott’s and Chuck’s
suggestions and pursue the data queue idea. But now you complicate things
further by having multiple instances of the program reading the data queue.
The whole idea of a queue is to serialize things. You can have multiple
jobs writing to the same queue; by having one job reading and processing
the queue there is always just one job writing to the file.

Joep Beckeringh



Op 13 aug. 2014, om 13:00 heeft Vinay Gavankar <vinaygav@xxxxxxxxx> het
volgende geschreven:

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.

--
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.