学习 mysql 的锁主要的目的就是
- mysql 都有什么锁
- 平时写语句的时候都回用到什么样的锁
- 如何排查死锁问题
lock与latch
数据库中lock与latch都称为锁，但两者有截然不同的意思
latch 称为闩锁(轻量级)，要求锁的时间必须非常短。在Innodb引擎中，有分mutex(互斥锁)和rwlock(读写锁)。用来保证并发线程操作临界资源的正确性，没有死锁检测机制
lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放(补孕酮事务隔离级别释放的时间可能不一样)
全局锁
如果数据库需要进行全局逻辑备份
在支持事务的存储引擎中，在可重复读隔离级别下开启一个事务，数据库备份的同时可以更新(mysqldump工具)
在不支持事务的存储引擎中，需要通过FTWRL方法进行全局加锁备份
要全库只读，为什么不使用set global readonly=true?
有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。 且在slave上，用户有超级权限的话readonly是失效的
在异常处理机制上有差异。
- 如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。
- 如果将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
全局锁是对整个数据库实例加锁，整个数据库处于只读状态，下面语句将会被阻塞：
- 数据更新语句(数据的增删改)
- 数据定义语句(建表、修改表结构等)
- 更新类事务的提交语句
加锁
Flush tables with read lock(FTWRL)解锁
unlock tables
整个数据库只读可能存在的问题：
-
如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
-
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟
表级锁
MySql里面表级别的锁有两种：
- 表锁
- 元数据锁（meta data lock，MDL)
表锁的语法是 lock tables … read/write，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放
需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象
MDL(metadata lock)，MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。
- DDL操作对表加MDL读锁，保证的是表结构不能修改，而与表数据无关，是可以CRUD的
- DML操作对表加MDL写锁，保证的是表结构不能被并行修改，同时表数据也不能读了
如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的
虽然MDL是默认添加的，但还是会碰到一个问题：给一个小表加个字段，导致整个库挂了
因为sessionB加了MDL读锁，导致后面的sessionC阻塞。如果sessionB一直没有完成select，那么sessionC申请写锁被阻塞，将会导致后面的sessionD等申请读锁都被阻塞。这个时候客户端如果有频繁重试的逻辑就会导致不停的和数据库建立连接，把连接池打满导致库不可用
事务中的 MDL 锁，在语句执行开始时申请，但在语句结束后并不会马上释放，而会等到整个事务提交后释放
如何安全的给数据库加字段？
首先需要解决长事务问题：事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务
如果要变更的表是一个热点表，虽然数据量不大但请求很频繁，而你不得不加个字段，你该怎么做呢？
比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程
行锁
行锁是由各个引擎自己实现的，而有的引擎并不支持行锁如MyISAM
InnoDB实现了如下两种标准的行级锁：
- 共享锁(S Lock)，允许事务读一行数据
- 排他锁(X Lock)，允许事务删除或更新一行数据
InnoDB支持多粒度(granular)锁，允许事务在行级上的锁和表级上的锁同时存在，称为意向锁(Intention Lock)，表级别的锁
- 意向共享锁(IS Lock)，事务想要获得一张表中某几行的共享锁
- 意向排他锁(IX Lock)，事务想要获得一张表中某几行的排他锁
由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫描以外的任何请求
| IS | IX | S | X | |
|---|---|---|---|---|
| IS | 兼容 | 兼容 | 兼容 | 不兼容 |
| IX | 兼容 | 兼容 | 不兼容 | 不兼容 |
| S | 兼容 | 不兼容 | 兼容 | 不兼容 |
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
若将上锁的对象看成一颗树，那么对最下层的对象上锁，首先要对上层对象上锁
当事务A commit结束之后，事务B才执行
InnoDB存储引擎有3种行锁的算法，分别是：
- Record Lock: 单个行记录上的锁
- Gap Lock: 间隙锁，锁定一个范围，但不包含记录本身
- Next-Key Lock: Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身
在REPEATABLE READ下，存储引擎使用Next-Key Locking机制来避免幻读(一个事务中，两次读到的数据数量不一致)
其他锁
一致性非锁写读
在默认配置即事务隔离级别为REPEATABLE READ模式下，InnoDB存储引擎的SELECT操作使用一致性非锁定读
一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中的行的数据。如果读取的行正在执行DELETE或UPDATE操作，读取操作不会等待行上锁的释放而是去读取行的一个快照
快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。undo用来在事务中进行回滚的，因此快照数据本身没有额外的开销
一个行记录可能不止一个快照数据，由此的并发控制，称为多版本并发控制(Multi Version Concurrency Control, MVCC)
- 在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据
- 在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本
一致性锁定读
某些情况下需要显示对数据库读取操作加锁已保证数据逻辑一致性。对于SELECT的只读操作，InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读(locking read)
- SELECT … FOR UPDATE : 对读取的行记录加一个X锁，其他事务不能加上任何锁
- SELECT … LOCK IN SHARE MODE： 对读取的行记录加一个S锁，其他事务锁定的行加S锁，但是如果加X锁，会被阻塞
自增长与锁
在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长在计数器(auto-increment counter)，对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：
select MAX(auto_inc_col) FROM t FOR UPDATE;
插入操作会依据这个计数器值加1服务自增长列，称为AUTO-INC Locking。它并不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放
自增长值的列必须是缩影，同时必须是索引的第一列，不是则抛出异常
死锁和死锁检查
事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：
- 一种策略是，直接进入等待直到超时。超时时间可通过参数 innodb_lock_wait_timeout 来设置(默认50s)。
- 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑
不能将第一种策略的超时时间设置的太短，可能导致误伤了不是死锁的情况
每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。死锁检测会消耗大量的CPU。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的
怎么解决由这种热点行更新导致的性能问题呢？
可以考虑将一行改成逻辑上的多行来减少冲突。如果一个账户的金额等于这10条记录的总和，这样给一个账号添加金额的时候可以随机选择一条来进行，这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗