Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    11

    Unanswered: Deadlock in newly created table

    Hi,
    I am facing problem of deadlock on newly created table.
    what I am doing is.
    STOP WAS PROCESS so that no user can connect to db
    KILL ALL DB CNNECTIONS
    rename 3 tables for eg tables are test1,test2,test3,
    rename them to test1_ar,test2_ar,test3_ar.
    create new tables TEST1,TEST2,TEST3
    START WAS PROCESS now user can connect to db.

    now when the users are inserting and updating the table test1,test2,test3 i face dead lock for a while then everything is normal after that i didn't face the problem till again i do the same process again..
    their is no connection between the test1, test1_ar table.
    as test1_ar become old table and test1 is the table where the user is inserting and updating data.

    249) Deadlock Event ...
    Deadlock ID: 1
    Number of applications deadlocked: 2
    Deadlock detection time: 04/11/2008 10:57:11.640164
    Rolled back Appl participant no: 2
    Rolled back Appl Id: 172.16.50.17.39794.080410154001
    Rolled back Appl seq number: : 1220

    250) Connection Header Event ...
    Appl Handle: 720
    Appl Id: 172.16.50.17.39794.080410154001
    Appl Seq number: 04640
    DRDA AS Correlation Token: 172.16.50.17.39794.080410154001
    Program Name : db2jcc_application
    Authorization Id: EXCHGUSR
    Execution Id : EXCHGUSR
    Codepage Id: 1208
    Territory code: 0
    Client Process Id: 1019228192
    Client Database Alias: EXCHGCS
    Client Product Id: JCC03020
    Client Platform: Unknown via DRDA
    Client Communication Protocol: Unknown
    Client Network Name: ecdrpt1
    Connect timestamp: 04/10/2008 23:40:00.684836

    251) Deadlocked Connection ...
    Deadlock ID: 1
    Participant no.: 2
    Participant no. holding the lock: 1
    Appl Id: 172.16.50.17.39794.080410154001
    Appl Seq number: 04640
    Appl Id of connection holding the lock: 172.16.50.17.43190.080411025509
    Seq. no. of connection holding the lock: 00001
    Lock wait start time: 04/11/2008 10:57:04.292216
    Lock Name : 0x0003009C000000108A08001A52
    Lock Attributes : 0x00000000
    Release Flags : 0x40000000
    Lock Count : 1
    Hold Count : 0
    Current Mode : none
    Deadlock detection time: 04/11/2008 10:57:11.722714
    Table of lock waited on : EC_KEYDATA
    Schema of lock waited on : EXCHGCS
    Data partition id for table : 0
    Tablespace of lock waited on : ECDESIGNERSPACE
    Type of lock: Row
    Mode of lock: X - Exclusive
    Mode application requested on lock: U - Update
    Node lock occured on: 0
    Lock object name: 71035256858
    Application Handle: 720
    Deadlocked Statement:
    Type : Dynamic
    Operation: Execute
    Section : 7
    Creator : NULLID
    Package : SYSSN200
    Cursor : SQL_CURSN200C7
    Cursor was blocking: FALSE
    Text : UPDATE ec_keydata SET value = ? WHERE message_id = ? AND process = ? AND attr = ? AND saveid = ?
    List of Locks:
    Lock Name : 0x0003009C000000108A08001B52
    Lock Attributes : 0x00000008
    Release Flags : 0x40000000
    Lock Count : 1
    Hold Count : 0
    Lock Object Name : 71035256859
    Object Type : Row
    Tablespace Name : ECDESIGNERSPACE
    Table Schema : EXCHGCS
    Table Name : EC_KEYDATA
    Data partition id : 0
    Mode : X - Exclusive

    Lock Name : 0x00000041000000010001B40056
    Lock Attributes : 0x00000000
    Release Flags : 0x40000000
    Lock Count : 1
    Hold Count : 0
    Lock Object Name : 0
    Object Type : Internal - Variation
    Data partition id : -1
    Mode : S - Share

    Lock Name : 0x00000039000000010001A50056
    Lock Attributes : 0x00000000
    Release Flags : 0x40000000
    Lock Count : 1
    Hold Count : 0
    Lock Object Name : 0
    Object Type : Internal - Variation
    Data partition id : -1
    Mode : S - Share

    Lock Name : 0x0003009A000000108EAC000752
    Lock Attributes : 0x00000000
    Release Flags : 0x40000000
    Lock Count : 1
    Hold Count : 0
    Lock Object Name : 71113113607
    Object Type : Row
    Tablespace Name : ECDESIGNERSPACE
    Table Schema : EXCHGCS
    Table Name : EC_JOURNAL
    Data partition id : 0
    Mode : X - Exclusive

    Lock Name : 0x00000031000000010001820056
    Lock Attributes : 0x00000000
    Release Flags : 0x40000000
    Lock Count : 1
    Hold Count : 0
    Lock Object Name : 0
    Object Type : Internal - Variation
    Data partition id : -1
    Mode : S - Share

    Lock Name : 0x535953534E323030C7D2493C41
    Lock Attributes : 0x00000000
    Release Flags : 0x40000000
    Lock Count : 1
    Hold Count : 0
    Lock Object Name : 0
    Object Type : Internal - Plan
    Data partition id : -1
    Mode : S - Share

    Lock Name : 0x0003009A000000000000000054
    Lock Attributes : 0x00000000
    Release Flags : 0x40000000
    Lock Count : 1
    Hold Count : 0
    Lock Object Name : 154
    Object Type : Table
    Tablespace Name : ECDESIGNERSPACE
    Table Schema : EXCHGCS
    Table Name : EC_JOURNAL
    Data partition id : 0
    Mode : IX - Intent Exclusive

    Lock Name : 0x0003009C000000000000000054
    Lock Attributes : 0x00000000
    Release Flags : 0x40000000
    Lock Count : 2
    Hold Count : 0
    Lock Object Name : 156
    Object Type : Table
    Tablespace Name : ECDESIGNERSPACE
    Table Schema : EXCHGCS
    Table Name : EC_KEYDATA
    Data partition id : 0
    Mode : IX - Intent Exclusive

    Locks Held: 8
    Locks in List: 8

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Do the following:

    1. Alter the tables involved to indicate that cardinality is volatile.

    db2 alter table <table-name> volatile;

    2. Run the following commands:

    • db2set DB2_SKIPINSERTED=ON
    • db2set DB2_SKIPDELETED=ON
    • db2set DB2_EVALUNCOMMITTED=ON
    • db2stop
    • db2start
    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
    Apr 2008
    Posts
    11

    Smile

    Thanks Marcus..
    Will implement in next downtime and hope it help me..

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by jagdishrawata
    Thanks Marcus..
    Will implement in next downtime and hope it help me..
    Guaranteed, or your money will be refunded.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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