How to perform a row lock?

Question

I want to lock one record and then no one may make changes to that record. When I release the lock, then people may change the record.

In the meantime that a record is locked, I want to show the user a warning that the record is locked and that changes are not allowed.

How can I do this?

I've tried all the IsolationLevel levels, but none of them has the behavior I want. Some of the Isolation levels wait until the lock is released and then make a change. I don't want this, because updating is not allowed at the moment a record is locked.

What can I do to lock a record and deny all changes?

I use SQL Server 2008

Solution 1

With the assumption that this is MS SQL server, you probably want UPDLOCK, possibly combined with ROWLOCK (Table hints). I'm having trouble finding a decent article which describes the theory, but here is quick example:

SELECT id From mytable WITH (ROWLOCK, UPDLOCK) WHERE id = 1 

This statement will place an update lock on the row for the duration of the transaction (so it is important to be aware of when the transaction will end). As update locks are incompatible with exclusive locks (required to update records), this will prevent anyone from updating this record until the transaction has ended.

Note that other processes attempting to modify this record will be blocked until the transaction completes, however will continue with whatever write operation they requested once the transaction has ended (unless they are timed out or killed off as a deadlocked process). If you wish to prevent this then your other processes need to use additional hints in order to either abort if an incompatible lock is detected, or skip the record if it has changed.

---

Also, You should not use this method to lock records while waiting for user input. If this is your intention then you should add some sort of "being modified" column to your table instead.

The SQL server locking mechanisms are really only suited for use to preserve data integrity / preventing deadlocks - transactions should generally be kept as short as possible and should certainly not be maintained while waiting for user input.

Solution 2

Sql Server has locking hints, but these are limited to the scope of a query.

If the decision to lock the record is taken in an application, you can use the same mechanisms as optimistic locking and deny any changes to the record from the application.

Use a timestamp or guid as a lock on the record and deny access or changes to the record if the wrong locking key is given. Be careful to unlock records again or you will get orphans

Comments

[Martijn]

I've thought about such an approach, but what if the application crashes? Or if the server goes down. How do I unlock the record in such a situation?

[stombeur]

There is no easy way, I think. Make the locks expire by recording a datetime that it was locked and use a trigger or scheduled job to cleanup locks that exist too long. What you're essentially doing is pessimistic locking. also see this: stackoverflow.com/questions/386162/pessimistic-lock-in-t-sql

[Martijn]

I've tried this, but I am still able to select data and when I perform an update, the statement waits until the lock is released and then performs the update. This is not what I want. I want to notify the user if a record is locked.

[Martijn]

When I use this the update statement waits until the lock is released and then perform an update. I don't want this behavior. As soon a record is blocked, the user must see a message. Doing it this way, I can't notify the user because the update statement waits until the lock is released. I want the update statement to stop and give somehow an error/exception/warning so I can notify the user.

[Justin]

@Martijn - Your second update statement needs to be executed with the NOWAIT hint, which will cause it to return as soon as a lock is encountered rather than wait for the timeout.

[pepe450]

Shouldn't the statement read SELECT id From mytable WITH (ROWLOCK, UPDLOCK) WHERE id = 1?