Locking record in sql table

Question

I want to lock a row in sql table when it is opened on the screen, at the same time another user opened the same record means, it should open in read only mode.

For example: I have a Customer table, I logged in as a user A and I am editing the customer xyz record(row), at the same time another user B logged in and trying to edit the record but user B should not edit the record(even the page should not be empty).

How can I do this one?

Detecting that a row is locked can be done, but is not simple (there are different kinds of locks). See http://stackoverflow.com/q/7436935/67392 for more. But, detecting locks is probably the wrong approach: keeping db locks while users have something open is going to kill your scalability.l — Richard, Oct 20 '15 at 15:00
Also: My SQL and SQL Server will require completely different solutions if you really want to do this: do you really want both? — Richard, Oct 20 '15 at 15:01
I would do it manually, putting one more column "IsLocked" for example and then you can manage that lock through a function or stored procedure like AcquireLock(customerId)... — mehmet mecek, Oct 20 '15 at 15:08

score 1 · Accepted Answer · answered Oct 20 '15 at 15:08

1

One approach is to use bit field edit and assign it to true while a record is being edited. At the application level don't allow edits to a record in edit mode. The problem you have there is someone is in edit and just leaves it there so you need to have a timeout that clears edit mode. And might as well track who put it in edit mode.

answered Oct 20 '15 at 15:08

paparazzo

44,497
23
105
176

This would work, but I would still use a database transaction, and check the bit column before actually updating the database, and then set the bit column off, and then end the transaction. – David P Oct 20 '15 at 15:19
Thanks for the reply Frisbee. – Karthikeyan Oct 20 '15 at 15:39

score 1 · Answer 2 · answered Oct 20 '15 at 15:15

A product that we used to use had a table that maintained when locks were placed. In their system, primary keys were always a single integer. So, the table was fairly simple: session identifier(s), table name, row key. When a person went into a record, a procedure was run that would attempt to insert the lock. If the row already existed, that person would only get view. Otherwise, a row was added to the lock table, granting the session permission to edit. Once the record was exited, the row was removed from the lock table.

If the application was closed without using the proper exit mechanism (they had to click on sign out on the screen), we would get records that were locked out. We wrote a method to clear out these lock rows. We asked the original developers to clear out everything for a session on application termination but weren't able to get it added.

I would try to put an expiration time in a lock table so that it can get cleared out as needed. While you can't guarantee an application will always exit cleanly, try to include something to clear out all edit locks for a normal application exit.

mehmet mecek · Answer 3 · 2015-10-20T15:57:59.687

CREATE TABLE Customer
(
Id int,
Name nvarchar(max),
LockedOn DateTime
);


CREATE PROCEDURE dbo.AcquireLock (@customerId int)
AS BEGIN
    Update Customer set LockedOn = GetDate() where Id=@customerId and LockedOn is Null
    select @@ROWCOUNT
END

CREATE PROCEDURE dbo.ReleaseLock (@customerId int)
AS BEGIN
    Update Customer set LockedOn = null where Id=@customerId
    select @@ROWCOUNT
END

And use them in your code like

if (AcquireLock(customerId) > 0){
    //yes you are editing it.
}
else{
    //It is locked, you can not edit it
}

In this way, update statement should handle the pessimistic locking likely situation internally.

Thanks for the reply Mecek – Karthikeyan Oct 20 '15 at 15:56 — Karthikeyan, Oct 20 '15 at 15:56

Locking record in sql table

3 Answers3