Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unhappy Unanswered: Transaction Problem

    Hello,

    I have two processes.

    The first process starts a transaction. Locks record x of table y.
    Then waits for a z time long before ending transaction.

    Process 2 want to write record x of table y(same record) after process 1 has locked the row. Now the process waits until process one has finished the transaction.
    -------------------
    I work with oledb provider and want to set a timeout. is this possible? Or can this be done with a sqlstatement?

    By the way. The above case can also cause a deadlock when the opposite happens at the same time.

    greetz Coen

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Before running the statement, you could try to lock the table in order to make sure it is "free" using

    LOCK TABLE table_name IN EXCLUSIVE MODE NOWAIT;

    (or its derivations). If the table is unavailable (i.e. locked), it will return an error which you could trap and perform an appropriate action.

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unhappy Another thing

    So you want me to loc the WHOLE table? With a lot of users I don't think this is a good idea. So other alternatives?

    greetz

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    select <something> from <somewhere> for update nowait

    This will also immediately return an error if the row(s) are locked.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Try using the FOR UPDATE clause of the select statement to lock record x of table y. This is likely to cause deadlocks given the naturalization of the transaction. By the way, is this a web application ?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by billm
    select <something> from <somewhere> for update nowait

    This will also immediately return an error if the row(s) are locked.
    And if you do want to wait a while...

    select <something> from <somewhere> for update wait 5;

    (i.e. wait for 5 seconds).

Posting Permissions

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