Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: Record Locking Problem

    Hi,

    Any one can help me out.....

    I am listing my complete problem.

    I have multiuser application on SQL Server running.

    One user is updating a Row which is having master-detail table.

    User updating the record. He is making changes to Detail table and updated one row in that. Transaction is still going on and it is not commited.

    And another user is now trying to read the record. He will get the Uncommitted data i.e. that Updated detail record. But I don't want that updated one row but whatever previous is there I want that record.
    I am using Read Past lock so obviously i will get the updated record but Anyone please tell me what to do with it?

    I wish that SQL Server give me the old detail record.

    and any other possibility? We can't use DateTime Stamp as it is master detail table. I need to read Master datetime stamp as well as detail datetime stamp.

    Thanks in Advance.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What programming language are you using for the application ?

  3. #3
    Join Date
    Dec 2003
    Posts
    4
    I am using Visual Basic 6.0 and SQL server 2000.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    What is the transaction isolation level on your SQL server 2000?

    Originally posted by hiren_gajjar
    I am using Visual Basic 6.0 and SQL server 2000.

  5. #5
    Join Date
    Dec 2003
    Posts
    4
    Using READCOMMITED Isolation Level with Update Lock.

  6. #6
    Join Date
    Oct 2003
    Posts
    706

    Red face

    What you'd like to have is the "generation based" architecture of a Postgres or Firebird (Interbase) database, where users can see the last committed "version" of any record.

    In this circumstance, what you really want is for the amount of time that a record remains locked to be minimal. It sounds like you're taking a "PC fileserver database" style of approach ... pessimistic locking, and holding those locks while the user sits and thinks, talks on the telephone, eats lunch, goes to the loo, or what-have-you.

    Anytime you're updating a shared database, you want an "optimistic" locking strategy: records are locked only for a few milliseconds, during the actual update, and the data is checked for third-party changes during the moment that the lock is held. ("Optimistically" expecting that there will have been none.)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Dec 2003
    Posts
    4

    Question

    Have I mentioned about pessimistic record locking, I dont think so. I am using locking hint (UPDLOCK) with the query itself. and in different recordset I am using NOLOCK hint. DO you think if I dont use these things the other user can see or modify it?

  8. #8
    Join Date
    Dec 2003
    Posts
    12
    See if you can you make use of "deleted table" here using the update trigger?

  9. #9
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    2

    Re: Record Locking Problem

    SQL server will not supply what you are asking for. Some other database support previous value, SQL Server just does not.

    You will have to work around it, either in the program, by not sending the data to the database until the user wants to commit, or by remodeling the database design to be able to hold two or more values per unique table.

    Since you are currently locking the data, I assume you do not have to support several clients updating the same data. (make sure you are row-level locking, otherwise the whole page or table might get locked by 1 user). For 1 updating client 1 more table holding the new values until user is ready to commit would do.

    In general, forcing update locks and uncommited read will allways get you into trouble. Just don't go there if you can avoid it. Both solutions just given (application and model based) do not need any kind of locking hints, read committed will do fine.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Record Locking Problem

    Originally posted by hiren_gajjar
    One user is updating a Row which is having master-detail table.

    User updating the record. He is making changes to Detail table and updated one row in that. Transaction is still going on and it is not commited.

    And another user is now trying to read the record. He will get the Uncommitted data i.e. that Updated detail record. But I don't want that updated one row but whatever previous is there I want that record.
    First off, that's not a true (and not very clear) statement

    Second, are we talking about 1 row...what is your application interface model?

    An update should be almost instantanious...unless you're attaching tables in access, and the guy goes to edit a record the foes to lunch

    And if I remeber my Access days, optimistic locking in Access allows dirty reads, so when he comes back, the other user will have grabbed the "old" record, and user that is "out to lunch" will get a message saying that the data has been updated by another user...and he'll have the option to update (overlay) or escape the transaction..

    I don't think (damn, I hate when that happens) that SQL Server allows Access's optimistic locking...left my Access2k books at home, but I'm sure I read that somewhere...could be wrong though


    ooops...sometime you have to read these threads....I see now VB 6...doesn't it work the same way though?

    With all the babbling aside...you're better off with very short transactions.

    Read the data, store it, close the record set...as soon as possible

    Now it's time for a MOO (my Own Opinion)

    PS Don't use NOLOCK
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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