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

subsequent OPTIMIZEs on the same table may cause complete table deadlock

    XMLWordPrintable

Details

    Description

      I get complete table deadlock with simple test case like below:

      1. setup default two-node galera cluster on the same host, e.g. on ports 3314 and 3315
      2. Create table:
      create table test.t select 1;
      3. Start following concurrent load:

      ( for i1 in {1..100000} ; do mysql -h127.0.0.1 -P3314 -e "insert into t select $i1" test || : ; done ) &> /dev/null &
      ( for i1 in {1..100000} ; do mysql -h127.0.0.1 -P3315 -e "insert into t select $i1" test || : ; done ) &> /dev/null &
       
      ( for k1 in {1..1000}; do sleep 10; mysql -h127.0.0.1 -P3314 -e "optimize table t" test & wait ; done ) &>/dev/null &
      ( for k1 in {1..1000}; do sleep 10; mysql -h127.0.0.1 -P3315 -e "optimize table t" test & wait ; done ) &>/dev/null &
       
       
      while true ; do
      mysql -h127.0.0.1 -P3314 -e 'show processlist'
      mysql -h127.0.0.1 -P3315 -e 'show processlist'
      mysql -h127.0.0.1 -P3314 -e 'select count(*) from t' test
      mysql -h127.0.0.1 -P3315 -e 'select count(*) from t' test
      sleep 2
      done
      

      The cluster usually hangs up within 1 min, this is what observed:

      (11:17 sql) > mysql -h 127.0.0.1 -P 3314 -uroot  -e 'show processlist'
      +-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+
      | Id  | User        | Host            | db   | Command | Time | State                   | Info                     | Progress |
      +-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+
      |   1 | system user |                 | test | Sleep   |  748 | Waiting for table flush | optimize table t         |    0.000 |
      |   2 | system user |                 | NULL | Sleep   |  815 | wsrep aborter idle      | NULL                     |    0.000 |
      | 518 | a           | localhost:37078 | test | Query   |  748 | query end               | insert into t select 487 |    0.000 |
      | 519 | a           | localhost:37080 | test | Query   |  748 | Waiting for table flush | select count(*) from t   |    0.000 |
      | 520 | a           | localhost:37122 | test | Query   |  738 | checking permissions    | optimize table t         |    0.000 |
      | 526 | root        | localhost:38382 | NULL | Query   |    0 | init                    | show processlist         |    0.000 |
      +-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+
      (11:17 sql) > mysql -h 127.0.0.1 -P 3315 -uroot -e 'show processlist'
      +-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+
      | Id  | User        | Host            | db   | Command | Time | State                | Info                     | Progress |
      +-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+
      |   1 | system user |                 | NULL | Sleep   |  799 | wsrep aborter idle   | NULL                     |    0.000 |
      |   2 | system user |                 | NULL | Sleep   |  748 | committed 992        | NULL                     |    0.000 |
      | 517 | a           | localhost:46558 | test | Query   |  748 | query end            | insert into t select 505 |    0.000 |
      | 518 | a           | localhost:46562 | test | Query   |  738 | checking permissions | optimize table t         |    0.000 |
      | 524 | root        | localhost:47822 | NULL | Query   |    0 | init                 | show processlist         |    0.000 |
      +-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+
      

      It is kid of strange to me that I cannot reproduce the problem with alter command below instead of OPTIMIZE:
      alter table t engine=innodb, algorithm=copy

      Attachments

        1. gdb.txt
          133 kB
          Andrii Nikitin

        Issue Links

          Activity

            People

              ramesh Ramesh Sivaraman
              anikitin Andrii Nikitin (Inactive)
              Votes:
              0 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.