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

Answer 1

See this duplicate question on SO.

Basically it's:

begin tran

select * from [table] with(holdlock,rowlock) where id = @id

--Here goes your stuff

commit tran

Archive

Something like this maybe?

update t 
set t.IsLocked = 1 
from [table] t 
where t.id = @id

Somewhere in the update trigger:

if exists (
select top 1 1 
from deleted d 
join inserted i on i.id = d.id
where d.IsLocked = 1 and i.RowVersion <> d.RowVersion)
begin
print 'Row is locked'
rollback tran
end

Answer 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

Answer 3

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 WHERE id = 1 WITH (ROWLOCK, UPDLOCK)

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.