前言
上篇说到数据库事务中的特性ACID和4个隔离级别，今儿就来看一下事务中的锁。
MySQL中的锁
锁是MySQL在服务器层和存储引擎层的并发控制，锁可以保证数据并发访问的一致性、有效性；
锁冲突也是影响数据库并发访问性能的一个重要因素
MySQL有三种级别的锁：「表级锁、行级锁、页级锁」
表级锁行级锁业级锁特点开销小、加锁快开销大、加锁慢加锁时间介于其余两者之间是否会死锁否是是并发度粒度大、锁冲突概率最高、并发低粒度小、锁冲突概率低、并发高粒度介于其余两者之间、并发一般存储引擎Innodb、MyISAMInnodbBDB
「术语：」
DDL，Data Definition Language，数据库定义语言
比如：CREATE，ALERT，DROP，TRUNCATE
DML，Data Manipulation Language，数据库操作语言
比如：SELECT，INSERT，UPDATE，DELETE，CALL，EXPLAIN PLAN，LOCK
DCL，Data Control Language，数据库控制语言
比如：COMMIT，SAVEPOINT，ROLLBACK，SET TRANSACTION
Innodb中的锁
表锁和行锁
表锁
Innodb有两种内部使用的意向锁（Intention Locks），都是表锁。
表锁分成三种：
「意向共享锁（IS）：」
事务计划给数据行加行共享锁，加共享锁之前必先获取该锁
「意向排他锁（IX）：」
事务打算给数据行加行排他锁，加排他锁之前必先获取该锁
「自增锁（AUTO-INC Locks）：」
特殊表锁，自增长计数器通过该“锁”来获得子增长计数器最大的计数值。
在加行锁之前必须先获得表级意向锁，否则等待 innodb_lock_wait_timeout 超时后根据innodb_rollback_on_timeout 决定是否回滚事务。
如何添加表锁
lock tables table_name read/write
「释放锁：」
释放锁不需要添加参数，其会释放当前用户的所有锁。
unlock tables
「例如：」
1、给student表添加读锁，看当前用户和其他用户是否能插入数据：
当前用户：报错无法插入
其他用户：一直等待
释放锁之后：
其他用户：插入成功
2、多个用户获取写锁
root用户获取写锁：
然后试一下lsy用户能否获取相同表的写锁
可看到是一直在等待。
当root用户释放写锁后：
lsy用户立马就获得了写锁：
行锁
共享锁（S）和排它锁（X）。
「共享锁（S）：」
多个事务可以一起读，共享锁之间不互斥，共享锁会阻塞排它锁。
「排他锁（X）：」
允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
加锁方式：
自动加锁
对于UPDATE、DELETE、INSERT语句，自动给相关数据加上排他锁
对于普通的SELECT语句，不加锁，属于快照读
「手动加锁：」
共享锁：
select * from table_name [where] lock in share mode;
排他锁：(这是我之前比较常用的)
select * from table_name [where] for updete
通过对索引数据页上的记录（record）加锁实现的。
主要实现算法有 3 种：
「Record Lock 锁：」
单个行记录的锁（锁数据，不锁 Gap）。
例如for update就是此锁
「Gap Lock 锁：」
间隙锁，锁定一个范围，不包括记录本身（不锁数据，仅仅锁数据前面的Gap）。
保证某个间隙内的数据在锁定期间不会发生任何变化。
当使用唯一索引进行搜索的时候，不会产生间隙锁
例如：student的id列是唯一索引
select * from student where id = 1;
当使用非唯一索引或者没有索引进行搜索的时候，会产生间隙锁
间隙范围：
根据检索条件向下寻找最靠近检索条件的记录值A作为左区间，向上寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B] 左开右闭。
例如：test的id列是没有索引
使用如下sql查询的时候
在lsy用户下执行
select * from test where id = 3 for update;
那么它的间隙范围就是(1，6]
如果在其他用户想往这区间插入数据就会阻塞，比如插入id是4的。
「Next-key Lock 锁：」
同时锁住数据，并且锁住数据前面的 Gap。
死锁
InnoDB 是逐行加锁的，极容易产生死锁。那么死锁产生的四个条件是什么呢？
「互斥条件：」
一个资源每次只能被一个进程使用；
「请求与保持条件：」
一个进程因请求资源而阻塞时，对已获得的资源保持不放；
「不剥夺条件：」
进程已获得的资源，在没使用完之前，不能强行剥夺；
「循环等待条件：」
多个进程之间形成的一种互相循环等待资源的关系。
发生死锁后，会出现CPU使用率高，QPS急剧下降，回滚请求失败的情况
避免死锁
加锁顺序一致
尽量基于primary或unique key更新数据。
单次操作数据量不宜过多，涉及表尽量少。
减少表上索引，减少锁定资源。
「死锁情况下打印错误日志」
Show engine innodb status\G或者innodb_print_all_deadlocks=ON 打印到错误日志
例如：
有两张表，分别是student和test表
1、在事务1中先删除student表中id=10的数据
2、在事务2中删除test表中id=6的数据
3、在事务1中删除test表中id=6的数据
4、在事务2中删除student表中id=10的数据
此时就会报死锁错误：
事务1：
事务2：
用Show engine innodb status\G查看日志：
元数据锁
Metadata Lock
用于解决或者保证DDL操作与DML操作之间的一致性。
当对一个表做增删改查操作的时候，加 MDL 读锁；
当要对表做结构变更操作的时候，加 MDL 写锁。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性
快速发现锁等待
「Innodb锁：」
select b.trx_mysql_thread_id as '被阻塞线程' ,b.trx_query as '被阻塞SQL'
,c.trx_mysql_thread_id as '阻塞线程' ,c.trx_query as '阻塞SQL'
,(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started)) as '阻塞时间'
from
information_schema.innodb_lock_waits a
join
information_schema.innodb_trx b on a.requesting_trx_id=b.trx_id
join
information_schema.innodb_trx c on a.blocking_trx_id=c.trx_id
where
(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started))>10;
或
select * from sys.innodb_lock_waits\G
「元数据锁：」
select * from performance_schema.metadata_locks;
根据上一条sql获取的线程id查询线程详细信息：
select * from performance_schema.threads where thread_id in (thread_ids)
注意：元数据锁信息需要开启performance_schema
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
===============================
我是Liusy，一个喜欢健身的程序员。
获取更多干货以及最新消息，公_众_号：上古伪神
如果对您有帮助，点个关注、转发就是对我最大的支持！！！谢谢