CODEWITHSUNDEEP
c#mysql.netsql-server
Karthikeyan
Karthikeyan

Reputation: 319

Locking record in sql table

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?

Upvotes: 0

Views: 1817

Answers (3)

mehmet mecek
mehmet mecek

Reputation: 2685

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.

Upvotes: 1

Martin Soles
Martin Soles

Reputation: 559

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.

Upvotes: 1

paparazzo
paparazzo

Reputation: 45106

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.

Upvotes: 1

Related Questions