|Subject||Re: SELECT FOR UPDATE WITH LOCK and Connections|
> "mucking about" but genuinely have a statement like this in aI am trying to have an application ported from an MS SQL backend to
> production application, CHANGE IT FAST!!
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
> Please read *carefully* the implementation and usage notes about FORserialization on a record-by-record basis. If you ever have this >
> 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 >
(rare) requirement, the scope of the query specification should be >
limited to a very few records (ideally, one record) by a very tight >
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?