Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513

    Unanswered: Deadlocks and view

    I have two tables, let's call them A and B.

    I have a view, call it V, which is defined as:
    SELECT * FROM A, B WHERE A.ID = B.ID

    Now I have an application #1 that has to update A and B. So I did an update to A, then I updated B.

    It's a live system, though, so in middle, applicatoin #2 asked for "select * from V".

    SQL0911 rc=2. From the event monitor, I can see that #1 has a lock on A, #2 has a lock on B.

    I have re-written #1 so it updates B then A, but it occurs to me that I have no guarantee from DB2 that "select * from V" will always get lock B before A. In fact the way the view is defined suggests the opposite.

    Help.
    --
    Jonathan Petruk
    DB2 Database Consultant

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Bumping, hopefully someone has an idea.

    I expect the only solution is just to keep the time between the update on A and the update on B as short as possible to reduce the possibility of someone grabbing the B lock before we get a chance.

    jono
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What isolation level the SELECT uses? Can you change it to a lower level?

    You may want to do "select ... for update" on both A and B _before_ actually updating it - at least you'll get a lock timeout instead of a deadlock, which you can catch and re-try...

  4. #4
    Join Date
    Oct 2005
    Posts
    109
    How about the DB2 registry variables DB2_SKIPINSERTED or DB2_SKIPDELETED and DB2_EVALUNCOMMITTED ??

    ok, the last would not help since no where-condition is specified.
    But DB2_SKIPINSERTED sounds good ...
    Juliane

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by n_i
    What isolation level the SELECT uses? Can you change it to a lower level?

    You may want to do "select ... for update" on both A and B _before_ actually updating it - at least you'll get a lock timeout instead of a deadlock, which you can catch and re-try...
    Both run as CS, so there isn't a practical lower level.

    I'll try the SELECT ... FOR UPDATE, that might work.

    I've set DB2_SKIPINSERTED, DB2_SKIPDELETED, DB2_EVALUNCOMMITTED, to no effect. Since it's an UPDATE, only EVALUNCOMMITTED really applies, and because the row is a matching row it still needs to lock.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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