• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

How to make lock to row for selection

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
ram27
Asked:
ram27
  • 5
  • 5
1 Solution
 
ram27Author Commented:
We are using Sql server 2012
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
ram27Author Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that kind of code will work fine for batch jobs, but nor for users taking single records in my experience
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and it's not lots of code, less than 50 lines of code or so...
0
 
ram27Author Commented:
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
 
ram27Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
ram27Author Commented:
How can I make sure it is thread safe
Can I use
sp_getapplock
Before insert statement ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now