Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4010

Deadlock on concurrent INSERT .. SELECT into an Aria table with statement binary logging

    XMLWordPrintable

Details

    Description

      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>

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              elenst Elena Stepanova
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.