Instead of a flag, introduce two columns ... column 1 holds user id, column
2 holds date/time of begin of lock ... you also need to decide the maximum
length of time that a user can hold a lock (5 minutes, 2 hours, etc.). I'd
suggest holding the maximum length of time in a table, rather than
When you want to lock the row, record the user id and the date/time of the
lock in the row ... let's say that user #1 is locking the row ... if user #1
experiences a power failure, when he comes back in the application is smart
enough to let user #1 back in even though the row is locked, because it is
the same user (and refreshes the lock datetime in the row).
If you decide on a maximum lock time of 2 hours, then after 2 hours, if user
#2 comes in and the row is still locked by user #1, then you let user #2 in
and update the locking information in the row.
The above will work, however there is significant overhead ... you are doing
a significant amount of updates on data just to implement locking. The above
is pessimistic locking ... i.e., it assumes that a user is reading data with
the intent of changing it.
If at all possible, implement optimistic locking ... add a column of data
type timestamp (not datetime!!). A timestamp is simply a number that is
guaranteed by SQL Server to be unique in the database, and is changed with
every update. When user #1 reads a row ... and user #1 may or may not update
the row ... return the timestamp value to the application. Let's assume that
user #2 also wants to read the row, and again, user #2 may or may not want
to update the row. So again the database returns the timestamp value of the
row. If both user #1 and user #2 decide that they both need to update the
row, then the first one to send the update wins because the timestamp value
that was read is returned to the database, and the database permits the
update if and only if the timestamp matches the current value in the
database. The second individual is informed that the data has changed since
he read it, and is (usually) prohibited from updating the data until the
application refreshes it and returns it so that the individual can re-review
it. This involves significantly fewer "wasted" I/O's.
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
> I have a client application that sets a flag in a table when a record is
> being edited and the resets the flag when the record is closed. The flag
> used to prevent other users from trying to edit the record at the same
> This design works assuming that nobody powers off their machine while a
> record is being edited leaving the flag set preventing all users from
> accessing the record and having someone accessing the raw data and
> the flag. I need to implement something more robust to lock the record
> while the user has the record open but to unlock the record if the user
> machine goes down while the record is locked. Any ideas would be much
> appreciated. Thanks in advance.