Amit <[email protected]> writes: > Also, I did jstack on derby process and got a thread in BLOCKED state, > what is the reason for this? > > "DRDAConnThread_5" prio=6 tid=0x27500c00 nid=0x178c in Object.wait() > [0x2801f000] > java.lang.Thread.State: BLOCKED (on object monitor) > at java.lang.Object.wait(Native Method) > at java.lang.Object.wait(Object.java:503) > at org.apache.derby.impl.store.raw.data.BasePage.setExclusive(Unknown Source) > - locked <0x18f464e8> (a org.apache.derby.impl.store.raw.data.StoredPage) > at org.apache.derby.impl.store.raw.data.BaseContainer.latchPage (Unknown > Source)
This means it is trying to access a data page that some other thread is also accessing. It doesn't necessarily mean something is wrong, as threads could occasionally be in this state under normal operation. If the thread stays in this state for a long time, there's probably something wrong, for example that a thread hasn't released the data page when it was done with it. It may be useful to check the CPU usage on the server when the query doesn't return. If the CPU is mostly idle, the query execution is likely blocked somewhere. If the CPU usage is high, it is more likely that the optimizer has chosen a not so optimal execution plan for the query, or that the query is doing unnecessary work for some reason. (Although, if others access the database at the same time, it could be tricky to tell if high CPU usage is because your query has gone wild, or if it's because of the load others are putting on the database.) -- Knut Anders
