MySQL基础-锁原理

锁

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

在如下操作中会用到锁
DDL（Data Definition Language）是指数据定义语言，用来定义数据的结构。例如，create table , alter table 语句。
DML（Data Manipulation Language）是指数据操作语言，用来操作数据，比如 insert ,update 和 delete 等语句。

全局锁

全局锁就是对整个数据库实例加锁。
MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。解锁是unlock tables。

使用场景：全库备份时候需要开启全局只读。如果不加全局锁，在备份两张表时，由于逻辑时间不一致，若其中一张表的更新业务数据会影响领一张表，恢复的时候就会出错。

官方自带的逻辑备份工具mysqldump在InnoDB中，当 mysqldump 使用参数–single-transaction 的时候，备份数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

但该功能只在支持事务的引擎中有用，如MyISAM不支持事务，就只能用FTWRL了。

同时set global readonly=true也可以使全局进入只读，但他是一个配置，而FTWRL是锁，在客户端意外发生断开时，锁会被释放，配置却不会解除，所以不建议使用该配置。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

读锁是共享锁，给表加了读锁之后别的线程和本线程只能读。
写锁是排他锁，给表加了写锁之后别的线程就不能读写。

元数据锁MDL

元数据锁是server层的表级锁，由系统自行增加/解除。
主要用于隔离DML和DDL操作之间的干扰。（简单来说就是对表结构的修改，DML不会修改表结构，DDL会修改表结构，如增加/删除一列）。

DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）。

我们现在知道，对于每一个增删改查操作，都会加MDL读锁，当有修改字段的请求时，就会申请MDL写锁。
而事务中的MDL读锁（比如某事务中有一句select），在语句结束后并不会马上释放MDL读锁，而是在事务提交以后才释放。

可能的问题：
假设此时有一张热点表（很多查询请求），需要进行修改表字段操作，线程便会发送一个MDL写锁的申请，如果一直没有申请到写锁，就会一直处于阻塞。
而MDL写锁的优先级是高于读锁的，所以该MDL写锁申请之后的所有MDL读锁申请也会处于阻塞状态，这个时候客户端如果有频繁重试的逻辑就会不停的和数据库建立连接，把连接池打满，导致库不可用。
所以有未提交的事务时无法修改表字段，而且在存在长事务时执行修改表字段命令是一个危险的操作，可能阻塞其它增删改查请求，或导致线程爆满。

如何解决：
1.解决长事务
2.如果要给热点数据做表结构变更要带上超时时间 拿不到写锁就放弃

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

行锁

两阶段协议锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁（行）尽量往后放。

比如买电影票涉及到如下三步操作，在同一个事务里：
1.客户账户扣除价钱
2.电影票数量-1
3.记录日志
根据以上信息，我们可以选择该事务顺序为3、1、2，因为电影票这个数据是热点行。

死锁和死锁检测

在热点业务/热点行中，由于行锁只在事务结束的时候释放，如果两个事务发生循环依赖，就会造成死锁

对于死锁，我们有两种策略：

• 直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout（默认50s） 来设置。
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

对于第一种，50s太长了，对于热点业务这么长的等待时间不可接受。如果设置成1s又可能造成误伤。所以大多数时间我们选择第二种：主动检测死锁。

检测的过程是，每当一个事务A被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此递归循环，最后判断是否出现了循环等待，A绕了一圈把A锁了，也就是死锁。

主动检测死锁默认是开启状态，也就是说每次有事务被锁住时，就会进行检测。
对于热点行，比如上文的电影票余量，每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。

每一个线程都得去检查队列里排在他后面的线程是否抓到了自己的"把柄", 抓到了就说明会造成死锁。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。死锁检测要耗费大量的 CPU 资源。

对应之策：

• 关掉死锁检测
• 控制并发度，加中间件或别的控制方法
• 将一行改成逻辑上的多行来减少锁冲突，比如100张票分成10行的10张票