开发者学堂课程【云数据库优化经典案例：锁优化】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/67/detail/1160
锁优化
内容介绍：
一、案例二：表级锁
二、案例三：Metadata lock
三、锁问题最佳实践
一、案例二：表级锁
1.分析问题方向
本章分析数据库锁的问题，从最底层的不同引擎来考虑，不同的引擎锁级别不同。
2.两种引擎的锁级别对比
Myisam 为较为古老的引擎，Innodb 为默认 redis 支持引擎。
Myisam 不支持事务，所以为表级锁，可能导致一个慢查询堵塞更新，同时索引可能损坏，不支持在线备份。Myisam 引擎在 mysql5.7 以不再维护。
Innodb 支持事务，不堵塞，锁级别为行级锁，同时支持在线备份，也不会损坏。
部分用户为了提升速度会使用 Myisam 引擎，因为该引擎的维护十分简便，如：其在备份时，常常是将整个库都锁住进而导致全部都不能使用。
Innodb 与 Myisam
引擎 |
支持事务 |
并发 |
索引损坏 |
锁级别 |
在线备份 |
Myisam |
不支持 |
查询堵塞更新 |
索引损坏 |
表 |
不支持 |
Innodb |
支持 |
不堵塞 |
不损坏 |
行 |
支持 |
3.Myisam 的表级锁展示以及转换 Innobd
通过以下代码测试 Myisam 引擎的表级锁，通过 ENGINE=MyISAM 确定其为 Myisam 引擎，同时设置 sleep(100)为100秒，而后续的 Waiting for table level lock 表示后续的操作都被锁住。解决方案为：将 Myisam 引擎改为 Innodb引擎。
代码如下：
CREATE TABLE 't_myisam`(`id`int(11)DEFAULT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
查询堵塞更新
Query |111 | User sleep | select id,sleep(100) from t_myisan |
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;
二、案例三：Metadata lock
1.DDL 操作的 Metadata lock
Metadata 锁是 mysql 为了保护 to DDL 时而去冻结元数据信息，为了保持数据一致性，会在表上添加一个元数据锁，但元数据锁还存在一些问题，如该案例。
此案例中表 t 为仅有几百行数据的表，对该表添加一个字段，有可能造成数据库故障，如该表和另一个大表作关联查询时，查询较长，对其做 DDL 时，就必须等待慢查询执行完成才可以执行 DDL 操作，但由于 DDL 操作对表在 Metadata锁时，表上的访问都会被 block 住。
代码如下：
DDL 操作: alter table t add column gmt_create datetime
数据库连接状态：
//设置 sleep 时间
Query | 6 | User sleep select id ,sleep(50) from t
//对表添加索引和字段
Query | 4 | Waiting for table metadata lock | alter table t add column gmt_create database
//而因为慢查询并未完成，所以之后的访问都会 block 住
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 过程中注意数据库中是否有大长事务，大查询。如：全表查询，批量数据灌入的大操作。
2.注意
在一些线上繁忙系统或数据库中已有许多 MySQL 中添加索引优化时，要注意将慢查询问题解决，再去添加索引，否则就会出现之前的慢查询还未结束，导致之后操作全部堵住，数据库崩溃。
三、锁问题最佳实践
不同阶段的注意事项
（1）设计开发阶段--准备期间
①避免使用 myisam 存储引擎，改用 innodb 引擎；
②避免大事务，长事务导致事务在数据库中的运行时间加长，表上的操作无法完成；
③因 ddl 本身也含有锁，在加索引和字段时容易锁全表，所以选择升级到 MySQL5.6版本，支持 online ddl，online ddl 只支持加字段和索引，而修改长度、字符集或主键需要锁表；
以上即在数据库开发设计阶段的注意事项。
（2）管理运维阶段--上线之后
①在业务低峰期执行上述操作，比如创建索引，添加字段;
②在结构变更前，观察数据库中是否存在长 SQL，慢 SQL、大事务;
③结构变更期间，监控数据库的线程状态是否存在 lock wait ;
④ApsaraDB 支持在 DDL 变更中加入 wait timeout，wait timeout 超时后 ddl 会自动结束，以此保护上述情况
（3）案例分享
一客户在数据库插入的时候超时，其共有两个事务，A 事务做表操作但其事务仍有其他操作，其中某个操作导致调入了其他业务模块，导致此 A 事务一直未提交，而B事务进入后同样对 A 事务上的行进行操作，因为 A 事务并未提交所以 B事务必须等待，最后就导致系统出现大量超时。
感悟：应该尽可能减少事务的操作范围
