About Share lock.

A lot of it is probably for backwards compability... (like the requirement for NO-UNDO during variable definition). I guess that's also why it's still the default. Good thing is that since a while you can modify that default using the -NL startup parameter (during compile time).

I used SHARE-LOCKS a couple of times (in 17 years) when I want to prevent changes to a record but also want to allow others to prevent changes at the same time. But I always specifiy the SHARE-LOCK during the FIND statement. I never use a FIND without a lock option written in the code.

The SHARE-LOCK has it's place obviously: you apply it in cases you want to improve concurrency and increase consistency. An EXCLUSIVE-LOCK might be inpropriate, since you're locking out everyone, a NO-LOCK might be inpropriate, since other processes might be updating the data. So a SHARE-LOCK is your answer.

Err, would you care to clarify that? There are a few special applications where I have seen a use for SHARE-LOCK (not that I have ever felt like I needed to use one), but what purpose would you have in applying a SHARE-LOCK. Why is NO-LOCK ever inappropriate unless you planning right now to update the record? And why is EXCLUSIVE-LOCK ever inappropriate if you are doing an update?

The key here, of course, is that the transaction scope needs to be as short as possible so that the EXCLUSIVE-LOCK is held for the shortest period of time. Putting an EXCLUSIVE-LOCK on a record for some long period during user input is obviously inappropriate, but degrading that to SHARE-LOCK doesn't make it right, it is simply fixing the wrong part.

Imagine you have a sort of a very, very long running calculation. That calculation is based on master data and system data (i.e. tax master or so). While that calculation is running some might want to prevent this tax master record from being changed.

But others should be able to run the same calculation at the same time.

As this is may be legacy code that accesses the database direct and does not load all system data into temp-tables it's key to prevent changes from others. How would you do that without a share lock and without creating a manual lock?

I would never ever use share locks in a situation where I'm not sure in the beginning if I'd like to change the lock status and update the row. I see share-lock just as a "prevent others from updating" lock.

Mike

OK, so let's consider this very, very long running calculation and the business rules that go with it. What does it mean if it is possible for the underlying data to change in the middle of the calculation. If the tax master changes in the middle should the calculation have been done on the old values or the new?

The answer, I think, is that no business is going to want something this ambiguous.

Most of the time, the business rule is that the report or operation should reflect the state of the data at the time the process is started. For that, the correct solution is PRESELECT NO-LOCK or a temp-table. Merely preventing the data from being updated in the middle is just creating a mysterious behavior. Even assuming that the error message is trapped ... which is unlikely in a vintage application ... what's it supposed to say, "Come back in a few hours and try again"?

OK, so you have an encountered a situation in which a problem existed, you weren't allowed to fix it properly, and SHARE-LOCK provided a fix.

This is a very different issue than proposing that SHARE-LOCK is a correct approach to a design problem.

Thanks , I m very much agree with u all. One thing I get from ur replies, that share-lock is not permanent lock for any transaction, means when we want to upgrade lock to EXCLUSIVE-LOCK , we just use it for making data consistant for other users .

Thanks

Vishwdeep

If multiple processes have a share-lock on a record, it makes it impossible for any single process to get an exclusive-lock to update the record.

There are cases where this is good, but they are few and specialized.