Subject Re: [firebird-support] Record Locking
Author Helen Borrie
At 04:42 AM 28/09/2005 +0000, you 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
>User 1 starts a transaction to edit the NOTES table.

No, user 1 starts a transaction, period. Starting a transaction merely
opens a conversation between a client and the database engine, involving
one or more databases and a bunch of attributes for the
conversation. "Editing" is a client-side notion that the database engine
knows nothing about.

>User 1 is editing NoteID = 100.

But only a copy of NoteID = 100 that she has brought into her program. At
this point, her program, along with any other users' programs looking at
this record, is just reading it.

>User 2 attempts to edit NOteID=100.

Again, same story. "Editing" is something that goes on inside a program,
not in the database.

>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?

If both User 1 and User 2 are looking at the same data both via read-write
transactions, the first to post an update to the database will "get the
lock". From there on, other transactions continue to have read access to
that record, but no others will be able to write to it, i.e. the other
users' programs will receive a lock conflict exception.

A common technique (which is used more often than is actually necessary) is
to set a "pessimistic lock" by having the first user post a dummy update
immediately your code puts the record into "editing mode". That is, submit
a request such as "update Notes set NoteId = NoteId where NoteId =
100". Each user then that tries to do this same dummy update will fail if
someone has already done it.

The downside of this is that you have to be really careful to write update
triggers in such a way that they will execute "no-op" if a dummy update
request is received.

Fb 1.5 and onward have a pessimistic locking syntax that gets past this
downside. But it's not a technique to use blindly or to apply to
multi-record sets. Details are in the v.1.5 release notes.