while running "ALTER TABLE t1 add column c7 int;" on Node1 (test db) and "insert into t2 values (1,2)" on node2 (test2 db),
I found that ALTER on test blocked DML on test2.
mysql> show processlist;
+----+-------------+-----------+-------+---------+-------+---------------------------+----------------------------------+-----------+---------------+-----------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------+-------+---------+-------+---------------------------+----------------------------------+-----------+---------------+-----------+
| 1 | system user | | test | Sleep | -4792 | copy to tmp table | ALTER TABLE t1 add column c7 int | 0 | 0 | 0 |
| 2 | system user | | NULL | Sleep | 33751 | wsrep aborter idle | NULL | 0 | 0 | 0 |
| 22 | root | localhost | test2 | Query | 14 | wsrep in pre-commit stage | insert into t2 values (1,2) | 0 | 0 | 0 |
| 23 | root | localhost | NULL | Query | 0 | sleeping | show processlist | 0 | 0 | 0 |
+----+-------------+-----------+-------+---------+-------+---------------------------+----------------------------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)
Able to reproduce the same with PXC 5.5.39
On Test DB:
mysql> CREATE TABLE `t1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `hostname` varchar(64) NOT NULL,
-> `port` int(11) NOT NULL,
-> `instime` datetime NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)
On Test2 DB:
mysql> use test2
Database changed
mysql> CREATE TABLE `t2` (
-> `c1` int(11) NOT NULL,
-> `c2` int(11) NOT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)
while running "ALTER TABLE t1 add column c7 int;" on Node1 (test db) and "insert into t2 values (1,2)" on node2 (test2 db),
I found that ALTER on test blocked DML on test2.
mysql> show processlist; ------- -----+- ------- ---+--- ----+-- ------- +------ -+----- ------- ------- ------- -+----- ------- ------- ------- ------- -+----- ------+ ------- ------- -+----- ------+ ------- -----+- ------- ---+--- ----+-- ------- +------ -+----- ------- ------- ------- -+----- ------- ------- ------- ------- -+----- ------+ ------- ------- -+----- ------+ ------- -----+- ------- ---+--- ----+-- ------- +------ -+----- ------- ------- ------- -+----- ------- ------- ------- ------- -+----- ------+ ------- ------- -+----- ------+
+----+-
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+----+-
| 1 | system user | | test | Sleep | -4792 | copy to tmp table | ALTER TABLE t1 add column c7 int | 0 | 0 | 0 |
| 2 | system user | | NULL | Sleep | 33751 | wsrep aborter idle | NULL | 0 | 0 | 0 |
| 22 | root | localhost | test2 | Query | 14 | wsrep in pre-commit stage | insert into t2 values (1,2) | 0 | 0 | 0 |
| 23 | root | localhost | NULL | Query | 0 | sleeping | show processlist | 0 | 0 | 0 |
+----+-
4 rows in set (0.00 sec)