Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    11

    Unanswered: Row Locking in SQL via ADO (VB 6)

    Hi,

    I'm trying to use the pessimistic row locking of SQL to get following result.

    When a customer form is openend, the row should be locked for writing.
    This lock should be left open until the user closes the customer form.

    I cannot use transactions because there can be more then 1 customer form open in the same app. In ADO a connection is IN transaction or is NOT, nested transactions are not supported.

    How can I keep this row locked on SQL and this until I unlock it or the connection is broken ( in case of problems on client machine )?
    And how can I see on another machine of this row ( customer ) is already locked so I can open him in read-only?

    For the moment I'm using extra fields that hold the info wether the customer is locked en by whom. But that's on application level, not on DB-level.

    I hope this is clear enough.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've often wondered if there is any reason that justifies pessimistic locking. So far I haven't found one. I recommend shutting down the SQL Server to get pessimistic locking... If the box is off, no other user can modify your data, and it makes the scaling problems caused by pessimistic locking less of a problem.

    To answer your question more directly, yes pessimistic locking can be done using ADO. It has been a long time since I've had any reason to try to hurt myself that badly after I established that it was possible, so I'm fuzzy on the details.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This may be what you're looking for:

    http://www34.brinkster.com/a213855/
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Feb 2004
    Posts
    11

    Other solution

    Pat,

    I'm convinced that pessimistic locking is not the ideal solution.
    How should i take care of the record-locking then?

    Regards,

    Sven Peeters

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ummmm, optimistic locking? I especially reccomend An update on UPDATing, but there are lots of good articles to read!

    -PatP

Posting Permissions

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