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

MS SQL Row Locking and Release

I am using MS SQL 2005.
I want to
- obtain the next unlocked record in a table and lock it all at once so no other user can fetch it.
- work on the record for maybe 5 minutes. ( while doing this no one else can use the record)
- update the record and release the lock on it.

Also if the user  decides not to update, a way to release the lock on
this record.

Your help is greatly appreciated.
Thank you
0
jimr111998
Asked:
jimr111998
1 Solution
 
Kobe_LenjouCommented:
Have you checked the HOLDLOCK and READPAST table hints?

The HOLDLOCK will hold your lock on the record until the currect transaction end.
You have to define you own transaction with BEGIN TRAN and, depending in the user a COMMIT TRAN or ROLLBACK TRAN

http://msdn.microsoft.com/en-us/library/ms187373.aspx
0
 
Anthony PerkinsCommented:
Create a column in the table that indicates the row is locked.  Set it when you are editing it and clear it when you are done.  In addition, have a job run every 5 minutes to clear the flag on rows that show locked more than 5 minutes.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is an applicative problem.

so, what you need is a "session" id, and update the record with that session id.
say the column is named "lock_id"
SET ROWCOUNT 1
UPDATE yourtable SET lock_id = <the session id> WHERE lock_id IS NULL  ... + other conditions, eventually 
SELECT * FROM yourtable WHERE lock_id = <the session id>
SET ROWCOUNT 0

Open in new window


and to release:
UPDATE yourtable SET lock_id = NULL WHERE lock_id = <the session id> 

Open in new window


this way, you can still query for the record, but the application itself, "getting" the record that way, will not get a record of some other "user".

now: you have though to consider the case the application crashes, network break or whatever before the "lock" is released ...
well, I usually have a "lock_time" column which is update at the same time, and regularly "unlock" them automatically after 1 hour or more.
this shall also be considered in the application's UPDATE : if the record's lock_id is no longer set or different, the update must be skipped, user warned, optionally given the option to "relock" the records and show any differences between the data when read, now in the db, and the user's changes ...

this should give you some ideas to think about.
0
 
jimr111998Author Commented:
angelIII:

Very good. I can do that. I don't think I need any more info. This will work fine.
Thanks for the ideas.
0
 
jimr111998Author Commented:
Very good response!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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