Subject Re: Record Locking
Author Adam
--- In, "sasidhardoc"
<madhusasidhar@n...> wrote:
> Helen,
> Thanks!
> For transactions involving single records, is there any downside to
> Select ... For Update With Lock.

You can only use this syntax if your databases are FB 1.5 or higher
(not 1.0)

> Instead of "dummy updates"
> should I use a separate table to store userID so that my application
> can find the user that has the record "locked"?

As I stated in my post, this will not work. The reason is that the
transaction from the first user is uncommitted. When the second user
requests the user id, what they see will depend on their transaction
settings, but it will not be the uncommitted insert.

Now the client could start a second transaction, update a separate
user table and insert their userid for that purpose and commit the
second transaction. Unless the second user gets in between the lock
being granted and the userid being inserted, this would work, and we
have implemented this in practice. You will need two separate error
messages in your client application, one that names the user, and one
generic error that someone else is using it. In practice, I have only
seen the generic error when I deliberately created a scenario to get
it, never in the real world.