Locking and unlocking table records...

Locking and unlocking table records...

Post by jw » Tue, 19 Sep 2000 15:06:28



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 is
used to prevent other users from trying to edit the record at the same time.
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 resetting
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.

JW

 
 
 

Locking and unlocking table records...

Post by BP Margoli » Tue, 19 Sep 2000 04:00:00


JEW,

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
hard-coding it.

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.

----------------------------------------------------------------
BP Margolin
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.


Quote:> 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
is
> used to prevent other users from trying to edit the record at the same
time.
> 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
resetting
> 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.

> JW


 
 
 

Locking and unlocking table records...

Post by Dan Guzma » Tue, 19 Sep 2000 04:00:00


Rather than locking programmatically, you are probably better off letting
SQL Server handle concurrency and keep your transactions short.  A common
practice is to use a TIMESTAMP column.  When updating, compare it with the
initially retrieved value and raise an error if the value has changed since
retrieval.  For example:

UPDATE TableName




 RAISERROR ('Row has been updated or deleted by another user',1,1)

Hopefully, multiple users do not often update the same data so the
occasional error is not an issue.

Hope this helps.


Quote:> 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
is
> used to prevent other users from trying to edit the record at the same
time.
> 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
resetting
> 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.

> JW