Record Locking in SQL

Posted on 2005-04-19
Last Modified: 2010-03-19
I SELECT a record from my SQL database, display it on my VB form, edit the data and finally UPDATE the record back to the SQL database.

My problem is that whilst editing the data, anyone else could load that record and start to edit it. I need to lock the record so that no one else would be able to load into the edit form.

I have looked at SQL, but the locking seems only to apply to a single transaction. I can lock the record whilst SELECTING or UPDATING, but as soon as that is completed the record is unlocked. I cannot keep it locked for twenty minutes whilst I edit the data. (or am I mistaken).

Is there a built in method of record locking, or do I need to design my own Lock table mechanism?

Question by:MortimerCat
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>Is there a built in method of record locking, or do I need to design my own Lock table mechanism?<<
    No and yes.
    LVL 5

    Accepted Solution

    In your SQL statement, you can:
    1. Specify the transaction isolation level:
    If it is serializable, and you read from the table with a lock (and the whole operation is transacted), then you can guarantee that oo one would made modifications to the underlying table.
    Other could read from the table using the NOLOCK querying hint.
    This is not a good practice through.

    If you do choose to puruse this or similar solutions, make sure that you only lock the row (i.e., read with ROWLOCK) or page (PAGLOCK) that contains that row. Otherwise you'd be locking others for a while, which is not a good practice.

    2. You can only achieve this if the entire operation is transacted. It would not be wise to transact such a big operation (i.e., get data, display in form, update data).
    Instead, you can simply record the fact that that row is updated (say, by storing another column which contains a bit - BeingEdited for example, together with EditingUserID) which tells others that they can't update that record.

    Hope this help.

    LVL 68

    Expert Comment

    In general it's not a good idea to lock a row while a person is viewing/editing it.  What if he/she goes to lunch?

    But, if you need to in your specific case, probably the best way is to add a bit column to the table to indicate that it's "in use" (of an int to store the id of the person updating, if you can determine that).  Note that this means that every SELECT from the table becomes an UPDATE *and* a SELECT.  
    LVL 1

    Author Comment

    Thanks, you confirmed what I feared!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    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…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 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

    20 Experts available now in Live!

    Get 1:1 Help Now