Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    17

    Unanswered: How to know the blocking lock

    When I use "db2 get snapshot for locks for db-alies" to get following message:

    Database Lock Snapshot

    Database name = ZMCCDB
    Database path = /home/zmccinst/zmccdb/db2inst2/NOD/
    Input database alias = ZMCCDB
    Locks held = 1
    Applications currently connected = 3
    Agents currently waiting on locks = 0
    Snapshot timestamp = 08-01-2004 17:40:57.756671

    Application handle = 323
    Application ID = GA465616.ECB2.0B2AA1085734
    Sequence number = 0028
    Application name = java
    CONNECT Authorization ID = DB2INST2
    Application status = UOW Waiting
    Status change time = Not Collected
    Application code page = 1208
    Locks held = 0
    Total wait time (ms) = 0
    Application handle = 228
    Application ID = GA465615.D358.0BA481083012
    Sequence number = 0141
    Application name = java
    CONNECT Authorization ID = DB2INST2
    Application status = UOW Waiting
    Status change time = Not Collected
    Application code page = 1208
    Locks held = 0
    Total wait time (ms) = 0


    Application handle = 284
    Application ID = *LOCAL.db2inst2.0497D1062726
    Sequence number = 0004
    Application name = db2bp
    CONNECT Authorization ID = DB2INST2
    Application status = UOW Waiting
    Status change time = Not Collected
    Application code page = 1386
    Locks held = 1
    Total wait time (ms) = 0

    List Of Locks
    Lock Name = 0x434F4E544F4B4E3153544E4441
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 0
    Object Type = Internal P Lock
    Mode = S

    I want to know which kind lock is the dead lock and the dead lock 's conficted lock.
    Thanks!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A deadlock is not a type of lock (like a S, U, or X lock). It is a condition that occurs when two or more applications are already holding one lock (successfully) and they each need to obtain another lock which another application holds. In this case, the deadly embrace will never be resolved, until DB2 returns a -911 reason code 2 to one of the applications. DB2 arbitrarily picks one of the applications involved in a deadlock as its victim for the -911, so that the other application can continue.

    A lock timeout (-911 reason code 68) is not quite the same as a deadlock. This occurs when one application holds another lock that is need by a second application. Eventually a lock timeout would resolve itself when the first application finishes, but most DBA's set a time limit for the wait time, after which the -911 is returned to the second application.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2004
    Posts
    17
    I understand what you mean. From the "db2 get snapshot for locks for db-alies" command, I can't get the deadlock information because the return information from this command does not show what the lock are obtaining.
    Also I know that DB2 can automatically balance this confict.
    However, I met with the case that there were many locks so that my application became slow. I had to use "db2 force application all" to close all the locks.
    So I want to watch the deadlocks in order to avoid this case just like in the ORACLE. Could you tell me the methods to watch the existed deadlock?
    thanks a lot

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can use the Event Monitor to capture information about deadlocks (and other events). The Event Monitor can be activated via the Control Center or by issuing the CREATE EVENT MONITOR SQL statement.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    http://www-106.ibm.com/developerwork...10wilkins.html

    is a very good artcile on the subject

    cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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