ddl锁表统计
2024-10-20 08:23:19
官方文档：
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
虽然官方统计了大部分DDL操作时，是否允许DML，但并不是很全，或者有些支持DML，但仅仅只支持查询，不支持增删改操作。
版本：MySQL 5.7
场景一、
修改 varchar 字段长度时，在 utf8mb4 字符集下，varhcar 长度从【1-63】长度变为 【64及以上】长度时，会锁表，只能查询。反之亦然
State：copy to tmp table
场景二、int 类型个性为 decimal(10,2)，会锁表，只能查询。
State：copy to tmp table
MySQL [(none)]> show processlist;
+----+------+-----------------+-------+---------+------+---------------------------------+-------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+-------+---------+------+---------------------------------+-------------------------------------------+
| 9 | root | localhost:39614 | ceshi | Sleep | 1604 | | NULL |
| 12 | root | localhost:39620 | NULL | Query | 3 | Waiting for table metadata lock | select * from ceshi.t1 limit 1 for update |
| 13 | root | localhost:39622 | NULL | Query | 0 | starting | show processlist |
| 17 | root | localhost:39630 | ceshi | Query | 5 | copy to tmp table | alter table t1 modify age delimal(10,2) |
+----+------+-----------------+-------+---------+------+---------------------------------+-------------------------------------------+
场景三、
varchar 长度 60 -> 100 ，同时新增加一个 varchar 字段。会锁表，只能查询。
MySQL [(none)]> show processlist;
+----+------+-----------------+-------+---------+------+-------------------+-------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+-------+---------+------+-------------------+-------------------------------------------------------------------------+
| 9 | root | localhost:39614 | ceshi | Sleep | 1903 | | NULL |
| 12 | root | localhost:39620 | NULL | Query | 0 | starting | show processlist |
| 13 | root | localhost:39622 | NULL | Sleep | 299 | | NULL |
| 17 | root | localhost:39630 | ceshi | Query | 6 | copy to tmp table | alter table t1 modify name varchar(100),add column describ varchar(100) |
+----+------+-----------------+-------+---------+------+-------------------+-------------------------------------------------------------------------+
4 rows in set (0.000 sec)
场景四、
varchar 修改为 text。会锁表，只能查询
MySQL [ceshi]> show processlist;
+----+------+-----------------+-------+---------+------+-------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+-------+---------+------+-------------------+------------------------------------+
| 3 | root | 127.0.0.1:33426 | ceshi | Query | 3 | copy to tmp table | alter table t1 modify describ text |
| 4 | root | 127.0.0.1:33428 | ceshi | Query | 0 | starting | show processlist |
+----+------+-----------------+-------+---------+------+-------------------+------------------------------------+
场景五、
varchar2048 -> 2047 减小会锁表
MySQL [ceshi]> show processlist;
+----+------+-----------------+-------+---------+------+-------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+-------+---------+------+-------------------+------------------------------------------+
| 3 | root | 127.0.0.1:33426 | ceshi | Query | 3 | copy to tmp table | alter table t1 modify name varchar(2047) |
| 4 | root | 127.0.0.1:33428 | ceshi | Query | 0 | starting | show processlist |
+----+------+-----------------+-------+---------+------+-------------------+------------------------------------------+
2 rows in set (0.000 sec)
最新文章
- 弹窗样式 idialog,purebox,artdialog4.1.2,jquery.alert.v1.2
- <select> 默认选中
- autoit小贴士
- 20145211 《Java程序设计》第6周学习总结——三笑徒然当一痴
- java万物皆对象
- c fopen文件读写
- (转)C#使用Mysql记录
- 20141031--SQL分组，数学函数，聚合函数
- 常见iis错误之一
- Java中泛型的理解
- 洛谷P3459 [POI2007]MEG-Megalopolis（树链剖分，Splay）
- js浮点数运算的坑，多少同学有碰到过？
- python多进程之间的通信：消息队列Queue
- MySQL yum 在线与本地包方式安装
- async和await学习笔记
- GlusterFS六大卷模式說明
- 中国程序化购买广告解析：RTB/DSP/Ad Exchange/SSP/DMP，思维导图
- Linux C 文件操作函数（~上善止水~）
- [na]icmp重定向
- ios 给键盘上面加上“完成”