Results 1 to 3 of 3

Thread: Deadlocks

  1. #1
    Join Date
    Oct 2010

    Exclamation Unanswered: Deadlocks

    We have two processes - one updating a table A and other inserting in table B but using table A i.e.selecting from A(along with other tables) in the query.
    We got a deadlock. Second process was using shared page lock on the page of A for which process was waiting for exclusive lock and process A was holding lock on page of table A for which B wanted shared lock. Is it possible for select to cause deadlock?

    1. update A
    set amt=xxX
    where <conditions>

    2. insert into #t1
    select x, y, z= (select max(amt)
    from A
    where condition)
    , a, b, c
    from B1, B2, B3
    where condition

    Process 2 was chosen as victim.

  2. #2
    Join Date
    Jan 2004
    Provided Answers: 4
    If the deadlock already has been resolved by killign the victim, there is no way to find out what exactly caused the deadlock anymore.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Apr 2008
    Iasi, Romania
    Provided Answers: 3
    1. Yes, a SELECT can apply a shared lock on a row/page/table that will prevent other transactions to write on that rows. That depends on your transaction isolation level:
    SyBooks Online

    2. In your example, setting isolation level to READ UNCOMMITTED will prevent the deadlock situation

    3. The locks in your example make sense: you select MAX from amt field in process 2, but you change the values in the same amt field in process 1.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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