Subject Re: Record Locking
Author Adam
--- In, "sasidhardoc"
<madhusasidhar@n...> wrote:
> I am using Firebird with ADO.NET. In a distributed application, I need
> to be able to "lock" a record for editing. Other, users should be able
> to "read" the data, but should be informed that the record is in use
> and the application will not allow the user to edit the data.
> Table NOTES
> NoteID
> Note
> User 1 starts a transaction to edit the NOTES table. User 1 is editing
> NoteID = 100. User 2 attempts to edit NOteID=100.
> How can my application notify User 2 that NoteID=100 is being edited
> by User 1 but allow him to view a "read only" version? TIA

Make sure the transactions are NO WAIT, otherwise user 2 will freeze
until user 1 saves.

A user must perform a dummy update before they can edit a note.

update notes set nodeid=noteid where noteid=100;

If it runs successfully, it will prevent any other transaction from
updating this record until your transaction commits (or rolls back).

If it fails, check the exception, (Lock conflict on NO WAIT I think),
and swallow it, you know you can only go into view only mode. It is a
bit more complex to actually store "who" is in there, but it can be
done. The main trick to remember is that even if you put the users name
into the table, because it is uncommitted, noone will be able to see it.

Once the first user is finished, the transaction is committed, which
will allow the next user to lock the record.

Be aware that this may hold transactions open for large lengths of
time, so at the very least use a timeout, or you will find the gap
between OIT and OAT will grow and garbage will build up.