如下表格所示：
| session1: | session2: |
| 10:30:27 https://www.shuzhiduo.com/A/lk5awMvqz1/root@localhost:[testdb] mysql.sock>select * from t2; +——+——–+——-+ | id | course | score | +——+——–+——-+ | 1 | math | NULL | +——+——–+——-+ 1 row in set (0.00 sec) |
|
| 10:30:20 https://www.shuzhiduo.com/A/lk5awMvqz1/root@localhost:[testdb] mysql.sock>select * from t2; +——+——–+——-+ | id | course | score | +——+——–+——-+ | 1 | math | NULL | +——+——–+——-+ 1 row in set (0.00 sec) |
|
| 10:30:24 https://www.shuzhiduo.com/A/lk5awMvqz1/root@localhost:[testdb] mysql.sock>begin; Query OK, 0 rows affected (0.00 sec) |
|
| 10:30:31 https://www.shuzhiduo.com/A/lk5awMvqz1/root@localhost:[testdb] mysql.sock>begin; Query OK, 0 rows affected (0.00 sec) |
|
| 10:30:33 https://www.shuzhiduo.com/A/lk5awMvqz1/root@localhost:[testdb] mysql.sock>alter table t2 drop column score;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0 10:30:54 https://www.shuzhiduo.com/A/lk5awMvqz1/root@localhost:[testdb] mysql.sock>alter table t2 add column score int default 0;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0 | |
| 10:30:38 https://www.shuzhiduo.com/A/lk5awMvqz1/root@localhost:[testdb] mysql.sock>select * from t2; +——+——–+——-+ | id | course | score | +——+——–+——-+ | 1 | math | 0 | +——+——–+——-+ 1 row in set (0.00 sec) |
|
| 10:31:24 https://www.shuzhiduo.com/A/lk5awMvqz1/root@localhost:[testdb] mysql.sock>alter table t2 drop column score; waiting…… |
|
| 10:31:52 https://www.shuzhiduo.com/A/lk5awMvqz1/root@localhost:[testdb] mysql.sock>commit; Query OK, 0 rows affected (0.00 sec) |
|
| 10:31:24 https://www.shuzhiduo.com/A/lk5awMvqz1/root@localhost:[testdb] mysql.sock>alter table t2 drop column score; Query OK, 0 rows affected (50.78 sec) Records: 0 Duplicates: 0 Warnings: 0 |
|
在session2中并没有进行select * from t2 for update,但是session1中的alter出现了等待，而session2在没有进行select * from t2时，session1是可以正常进行alter操作的。（把上面的select * from t2换成select 1 from t2的效果也一样。）
即mysql中的DDL操作需要没有其它线程操作其表时（即使是select也算），才会进行，要不会进行锁等待情况。
