Thread pool and lock tables cause deadlock

[Nickolay Ihalainen]

MySQL settings:
[mysqld]
thread_handling = pool-of-threads
extra_port = 3307

Create test table:
create table test;
create database mysqlslap;
create table test.a (a int) engine=innodb;
insert into test.a values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Two mysqlslap processes running in parallel:
mysqlslap --concurrency=100 --iterations=1000000 --query='select * from test.a where a=1;select * from test.a where a=2;select * from test.a where a=3;select * from test.a where a=4;select * from test.a where a=5;select * from test.a where a=6;select * from test.a where a=7;select * from test.a where a=8;select * from test.a where a=9;select * from test.a where a=10;'

cat cre_simple.sql
delimiter ;;
lock tables test.a write;;
drop trigger if exists a_insert;;
create definer='root'@'localhost' trigger a_insert after insert on test.a for each row begin
UPDATE test.a SET a.a = NEW.a + 1 WHERE a.a = NEW.a;
end;;
unlock tables;;
delimiter ;

Execute create trigger several times until stale:
mysql test < cre_simple.sql

Lock tables is seems to be important (I wasn't able to reproduce the issue without lock tables for write.

extra_port and performance schema allows to see that select queries are awaiting for metadata lock, but the thread re-creating trigger is inactive.

I was able to reproduce this instance with latest PS 5.6 & 5.7

[Laurynas Biveinis (laurynas-biveinis)]

Create trigger is not required for reproduction, lock/unlock tables are enough.

[Laurynas Biveinis (laurynas-biveinis)]

This is caused by LOCK TABLES thread going to low-priority thread queue with the lock taken, then SELECT connections piling up until thread pool oversubscribe limit is reached. At this point, the LOCK TABLES thread will never be dequeued from the low priority queue, resulting in a deadlock.

As a workaround, LOCK TABLES should be using high priority queue: set session thread_pool_high_prio_mode = statements

For a fix, bug 1374930 suggestion should be implemented: connections owning any kind of locks should be treated as connections with open transactions for the purpose of thread pool scheduling.

[Laurynas Biveinis (laurynas-biveinis)]

    https://github.com/percona/percona-server/pull/992
    https://github.com/percona/percona-server/pull/993

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1003