Mysql十大经典案例 锁

表级锁

Innodb 与Myisam

CREATE TABLE `t_myisam` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8；

查询堵塞更新
Query |111 | User sleep | select id,sleep(100) from t_myisam |
Query |108 | Waiting for table level lock | update t_myisam set id=2 where id=1|
Query | 3 | Waiting for table level lock | update t_myisam set id=2 where id=1|

解决 ：
Alter table t_myisam engine=innodb;

DDL操作：alter table t add column gmt_create datetime
数据库连接状态：
Query |6 | User sleep | select id ,sleep(50) from t
Query |4 | Waiting for table metadata lock | alter table t add column gmt_create datetime
Query |2 | Waiting for table metadata lock | select * from t where id=1
Query |1 | Waiting for table metadata lock | select * from t where id=2
Query |1 | Waiting for table metadata lock | update t set id =2 where id=1
Tips：DDL过程中注意数据库中大长事务，大查询