Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Long_Waiting_Application ?

    Hi Guys,


    For few weeks I am having an application which is in UOWWAIT state and holding locks for about 5 to 12 hours.

    By luck no other application was held back ( also because the table lock is Intent share ). This application is currently holding total 8 locks including 1 table lock. This is reoprted as in UOWWait State. I cannot catch it via LONG_RUNNING_SQL because ELAPSED_TIME_MIN is reported to be 0.

    I need help to learn how to resolve/investigate this situation.
    This keeps me from REORG indexes of this table. Otherwise none of regular applications is affected.

    Attached is the application snapshot and DB2Monitor screenshot.

    DB2V9.5 fp 4 on Win2K3

    Thanks in advance.

    DBFinder
    Attached Thumbnails Attached Thumbnails APPL.bmp  
    Attached Files Attached Files
    Last edited by DBFinder; 11-16-09 at 08:45.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    From the information you've provided, it looks like the application did not commit its last UOW and is waiting for user input or some other event. Knowing the query it ran and the IP address of the client you can analyse the application and make sure it issues commits when appropriate.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thank you very much !

    Nick, I wanted to cofirm with you guys. Commit was not issued. This needs to be conveyed to another department in my workplace, before they provide me the code.

    I am attaching second snapshot if someone may want to compare with (for any time relative parameters).

    Thanks again.

    DBFinder
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by n_i View Post
    From the information you've provided, it looks like the application did not commit its last UOW and is waiting for user input or some other event. Knowing the query it ran and the IP address of the client you can analyse the application and make sure it issues commits when appropriate.
    Nick,

    Next question is that from SQL I see that it is not updating anything. Do we still need a commit ??

    Regards
    DBFinder

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you want the locks to be released, you need to have it commit or rollback. if you force the application then it will rollback.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Well,

    Is this true in SELECT only transactions also ??

    Sorry for my limited experience !

    Regards

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Select transactions acquire locks as well - snapshot shows 9 locks held by this appl. Some locks will be on the target tables, some on the system catalog table(s) and some can be internal locks. The application has to commit/rollback in order to release them.

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thank you Bella,

    I got it. I will proceed with the knowledge provided. This changed my thinking that Select also needs COMMIT.


    Thanks Everyone.

    DBFinder

  9. #9
    Join Date
    Oct 2007
    Posts
    246
    Kindly correct me on this

    IF ur isolation level CR, whenever there is select it does a share lock on each row
    when its huge amt of data is selected it gets escalates to table lock.
    regds
    Paul

  10. #10
    Join Date
    Oct 2007
    Posts
    246
    Quote Originally Posted by db2girl View Post
    Select transactions acquire locks as well - snapshot shows 9 locks held by this appl. Some locks will be on the target tables, some on the system catalog table(s) and some can be internal locks. The application has to commit/rollback in order to release them.
    Kindly correct me on this

    IF ur isolation level CR, whenever there is select it does a share lock on each row
    when its huge amt of data is selected it gets escalates to table lock.
    regds
    Paul

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    The higher the isolation level, the more locks DB2 will acquire. With RR (I think you meant RR when you wrote CR), locks are acquired on the scan and kept until the end of the scan and therefore lock escalations are more likely. The optimizer can decide to just lock the table in case of RR (it will consider locklist/maxlock, estimate number of locks...).

  12. #12
    Join Date
    Oct 2007
    Posts
    246
    i m so sorry its not CR its CS , share lock happens in row when there is select on the row
    when this increase and used memory of locklist table lock happens in select, as select also lock, kindly correct me on this
    regds
    Paul

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    With CS, the lock is released when the scan moves off a row so lock escalations are less likely than with RR/RS. If lock escalations occur, locklist/maxlocks is low.

  14. #14
    Join Date
    Oct 2007
    Posts
    246
    want to know abt select lock which lock the entire table, as share lock happens in row in select when huge rows are select the share lock is converted to lock escalation.
    kindly correct me on this
    regds
    Paul

  15. #15
    Join Date
    Nov 2009
    Posts
    1

    Where to watch Gossip Girl Online.

    Do you like watching Leighton Meester and Gossip Girl every week?

    Do you want to get caught up watching this TV series? Gossip Girl is a very popular TV show.

    Find out what Serena van der Woodsen is up to this week!

    Gossip Girl Episodes[url=http://www.Gossip-Girl-Now.info]

Posting Permissions

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