LP:909537 - Thread pool doesn't recognize threads waiting on row or metadata locks as idle

Thread pool does not switch threads which are stuck waiting for a row lock or table metadata lock to 'idle' (or waiting). Consequently, it counts them as active while making a decision whether to create a new one.

bzr version-info
revision-id: <email address hidden>
date: 2011-12-28 03:51:12 +0100
build-date: 2011-12-29 02:46:37 +0200
revno: 3182
branch-nick: lp-5.5-threadpool

1. Test case 1 (for row locks):

--source include/have_innodb.inc

SET GLOBAL thread_pool_idle_timeout=1;
SET GLOBAL thread_pool_stall_limit=60;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

1. number of connections
   --let $count=8

--let $id=$count
while ($id)
{
--connect(con$id,localhost,root,,)
--dec $id
}

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 ( i INT PRIMARY KEY ) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2);

FLUSH STATUS;
BEGIN;
UPDATE t1 SET i=3 WHERE i=1;

--let $id=$count

while ($id)
{
--connection con$id
--send UPDATE t1 SET i=4 WHERE i=1
--dec $id
}

--connection default

1. long sleeps are ugly, but they're here to rule out side-effects
2. of some other timing problems that I observe.
3. The production test case should be smarter
   --sleep 4
   SHOW GLOBAL STATUS LIKE 'threadpool_%threads';
   --sleep 4
   SHOW GLOBAL STATUS LIKE 'threadpool_%threads';

SHOW PROCESSLIST;
--query_vertical SHOW ENGINE INNODB STATUS

--exit

1. End of test case 1

Here the first SHOW GLOBAL STATUS returns
Threadpool_idle_threads 3
Threadpool_threads 11

and the second one still
Threadpool_idle_threads 3
Threadpool_threads 11

InnoDB status shows that the transactions are indeed inside InnoDB.