Solved

How to make lock to row for selection

Posted on 2016-08-18
10
38 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
Comment Utility
We are using Sql server 2012
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
 
LVL 142

Expert Comment

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

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
and it's not lots of code, less than 50 lines of code or so...
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:ram27
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
How can I make sure it is thread safe
Can I use
sp_getapplock
Before insert statement ?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now