Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2002
    Posts
    8

    Unanswered: how to avoid deadlocks

    HI,
    I have DB2 UDB on RedHat Linux7.0.when took the snapshot of my database i found following

    Locks held currently = 0
    Lock waits = 866
    Time database waited on locks (ms) = 1631180
    Lock list memory in use (Bytes) = 7056
    Deadlocks detected = 4
    Lock escalations = 0
    Exclusive lock escalations = 0
    Agents currently waiting on locks = 0
    Lock Timeouts = 0


    Internal automatic rebinds = 0
    Internal rows deleted = 0
    Internal rows inserted = 0
    Internal rows updated = 0
    Internal commits = 6269
    Internal rollbacks = 77
    Internal rollbacks due to deadlock = 4

    I do see some deadlocks ocuring in the database.How can we avoid deadlocks ?.is there a parameter that needs to configured ?

    recently i increased maxappls and maxagents does these parameters are contributing to deadlocks?

    Any sort of help is greatly appreciated.

    Thanks
    Jackcool
    Last edited by jackcool; 02-12-02 at 12:38.

  2. #2
    Join Date
    Feb 2002
    Posts
    33
    Hi!
    Deadlocks! These can occur in any environment which is handling a high volume of activity. Indirectly, increasing the maxagents and maxappls can affect this (now have more apps trying to do work, and they may overlap in the data they require, resulting in potential deadlock).
    To help track deadlocks you can db2evmon (Event Monitor, check the Monitoring Reference Guide). This will give you a bit more detail on which table was involved, application, query etc. Using this, you can judge if the isolation level being used is approriate for your environment.

  3. #3
    Join Date
    Feb 2002
    Posts
    8
    Raj Thanks for the reply.

    I have created an event monitor for deadlocks using following syntax
    db2 "create event monitor dlocmon for deadlocks write to file '/tmp/dlocks'"

    when view the event monitor using following command

    db2evmon -path /tmp/dlocks

    what i see is number of connection headers.
    my question is why event monitoring all the connection headers i want event monitor to record only deadlock event.Is the above mentioned systax wrong?.

    Any help is greatly appreciated

    Thanks again

    Jackcool

  4. #4
    Join Date
    Feb 2002
    Posts
    33
    Hi. I'm sorry, I don't recall offhand what the output looks like, could you just paste what you are getting? If my memory serves me right, you should see a token which specifically says 'Deadlock'... let me check into this.

  5. #5
    Join Date
    Feb 2002
    Posts
    8
    HI,
    Thanks Raj.I appreciate your help.
    this is what iam getting in the event monitor file.I cant paste whole file as it is too large.

    239) Connection Header Event ...
    Appl Handle: 11
    Appl Id: *LOCAL.db2inst1.010212180102
    Appl Seq number: 0001
    DRDA AS Correlation Token: *LOCAL.db2inst1.010212180101
    Program Name : db2bp
    Authorization Id: KWADMIN
    Execution Id : root
    Codepage Id: 819
    Country code: 1
    Client Process Id: 2926
    Client Database Alias: BID3
    Client Product Id: SQL07020
    Client Platform: Unknown
    Client Communication Protocol: Local
    Client Network Name:
    Connect timestamp: 02-12-2001 10:01:01.991168

    240) Connection Header Event ...
    Appl Handle: 8
    Appl Id: *LOCAL.db2inst1.010212180202
    Appl Seq number: 0001
    DRDA AS Correlation Token: *LOCAL.db2inst1.010212180202
    Program Name : db2bp
    Authorization Id: KWADMIN
    Execution Id : root
    Codepage Id: 819
    Country code: 1
    Client Process Id: 2985
    Client Database Alias: BID3
    Client Product Id: SQL07020
    Client Platform: Unknown
    Client Communication Protocol: Local
    Client Network Name:
    Connect timestamp: 02-12-2001 10:02:02.084687

    241) Connection Header Event ...
    Appl Handle: 8
    Appl Id: *LOCAL.db2inst1.010212180203
    Appl Seq number: 0001
    DRDA AS Correlation Token: *LOCAL.db2inst1.010212180202
    Program Name : db2bp
    Authorization Id: KWADMIN
    Execution Id : root
    Codepage Id: 819
    Country code: 1
    Client Process Id: 2985
    Client Database Alias: BID3
    Client Product Id: SQL07020
    Client Platform: Unknown
    Client Communication Protocol: Local
    Client Network Name:
    Connect timestamp: 02-12-2001 10:02:02.148102

    242) Connection Header Event ...
    Appl Handle: 9
    Appl Id: *LOCAL.db2inst1.010212180302
    Appl Seq number: 0001
    DRDA AS Correlation Token: *LOCAL.db2inst1.010212180302
    Program Name : db2bp
    Authorization Id: KWADMIN
    Execution Id : root
    Codepage Id: 819
    Country code: 1
    Client Process Id: 3044
    Client Database Alias: BID3
    Client Product Id: SQL07020
    Client Platform: Unknown
    Client Communication Protocol: Local
    Client Network Name:
    Connect timestamp: 02-12-2001 10:03:02.175585



    I came to know that i need to look for any entry like this
    Deadlocked Connection ...
    Appl Id: *LOCAL.bourbon.970603173409
    Appl Seq number: 0001
    Appl Id of connection holding the lock: *LOCAL.bourbon.970603173330
    Seq. no. of connection holding the lock:
    Lock wait start time: 06-03-1997 13:36:43.251687
    Deadlock detection time: 06-03-1997 13:36:48.817786
    Table of lock waited on : STAFF
    Schema of lock waited on : BOURBON
    Tablespace of lock waited on : USERSPACE1
    Type of lock: Row
    Mode of lock: X
    Lock object name: 39

    there is no such entry in the file.This means no deadlock occured after the monitor is activated.

    My question is i created the event monitor on deadlocks ,even though there no deadlocks occured after the event monitor is activated still it is
    writing all connection headers.Can i have event monitor that writes only those connections that were held in deadlock.

    Also one more question i have is for how much time i need to run the event monitor.I ran it for an hour i could not find any dead lock connections.since it IO overhead i turned off the event monitor.what is maximum time an event monitor can be run.


    Thanks
    jackcool

  6. #6
    Join Date
    Feb 2002
    Posts
    33
    Hi. sorry for taking so long to get back to you.

    You can create an event monitor specifically to watch for deadlocks:

    for example:

    CREATE EVENT MONITOR DEADLOCK_EVTS
    FOR DEADLOCKS
    WRITE TO FILE 'DLOCKS'
    MAXFILES 1
    MAXFILESIZE NONE
    AUTOSTART

    You can check the System Monitor Guide and Reference for more details (check under 'CREATE EVENT MONITOR')

    As for how long to run it... really upto you, but a good guideline is during times where you expect the database to be under normal usage. Better to watch what is going to happen 'normally'.

    Raj

Posting Permissions

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