How To Lock The record in SQL SERVER 2005 When It is being accessed by Other Person? RRS feed

  • Question

  • Lock The record in SQL Server 2005 When It is being accessed by Other Person.It's urgent please.

    Suppose Table "test" contain 10 records .

    If Person one is editing record Number "1" then other person could not get access to update record Number "1" he only view that record doesn't have permission to edit that record.

    Saturday, September 13, 2008 11:00 AM

All replies

  • Being the second post on the same topic in two weeks, and you have not gotten new answer. I would suggest you to explain the problem statement you are trying to solve.  Record locking is not a new problem.  It has been solved and addressed many different ways for many years.  You may have to explain what is driving to "Locking the record"
    Saturday, September 13, 2008 6:24 PM
  • Since this is a SQL Server question, why don't you post it to the SQL Server forums? Asking questions in irrelevant forums is a sure way to get no answer. 

    As far as your question is concerned ... what is the question? Do you want to prevent the second person from reading a record or do you want him to be able to read but not write ? If this is such an urgent question why don't you provide enough information so that you can get a quick answer?

    I'll go on a limb here and try to guess what the question is. I assume that User 1 opens a form with Record 1 and you want to prevent User 2 from reading/writing to Record 1 while User 1 works with the form. I assume that is what you mean by "editing".

    In order to solve your problem you have to understand what is going on - and why locking the record is a horrible idea.  In reality, User 1 is NOT editing record 1. His application has simply read Record1 from the database, doing something with it that the database doesn't know or care about and may in the future write back to it. The database should never be affected by what the user is doing offline so  - no locking of the record. 

    Locking records for a long time creates huge problems. The first one is blocking. If you lock a record, other transactions that use the record will freeze. Second, users hate it. They hate it when they have to go searching for the person that left a record locked and went out for lunch. Third, database administrators hate it, mainly due to blocking and performance issues.

    There are many other ways to inform User 2 that someone else is working with Record 1. The easiest way is to use optimistic locking to detect that User 1 and User 2 have made changes to the same record. 
    You can add a status field to the table with the name of the user currently editing it, but you have to make sure the field is cleared event when User 1 computer's crashes and fails to clean it. 
    You can use a server component to keep track of who uses what in memory, thus avoiding the cleanup problem.

    So, what is the question?

    Monday, September 15, 2008 6:56 AM
  • Sql server controls lock escalation. You can control this by supplying lock hints to Sql commands.


    see http://msdn.microsoft.com/en-us/library/aa213026.aspx


    hope this helps.

    Monday, September 15, 2008 1:39 PM