Is it possible in DB2 or in any Database to detect if the table is locked or not?

Question

Is it possible in DB2 to detect if the table is locked or not. Actually whenever we use Select statement and if that table is locked [ may be because of on going execution of insertion or deletion ] , then we have to wait till the table is unlocked.

In our application sometimes it goes to even 2-3 mins. What i think is, if i can have some mechanism by which i can detect the locked table, then i will not even try to fetch the records, instead i will splash some message.

Not only in DB2, but is it possible to detect this in any Database.

Answer 1

I've never used DB2, but according to the documentation it seems you can use the following to make queries not wait for a lock:

SET CURRENT LOCK TIMEOUT NOT WAIT

Alternatively, you can set the lock timeout value to 0

SET CURRENT LOCK TIMEOUT 0

Both the statements have the same effect.

Once you have this, you can try to select from the table and catch the error.

Answer 2

I would recommend against NO WAIT, and rather, specify a low LOCK TIMEOUT (10-30s). If the target table is only locked temporarily (small update, say for 1 second), your second app will timeout immediately. If you have a 10s timeout, the second app would simply wait for the first app to COMMIT or ROLLBACK (1 sec) then move forward.

Also consider there's a bit of a "first come, first served" policy when it comes to handing out locks - if the second app "gives up", a third app could get in and grab the locks needed by the second. It's possible that the second app experiences lock starvation because it keeps giving up.

If you are experiencing ongoing concurrency issues, consider lock monitoring to get a handle on how the database is being accessed. There's lots of useful statistics (such as average lock-wait time, etc.) that can help you tune your parameters and application behaviour.

DB2 V9.7 Infocenter - Database Monitoring