Mysql DDL和DML之间影响

原创谢辉元 2021-09-06

1101

同一会话

若执行DML后未提交，再执行DDL，DML会自动被提交

创建测试表：
drop table if exists test;
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dt` date DEFAULT NULL,
  `dt1` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

begin;
insert into test(name,dt,dt1) select 'test',now(),now();
select * from test;
rollback;
select * from test;

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name,dt,dt1) select 'test',now(),now();
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from test;
+----+------+------------+---------------------+
| id | name | dt         | dt1                 |
+----+------+------------+---------------------+
|  1 | test | 2021-09-06 | 2021-09-06 21:53:06 |
+----+------+------------+---------------------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

可以看到没有DDL时，可rollback;

DML后执行DDL，添加字段，会提交事务

begin;
insert into test(name,dt,dt1) select 'test',now(),now();
select * from test;
alter table test add column dt datetime;
rollback;
select * from test;
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name,dt,dt1) select 'test',now(),now();
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from test;
+----+------+------------+---------------------+
| id | name | dt         | dt1                 |
+----+------+------------+---------------------+
|  2 | test | 2021-09-06 | 2021-09-06 21:55:36 |
+----+------+------------+---------------------+
1 row in set (0.01 sec)

mysql> alter table test add column dt datetime; --即使DDL失败了，前面的DML依旧提交了
rollback;
select * from test;
ERROR 1060 (42S21): Duplicate column name 'dt'
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+------+------------+---------------------+
| id | name | dt         | dt1                 |
+----+------+------------+---------------------+
|  2 | test | 2021-09-06 | 2021-09-06 21:55:36 |
+----+------+------------+---------------------+
1 row in set (0.00 sec)

DML后执行DDL，创建索引，会提交事务


begin;
insert into test(name,dt,dt1) select 'test',now(),now();
select * from test;
alter table test add index idx_dt(dt);
rollback;
select * from test;

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name,dt,dt1) select 'test',now(),now();
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from test;
+----+------+------------+---------------------+
| id | name | dt         | dt1                 |
+----+------+------------+---------------------+
|  2 | test | 2021-09-06 | 2021-09-06 21:55:36 |
|  3 | test | 2021-09-06 | 2021-09-06 21:57:00 |
|  4 | test | 2021-09-06 | 2021-09-06 21:59:04 |
+----+------+------------+---------------------+
3 rows in set (0.00 sec)

mysql> alter table test add index idx_dt(dt);
rollback;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+----+------+------------+---------------------+
| id | name | dt         | dt1                 |
+----+------+------------+---------------------+
|  2 | test | 2021-09-06 | 2021-09-06 21:55:36 |
|  3 | test | 2021-09-06 | 2021-09-06 21:57:00 |
|  4 | test | 2021-09-06 | 2021-09-06 21:59:04 |
+----+------+------------+---------------------+
3 rows in set (0.00 sec)

分析表，会提交DML事务

begin;
insert into test(name,dt,dt1) select 'test',now(),now();
select * from test;
analyze table test;
rollback;
select * from test;

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name,dt,dt1) select 'test',now(),now();
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from test;
+----+------+------------+---------------------+
| id | name | dt         | dt1                 |
+----+------+------------+---------------------+
|  2 | test | 2021-09-06 | 2021-09-06 21:55:36 |
|  3 | test | 2021-09-06 | 2021-09-06 21:57:00 |
|  4 | test | 2021-09-06 | 2021-09-06 21:59:04 |
|  5 | test | 2021-09-06 | 2021-09-06 22:00:42 |
+----+------+------------+---------------------+
4 rows in set (0.00 sec)

mysql> analyze table test;
rollback;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| xhy.test | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+------+------------+---------------------+
| id | name | dt         | dt1                 |
+----+------+------------+---------------------+
|  2 | test | 2021-09-06 | 2021-09-06 21:55:36 |
|  3 | test | 2021-09-06 | 2021-09-06 21:57:00 |
|  4 | test | 2021-09-06 | 2021-09-06 21:59:04 |
|  5 | test | 2021-09-06 | 2021-09-06 22:00:42 |
+----+------+------------+---------------------+
4 rows in set (0.00 sec)

不同会话

创建测试表：
drop table if exists test;
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dt` date DEFAULT NULL,
  `dt1` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

会话一：
begin;
insert into test(name,dt,dt1) select 'test',now(),now();
select * from test;
mysql> select * from test;
+----+------+------------+---------------------+
| id | name | dt         | dt1                 |
+----+------+------------+---------------------+
|  1 | test | 2021-09-06 | 2021-09-06 22:02:53 |
+----+------+------------+---------------------+
1 row in set (0.00 sec)
会话一不提交

会话二：执行创建索引操作
alter table test add index idx_dt(dt);
mysql> alter table test add index idx_dt(dt);
卡住。。。
会话三：可以看到会话二在等元数据锁
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                  |
+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+
|  7 | root | localhost | xhy  | Sleep   |   95 |                                 | NULL                                  |
| 10 | root | localhost | xhy  | Query   |   41 | Waiting for table metadata lock | alter table test add index idx_dt(dt) |
| 12 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                      |
+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+
3 rows in set (0.00 sec)

会话一：
begin;
insert into test(name,dt,dt1) select 'test',now(),now();
select * from test;
mysql> select * from test;
+----+------+------------+---------------------+
| id | name | dt         | dt1                 |
+----+------+------------+---------------------+
|  2 | test | 2021-09-06 | 2021-09-06 22:07:14 |
+----+------+------------+---------------------+
1 row in set (0.00 sec)
不提交
会话二：执行analyze可正常执行
mysql> analyze table test;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| xhy.test | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.00 sec)

会话一：回滚，可以看到回滚成功
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
Empty set (0.00 sec)

总结：

同一会话中若先执行DML，跟着执行DDL,DML 会自动被提交；
在不同会话中，若有未提交的事务，则其他会话执行DDL会被阻塞，需等待元数据锁。
analyze table 在同一会话中执行会提交前面的事务，在不同会话中无需等待元数据锁，可执行但也不会提交其他会话的事务。

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者