How to implement a simple lock mechanism for multi-user application?

Question

I really don't want to re-invent the well here, so I'm asking for ideas to implement a simple (row) lock mechanism in a multi-user DB application.

Suppose I have a Table called Products which of course has an ID (PK), and also a rowversion column (which is not used so far), and I want to allow only one user to be able to Edit a specific row.

While this users is editing the record (after a "check in"), other users can only View this record (read-only), but not modify or delete it. when the user is done and saved the record ("check out") this record will be again available for editing/deleting by other users.

I have a few ideas (such as adding a "status" column, or maybe create a "lock" table), but there also be considerations if the "locking user" is holding the record for a long time (suppose he went for a weekend and left his computer opened in edit mode). also how to unlock the record if the program crashed/system power down on the client machine...

I was wondering if there is a good and relatively simple pattern for this (which might include SQL-Server features)?

BTW, My client app is Delphi/ADO (not that it's very relevant).

Answer 1

A simple solution I implemented in an application ....

CREATE TABLE RecordLocks(
[RecordId] [varchar](8) NOT NULL,
[UserName] [varchar](100) NOT NULL,
[datetimestamp] [smalldatetime] NOT NULL,
[PC] [varchar](100) NOT NULL

)
GO

datetimestamp has a default of GetDate() RecordId is a VARCHAR because of the primary key in the table I am locking (not my choice). Also this table has the obvious indexes

CREATE PROCEDURE usp_LockRecord @RecordId VARCHAR(8), @UserName VARCHAR(100), @ComputerName VARCHAR(100)
AS
BEGIN
BEGIN TRAN; 
DELETE FROM RecordLocks WHERE DATEDIFF(HOUR, datetimestamp, GETDATE()) > 2; 
IF NOT EXISTS (Select * from RecordLocks WHERE RecordId = @RecordId) 
    INSERT INTO RecordLocks (RecordId, username, PC) VALUES (@RecordId, @UserName, @ComputerName); 

Select * from RecordLocks WHERE RecordId = @RecordId; 
COMMIT TRAN;
END
GO

First delete and records older than 2 hours (change to suit)

Check there is no record already locking the the one to lock and if not insert the lock.

Select the record with the RecordId we are interested in.

Then in the calling code check to see if the lock has been successful. If the username and PC coming back from the select matches the data just passed in the lock was successful. If the username matches but the PC doesn't the same user has the record open on a different machine. if the username does not match another user already has it open. I display a message to the user if its unsuccessful I.E This record is currently locked by JoeB on workstation XYZ.

When the user saves the record or navigates away just delete the record lock.

Im sure there are other ways but this works well for me.

Update

A record will only be inserted if one does not exist. The following select will return a record. If the username and/or pc is different to the data you attempt to insert the record is already locked by another user (or same user on a different machine). So one call does all (so to speak). So if I make a call Exec usp_LockRecord(1234, 'JoeB', 'Workstation1') and the record I get back matches that data I have successfully got a lock on that record. If the username and/or PC I get back is different the record is already locked. I can then display a message to the user advising the record is locked, make fields read only, disable save buttons and tell them who has a lock on it if I wish.

Answer 2

With a timestamp, you can get away with "cheating". The workflow looks something like this:

1. Read the row (including timestamp) into memory
2. Let the user do some editing, keeping track of old and new values
3. User hits "save"
4. Read the row's timestamp again (in an isolation level that prevents unrepeatable reads)
   1. If the recently read timestamp is the same as the old timestamp, update the row with the user changed values and commit
   2. If the recently read timestamp is different than the old timestamp, you can attempt to "merge" the changes (i.e. if a completely disjoint set of properties changed, the two changes are compatible). If there is any overlap, warn the user and disallow the save

That should do it.