Subject Re: SELECT FOR UPDATE WITH LOCK and Connections
Author sasidhardoc
> "mucking about" but genuinely have a statement like this in a
> production application, CHANGE IT FAST!!
I am trying to have an application ported from an MS SQL backend to
FB. A table, (progress notes for patients) has multiple records that
are patient progress notes. Only one person should be able edit a
progress note at a time - so we open it using a "SELECT (WITH LOCK)"
in MS SQL. If it succeeds, then a second transaction writes the user
name to an AUDIT table and commits.
Subsequent attempts to EDIT this progress note while the first
transaction is still active will result in an error that is trapped.
For subsequent transactions, the note is opened "read only" and also
retrieves information from AUDIT to show who is currently editing the
note etc.
> Please read *carefully* the implementation and usage notes about FOR
> UPDATE WITH LOCK in the Firebird 1.5.3 release notes. It was *not*
> designed to be a workaround for developers who can't be bothered
> handling lock conflicts. It's purpose is to apply strict >
serialization on a record-by-record basis. If you ever have this >
(rare) requirement, the scope of the query specification should be >
limited to a very few records (ideally, one record) by a very tight >
WHERE clause.
The SP retrieves ONE record - the NOTE that we are trying to edit -
using "WHERE ID(PK) = x"
In any case, it looks like SELECT FOR UPDATE with LOCK will not be an
adequate replacement for MS SQL "SELECT (WITH LOCK)" since dropped
connections (such as flaky laptop wireless connections) will wreak
havoc leaving behind locked records.
How would I address my need to allow only one user to edit the record
at a time?