Thread pool and lock tables cause deadlock

Bug #1619559 reported by Nickolay Ihalainen on 2016-09-02
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server
Status tracked in 5.7
Laurynas Biveinis
Laurynas Biveinis
Laurynas Biveinis

Bug Description

MySQL settings:
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;
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

description: updated

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

summary: - Thread pool, lock tables and create trigger causing deadlock
+ Thread pool and lock tables cause deadlock

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.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers