MySQL 全局锁
- 对整个数据库实例加锁
- Flush tables with read lock (FTWRL)
- 数据库实例处于只读状态
- 阻塞DML、DDL、更新类事物提交语句
使用场景
- 全库逻辑备份
- mysqldump 的single-transaction参数只适用所有表使用事物引擎(InnoDB) 的库，对于存在MyISAM引擎表的库只能通过FTWRL获取全库一致性备份
- readonly
set global readonly = on 也可以使全库只读，但还是建议FTWRL的方式
- readonly常用来判断一个库是主还是备。修改global变量影响面更大 - 异常处理机制的差异。FTWRL在客户端异常端口后，MySQL会自动释放该全局锁；而设置readonly后若客户端异常断开，MySQL会一直保持readonly状态，风险高。复制
MySQL 表级锁
表锁
表锁种类
意向共享锁（IS）
事务计划给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX）
事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
自增锁（AUTO-INC Locks）
特殊表锁，自增长计数器通过该“锁”来获得子增长计数器最大的计数值。
在 MySQL InnoDB 存储引擎中，我们在设计表结构的时候，通常会建议添加一列作为自增主键。这里就会涉及一个特殊的锁：自增锁（即：AUTO-INC Locks），它属于表锁的一种，在 INSERT 结束后立即释放。我们可以执行 show engine innodb status\G 来查看自增锁的状态信息。
在加行锁之前必须先获得表级意向锁，否则等待 innodb_lock_wait_timeout 超时后根据innodb_rollback_on_timeout 决定是否回滚事务。
表锁语法
- 表锁语法为lock tables xx read/write
- 可通过 unlock tables 释放
- read-lock 允许其他并发的读请求，但阻塞写请求，即可以同时读，但不允许任何写。也叫共享锁
- write-lock 不允许其他并发的读和写请求，是排他的(exclusive)。也叫独占锁
表锁示例
# 表级别共享锁 lock tables ${tableName} read; # 表级独占锁 lock tables ${tableName} write; mysql> lock table t1 read; Query OK, 0 rows affected (0.00 sec) # 如何释放表锁 mysql> unlock table; Query OK, 0 rows affected (0.00 sec) # 其他锁住全表的方式 # 手动开启事务 begin; # 检索是加上共享读锁，这时其他的线程如果向insert则会被阻塞 select * from t lock in share mode;复制
MDL(元数据锁)
MDL 工作机制
MySQL 5.5版本后引入MDL
- 对一个表做DML操作时，加MDL读锁
- 对一个表做DDL操作时，加MDL写锁
MDL不需要显示使用，访问表时自动加上，其作用是保证读写正确性。
如果一个查询在遍历表中数据，而执行期间另一个线程对该表结构做DDL变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定不行的。
- 读锁之间不互斥，因此支持多个线程同时对一张表做DML。
- 读写锁之间、写锁之间互斥，用以保证DDL的安全性。
MDL 常见问题
因积压查询而导致表夯死
如果一个表，查询语句频繁，且由执行了DDL操作非常容易导致上图所示中表锁住，而最后查询线程不断重试导致MySQL的线程爆满最终数据库崩溃
如何安全的做DDL
- 解决长事物问题
- 在DDL语句中设置等待时间，如果指定时间内能够拿到MDL写锁最好，拿不到也不阻塞后续业务语句；MariaDB、AliSQl支持该种写法
alter table tb_name nowait add column .. alter table tb_name wait N add column ..复制MySQL 5.6 支持Online DDL 但为啥还是会阻塞？
Online DDL过程是：
- 获取MDL写锁
- 降级为MDL读锁
- 真正做DDL
- 升级成MDL写锁
- 释放MDL
1、2、4、5若果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，该期间表可以正常读写数据，因此称为‘Online’。而如果该表已经存在MDL读锁，那么第一步就阻塞后续线程。
MySQL 行锁
- MySQL的行锁由各自存储引擎层实现
- MyISAM引擎不支持行锁
- InnoDB事物中，行锁是在需要的时候才加上，但并不马上释放，而是待事务结束后才释放。这个就是两阶段锁协议。
- 若事物需要锁多行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
行锁产生原因
当多个事务同时去操作（增删改）某一行数据的时候，MySQL 为了维护 ACID 特性，就会用锁的形式来防止多个事务同时操作某一行数据，避免数据不一致。只有分配到行锁的事务才有权力操作该数据行，直到该事务结束，才释放行锁，而其他没有分配到行锁的事务就会产生行锁等待。如果等待时间超过了配置值（也就是 innodb_lock_wait_timeout 参数的值，个人习惯配置成 5s，MySQL 官方默认为 50s），则会抛出行锁等待超时错误
- 程序中非数据库交互操作导致事务挂起
将接口调用或者文件操作等这一类非数据库交互操作嵌入在 SQL 事务代码之中，那么整个事务很有可能因此挂起（接口不通等待超时或是上传下载大附件）。
- 事务中包含性能较差的查询 SQL
事务中存在慢查询，导致同一个事务中的其他 DML 无法及时释放占用的行锁，引起行锁等待。
- 单个事务中包含大量 SQL
通常是由于在事务代码中加入 for 循环导致，虽然单个 SQL 运行很快，但是 SQL 数量一大，事务就会很慢。
- 级联更新 SQL 执行时间较久
这类 SQL 容易让人产生错觉，例如：update A set ... where ...in (select B) 这类级联更新，不仅会占用 A 表上的行锁，也会占用 B 表上的行锁，当 SQL 执行较久时，很容易引起 B 表上的行锁等待。
- 磁盘问题导致的事务挂起
极少出现的情形，比如存储突然离线，SQL 执行会卡在内核调用磁盘的步骤上，一直等待，事务无法提交。
综上可以看出，如果事务长时间未提交，且事务中包含了 DML 操作，那么就有可能产生行锁等待，引起报错。
行锁种类
Innodb 行锁是通过索引上的索引项加锁来实现的，如果没有索引，Innodb 将通过隐藏的聚簇索引来对记录加锁。
- Record Lock 锁：单个行记录的锁,对索引项加锁（锁数据，不锁 Gap）
- Gap Lock 锁：间隙锁，锁定一个范围，不包括记录本身（不锁数据，仅仅锁数据前面的Gap），对索引项之间的“间隙”，即第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。
- Next-key Lock 锁：前面2种的组合，同时锁住数据，并且锁住数据前面的 Gap，左开右闭
默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录
Innodb 行锁实现方式
Innodb 这种行锁实现特点意味着：
- 如果不通过索引条件检索数据，那么 Innodb 将对所有记录加锁，实际效果跟表锁一样。
- 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的，应用设计的时候要注意这一点。
- 当表有多个索引的时候，不同的事务可以不同的索引锁定不同的行，无论是使用主键索引、唯一索引或普通索引，Innodb 都会使用行锁来对数据加锁。
- 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下Innodb也会对所有记录加锁。因此，在分析索冲突时，需检查SQL的执行计划，以确认是否真正使用了索引。
行锁排查
查看session中用的锁信息：
show engine innodb status\G;复制
如果查不到lock信息，需要打开：
set global innodb_status_output_locks=on;复制
5.6,5.7查看锁 wait信息
select * from information_schema.innodb_trx\G; select * from information_schema.innodb_locks\G; select * from information_schema.innodb_lock_waits\G; /*innodb 行锁等待脚本*/ /*innodb 行锁等待脚本*/ SELECT r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query, concat(timestampdiff(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP()),'s') AS duration, b.trx_mysql_thread_id blocking_thread,t.processlist_command state,b.trx_query blocking_current_query,e.sql_text blocking_last_query FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id JOIN performance_schema.threads t on t.processlist_id = b.trx_mysql_thread_id JOIN performance_schema.events_statements_current e USING(thread_id)复制
Linux 上后台跑监控脚本（innodb_lock_monitor.sh）来记录 MySQL 阻塞情况，脚本如下：
#!/bin/bash #账号、密码、监控日志 user="root" password="Gepoint" logfile="/root/innodb_lock_monitor.log" while true do num=`mysql -u${user} -p${password} -e "select count(*) from information_schema.innodb_lock_waits" |grep -v count` if [[ $num -gt 0 ]];then date >> /root/innodb_lock_monitor.log mysql -u${user} -p${password} -e "SELECT r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query, \ concat(timestampdiff(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP()),'s') AS duration,\ b.trx_mysql_thread_id blocking_thread,t.processlist_command state,b.trx_query blocking_query,e.sql_text \ FROM information_schema.innodb_lock_waits w \ JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id \ JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id \ JOIN performance_schema.threads t on t.processlist_id = b.trx_mysql_thread_id \ JOIN performance_schema.events_statements_current e USING(thread_id) \G " >> ${logfile} fi sleep 5 done复制
--使用 nohup 命令后台运行监控脚本 [root@192-168-188-155 ~]# nohup sh innodb_lock_monitor.sh & [2] 31464 nohup: ignoring input and appending output to ‘nohup.out’ --查看 nohup.out 是否出现报错 [root@192-168-188-155 ~]# tail -f nohup.out复制
产生行锁的原因
- 表中的索引（主键，二级索引）
- 事务隔离级别（修改数据的事务隔离级别）
- 操作:select或者DML
- SQL执行计划
意向锁 Intention lock
insert一般情况下没有锁对象，称为“隐式锁”，和delete以及update不一样
如何避免死锁
锁死指的是并发系统中不同的线程之间出现了资源的循环依赖，也就是说大家各自锁住了对方需要的资源，而且谁也不主动释放。夯住，出现死锁。
- 加锁顺序一致，尽可能一次性锁定所有所需的数据行。
- 尽量把那些更容易出现并发冲突的SQL放在事务的最后执行，这样性能更好。
- 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会，尽量基于 primary 或 unique key 更新数据。
- 单次操作数据量不宜过多，涉及表尽量少，减少锁定数据量和锁定时间长度 （innodb_row_lock_time_avg）。
- 相关工具：pt-deadlock-logger。
- insert ... select ... 和 create table ... select ... 语句，可能会阻值对源表的并发更新，如果查询比较复杂，会造成严重的性能问题，读者应该在应用中尽量避免使用；
- 通过使用使用“select * from source_tab ... into outfile” 和“load data infile ...” 语句组合来间接实现，采用这种方式不会给 source_tab加锁
- 使用基于行的 binlog 格式和基于行数据的复制
- 尽量使用相等的条件访问数据，这样可以避免 Next-key 锁对并发插入的影响
- 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
- 对于一些特定的事务，可以使用表锁来提高处理速度或减少发生死锁的概率。
解决死锁思路
乐观锁和悲观锁
数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
悲观锁： 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制
乐观锁： 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。
两种锁的使用场景
从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。
