session1:
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2;
+----+------+------+------+------+
| id | a | b | c | e |
+----+------+------+------+------+
| 0 | 0 | NULL | 0 | 0 |
| 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 1 | 2 | 2 |
| 3 | 3 | 1 | 1 | 3 |
| 4 | 4 | 2 | 1 | 4 |
| 5 | 5 | 2 | 2 | 5 |
| 6 | 6 | 1 | 1 | 6 |
| 7 | 7 | 2 | 2 | 7 |
| 8 | 8 | 3 | 3 | 8 |
| 9 | 9 | 4 | 5 | NULL |
+----+------+------+------+------+
10 rows in set (0.03 sec)
mysql> select * from t3;
+----+------+------+------+------+
| id | a | b | c | e |
+----+------+------+------+------+
| 0 | 0 | NULL | 0 | 0 |
| 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 1 | 2 | 2 |
| 3 | 3 | 1 | 1 | 3 |
| 4 | 4 | 2 | 1 | 4 |
| 5 | 5 | 2 | 2 | 5 |
| 6 | 6 | 1 | 1 | 6 |
| 8 | 8 | 3 | 3 | 8 |
| 9 | 9 | 4 | 5 | NULL |
+----+------+------+------+------+
9 rows in set (0.04 sec)
session2:
mysql> drop table t2;
session3:
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+------------------+
| 9 | root | localhost | test | Sleep | 122 | | NULL |
| 10 | root | localhost | test | Query | 49 | Waiting for table metadata lock | drop table t2 |
| 11 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+---------------------------------+------------------+
3 rows in set (0.01 sec)
session1:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
session3:
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 9 | root | localhost | test | Sleep | 30 | | NULL |
| 10 | root | localhost | test | Sleep | 202 | | NULL |
| 11 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
3 rows in set (0.00 sec)