Thread pool doesn't recognize threads waiting on row or metadata locks as idle

[Elena Stepanova]

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

# 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;

# 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
# long sleeps are ugly, but they're here to rule out side-effects
# of some other timing problems that I observe.
# 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

# 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.

[Elena Stepanova (elenst)]

# Test case 2 (metadata locks)

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 ( i INT );
LOCK TABLE t1 WRITE;

--let $count=8

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

--let $id=$count
while ($id)
{
  --connection con$id
  --send SELECT 1 FROM t1
  --dec $id
}

--connection default
--sleep 4
SHOW GLOBAL STATUS LIKE 'threadpool_%threads';
--sleep 4
SHOW GLOBAL STATUS LIKE 'threadpool_%threads';
SHOW PROCESSLIST;

DROP TABLE t1;

--exit

# End of test case 2

Same output as in the 1st test case -- both times

Threadpool_idle_threads 3
Threadpool_threads 12

Process list shows that all threads are "Waiting for table metadata lock".

[Elena Stepanova (elenst)]

Fix released with 5.5.21.