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

Question

I really don't want to reinvent the well here, so I ask for ideas to implement a simple row locking mechanism in a multi-user DB application.

Suppose I have a table called Products , which of course has an identifier (PK), as well as a rowversion column (which is not used yet), and I want only one user to be able to edit a specific row.

While these users are editing the record (after "checking"), other users can view this record (read-only), but not modify or delete it. when the user is executed and saves the record (“check”), this record will again be available for editing / deletion by other users.

I have some ideas (for example, adding a “status” column or creating a “lock” table), but also have ideas if the “blocking user” holds the record for a long time (suppose he went on a weekend and left his computer open in edit mode ) as well as unlocking the record if the program crashed / disabled the system on the client machine ...

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

BTW, my client application is Delphi / ADO (this is not very relevant).

Answer 1

A simple solution implemented in the 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 GetDate() RecordId is VARCHAR because of the primary key in the table that I am locking (not my choice). This table also has 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 record more than 2 hours (change is appropriate)

Check if there is a record that already locks the lock and if it does not insert the lock.

Select a record using the RecordId that interests us.

Then, in the call code, check if the lock was successful. If the username and the PC returning from the selection match, the data that just passed in the lock was successful. If the username matches, but the PC does not have the same user, the record will be opened on another machine. if the username does not match with another user, it is already open. I show a message to the user if his unsuccessful IE. This entry is currently blocked by JoeB on the XYZ workstation.

When the user saves the record or moves, just remove the record lock.

I'm sure there are other ways, but this works well for me.

Update

A record will be inserted only if it does not exist. The next selection will return the entry. If the username and / or PC is different from the data you are trying to insert, the record is already locked by another user (or by the same user on another machine). Thus, one call does everything (so to speak). Therefore, if I make a call to Exec usp_LockRecord(1234, 'JoeB', 'Workstation1') , and the record I get matches this data, I successfully got a lock on this record. If the username and / or PC returns me, then the record is already locked. Then I can display a message to the user informing that the record is locked, make the fields read-only, disable the save buttons and let them know who has the lock if I want.

Answer 2

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

• Read the line (including timestamp) into memory
• Allows user to edit, tracking old and new values
• User removes "save"
• Read the timestamp of the line again (at the isolation level, which prevents unique reads)
  • If the recently read timestamp matches the old timestamp, update the line with the changed user values ​​and commit
  • If the newly created timestamp is different from the old timestamp, you can try to “merge” the changes (that is, when changing a completely disjoint set of properties, these two changes are compatible). If there is overlap, warn the user and prohibit saving

That should do it.