If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
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.
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, 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).
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 ??
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.
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
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
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...).
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
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.
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
dBforums
>
Long_Waiting_Application ?
11-16-09, 07:41
Linear Mode
