Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: Record Locking Bug/Issue

    Hi there,

    Up until this point I've been quite lazy in trying to understand how record locking really works in SQL server however one of my applications now has a bug so the time has come for me to take the issue seriously.

    Basically, a user clicks a button in my app and updates a field in a table in SQL Server (say at 10:34:55). At the almost exactly the same time (lets say 10:34:57) another user tries to update the same record (not necessarily the same field) but the second users update is not connected to the database.

    How can I store the second users update and then commit to database once the first user is finished...is it possible or do i need to approach this from a different angle?

    Thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578

  3. #3
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Useful

    Thanks for that, very interesting. The application in question is a complete mess, a VB 6 front-end linked to SQL Server 2000 through an Access 97 database with security controlled through a workgroup file. Hence, most of the logic is in the front-end and not in stored procedures - nice!! I've moved a lot of it into stored procs but don't have time at the moment to re-write the entire database.

    Think the easiest way for me to get round the record-locking issue is to use a field to flag when a record is locked/not locked. Should be re-writing the app later on in the year so will be pure VB.NET/SQL 2000 app - will make sure record-locking is considered properly at this stage.

    Cheers

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Ok, how about schedule of database maintenance tasks and recompiling the stored procedures that will help to fine tune the performance.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Do you mean something more along the lines of you have a (hyper-simplified) table of three columns:
    Code:
    id    col1 col2
    ---- ----- ------
    1     a      b
    User1 updates col1 at 10:34:55 so you have:
    Code:
    id    col1 col2
    ---- ----- ------
    1     u1    b
    But User2 issues an update (ostensibly for col2) that puts you in the uncomfortable postion of explaining why the table now looks like:
    Code:
    id    col1 col2
    ---- ----- ------
    1     a      u2
    I have heard this called the "Lost Update effect", and is generally caused by a blanket update statement like:
    Code:
    update table
    set col1 = val1,
      col2 = val2
    where id = n
    One way around it is to add a "Last Updated" datetime field that would have to be checked before the update query is run. In short, if the application goes to update, and notices the table has a last updated field later than what it got when it read its copy of the original record, the update will not go through, and maybe the user's (user 2 in this case) screen gets refreshed with the new values.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    BOoks online and http://www.sql-server-performance.co...ocks_hints.asp is a good resource to reduce the locking during update. If you want reduce the issue then you have to rewrite or optimize the query for optimum performance.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •