How to make lock to row for selection

Posted on 2016-08-18
Medium Priority
Last Modified: 2016-10-02
I have Below requirement
Web have web application , there is a page we display one record  from table called "TableA".
The user has option to go to next record  or mark it as complete by clicking on complete button .
The requirement is when the record is displayed on the screen , that record should be locked for that user and for any other users that record should not displayed until the current user closes the application or session time out or complete it
We are using asp.net and Sql server database
Pls let me know how to achieve this ?

Question by:ram27
  • 5
  • 5

Author Comment

ID: 41761468
We are using Sql server 2012
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41762101
you have to store the information next to the record (eventually in the table itself or in a separate table)

you will need 2 fields:
locked_by (username or FK to a "users" table, if you have such a table already)
locked_dt (time when the record was locked)
if it's in a separate table, you will need the primary key of the actual table as foreign key to tableA, and unique in the separate table

the process is then "simple":
when "fetching" a record to display (and hence lock) for this user, you "lock" a (unlocked) record, and return it.
note: if there is already a record locked for the user, you may simple return that one, and skip the "locking" of another record => you may also want to make "locked_by" field a unique constraint...

the "timeout" (using lock_dt) may "free up" the record to "unlocked", by clearing the 2 fields (set to null if in the same table) or deleting the record if it's in a separate table after some time.

on the button "complete", you should also check if the "lock" was still there, and eventually take appropriate actions (inform the user, run a "double-check" process to avoid "duplicate" processing etc ...

do you need further help?

Author Comment

ID: 41762441
Set lock_timeout 1800;
WHERE some condition
Will that work?
Maintaining separate table means lot of work and lot is code changes.
I tested with above query seems to be working
Pls advice
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41764102
that kind of code will work fine for batch jobs, but nor for users taking single records in my experience
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41764103
and it's not lots of code, less than 50 lines of code or so...

Author Comment

ID: 41764113
Thanks for your comment
If u go with ur approach , how will I make sure
to avoid to fetch same record in occurrence scenario

Author Comment

ID: 41764116
I want to go with ur approach :
TableA has primary key "PktableA"

I mean while checking whether the record exits or not in
Table called "userlock" table
Is there any way to allow one only one thread to execute stored proc code in Sql server .
Example :
Inside strored proc :
Line 1: Lock stament to allow only one thread to execute bellow code
Line 2: check any record exist for that user in "userlock" table , if so delete that record .  Code to fetch record from TableA
Line 3: check whether PKTableA  (fetched from prev step) exist in "userlock" table ,
Line4: if not insert record to "userlock" table  , then return that record from "tableA"
Line 5: if yes, just return same record . ( this won't happen as we are deleting prev records from "userlock" table before doing anything )
Line 6: code to unlock statement
How to achieve line 1 and line 6
That is lock statement in stored proc ,
To allow only one thread to execute price of code at any point of time .
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 41764134
this is the simple statement to "lock" a record, 1 single procedure
create procedure lock_table_a ( @user varchar(100) )
set nocount on

-- attempt to lock a record (that is not locked yet)
insert into lock_table ( PKTableA  , usedby, lock_dt )
select top 1 a.PKTableA  , @user, getdate()
   from tableA a
where not exists(select null from  lock_table x
              where x.PKTableA   = a.PKTableA  
     --and add here some conditions to make sure to lock only records that actually need processing

set nocount off
-- and to return that record, if any:
select top 1 a.*
  from lock_table x
  join tableA a
     on a.PKTableA   = x.PKTableA  
  where x.usedby  = @user

Open in new window

to release the record:
create procedure release_lock_a( @user varchar(100))
   delete lock_table 
   where usedby  = @user

Open in new window

if the first procedure does not return any records, there is nothing to be done
the lock_table, as indicated, needs to have a unique constraint on used_by and a second unique constraint on PKTableA field, to ensure that even if somehow things would not work, it would avoid any issue.

and finally, you can create a scheduled job to run every 10 minutes to release the "locks" after 60 minutes, for example:
create procedure release_timedout_locks
   delete lock_table 
   where locked_dt < dateadd(hour, -1, getdate())

Open in new window


Author Comment

ID: 41764196
How can I make sure it is thread safe
Can I use
Before insert statement ?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41764329
no need to make it "thread-safe", the unique indexes will handle that.
the only thing you need to do is on the procedure call to check if you get a record or an error.
on "error", you can just retry running the procedure.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question