MySQL--锁机制详解

什么是锁？

锁是计算机协调多个进程并发访问贡献资源的一种机制，MySQL数据库的锁机制简单来说，就是数据库为了保证访问共享数据的一致性的一种机制，有锁存在就一定存在锁冲突，锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库而言显得尤其重要。

MySQL锁的分类？

InnoDB存储引擎和MyISAM对锁支持？
InnoDB支持丰富的锁类型，行级锁、表级锁、间隙锁、临键锁等，MyISAM只支持表级锁，故一下对锁的分析基本都是针对InnoDB引擎。

表级锁：表级锁是MySQL数据库中最大粒度的锁机制，每次加锁就锁定全表。

• 优点：加锁逻辑简单，所以加锁释放锁都非常快。
• 缺点：表级锁有一个致命的缺点就是并发性能差，因为锁全表导致我访问一条数据也要把全表锁定，导致并发性能很差。

行级锁：行级锁，见名知意，就是一次锁定一行数据，加锁粒度小。

• 优点：加锁粒度小，并发性能好。
• 缺点：加锁较为复杂，所冲突的概率变大，甚至可能出现死锁。

页级锁：页级锁，介于表级锁和行级锁之间的一种锁机制。

• 优点：加锁解锁消耗时间介于表级锁和行级锁中间，并发性能也介于两者之间，会发生死锁。

• 缺点：加锁较为复杂，所冲突的概率变大，甚至可能出现死锁。

  共享锁与排他锁

• 共享锁：共享锁又叫S锁，不会阻塞其他连接对加锁数据的读操作，但是会阻塞对加锁数据的写做操，因此共享锁又叫读锁。

• 排他锁：排他锁又叫X锁，排他顾名思义就是排斥，对于其他连接既不允许读操作也不允许写操作，因此又叫写锁。

共享锁与排他锁的兼容情况：

乐观锁与悲观锁

• 乐观锁：假设多用户在并发事务时候相互不影响，在更改数据的时候进行数据校验，如果数据发生了变化就回滚操作，乐观锁只是一种思想，MySQL并没有帮我们实现，一般是通过版本号机制来实现乐观锁。
• 悲观锁：悲观锁的思想是默认数据会被其他事务影响，在获取数据的时候就对数据进行显式加锁，悲观锁依赖MySQL的锁机制，要使用悲观锁，必须关闭MySQL的自动提交，其常用的加锁方式是 select * from table where … for update。

记录锁：记录锁就是对表中的记录加锁，其实就是行锁，一次锁定一行，否则退化为间隙锁。
记录锁加锁语法：

#对id=1的记录加记录锁，id列必须是唯一索引列或者主键索引列，否则退化为间隙锁
select * from  user where id=1 for update;
#查询结果只能是一条数据，否则退化为间隙锁，如下加锁方法就会退化为间隙锁
select * from  user where id>1 and id<3  for update;

间隙锁：间隙锁是MySQL InnoDB引擎在可重复读的隔离级别下解决幻读引入的一种锁机制，间隙锁不仅仅锁住间隙内的数据，同时不允许其他客户端往这个间隙内插入数据。

#对id大于10小于15之间加间隙锁
select * from  user where id>10 and id<15 for update;

对于上面的加锁sql语句，假设表中只有id=11、id=12、id=13三条数据，在当前加锁的客户端事务没有提交的情况下，其他客户端试图往user表中插入一条14的数据是插入不了的。

临键锁：Next-key称之为临键锁，临键锁锁住的是索引本身及索引之前的间隙，是一个左开右闭的区间，当SQL使用到非唯一索引时候，会给匹配到的数据加上临键锁，临键锁也只有在隔离级别为RR（REPEATABLE-READ：可重复读）级别才生效。
如下user表数据，age字段有非唯一索引，隔离级别为RR。

id	age	name
1	12	张三
2	15	李四
3	18	王五
4	20	赵六
5	21	刘七

在事务A中执行如下SQL：

select * from user age<18 for update;

此时user表中存在临键锁(15, 18]。
在事务A还没有提交的情况下，事务B中执行如下SQL，会被阻塞，因为临键锁锁定了区间(15, 18]。

insert into user  values (6,17,'胡九');

意向锁：意向锁是表级锁，分为意向共享锁和意向排他锁，它的存在是为了协调行级锁和表级锁的关系，InnoDB允许行锁和表锁同时存在，意向锁不会与行级锁冲突。

• 意向锁是一种不与行级锁冲突的表锁，事务如果想要获得某些行数据的共享锁，就必须先获得表的意向共享锁，同理事务如果想要获取某些数据的排他锁，也必须先获取该表的意向排他锁。
• 意向锁相互之间是互相兼容的，与行级共享锁、排他锁也是兼容的，但是和表级共享锁、排他锁不兼容。
• 当事务想对表加锁的时候，会先看表中是否有意向锁，如果有意向锁就会等锁释放，再去尝试加锁，减少了锁的查询。

开发常用的SQL的加锁情况

SQL类型	加锁类型	加锁方式
insert into …	排它锁	MySQL自动加锁
update …	排它锁	MySQL自动加锁
delete …	排它锁	MySQL自动加锁
select * from table	无锁	MySQL自动加锁
select * from table for update	排它锁	手动加锁
select * from table lock in share mode	共享锁	手动加锁

查询正在被阻塞的SQL语句：

SELECT * FROM information_schema.INNODB_TRX;

什么是死锁？
死锁就是两个或两个以上的线程互相持有对方想要的资源，且各自处于阻塞等待获取资源的情况，造成线程无限制的阻塞等待下去，以至于程序无法执行下去，这就是死锁。

死锁产生的条件？

• 互斥条件：资源同一时刻只能被一个进程使用。
• 请求与保持条件：一个进程占有一个资源，且在等待另外一个资源，但是另外一个资源被其他进程占用。，
• 不可剥夺条件：占有的资源不能被强行抢占，除非自己释放资源。
• 循环等待条件：多个进程相互持有对方的资源，且相互等待获取对方的资源，形成了环路。

以上是产生死锁的四个必要条件，缺一不可，那如果想要打破死锁，就只需要破坏其中任何一个条件即可。

MySQL避免死锁的一些方法：

• 尽量基于主键或者唯一索引更新数据。
• 控制单次事务中操作的数据量。
• 控制单次事务中设计的表数量。
• 一个事务中涉及到多个表加锁的时候，加锁解锁尽量保证顺序一致。
• 数据检索时候尽量能够用到索引，尽量避免无索引导致的表级锁。

锁分析的方法
SQL如下：

show status like 'innodb_row_lock%';

执行结果：

• Innodb_row_lock_current_waits：当前正在等待锁定的数量。
• Innodb_row_lock_time：等待总时长，从系统启动到现在锁定总时间长度。
• Innodb_row_lock_time_avg：平均等待时长，每次等待所花平均时间。
• Innodb_row_lock_time_max：最大等待时长，从系统启动到现在等待最长的一次所花的时间。
• Innodb_row_lock_waits：等待此时，统计系统启动后到现在总共等待的次数。

如有不正确的地方请各位指出纠正。