MS SQL Row Locking and Release

Posted on 2011-05-05
Last Modified: 2012-08-14
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
Question by:jimr111998
    LVL 8

    Expert Comment

    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
    LVL 75

    Expert Comment

    by:Anthony Perkins
    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.
    LVL 142

    Accepted Solution

    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"
    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>

    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.

    Author Comment


    Very good. I can do that. I don't think I need any more info. This will work fine.
    Thanks for the ideas.

    Author Closing Comment

    Very good response!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how the fundamental information of how to create a table.

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now