Re: Question on File locking

On 11-Aug-2014 15:58 -0500, Vinay Gavankar wrote:

I have a logical file with an Id and Sequence number as a Unique key.
The first record for an Id is written with Seq No. as 99999 and
subsequent records are to be written with (current lowest sequence -
1).

Although an LF may be used to define the Access Path and UNIQUE, the data is in the physical. The database can write the sequence for you. Look into Identity columns. The database file must be an SQL TABLE to use that feature.

There is only one program that is writing to this file, but is
running concurrently.

What would be a fool-proof way of writing to the file without having
to trap for error on write and looping.

<<SNIP /that/ record etc. I could not comprehend>

TIA for any suggestions.

Assumed the concurrency either does not exist for the initial row for a particular Id, or any issues with that specific concurrency are already resolved\dealt-with.

Use the initial row as a means to establish a semaphore for adding any new row with a newly calculated sequence. Make the writer of each additional row that has a new sequence number for a particular ID, obtain a lock on the /initial row/ of that ID [i.e. the row with the ID being processed and SeqNbr=999999], then while that row lock is still held determine the lowest existing sequence for that ID, calculate the new sequence number and set the buffer for the row to be written, then write that row with the new sequence for that ID, then finally release the lock on the initial row. Of course, any updater outside of that application['s algorithm to ensure proper sequencing] could break that process.