MySQL 锁机制

优质博文：IT-BLOG-CN

定义：锁是计算机协调多个进程或线程并发访问某一资源的机制。

一、表锁（偏读）

MyISAM 引擎，开销小，加锁快，无死锁、锁定粒度大、发生锁冲突的粒度最高，并发度低。
【1】手动增加表锁：lock table 表名1 read(write),表名2 read(write),其他；
【2】查看那些表加锁：show open tables；
【3】释放表：unlock tables，也可以在客户端断开的时候自动释放；

【结论】： 当 session1 对 my_lock 表加了 read 表锁后，①、不能对其它表进行操作。②、当 session 对 my_lock 进行写操作时，会挂起排队等待解锁。当 session1 对 my_lock 表加了 write 表锁后，①、当 session 读 my_lock 表时，会阻塞等待 session1 释放锁。

表锁主要是 MyISAM 引擎的特点，主要用于查询操作。MyISAM 在执行查询语句前，会自动给涉及到的所有表加读锁，在执行增删改之前，会给所有的表加写锁。

【如何分析表锁定】： 可以通过检查table_locks_waited 和 table_locks_immediate状态来分析系统上的表锁定。

show status like 'table%';

 ■ table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每获取锁一次值加1；
 ■ table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁）；

二、行锁（偏写）

行锁就是针对数据表中行记录的锁。比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。

行锁分为如下两种：
共享锁（Shared Locks）： 简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁；
独占锁（Exclusive Locks）： 简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁；

读取一条记录时需要获取一下该记录的S锁，其实这是不严谨的。如果只是普通的读，那么是不会加锁的。想要在读取记录时获取记录的锁有两种SELECT语句：
【1】对读取的记录加S锁：

SELECT ... LOCK IN SHARE MODE;

【2】对读取的记录加X锁:

SELECT ... FOR UPDATE;   

写操作所加的锁：
【1】DELETE： 对一条记录做 DELETE操作的过程其实是先在 B+树中定位到这条记录的位置，然后获取这条记录的X锁，然后再执行 delete mark操作。
【2】UPDATE： ① 如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在 B+树中定位到这条记录的位置，然后再获取记录的 X锁；② 如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在 B+树中定位到这条记录的位置，然后获取记录的 X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。也就是会获取 X锁和隐式锁。③ 如果修改了该记录的键值，则相当于在原记录上做 DELETE操作之后再来一次 INSERT操作，加锁操作就需要按照 DELETE和 INSERT的规则进行了。
【3】INSERT： 通过一种称之为隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

行锁偏向 InnoDB 引擎，开销大，加锁慢、会出现死锁、锁定粒度小、发生冲突的概率低，并发度高。

InnoDB 与 MyISAM 最大的不同是：①、一个支持事务，②、采用了行级锁。

【结论】： 当 session1 对一行数据修改，但未提交时，session2 修改行数据时会阻塞，但是可以查询，但查到的是旧数据。

取消自动提交：set autocommit=0;

【无索引行锁升级为表锁】： 当索引失效后，会导致此问题。
【间隙锁危害】： 当我们使用范围条件而不是相等条件检索数据，并请求共享锁或排它锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做 “间隙（GPA）”，InnoDB 也会对这个 “间隙” 加锁，这种锁机制就是所谓的“间隙锁”（Next-Key锁），解决幻读问题。
因为 Query 执行过程中通过范围进行查找，他会锁定整个范围内索引的索引键值，即使这个键值不存在。间隙锁有一个致命的弱点，就是当锁定一个范围之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据，在某些场景下这可能会对性能造成很大的危害。
【如何加行锁】：

【行锁分析命令如下】：

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：系统启动后到现在总共等待的次数。

三、隐试锁 Gap Lock以及 next-key lock

Gap Lock以及next-key lock是为了解决幻读的。产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。间隙锁，锁的就是两个值之间的空隙。如下，表t，初始化插入了6个记录，这就产生了7个间隙。

 CREATE TABLE `t` (
   `id` int(11) NOT NULL,
   `c` int(11) DEFAULT NULL,
   `d` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `c` (`c`)
 ) ENGINE=InnoDB;
 
 insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

当你执行 select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。间隙锁之间是不存在冲突的，例如：

这里 session B并不会被堵住。因为表t 里并没有 c=7这个记录，因此 session A加的是间隙锁(5,10)。而 sessionB 也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但它们之间是不冲突的。间隙锁和行锁合称 next-key lock， 每个 next-key lock是前开后闭区间。如果用 select * from t for update要把整个表所有记录锁起来，就形成了7个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

间隙锁造成的死锁： 我用两个 session来模拟并发，并假设往表里插入一条 id=9的数据。

【1】session A 执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10);
【2】session B 执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
【3】session B 试图插入一行(9,9,9)，被session A的间隙锁挡住了，只好进入等待；
【4】session A试图插入一行(9,9,9)，被session B的间隙锁挡住了；
间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的；

四、全表扫描

假如 country列上未建索引，所以只能采用全表扫描的方式来执行这条查询语句，扫描过的行会先加锁，然后再释放掉：

SELECT * FROM hero WHERE country  = '魏' LOCK IN SHARE MODE;

对于UPDATE …和DELETE …的语句来说，在遍历聚簇索引中的记录，都会为该聚簇索引记录加上X型行锁，然后：
【1】如果该聚簇索引记录不满足条件，直接把该记录上的锁释放掉；
【2】如果该聚簇索引记录满足条件，则会对相应的二级索引记录加上 X型行锁。

页锁：开销和加锁时间界于行表锁之间；会出现死锁；锁定粒度界于行表锁之间，并发度一般。（了解即可）