Re: Question on File locking

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.