MySQL 锁机制
感谢，转自 https://blog.csdn.net/user2025/article/details/115394211
https://www.cnblogs.com/crazylqy/p/7821481.html
锁
锁是计算机协调多个线程访问同一共享资源的机制，主要用于解决多线程访问同一共享资源的并发安全问题
锁分类
（1）从性能分为：乐观锁和悲观锁
MySQL中采用版本号控制，JVM中使用CAS
（2）从数据库操作类型分为：读锁（共享锁）、写锁（排他锁） 【读锁和写锁都属于悲观锁】
读锁（共享锁)：针对相同行记录，多个读操作可以同时进行，但任何事务都不能进行
写锁（排他锁)：获取排他锁的事务既能读数据，又能修改数据。获取到写锁的事务为完成之前，会阻塞其他事务获取写锁或读锁。
（3）从颗粒度划分为，表锁和行锁
注意：
（1）读锁、写锁都属于行级锁，即事务1 对商品A 获取写锁，和事务2对商品B 获取写锁互相不会阻塞的。
（2）如果sql语句使用了行锁，当SQL未使用索引而使用全表扫描的时候，行级锁会变成表锁。
（3）锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。
（4）Innodb引擎执行普通的修改、删除sql语句时，会给修改和删除的行加行锁。
共享锁（读锁，读写互斥，读读互不影响）
事务A使用共享锁获取了某条（或某些）记录时，事务B可以读取这些记录，可以继续添加共享锁，但是不能修改这些记录（当事务C对这些数据修改或删除时，会进入阻塞状态，直至锁等待超时或者事务A提交）
- 如何使用共享锁和释放共享锁
# 加锁 SELECT ... LOCK IN SHARE MODE # 释放锁 commit; rollback;
- 作用
SELECT … LOCK IN SHARE MODE在读取的若干行记录加共享锁（share lock），其他事务只能对这些行查询而不能修改这些记录。多个事务可以对相同的行记录加共享锁，因此获取了共享锁的事务不一定能修改行数据
- 使用场景： 读取结果集的最新版本，同时防止其他事务产生更新该结果集
例如：并发下对商品库存的操作
排他锁（写锁，独占锁）
select … for update在对读取的行记录加排他锁，只允许获取到该排他锁的事务修改该行记录，防止其他事务对该行进行修改，就像普通的update语句执行时会加行锁一样。
- 如何使用排他锁
# 加排他锁 select ... for update //排他锁 # 释放锁 commit; rollback;
共享锁和排他锁的区别
（1）一旦某个事务获取了排他锁，其他事务就无法再获取排他锁。
多个事务可以对相同行数据加共享锁。
（2）给指定行加了共享锁的事务，不一定能修改该行数据，因为其他事务也可能对该行加了共享锁或排他锁； 给指定行加了排他锁的事务，则可以修改加了排他锁的该行数据
表锁
使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。
表锁一般在数据迁移时使用。
意向锁：意向共享锁和意向排他锁
某行加共享锁的前提是：该行数据所在表会先获取到意向共享锁。
某行加排他锁的前提是：该行数据所在表会先获取到意向排他锁。
注意：意向共享锁和意向排他锁均是表锁，无法手动创建。
为什么需要加入意向锁
意向锁是为了告知mysql该表已经存在数据被加锁，而不需要逐行扫描是否加锁，提搞加锁的效率。
单个表锁定
lock tables saas.goods read,saas.account write; // 给saas库中的goods表加读锁,account表加写锁 unlock tables; //解锁
全局表锁定
FLUSH TABLES WITH READ LOCK; // 所有库所有表都被锁定只读 unlock tables; //解锁
注意： 在客户端和数据库断开连接时，都会隐式的执行unlock tables。如果要让表锁定生效就必须一直保持连接。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
行锁
- 行锁是锁一行或者多行记录
- MySQL的行锁是基于索引，行锁是加在索引上，而不是加在行记录上的。
如上图所示，数据库中有1个主键索引和1个普通索引，图中的sql语句是基于普通索引查询，命中4条记录，此时一把行锁就锁定两条记录，而其他事务修改这两条记录中的任意一条，都会一直阻塞【获取锁的事务没有执行commit之前】，下图就是上图没有执行commit语句时的情况。
行锁升级为表锁（行锁实际是给索引加锁，如果没用索引而全表扫描，则会给全表加锁）
上图中where条件中，虽然template_name建立普通索引，但使用or关键字，导致template_name的索引失效，从而进行了全表扫描，锁定了整张表。
修改、删除某一行记录，且未提交事务时，该行会一直被行锁锁定
窗口1中删除某一行，但没有提交。窗口2中更新该行会一直处于阻塞中。
记录锁
- 行锁：行锁是命中索引，一把锁锁的是一张表的一条记录或多条记录
- 记录锁：记录锁是在行锁的衍生锁，记录锁锁的是表中的某一条记录，记录锁出现的条件必须是：精确命中索引，且索引是唯一索引（比如主键id、唯一索引列）
间隙锁
下表中（见图一），id为主键，number字段上有非唯一索引的二级索引，有什么方式可以让该表不能再插入number=5的记录？
图一
根据上面生活中的例子，我们自然而然可以想到，只要控制几个点，number=5之前不能插入记录，number=5现有的记录之间不能再插入新的记录，number=5之后不能插入新的记录，那么新的number=5的记录将不能被插入进来。
那么，mysql是如何控制number=5之前，之中，之后不能有新的记录插入呢（防止幻读）？
答案是用间隙锁，在RR级别下，mysql通过间隙锁可以实现锁定number=5之前的间隙，number=5记录之间的间隙，number=5之后的间隙，从而使的新的记录无法被插入进来。
间隙是怎么划分的？
注：为了方面理解，我们规定（id=A,number=B）代表一条字段id=A,字段number=B的记录，（C，D）代表一个区间，代表C-D这个区间范围。
图一中，根据number列，我们可以分为几个区间：（无穷小，2），（2，4），（4，5），（5，5），（5,11），（11，无穷大）。
只要这些区间对应的两个临界记录中间可以插入记录，就认为区间对应的记录之间有间隙。
例如：区间（2，4）分别对应的临界记录是（id=1,number=2），（id=3，number=4），这两条记录中间可以插入（id=2,number=3）等记录，那么就认为（id=1,number=2）与（id=3，number=4）之间存在间隙。
很多人会问，那记录（id=6，number=5）与（id=8，number=5）之间有间隙吗？
答案是有的，（id=6，number=5）与（id=8，number=5）之间可以插入记录（id=7，number=5），因此（id=6,number=5）与（id=8,number=5）之间有间隙的，
间隙锁锁定的区域
根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。
图一中，where number=5的话，那么间隙锁的区间范围为（4,11）；
间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）
innodb自动使用间隙锁的条件：
（1）必须在RR级别下
（2）检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）
接下来，通过实际操作观察下间隙锁的作用范围
图三 表结构
案例一：
```` session 1: start transaction ; select * from news where number=4 for update ; session 2: start transaction ; insert into news value(2,4);#（阻塞） insert into news value(2,2);#（阻塞） insert into news value(4,4);#（阻塞） insert into news value(4,5);#（阻塞） insert into news value(7,5);#（执行成功） insert into news value(9,5);#（执行成功） insert into news value(11,5);#（执行成功） ````
检索条件number=4,向左取得最靠近的值2作为左区间，向右取得最靠近的5作为右区间，因此，session 1的间隙锁的范围（2，4），（4，5），如下图所示：
间隙锁锁定的区间为（2，4），（4，5），即记录（id=1,number=2）和记录（id=3,number=4）之间间隙会被锁定，记录（id=3,number=4）和记录（id=6,number=5）之间间隙被锁定。
因此记录（id=2,number=4），（id=2,number=2），（id=4,number=4），（id=4,number=5）正好处在（id=3,number=4）和（id=6,number=5）之间，所以插入不了，需要等待锁的释放，而记录(id=7,number=5)，（id=9,number=5），（id=11,number=5）不在上述锁定的范围内，因此都会插入成功。
案例二：
```` session 1: start transaction ; select * from news where number=13 for update ; session 2: start transaction ; insert into news value(11,5);#(执行成功) insert into news value(12,11);#(执行成功) insert into news value(14,11);#(阻塞) insert into news value(15,12);#(阻塞) update news set id=14 where number=11;#(阻塞) update news set id=11 where number=11;#(执行成功) ````
检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，因此，session 1的间隙锁的范围（11，无穷大），如下图所示：
此表中没有number=13的记录的，innodb依然会为该记录左右两侧加间隙锁，间隙锁的范围（11，无穷大）。
有人会问，为啥update news set id=14 where number=11会阻塞，但是update news set id=11 where number=11却执行成功呢？
间隙锁采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入，此图间隙锁锁定的区域是（11，无穷大），也就是记录（id=13,number=11）之后不能再插入记录，update news set id=14 where number=11这条语句如果执行的话，将会被插入到（id=13,number=11）的后面，也就是在区间（11，无穷大）之间，由于该区间被间隙锁锁定，所以只能阻塞等待，而update news set id=11 where number=11执行后是会被插入到（id=13,number=11）的记录前面，也就不在（11，无穷大）的范围内，所以无需等待，执行成功。
案例三：
```` session 1: start transaction ; select * from news where number=5 for update; session 2: start transaction ; insert into news value(4,4);#(阻塞) insert into news value(4,5);#(阻塞) insert into news value(5,5);#(阻塞) insert into news value(7,11);#(阻塞) insert into news value(9,12);#(执行成功) insert into news value(12,11);#(阻塞) update news set number=5 where id=1;#(阻塞) update news set id=11 where number=11;#(阻塞) update news set id=2 where number=4 ;#（执行成功） update news set id=4 where number=4 ;#（阻塞） ````
检索条件number=5,向左取得最靠近的值4作为左区间，向右取得11为右区间，因此，session 1的间隙锁的范围（4，5），（5，11），如下图所示：
有人会问，为啥insert into news value(9,12)会执行成功？间隙锁采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入，（id=9,number=12）很明显在记录（13,11）的后面，因此不再锁定的间隙范围内。
为啥update news set number=5 where id=1会阻塞？
number=5的记录的前面，后面包括中间都被封锁了，你这个update news set number=5 where id=1根本没法执行，因为innodb已经把你可以存放的位置都锁定了，因为只能等待。
同理，update news set id=11 where number=11由于记录（id=10,number=5）与记录（id=13,number=11）中间的间隙被封锁了，你这句sql也没法执行，必须等待，因为存放的位置被封锁了。
案例四：
session 1: start transaction; select * from news where number>4 for update; session 2: start transaction; update news set id=2 where number=4 ;#(执行成功) update news set id=4 where number=4 ;#(阻塞) update news set id=5 where number=5 ;#(阻塞) insert into news value(2,3);#(执行成功) insert into news value(null,13);#(阻塞)
检索条件number>4,向左取得最靠近的值4作为左区间，向右取无穷大，因此，session 1的间隙锁的范围（4，无穷大），如下图所示：
session2中之所以有些阻塞，有些执行成功，其实就是因为插入的区域被锁定，从而阻塞。
next-key锁【临键锁】
next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身，InnoDB默认加锁方式是next-key 锁。
上面的案例一session 1中的sql是：select * from news where number=4 for update ;
next-key锁锁定的范围为间隙锁+记录锁，即区间（2，4），（4，5）加间隙锁，同时number=4的记录加记录锁。
select * from news where number=4 for update ;
next-key锁锁定的范围为间隙锁+记录锁，即区间（2，4），（4，5）加间隙锁，同时number=4的记录加记录锁，即next-key锁的锁定的范围为（2,4],(4,5]。
记录锁、间隙锁、临间锁的区别
update news set number=0 where id>15
sql默认加的是next-key锁。根据上图，next-key锁的区间为（-∞,1],（1,5]，（5,9]，（9，11]，(11,+∞），上面id>15，实际上next-key锁是加在[11，+∞）这个范围内，而不是（15，+∞）这个范围内。注意：需要使用锁的字段必须加索引，因为锁是加在索引上的，没有索引则加的表锁。
