Solved

How to make lock to row for selection

Posted on 2016-08-18
10
41 Views
Last Modified: 2016-10-02
Hi
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 ?

Thanks
Ram
0
Comment
Question by:ram27
  • 5
  • 5
10 Comments
 

Author Comment

by:ram27
ID: 41761468
We are using Sql server 2012
0
 
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?
0
 

Author Comment

by:ram27
ID: 41762441
Set lock_timeout 1800;
SELECT * FROM TabA WITH ( UPDLOCK,HOLDLOCK)
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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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
0
 
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...
0
 

Author Comment

by:ram27
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
0
 

Author Comment

by:ram27
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 .
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41764134
this is the simple statement to "lock" a record, 1 single procedure
create procedure lock_table_a ( @user varchar(100) )
as
begin
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))
as
begin
   delete lock_table 
   where usedby  = @user
end

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
as
   delete lock_table 
   where locked_dt < dateadd(hour, -1, getdate())
end

Open in new window

0
 

Author Comment

by:ram27
ID: 41764196
How can I make sure it is thread safe
Can I use
sp_getapplock
Before insert statement ?
0
 
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

839 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