Mysql的锁

一、写在前面

MySQL加锁的目的是什么

数据库的锁是为了解决事务的隔离性问题，为了让事务之间相互不影响，每个事务进行操作的时候都会对数据加上一把特有的锁，防止其他事务同时操作数据。

MySQL的锁是基于什么实现的

数据库里面的锁是基于索引实现的，在Innodb中我们的锁都是作用在索引上面的，当我们的SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，如果没有命中索引的话，那我们锁的就是整个索引树（表锁）

二、并发事务访问相同记录

读读情况

并发事务相继读取相同记录，允许发生

写写情况

这种情况会出现脏写的问题，任何一种隔离级别都不允许这种问题的发生，是通过加锁来实现的。这个所谓的锁其实是一个内存中的结构。
当一个事务想对一条记录作出修改时，首先会看看内存中有没有与这条记录相关的锁结构，当没有的时候就会在内存中生成一个锁结构与之相关联。比如，事务T1要对这条记录做改动，就需要生成一个锁结构与之关联：

锁结构两个比较重要的属性：

trx信息：表示锁结构与哪个事务有关
is_waiting：表示当前事务是否在等待

锁获取的状态：
获取锁成功：内存中生成了对应的锁结构，而且is_waiting的值为false。（除了隐式锁）
获取锁失败：内存中生成了对应的锁结构，而且is_waiting的值为true，事务需要等待
不加锁：内存中没有生成锁结构，可以直接操作。（不包括隐式锁）
释放锁：释放锁，发现还有事务等待锁，修改对应锁结构is_waiting的值为true

读-写或写-读情况

读-写或写-读，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生脏读、不可重复读、幻读的问题。

并发问题的解决情况

怎么解决脏读、不可重复读、幻读的问题，有两种解决方案：

• 方案一：读操作利用多版本并发控制(MVCC),写操作进行加锁

所谓MVCC，就是生成一个ReadView，通过ReadView找到符合条件的记录版本。
查询语句只能读到在生成ReadView之前已提交事务所做的修改。
写操作针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读写操作并不冲突。

• 方案二：读写操作都采用加锁的方法

脏读的产生是因为当前事务读取了另一个未提交事务写的一条记录，如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，也就不会有脏读问题的产生
不可重复读的产生是因为当前事务先读取一条记录，另一个事务对该记录做了改动之后并提交，当前事务再次读取时会获得不同的值，如果当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，不可重复读的问题就不可能发生
幻读问题的产生是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新纪录，当前事务再次读取该范围的记录时发现新插入的记录，采用加锁的方式解决幻读问题就会有一些麻烦，因为当前事务在第一次读取记录时幻影记录并不存在，所以读取的时候加锁不知道给谁加锁

三、锁的分类

基于锁的属性分类：共享锁、排他锁。

基于锁的粒度分类：行级锁（(innodb )、表级锁（ innodb、myisam)、页级锁（ innodb引擎)、记录锁、间隙锁、临键锁、自增锁。

基于锁的状态分类：意向共享锁、意向排它锁。

基于加锁的态度分类：悲观锁、乐观锁。

3.1基于数据操作类型分类：共享锁、排他锁

• 共享锁又称读锁，简称S锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。
  共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。
• 排他锁又称写锁，简称X锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。
  排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题。

锁定 读

对于读操作来说，既可以加共享锁，也可以加排他锁
对读取的记录加共享锁，如果别的事务想要获取 加该锁记录 的X锁，它们会阻塞

SELECT ... LOCK IN SHARE MODE;
#或
SELECT ... FOR SHARE;     #(8.0新增)

对读取的记录加排他锁，如果别的事务想要获取 加该锁记录 的X锁 和S锁，它们会阻塞

SELECT ... FOR UPDATE;

写操作

平时用到的写操作无非是增、删、改这三种。对于写操作来说，只能加排他锁

DELETE

先在B+树中定位到记录，然后获取X锁，最后执行 delete mark 操作（添加删除标记）

UPDATE

情况一：未修改主键值，并且被更新的列所占用的存储空间未改变。
定位到B+树的记录位置，获取X锁，修改值。
情况二：未修改主键值，并且被更新的列至少有一个所占用的存储空间改变。
定位到B+树的记录位置，获取X锁，彻底删除记录（而不是delete mark），然后再新增记录。新插入的记录由insert操作提供的隐式锁进行保护
情况三：修改记录的键值。
定位到B+树的记录位置，获取X锁，执行DELETE操作，然后再执行INSERT操作。

INSERT

一般情况下，新插入记录受隐式锁保护，不生成对应的锁结构。

3.2基于数据操作的粒度分类：表级锁、页级锁、行锁

1.表锁

该锁会锁定整张表，是MYSQL中最基本的锁策略，并不依赖于存储引擎， 粒度大，加锁简单，开销最小，由于表锁会一次将整个表锁起来，所以可以很好的避免死锁问题。缺点是出现锁资源征用的概率也会最高，导致并发率大打折扣。

1.1表级别的S锁、X锁

表级别的锁一般用在执行 ALTER TABLE 或 DROP TABLE的DDL语句时，然后再执行增删改查时会阻塞。

lock table user read(write); 

1.2意向锁

意向锁要解决的问题：
现在由T1、T2两个事务，T2试图在该表级别上应用共享或排他锁，这时T2要去依次检查各个页或行是否存在锁，比较麻烦；如果存在意向锁，T2在锁定表前不必检查各个页或行锁，只需要检查表上的意向锁。简单来说就是给 更大一级别 的空间示意里面是否已经上过锁。
在数据表的场景中，如果我们给某一行数据加上了排他锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排他锁了。
意向锁也是表级锁，也可分为读意向锁（IS 锁）和写意向锁（IX 锁）。
分为如下情况：
某条记录加S锁时，需要先在表级别加IS锁。
某条记录加X锁时，需要先在表级别加IX锁。
加表级别S锁时，此表不能加有IX锁。
加表级别X锁时，此表不能加有IS、IX锁。
意向锁是为了，在加表级别S、X锁时，快速判断表中记录是否被上锁，避免遍历该表的所有记录。

什么时候释放表锁？
使用 unlock tables 显示释放锁
会话持有其他表锁时执行 lock table 语句会释放会话之前持有的锁
会话持有其他表锁时执行 start transaction 或者 begin 开启事务时，也会释放之前持有的锁。

1.3自增锁（AUTO-INC锁）

AUTO-INC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。

主要实现方式有两种：
采用AUTO-INC锁。执行插入语句时，加一个表级别的AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT列分配递增的值，插入执行完毕，锁释放。
采用一种轻量级的锁（mutex，MySQL 从 5.1.22 版本开始引入）。生成自增值后释放，而不是要等插入完成才释放锁。

innodb_autoinc_lock_mode来控制使用哪种锁：
值为0，一律使用AUTO-INC锁
值为1，插入数量确定使用轻量级锁，不确定使用AUTO-INC锁
值为2，一律使用轻量级锁（不同事务自增列值交叉，为任何给定语句插入的行生成的值可能不是连续的，主从复制不安全）,提高了插入数据的并发性

注意事项：
当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO-INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。

AUTO-INC 锁具有如下特点：
AUTO-INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

1.4元数据锁（MDL锁）

MDL的作用是，保证读写的正确性。
当对一个表做增删改查操作时，加MDL读锁；当要对表做结构变更操作时，加MDL写锁

2.InnoDB的行锁

行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问；

特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高；
行锁的原理
行锁的原理和索引有关。

InnoDB 是聚簇索引，也就是 B+树的叶节点既存储了主键索引也存储了数据行。而 InnoDB 的二级索引的叶节点存储的则是主键值，所以通过二级索引查询数据时，还需要拿对应的主键去聚簇索引中再次进行查询。

单行记录行锁原理

下面以两条 SQL 的执行为例，讲解一下 InnoDB 对于单行数据的加锁原理。

聚簇索引执行修改

update user set age = 10 where id = 49;

二级索引执行修改

update user set age = 10 where name = 'Tom';

第一条 SQL 使用主键索引来查询，则只需要在 id = 49 这个主键索引上加上写锁；

第二条 SQL 则使用二级索引来查询，则首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加写锁。

多行记录行锁原理

update user set age = 10 where id > 49;

MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录，然后 InnoDB 引擎会将第一条记录返回并加锁，接着 MySQL Server 发起更新改行记录的 UPDATE 请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有匹配的记录为止。

2.1记录锁（Record Lock）

记录锁：事务在加锁后锁住的只是表的某一条记录。(官方命名LOCK_REC_NOT_GAP)

大致触发条件：
精准条件命中，并且命中的条件字段是唯一索引。
例如：update user_info set name=’张三’ where id=1 ，这里的id是唯一索引。
当 SQL 语句无法使用索引时，会进行全表扫描，这个时候 MySQL 会给整张表的所有数据行加记录锁，再由 MySQL Server 层进行过滤。但是，在 MySQL Server 层进行过滤的时候，如果发现不满足 WHERE 条件，会释放对应记录的锁。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。
所以更新操作必须要根据索引进行操作，没有索引时，不仅会消耗大量的锁资源，增加数据库的开销，还会极大的降低了数据库的并发性能。

记录锁的作用：加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

2.2间隙锁（Gap Lock）

间隙锁：在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。

比如下面的表里面的数据ID 为 1,4,5,7,10 ,那么会形成以下几个间隙区间，-n-1区间，1-4区间，7-10区间，10-n区间 （-n代表负无穷大，n代表正无穷大）

大致触发条件：范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复读)的事务级别中。

例如：对应上图的表执行 select * from user_info where id>1 and id<4 (这里的id是唯一索引) ，这个SQL查询不到对应的记录，那么此时会使用间隙锁。

注意事项：

Infimum：表示页面最小记录
Supremum：表示页面最大记录
间隙锁作用：防止幻读问题。

2.3临键锁 (Next-Key Lock)

临键锁：是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

**例如：**下面表的数据执行 select * from user_info where id>1 and id<=13 for update ;

会锁住ID为 1,5,10的记录；同时会锁住，1至5,5至10,10至15的区间。

大致触发条件：范围查询并命中，查询命中了索引。

临键锁的作用：结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入。

2.4插入意向锁（LOCK_INSERT_INTENTION）

插入意向锁是一种特殊的间隙锁（Insert Intention Lock）表示插入的意向，只有在 INSERT 的时候才会有这个锁。

一个事务插入一条记录，需要判断插入位置是否被别的事务加了gap锁（包含next-key锁）。如果有的话，插入操作需要等待，直到加gap锁的事务提交。

3.3从对待锁的态度分类：乐观锁、悲观锁

从乐观锁和悲观锁这两个名字可以看出这两种锁是两种对待数据并发的思维方式，需要注意的是，乐观锁和悲观锁并不是锁，而是锁的设计思想。

悲观锁

顾名思义，悲观锁是基于一种悲观的态度类来防止一切数据冲突，它是以一种预防的姿态在修改数据之前把数据锁住，然后再对数据进行读写，在它释放锁之前任何人都不能对其数据进行操作，直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁，然后才可以对数据进行操作，一般数据库本身锁的机制都是基于悲观锁的机制实现的。

特点：可以完全保证数据的独占性和正确性，因为每次请求都会先对数据进行加锁， 然后进行数据操作，最后再解锁，而加锁释放锁的过程会造成消耗，所以性能不高。

注意：select … for update 语句执行过程中所有扫描的行都会被上锁，因此在MySQL中用悲观锁必须确定使用了索引，而不是全表扫描，否则会把整个表锁住
悲观锁不适用的场景较多，它存在一些不足，因为悲观锁大多数情况下依靠数据库的锁机制来实现，以保证程序的并发性，同时这样对数据库的性能开销影响也很大，特别是长事务而言，这样的开销往往无法承受，这时就需要乐观锁。

乐观锁

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新数据的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者CAS机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

1.乐观锁的版本号机制
在表中设计一个版本字段version，第一次读的时候会获取version字段的值，然后对数据进行更新或者删除操作时，会执行UPDATE…SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了修改，修改就不会成功
2.乐观锁的时间戳机制
时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前获得的时间戳进行比较
秒杀案例：

1.查出商品库存
select quantity from items where id=1001;
2.根据商品信息生产订单
insert into orders (item_id) values(1001);
3.修改商品库存
updaste items set quantity=quantity-num,version=version+1 where id=1001 and version=#{version};