[#MDEV-4010] Deadlock on concurrent INSERT .. SELECT into an Aria table with statement binary logging

In the provided test scenario, 2 threads run INSERT .. SELECT on the same Aria table. Very soon after the test start processlist starts showing something like

+----+------+-----------------+------+---------+------+------------------------------+---------------------------------------+----------+

| Id | User | Host            | db   | Command | Time | State                        | Info                                  | Progress |

+----+------+-----------------+------+---------+------+------------------------------+---------------------------------------+----------+

|  4 | root | localhost:54206 | test | Query   |  112 | Waiting for table level lock | INSERT INTO A SELECT * FROM A LIMIT 0 |    0.000 |

|  5 | root | localhost:54207 | test | Query   |  112 | Waiting for table level lock | INSERT INTO A SELECT * FROM A LIMIT 0 |    0.000 |

|  8 | root | localhost:54210 | NULL | Query   |    0 | NULL                         | show processlist                      |    0.000 |

+----+------+-----------------+------+---------+------+------------------------------+---------------------------------------+----------+

Killing one of the threads (or a query in a thread) does not unlock another one:

elenst@ubuntu11:~/5.5.28-release$ mysql -uroot --protocol=tcp --port=3306 -e "kill 4"

elenst@ubuntu11:~/5.5.28-release$ mysql -uroot --protocol=tcp --port=3306 -e "show processlist"

+----+------+-----------------+------+---------+------+------------------------------+---------------------------------------+----------+

| Id | User | Host            | db   | Command | Time | State                        | Info                                  | Progress |

+----+------+-----------------+------+---------+------+------------------------------+---------------------------------------+----------+

|  5 | root | localhost:54207 | test | Query   |  129 | Waiting for table level lock | INSERT INTO A SELECT * FROM A LIMIT 0 |    0.000 |

| 10 | root | localhost:54212 | NULL | Query   |    0 | NULL                         | show processlist                      |    0.000 |

+----+------+-----------------+------+---------+------+------------------------------+---------------------------------------+----------+

Reproducible on debug and release builds.
Reproducible on MariaDB 5.1, 5.2, 5.3, 5.5, 10.0.
Reproducible on Windows, Linux 64-bit and Linux 32-bit.
Could not reproduce with MyISAM or InnoDB, hence could not check whether the problem exists in MySQL.
Could not reproduce without binary logging or with row-based binlog format.

------------
Later addition:

While looking into ~~MDEV-7314~~, I got an MTR test case:

--source include/have_binlog_format_statement.inc

create table t1 (pk int primary key) engine=Aria;

insert into t1 values (1);

send insert into t1 select sleep(2)+1 from t1;

--connect (con1,localhost,root,,)

insert into t1 select 2 from t1;

--connection default

--reap

------------

Initial test (RQG)

If on some reason you are not getting the deadlock right away, try to increase threads and/or duration in the command-line options below.

RQG grammar (put it in the root of randgen folder as test.yy):

query_init:

	SET binlog_format = STATEMENT ; DROP TABLE IF EXISTS A ; CREATE TABLE IF NOT EXISTS A (`i` INT) ENGINE=Aria ; INSERT INTO A VALUES (1),(2),(3);

query:

	INSERT INTO A SELECT * FROM A LIMIT 0;

The test flow above contains LIMIT 0 only to prevent the table from growing uncontrollably, if the deadlock doesn't happen right away. The clause is unimportant, the problem happens without it as well.

RQG command line to run on a previous started server, assuming it's running on port 3306 (if the port is different, modify dsn); don't forget to start server with log-bin, no other options are necessary:

perl gentest.pl --threads=2 --queries=100M --duration=60 --dsn=dbi:mysql:host=127.0.0.1:port=3306:user=root:database=test --grammar=test.yy

RQG command line to run full test (which starts servers, etc.); it will start server on port 19300:

perl ./runall.pl \

--threads=2 \

--duration=60 \

--queries=100M \

--grammar=test.yy \

--skip-gendata \

--rpl_mode=statement \

--basedir=<your basedir> \

--vardir=<your vardir>

[MDEV-4010] Deadlock on concurrent INSERT .. SELECT into an Aria table with statement binary logging Created: 2013-01-07 Updated: 2014-12-15 Resolved: 2014-12-15
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - Aria
Affects Version/s:	10.0.1, 5.5.28a, 5.3.11, 5.2.13, 5.1.66
Fix Version/s:	5.5.41, 10.0.16

[MDEV-4010] Deadlock on concurrent INSERT .. SELECT into an Aria table with statement binary logging Created: 2013-01-07 Updated: 2014-12-15 Resolved: 2014-12-15