Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-3418

Prevent DDL-DML deadlock by making in-place ALTER take shared MDL for the whole duration.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.30-31.43, 5.7.31-31.45
    • Fix Version/s: 5.7.32-31.47, 8.0.21-12.1
    • Component/s: None
    • Labels:
      None

      Description

      It is possible to hit a writer node deadlock when ALTER TABLE is run few times on another node, concurrent with a read/write load on the writer node.

      Reproduction steps:

      $ dbdeployer --sandbox-binary /opt/percona_xtradb_cluster deploy --topology=pxc replication 5.7.30
      

      Change wsrep_slave_threads to 8 or 16 (not sure how critical it is to do this, default with dbdeployer is 2).

      $ sysbench /usr/share/sysbench/oltp_read_write.lua \
      --db-driver=mysql --auto_inc=off \
      --tables=1 --table_size=1000000 \
      --mysql-host=127.0.0.1 --mysql-port=26630 \
      --mysql-user=root --mysql-password='msandbox' \
      --mysql-db=test prepare
      $ sysbench /usr/share/sysbench/oltp_read_write.lua \
      --db-driver=mysql --db-ps-mode=disable --skip_trx=on \
      --mysql-user=root --mysql-password='msandbox' \
      --mysql-db=test --mysql-host=127.0.0.1 --mysql-port=26630 \
      --mysql-ignore-errors=all --table_size=1000000 \
      --tables=1 --threads=40 --report-interval=1 \
      --time=0 --events=0 --rate=100 run | grep tps
      

      On any other node run (default, TOI mode):

      ALTER TABLE sbtest1 ENGINE=InnoDB;
      ALTER TABLE sbtest1 ENGINE=InnoDB;
      ... copy as many times as you want, 10 should do ...
      

      After a while, writer node will get locked up with a similar processlist:

      +-----+-------------+-----------------+------+---------+-------+------------------------------------------------------+-----------------------------------------
      | Id  | User        | Host            | db   | Command | Time  | State                                                | Info
      +-----+-------------+-----------------+------+---------+-------+------------------------------------------------------+-----------------------------------------
      |   1 | system user |                 | NULL | Sleep   | 36435 | wsrep: aborter idle                                  | NULL
      |   2 | system user |                 | NULL | Sleep   | 32702 | wsrep: committed write set (1266293)                 | NULL
      |   4 | system user |                 | NULL | Sleep   | 32693 | wsrep: committed write set (1269834)                 | NULL
      |  48 | root        | localhost:42650 | test | Query   | 32654 | wsrep: initiating pre-commit for write set (1336952) | UPDATE sbtest1 SET k=k+1 WHERE id=500510
      ...
      |  86 | root        | localhost:42742 | test | Query   | 32654 | wsrep: initiating pre-commit for write set (1336940) | UPDATE sbtest1 SET k=k+1 WHERE id=404705
      |  87 | root        | localhost:42744 | test | Query   | 32654 | wsrep: initiating pre-commit for write set (1336942) | UPDATE sbtest1 SET k=k+1 WHERE id=531825
      |  88 | system user |                 | NULL | Sleep   | 32719 | wsrep: applier idle                                  | NULL
      ...
      |  98 | system user |                 | NULL | Sleep   | 32701 | wsrep: committed write set (1266747)                 | NULL
      |  99 | system user |                 |      | Sleep   | 32689 | Waiting for table metadata lock                      | alter table test.sbtest1 engine=innodb
      | 100 | system user |                 | NULL | Sleep   | 32696 | wsrep: committed write set (1268536)                 | NULL
      | 101 | system user |                 | NULL | Sleep   | 32692 | wsrep: committed write set (1270277)                 | NULL
      | 105 | root        | localhost:56760 | test | Sleep   |  1266 |                                                      | NULL
      | 110 | root        | localhost:59474 | test | Sleep   |  1124 |                                                      | NULL
      | 111 | root        | localhost:59476 | test | Sleep   |  1124 |                                                      | NULL
      | 112 | root        | localhost:52090 | NULL | Query   |     0 | starting                                             | show processlist
      +-----+-------------+-----------------+------+---------+-------+------------------------------------------------------+-----------------------------------------
      

      Issue looks similar to PXC-2382.

      Attached are:

      • thread apply (all) bt outputs
      • processlist

      Over at highram there are few sets of core dumps and all the other details I gathered.

        Attachments

        1. 10597.processlist.out
          8 kB
        2. 10597.bt-full.out
          477 kB
        3. 10597.bt.out
          190 kB
        4. PXC-3418_galera_changes.diff
          1 kB
        5. PXC-3418_MTR_testcase_server.diff
          2 kB

          Issue Links

            Activity

              People

              Assignee:
              venkatesh.prasad Venkatesh Prasad
              Reporter:
              sergey.kuzmichev Sergey Kuzmichev
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 3 weeks, 7 hours, 47 minutes
                  3w 7h 47m

                    Smart Checklist