mysql 笔记(三)-InnoDB存储引擎 锁定机制

数据库锁机制是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序锁设计的一种规则.
Mysql 由于其自身架构的特点,存在多种数据存储引擎,每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计,所以各存储引擎的锁定机制也有比较大的区别.

锁分类

操作粒度

Mysql 各存储引擎使用了三种级别的锁机制:表级锁,行级锁和页级锁.

	行锁	表锁
MyISAM		√
InnoDB	√	√

表级锁

表级锁是Mysql各存储索引中最大颗粒度的锁定机制.该锁定机制最大特点是实现逻辑非常简单,带来的系统负面影响最小.所以获取锁和释放锁的速度很快.

表级锁每次操作都锁住整张表，并发低。锁定颗粒度大带来缺点就是出现锁定资源争用的概率也会变高，导致并发度大大打折扣。 使用表级锁的主要是MyISAM,MEMORY，CSV 等一些非事务性存储引擎。

常用命令如下：
手动增加表锁

lock table 表名称1 read|write，表名称2 read|write；

查看 表上加过的锁：

show open tables;

删除表锁：

unlock tables;

行级锁

行级锁最大的特点就是锁定对象的颗粒度很小，由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

由于锁定资源的颗粒度小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了， 而且行级锁也最容易发生死锁。

使用行级锁的主要是InnoDB存储引擎。InnoDB行锁是通过对索引数据页上的记录加锁实现，主要实现算法有3种： Record Lock、Gap Lock 和 Next-key Lock。

记录锁(Record Locks)

记录锁简称为行锁，是InnoDB存储引擎的3中行锁算法之一， 是对表中单个行记录加锁。 RC、RR隔离级别都支持。

记录锁 是锁住记录，锁住索引记录，而不是真正的数据记录。如果要锁的列没有索引，进行全表记录加锁， 记录锁也是排它锁（X），会阻塞 其他事务对其插入、更新、删除。
例如

select * from product where id=1 for update;

会在 id==1的记录上加记录锁， 达到阻止其他事务插入、更新、删除id=1 这一条记录， 这里需要注意的时候， id列必须为唯一索引或主键列，否则上述语句加的锁就会变成临键锁。 同时查询语言必须为精准匹配（=）、不能为>、<、like等，否则也会退化成临键锁。

在通过主键索引与唯一索引对数据进行update 操作时，也会对该行数据加记录锁–id 列为主键列或唯一索引列

update set age=50 where id =1

间隙锁(Gap Locks)

间隙锁是Innodb 在RR隔离级别下为了解决幻读问题时引入的锁机制。间隙锁是InnoDB中行锁的算法之一，使用间隙锁锁住的是一个区间。Gap Lock 的作用是为了阻止多个事务将记录插入到同一个范围内，导致Phantom Problem 问题的产生。

例如

      select * from product where  id BETWEEN 100 and 300  for   update; 

以上语句 防止其他事务将value 200插入colunn中product.id 。无论该列中是否已有这样的值，因为该范围中所有现有值之间的间隙都是锁定的。
间隙可能跨越单个索引值，多个索引值，甚至为空。间隙锁是性能和并发性之间权衡的一部分，并且在某些事务隔离级别而非其他级别中使用。

对于使用唯一索引来锁定唯一行来锁定行的语句就不需要间隙锁定，例如

      select * from product where    id=500

如果id未建立索引或索引不唯一，则该语句会锁定前面的间隙。可以通过不同的事务将冲突的锁保持在间隙上。例如，事务A可以在间隙上保留一个共享的间隙锁（间隙S锁），而事务B可以在同一间隙上保留排他的间隙锁（间隙X锁）。允许冲突的间隙锁的原因是，如果从索引中清除记录，则必须合并由不同事务保留在记录上的间隙锁。

可以通过一下两种方式来显式的关闭Gap lock：
- 将事务的隔离级别设置为Read Committed；
- 将参数innodb_locks_unsafe_for_binlog 设置为1

在上述配置下，除了外键约束和唯一性检查依然需要的Gap lock，其余情况仅使用Record Lock进行锁定。上述设置破坏了事务的隔离性，并且对于replication可能会导致数据不一致性。

临键锁(Next-Key Locks)

Next-Key locks 仅支持RR隔离级别；Next-Key locks结合了Gap lock 和Record lock的一种锁定算法，解决的是当前读情况下的幻读。Next-Key locks可以理解为一种特殊的间隙锁，每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该行数据行的临键锁时，会锁住一段左开右闭区间的数据。

InnoDB 中行级锁是基于索引实现的，采用Next-key lock的锁定技术称为Next-key locking， 假如一个索引有100、175、200、300 这个四个值，那么该索引可能被Next-key locking 的区间为（-∞，100] (100,175] (175,200] (200,300] (300,+∞) 若事务T1 通过next-key locking锁定的如下范围： (100,175] (175,200] 当插入新的记录190时，则锁定的范围会变成（100,175] （175,190] （190,200]

但是，当查询的索引含有唯一属性时，InnoDB 存储引擎会对next-key lock进行优化， 将其降级为Record lock，仅锁住索引本身，而不是范围。

例如 创建测试表t，并插入数据

create table t(a int PRIMARY key);
 INSERT INTO t SELECT 1; INSERT INTO t SELECT 2; INSERT INTO t
       SELECT 5; 

表t共有1,2，5三个值，接下来执行的

sql	会话A	会话B
1	begin;
2	select * from mysql_study.t where a=5for update;
3		begin;
4		insert into mysql_study.t select 4;
5		commit;

在会话A中首先对a=5进行X锁定。由于a是主键且唯一，因此锁定的仅是5这个值，而不是（2,5）这个范围，这样在会话B中插入值4而不会阻塞，可以立即插入并返回，在此过程中锁定由Next-key-lock算法降级为了Record Lock，从而提高应用的并发性。

在RR 隔离级别，InnoDB对于记录加锁行为都是采用Next-Key Lock，但是当SQL 操作含有唯一索引时，InnoDB会对Next-Key Lock进行优化，降级为RecordLock，仅锁住索引本身而非范围。

• select … from 语句：InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁；
• select … from lock in share mode语句：追加了共享锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
• select … from for update语句：追加了排他锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
• update … where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
• delete … where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
• insert语句：InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。

以“update student set name=‘张三’ where id=3 ”操作为例， 分析InnoDB 在RR隔离级别下 对不同索引的加锁行为，

主键加锁
加锁行为：仅在id=10的主键索引记录上加X锁。

唯一键加锁
加锁行为： 现在唯一索引id上加X锁，然后在id=3 的主键索引记录上加X锁。

非唯一键加锁
加锁行为：对满足id=3条件的记录和主键分别加X锁，然后在（2，李四）-（3，王五）、（3，王五）-（3，王柳）、（3，王柳）-（4，马六）范围分别加Grap 锁。

无索引加锁
加锁行为： 表里所有行和间隙都会加 X锁。（当没有索引时，会导致全表锁定，因为InnoDB引擎锁机制是基于索引实现的记录锁定）。

页级锁

页级锁是Mysql 中比较独特的一种锁级别， 页级锁的特点是锁定颗粒度介于行级锁和表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也是介于行级锁和表级锁之间的。

操作类型

共享锁和排他锁

InnoDB 实现了标准的行级锁，可分为共享锁（S）和排他锁（X）
共享锁（S）：又称读锁，允许持有锁的事务读取行；
排他锁（X）：互斥锁，写锁；允许持有锁的事务更新或删除行；

如果事务T1 在行r上持有共享锁，不同的事务2对行r上锁的请求会按以下方式执行：

• 如果T2请求的是共享锁（S），可以立即获取，此时T1和T2同时占有行的共享锁；
• 如果T2请求的是排他锁（X），需求等待T1释放后才能获取；
  

共享锁和排他锁的兼容性如下：

兼容性	S	X
S	兼容	不兼容
X	不兼容	不兼容

从上表可知,:

• 读锁会阻塞写(X),但是不会堵塞读(S)
• 而写锁则会把读(S)和写(X) 都阻塞.

对于InnoDB 在RR 隔离级别而言,对于update,delete和insert 语句,会自动给涉及数据集加排它锁(X),对于普通select 语言,innodb 不会加任务锁.如果想在select 操作的时候加上S锁或者X锁,需要手动加锁:

//共享锁
select * from 表名 lock in share mode
 
//排他锁
select * from 表名 for update

读的时候,用select * from 表名 lock in share mode 获取共享锁, 主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行update或者delete操作.

读的时候,用select * from 表名 for update 获取排它锁, 但是如果当前事务也需要对该记录进行更新操作,则有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用select…for update 方式获得排它锁.

意向锁(Intention Locks)

InnoDB引擎支持多粒度锁定, 这种锁定允许事务在行级上的锁和表级上的锁同时存在,为了支持在不同粒度上进行加锁操作,InnoDB存储引擎还支持意向锁.意向锁是将锁定的对象分为多个层次,意味着事务希望在更细粒度上进行加锁.如图所示:

如果需要对页上记录r进行上X锁,那么分别需要对数据库A、表、页上意向锁IX，最后对记录r上X锁。如果其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。

意向锁是表级别的锁，其目的是为了让InnoDB中的行锁和表锁更高效的共存， 意向锁又分为共享意向锁（IS）和意向排它锁（IX）。

• 意向共享锁（IS）：事务有意向对表中的某行加共享锁（S）

-- 事务要获取些行的S锁，必须先获得标的IS锁
select column from table  ... Lock in share mode;

• 意向排它锁（IX）： 事务有意向对表中的某些行家排它锁（X）

--事务要获取某些行的X锁，必须先获得标的IX锁
select column from table  ... for update；

意向锁是一种不与行级锁冲突的表级锁，是InnoDB 自动加的， 不需要用户干预；意向锁只在InnoDB下存在的内部锁。

InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求，意向锁和行级锁的兼容性如下：

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

此处的S 和X 指的是表锁， 意向锁不会为难意向锁，也不会为难行级共享锁和排它锁，但是会为难表级共享锁（S）和排它锁（X）。 意向锁和意向锁之间永远都是兼容的，因为当你无论加行级的X/S锁，都会自动获取表级的IX/IS。例如你有10个事务，对不同的10行加了行级锁X锁，那么这个时候就存在10个IX锁。 这个10IX锁的存在的目的是就是假如此时有个事务，想对整个表加排它锁(X），那么它不需要遍历每一行是否有X/S锁， 而是看有没有存在意向锁，只要存在一个意向锁，那么这个事务就加不了表级排它锁，要等上面10个IX全部释放才可以。

操作性能

从操作的性能可以分为乐观锁和悲观锁。

悲观锁

悲观锁是指数据处理过程，将数据处于锁定状态，一般使用数据库的锁机制试下，从广义上将，行锁、表锁、读锁、写锁、共享锁、排他锁等都属于悲观锁。

乐观锁

乐观锁是相对于悲观锁而言的， 它不是数据库体用的功能，需要开发者自己去实现。在数据库操作时，想法很乐观，认为这次的操作不会导致冲突，因此在数据库操作时并不做任何的特殊处理，即不加锁，而是在进行事务提交时再去判断是有冲突。

乐观锁实现的关键点是冲突检测。 乐观锁实现原理：

• 使用版本字段
  先给数据表增加一个版本（version）字段，每操作一次，将那条记录的版本号加1. version 是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。
  

• 使用时间戳（Timestamp）
  与使用version版本字段相似，同样需要给在数据表增加一个字段，字段类型使用timestamp
  时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳
  进行对比，如果一致则提交更新，否则就是版本冲突，取消操作。

除了自己手动实现乐观锁之外，许多数据库访问框架也封装了乐观锁的实现，比如
hibernate框架。MyBatis框架大家可以使用OptimisticLocker插件来扩展。

死锁与解决方案

死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一个互相等待的现象。

以下介绍常见的死锁现象和解决方案：

表锁死锁

用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图
访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要
等用户A释放表A才能继续，这就死锁就产生了。

解决方案：
这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分
析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个
资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任
何时刻都应该按照相同的顺序来锁定资源。

行锁死锁

场景1：
如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等
价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发
生阻塞或死锁。

解决方案
SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于
有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

场景2：
两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁，如下图：

解决方案：
在同一个事务中，尽可能做到一次锁定所需要的所有资源；按照id对资源排序，然后按顺序进行处理。

共享锁转换为排它锁

事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于
事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，
此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经
有一个排他锁请求，并且正在等待事务A 释放其共享锁。

事务A: 
select * from dept where deptno=1 lock in share mode; //共享锁,步骤1

update dept set dname='java' where deptno=1;//排他锁,步骤3

事务B: 
update dept set dname='Java' where deptno=1;//由于1有共享锁，没法获取排他锁，需等待，步骤二2

解决方案：
方案一：对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操
作；
方案二：使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。

死锁排查

MySQL提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。

• 查看死锁日志

show engine innodb status; //命令查看近期死锁日志信息。

使用方法：1、查看近期死锁日志信息；2、使用explain查看下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：系统启动后到现在总共等待的次数
  如果等待次数高，而且每次等待时间长，需要分析系统中为什么会有如此多的等待，然后着
  手定制优化。