Re: Question on File locking

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.