今天给线上一个一百多万的表加注释的时候,表锁住了半小时,最后 kill 解决了。 由于用的是 Navicat,我想知道,在命令行里 alter comment 会不会也会锁表? (我知道改字段类型等是会锁的,但是加个注释而已,不至于吧?)
基本明确了,用的5.7mysql,加注释确实会锁表。至于为什么一直锁住,应该是navicat超时断开连接导致的,更具体原因不清楚,应该是bug类。
1
snappyone 2020-05-19 14:53:50 +08:00 via Android
是不是加了之后没 commit,100 万就算改列也没这么久
|
2
sagaxu 2020-05-19 15:00:35 +08:00 via Android 1
哪个 db 能把注释存到每一行里?
|
3
Coolha 2020-05-19 15:57:26 +08:00
alter table 就会给表加锁,和在哪里改的没关系吧
|
4
zhangysh1995 2020-05-19 17:31:25 +08:00
ALTER TABLE operations are processed using one of the following algorithms:
COPY: Operations are performed on a copy of the original table, and table data is copied from the original table to the new table row by row. Concurrent DML is not permitted. 不可以并发 INPLACE: Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported. 可能加锁,一般可以并发 INSTANT: Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12) 允许并发 https://dev.mysql.com/doc/refman/8.0/en/alter-table.html |
7
0000zjn OP @zhangysh1995 谢谢!正解
|