Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012

    Unanswered: a question on deadlocks and isolation levels

    Hi all,
    I'm using DB2 10.5 on Linux.

    I'm trying to determine the root cause of a deadlock. I have a deadlock trace in which I see 2 participants, a merge statement and a select statement (the victim).

    The only table present in the merge statement is DEVICES and the deadlock trace shows a lock list made by 4 X row locks and 1 IX table lock on DEVICES as expected.
    The select statement from the deadlock trace is the following:

    Deadlocked Statement:
    Type : Static
    Operation: Fetch
    Section : 2
    Creator : DB2INST1
    Package : P488946877
    Cursor : C100002
    Cursor was blocking: FALSE
    Text : DECLARE C100002 cursor with return to caller for
    select D.ID, D.CHECKTIME from DEVICES D where D.ISDEP = 0
    exists ( select * from DEVICE_OWNER DO
    where DO.ID = D.ID
    and DO.USER = :HV00008 :HI00008
    and DO.ISREADY = 1 )

    This statement is part of a stored procedure which in this case runs just this statement (other statements are in the else clause of an IF).
    Then in the trace the lock list follows where I can see 31500 locks on DEVICES of type NS - Share (CS/RS).
    No locks are listed on other tables (that is, I see only locks on DEVICES) apart from an IS lock on DEVICE_OWNER.

    The dynamic of the deadlock seems to be that the select holds a NS lock on a DEVICES row which the merge is trying to acquire in X mode, while the merge holds a X lock on DEVICES row which the select is trying to acquire in NS mode.

    So my question is:
    Can I say from the information above that this select statement is running in RS isolation level?
    In case of a CS I would expect a row lock to be released before acquiring the next one, so I shouldn't see this 31500 NS locks. Is it correct?
    But since no isolation level is explicitly specified, how is it possible that the RS level is used... shouldn't CS be the default?

    Thanks for any hint or clarification you can provide.

  2. #2
    Join Date
    Apr 2012
    Provided Answers: 27
    You get more details from an event monitor for locking, including the isolation level of the parties involved in the -911 event, although more setup is required. I prefer DB2 to tell me the isolation levels of the pkgs involved instead of guessing, unless I have the full code and configs of the parties involved. If any of the parties have code being issued by WAS the isolation default is often RR (not CS) so defaults can vary.

  3. #3
    Join Date
    Jul 2016
    Provided Answers: 45

    Seems, that this select runs with (select isolation from syscat.packages where pkgschema='DB2INST1' and pkgname='P488946877') isolation.

  4. #4
    Join Date
    Mar 2012
    Thanks for your replies,
    I'm going to check the isolation of the package like suggested by Mark but, regardless of this, my question was more about what you would expect from such a behavior. I'm wondering if this select was holding so many NS locks when the deadlock happens, does it mean it's NOT behaving like the CS isolation level is supposed to behave? In other words, is it correct to expect that all those locks except one should have been released for a query running under CS isolation? Or are there any cases in which this doesn't happen?

Posting Permissions

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