RUNSTATS ahoy!

I have to say, this has got to be the biggest nasty surprise of all! Again, this is the standard blocking issue we all frown upon.

Test case:

Disable auto-commit. Perform runstats on a table, say, T1:

RUNSTATS ON TABLE T1


Start up another CLP session, issue this:

SELECT * FROM T1

What do you get? Nothing! Well, it is more like 'waiting for something to happen', but it is as good as nothing.

This SELECT session is waiting on a lock type CatCacheLock, presumably the RUNSTATS command has an exclusive lock on this, and this SELECT session needs it in EXCLUSIVE mode too ....why exclusive mode?  Hmmm ...another Alfred Hitchcock moment ... :)

What is the damage then, you may ask. Imagine this scenario: you have a huge table with billions of rows, and you are running RUNSTATS on it, and at the same time, there are some users wanting to read this table ... you will get an ultimate test of patience ;)

Update: This behaviour seems to be timing-sensitive. So it seems that the other read session only requests for an exclusive 'loading lock' after runstats ends and the read begins.