Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2009
    Posts
    5

    Unanswered: DB2 Locking problem

    Hi,

    I am facing a locking problem with DB2.
    Following is the scenario :

    A select query is executed on XX table in one trasaction
    after that a batch(batch size is 10) insert on XX table is executed in different transaction.

    The batch insert hangs up, it is waiting for select statement.
    Same thing is working fine with Oracle database.

    We are using DB29.5 with JBOSS5.
    These flags are activated on DB side:

    DB2_SKIPINSERTED=ON
    DB2_SKIPDELETED=ON
    DB2_EVALUNCOMMITTED=ON

    Can you pls help us?

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What is your isolation level specified in java? I would add the following to the end of the select:

    WITH CS;

    If that doesn't work, add this to the end of the select:

    WITH UR:
    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
    Sep 2009
    Posts
    5
    Hi Fedman,

    Thanks for your reply.

    I tried with both the options, but it's not working.

    Is there any other configuration parameter that we are missing?

    Thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think you need to supply details that show why you think the select is blocking the insert.
    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
    Sep 2009
    Posts
    5
    I found it using a monitoring tool "db2mon"

    I have Attached the screenshot.

    Thanks
    Attached Thumbnails Attached Thumbnails db2lock.jpeg  

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    121 is in lockwait state, but how do you who is blocking it? UOW Waiting does not mean anything in this context.

    Change the lockwait parm in the db config from -1 to 30 seconds (or whatever is necessary to force a locktimeout error) and follow the directions below to determine exactly what happens when you get a locktimeout (-911 rc 68):
    New options for analyzing lock timeouts in DB2 9.5
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Sep 2009
    Posts
    5
    Thank you.

    Here is the locktimeout output :
    LOCK TIMEOUT REPORT

    Date: 14/09/2009
    Time: 19:07:18
    Instance: DB2
    Database: TEST
    Database Partition: 0


    Lock Information:

    Lock Name: 03003A00370000000000000052
    Lock Type: Row
    Lock Specifics: Tablespace ID=3, Table ID=58, Row ID=x3700000000000000


    Lock Requestor:
    System Auth ID: TEST
    Application Handle: [0-32]
    Application ID: 10.97.98.89.33805.090914130413
    Application Name: db2jcc_application
    Requesting Agent ID: 3760
    Coordinator Agent ID: 3760
    Coordinator Partition: 0
    Lock timeout Value: 30000 milliseconds
    Lock mode requested: .NS
    Application Status: (SQLM_UOWEXEC)
    Current Operation: (SQLM_EXECUTE)
    Lock Escalation: No

    Context of Lock Request:
    Identification: UOW ID (5); Activity ID (1)
    Activity Information:
    Package Schema: (NULLID )
    Package Name: (SYSSH200NULLID )
    Package Version: ()
    Section Entry Number: 1
    SQL Type: Dynamic
    Statement Type: DML, Insert/Update/Delete
    Effective Isolation: Cursor Stability
    Statement Unicode Flag: No
    Statement: INSERT INTO XX (YY_ID, SEQUENCE) VALUES ( ?, ?)


    Lock Owner (Representative):
    System Auth ID: TEST
    Application Handle: [0-29]
    Application ID: 10.97.98.89.33037.090914130410
    Application Name: db2jcc_application
    Requesting Agent ID: 1000
    Coordinator Agent ID: 1000
    Coordinator Partition: 0
    Lock mode held: ..X

    List of Active SQL Statements: Not available

    List of Inactive SQL Statements from current UOW:

    Entry: #1
    Identification: UOW ID (84); Activity ID (2)
    Package Schema: (NULLID )
    Package Name: (SYSSH200)
    Package Version: ()
    Section Entry Number: 1
    SQL Type: Dynamic
    Statement Type: DML, Insert/Update/Delete
    Effective Isolation: Cursor Stability
    Statement Unicode Flag: No
    Statement: UPDATE YY SET col1 = ? , col2 = ? , col3 = ? WHERE YYID = ?

    Entry: #2
    Identification: UOW ID (84); Activity ID (1)
    Package Schema: (NULLID )
    Package Name: (SYSSH200)
    Package Version: ()
    Section Entry Number: 1
    SQL Type: Dynamic
    Statement Type: DML, Select (blockable)
    Effective Isolation: Cursor Stability
    Statement Unicode Flag: No
    Statement: SELECT * from YY WHERE YYID = ?


    Here YY is the parent table and XX is the child table.
    While iserting into xx table, yy table id is used.

    Thanks

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Looks to me like maybe the UPDATE is blocking the insert, not SELECT. You could do a snaphot for locks (turn lock monitor on in the dbm cfg before hand) to see what locks are being held.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also doyou have an index on child table to support the RI check being performed when you update he YY table?
    Dave

  10. #10
    Join Date
    Sep 2009
    Posts
    1
    Have you identified the table referred to in the Lock Specifics of your Lock Time Out Report? Make sure you positively identify what tables are holding locks.
    We have had nothing but problems since implementing v9.5. I have an added layer of bugs called Federation Server. I found all my deadlocks and lock timeouts are on SYSIBM tables. I found some of my packages had changed from valid to invalid. This causes my applications to rebind on every execution. The locking happens during the binding process, nothing to do with our code or source tables. Rebinding my packages only makes them valid for a period of time. I had to figure all that out in order to defend the Federation Support suggestion that we may need to check our application code. Rebinding my packages only makes them valid for a period of time. I guess they don't actually go through at all those traces, logs and command output they always ask for. Federation Server support and db2 support keep passing the PMR off to one another.
    Good luck! I am about ready to return to the unsupported v8.2.

  11. #11
    Join Date
    Sep 2009
    Posts
    5
    Thank you very much for your all responses

    The problem was with update on yy table.

    Now we are excuting both the statements in same transaction and it resolve the problem.

    Can you please provide a link which will give the information about how we can resolve the locking problems in DB2?

    Thanks

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ravisoni1986
    Can you please provide a link which will give the information about how we can resolve the locking problems in DB2?
    Locks are released when a transaction is closed or a commit is issued. Many applications these days are poorly coded, and don't do these things properly. I don't know if that is the problem in your case, but it sound like it is.
    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
  •