MySQL 基础——「MySQL服务器锁和Innodb锁概述」

锁的概述

并发程序处理中，最基础也最重要的就是解决共享资源的争用，就是针对一个全局变量进行并发的更新和读取。这个全局变量，可以是一个全局计数器，统计某个事件在多线程中发生的次数。例如MySQL自增主键的并发增长处理。
解决资源争用，最直接的想法是引入锁，对并发读写的数据进行保护（更高级的则包括无锁编程—— Lock Free Programming）。

本文主要介绍的是MySQL以及Innodb引擎中的锁，按照用途进行分类。

MySQL锁概述

MySQL 最主要的两种锁特性归纳 ：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度也最高

MySQL 不同的存储引擎支持不同的锁机制。
MyISAM 和 memory 存储引擎采用的是 表级锁；
Innodb 存储引擎既支持行级锁，也支持表级锁，但默认情况下采用行级锁。

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 web 应用；
而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。

MySQL 服务器级别的锁

1.全局锁

可以通过FLUSH TABLES WITH READ LOCK或者设置read_only=1来获取全局读锁，它与任何的表锁都冲突。
全局读锁有以下注意点：

• FLUSH会等待查询完成：将要被FLUSH的表上如果尚有查询未完成，则会等待。

session1：
mysql> begin work;
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep(20),id from user limit 1;
+-----------+----+
| sleep(20) | id |
+-----------+----+
|         0 |  3 |
+-----------+----+
1 row in set (20.00 sec)

session2：
mysql> flush tables with read lock;
堵塞20秒...............
Query OK, 0 rows affected (11.52 sec)

session3:
堵塞过程中，查看：
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:53478
     db: sql_test
Command: Query
   Time: 7
  State: User sleep
   Info: select sleep(20),id from ddd limit 1
*************************** 2. row ***************************
     Id: 2
   User: root
   Host: localhost:53481
     db: sql_test
Command: Query
   Time: 5
  State: Waiting for table flush
   Info: flush tables with read lock
*************************** 3. row ***************************

• 自动提交事务：调用FLUSH TABLES 会自动提交事务
• 调用UNLOCK TABLES 进行解锁

2.命名锁

表锁的一种，服务器在重命名和删除一个表的时候创建。命名锁和普通的表锁冲突也和Innodb的意向锁冲突。如果在表被LOCK的情况下，调用ALTER TABLE RENAME则会挂起。

mysql> lock tables user1 read;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE user1 RENAME user; //挂起

mysql> show full processList\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:53478
     db: sql_test
Command: Sleep
   Time: 39
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: root
   Host: localhost:53481
     db: sql_test
Command: Query
   Time: 10
  State: Waiting for table metadata lock
   Info: alter table user1 rename user
*************************** 3. row ***************************

3.字符锁

通过GET_LOCK()及其相关函数在服务器级别内锁住和释放任意一个字符串。

4.表锁

表锁既可以是显式的也可以是隐式的，显示的可以通过LOCK TABLES来创建。 MySQL允许客户端会话显式地获取表锁，以便与其他会话协作访问表，或者防止其他会话在会话需要独占访问时修改表。会话只能为自己获取或释放锁。一个会话无法获取另一个会话的锁定或另一个会话所持有的释放锁定。

锁定规则：

• LOCK TABLES 会隐式的提交当前客户端的事务
• 用显式锁定LOCK TABLES表，则触发器中使用的任何表也会隐式锁定
• 可以同时锁定多张表
• 禁止访问除了锁定表以外的表，直到锁释放为止。（我觉得这样可以避免锁完后忘记解锁的情况，锁什么用什么，或者说需要用到什么才锁什么）

mysql> lock tables user read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ddd;
ERROR 1100 (HY000): Table 'ddd' was not locked with LOCK TABLES

• commit、rollback 都不会释放表锁

锁定释放规则

• UNLOCK TABLS 方式释放锁，如果存在表被锁定的情况，则会提交当前事务。
• 当调用LOCK TABLES获取锁时，会隐式的释放现有的锁
• 当表被加上表锁时，如果在当前客户端开启一个事务，则会伴随这UNLOCK TABLES调用，表锁将释放

Innodb使用表锁需要注意两点：
- 表锁不是由 innodb 存储引擎管理的，而是由其上一层— MySQL server 负责的，仅当 autocommit=0、innodb_table_locks=1(默认设置)时，innodb 层才知道 MySQL 加的表锁，MySQL server 也才能够感知 innodb 加的行锁，这种情况下，innodb 才能自动识别涉及到的锁
- 在用 lock_tables 对 innodb 表加锁时要注意，要将 autocommit 设为 0，否则 MySQL 不会给表加锁；事务结束前，不要用 unlock tables 释放表锁，因为 unlock tables 会隐含的提交事务；commit 或 rollback 并不能释放用 lock tables 加的表锁，必须用 unlock tables 释放表锁

正确用法：
set autocommit = 0;
lock tables ti write, t2 read, ...;
[do something with tables t1 and t2 here];
commit;
unlock tables;

错误用法：
SET autocommit=0;
LOCK TABLES foo WRITE;
INSERT INTO foo (foo_name) VALUES ('John');
LOCK TABLES bar WRITE; -- Implicit commit
ROLLBACK; -- No effect: data already committed

表锁和全局锁的冲突

• 全局读锁和表锁冲突：如果有表锁存在，无法加上全局读锁。同一个客户端连接，则会报错。不同客户端连接，会堵塞。
• 全局锁和表锁加的顺序不同，效果不一样：先上全局读锁，可以再加表的读锁 LOCK TABLES [NAME] READ，不能加表写锁。

当存在表被锁定（无论是read还是write）时，不允许调用FLUSH TABLES，否则会报以下错误：
mysql> lock tables dici write;
Query OK, 0 rows affected (0.00 sec)

mysql> flush tables with read lock;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

mysql> lock tables dici read;
Query OK, 0 rows affected (0.00 sec)

mysql> flush tables with read lock;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction


不同客户端，则会被堵塞，直到表锁定被释放。
mysql> show full processlist \G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:53478
     db: sql_test
Command: Query
   Time: 5
  State: Waiting for table flush
   Info: flush tables with read lock
*************************** 2. row ***************************
     Id: 2
   User: root
   Host: localhost:53481
     db: sql_test
Command: Sleep
   Time: 11
  State:
   Info: NULL

• 全局读锁存在，如果对表加写锁会报错，支持加读锁。

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from user FOR UPDATE;
ERROR 1223 (HY000): Can't execute the query because 
you have a conflicting read lock

mysql> select * from user LOCK IN SHARE MODE;
+----+---------+------+--------+-----------+
| id | name    | age  | gender | depart_id |
+----+---------+------+--------+-----------+
|  3 | t-dirty |   25 |      0 |         1 |
|  5 | Mircle  |   40 |      0 |         3 |
|  6 | Josn    |   60 |      0 |         4 |
|  7 | Sgodon  |   70 |      0 |         5 |
|  8 | TEST    |  100 |      0 |         5 |
| 10 | tt      |  110 |      0 |         1 |
| 11 | tt      |  130 |      0 |         1 |
| 12 | tt      |  199 |      0 |         1 |
+----+---------+------+--------+-----------+
8 rows in set (0.00 sec)

Innodb层次的锁

Innodb的锁按照不同维度可以分类，有以下几种：

• 锁的精度：行锁、表锁（意向锁和自增锁，MySQL的表锁由服务器自行实现和管理）
• 锁的范围：Record Lock、Gap LOCK、Next-Key LOCK，也是对应这三种锁算法
• 锁的模式：S读锁、X写锁、IS意向读锁、IX意向写锁、自增锁
• 锁的实现方式：显式锁、隐式锁

1.基本锁|行级锁

a.概念

MySQl本身并不支持行级锁，所以这是由Innodb引擎内部实现的。行锁的本质是一个索引记录锁，锁定的是索引上的记录。
基本锁：共享锁(Shared Locks：S锁)与排他锁(Exclusive Locks：X锁)
mysql允许拿到S锁的事务读一行，允许拿到X锁的事务更新或删除一行。
加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁；
加了X锁的记录，不允许其他事务再加S锁或者X锁。

b.锁的兼容矩阵

兼容矩阵	S(共享锁)	X(排他锁)
S(共享锁)	兼容	不兼容
X(排他锁)	不兼容	不兼容

c.隐式锁|显式锁

explicit lock(显式锁)可以是s-lock也可以是x-lock,但是implicit lock(隐式锁)只能是x-lock。
explicit lock 可以通过如下语句实现：

加S锁：select…lock in share mode  
加X锁：select…for update

implicit lock 是指索引记录逻辑上有x-lock，但是实际上内存对象中并不含有这个锁信息。对于聚集索引记录，例如用户插入ID=4的记录，在事务尚未提交时，这时id=4的记录就包含有一个implicit lock。

d.非锁定读

注意点：默认的SQL是不会加锁的，如下语句。因为为了支持更高的并发读取，Innodb引擎支持非锁定读，而且默认的读取方式就是非锁定读。即除非显示的声明（Lock in 或者 FOR UPDATE），默认为无锁读取。

#这条语句不会加锁，后面的例子会验证下。
SELECT * FROM user;

e.行锁分类

行锁S、X锁上做了一些精确的细分，在代码中称作Precise Mode。这些精确的模式,使的锁的粒度更细小。可以减少冲突。

• 间隙锁(Gap Lock),只锁间隙。
• 记录锁(Record Lock) 只锁记录。
• Next-Key Lock(代码中称为Ordinary Lock)，同时锁住记录和间隙.
• 插入意图锁(Insert Intention Lock),插入时使用的锁。

兼容矩阵	G(间隙锁)	I(插入意向锁)	R(记录锁)	N(Next-key Lock)
G(间隙锁)	兼容	兼容	兼容	兼容
I(插入意向锁)	不兼容	兼容	兼容	不兼容
R(记录锁)	兼容	兼容	不兼容	不兼容
N(Next-key Lock)	兼容	兼容	不兼容	不兼容

S锁和S锁是完全兼容的，因此在判别兼容性时不需要对比精确模式。
精确模式的检测，用在S、X和X、X之间。
这个矩阵是从lock0lock.c:lock_rec_has_to_wait()的代码推出来的。从这个矩阵可以看到几个特点：

• INSERT操作之间不会有冲突。
• GAP,Next-Key会阻止Insert。
• GAP和Record,Next-Key不会冲突
• Record和Record、Next-Key之间相互冲突。
• 已有的Insert锁不阻止任何准备加的锁。

2.意向锁(Intention Locks)

a.概念

为了支持在不同的粒度上进行加锁操作，Innodb提供了一种额外的的锁——意向锁。意向锁将锁定对象分成多个层次，意味着事务希望在更细的粒度上进行加锁。

若将上锁的对象看成一颗树，那么对最下层的对方上锁，需要先对上层的节点上锁。
例如事务A希望修改表1的某几行，所以分别需要锁数据库A、表1、页上意向锁IX，最后对记录上X锁。 如果在A希望上锁之前，事务B已经给表1加了S共享锁，这时候锁就不兼容了，需要等待事务B的锁释放。

b.冲突矩阵

随着意向锁的加入，锁类型之间的兼容矩阵也变得愈加复杂：

兼容矩阵	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

c.意向锁的意义

有的人可能会对意向锁的目的并不是完全的理解，我们在这里可以举一个例子：
如果没有意向锁，当已经有人使用行锁对表中的某一行进行修改时，如果另外一个请求要对全表进行修改，那么就需要对所有的行是否被锁定进行扫描，在这种情况下，效率是非常低的；不过，在引入意向锁之后，当有人使用行锁对表中的某一行进行修改之前，会先为表添加意向互斥锁（IX），再为行记录添加互斥锁（X），在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需要通过等待意向互斥锁被释放就可以了。

例子：

A. trx1 BEGIN
B. trx1 给 T1 加X锁。
C. trx2 BEGIN
D. trx2 给 T1 加IX锁（事务被阻塞，等待加锁成功)
E. trx1 给 T1 解锁。
F. trx2 给 T1 的一行记录加S或X锁.

MySQL手册中说法：
intention locks do not block anything except full table requests (for example, LOCK TABLES … WRITE). The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.

特别注意：请看上方表锁的说明，由于意向锁时Innodb管理的，表锁是服务器管理的，所以存在互相不了解对方已经加锁的情况。

3.自增锁

在Innodb存储引擎的内存结构里面，对每一个含有自增长列的表都有一个自增长计数器。当存储引擎启动的时候，都会执行如下操作，来获取当前自增长值：

SELECT MAX(auto_inr_col) FROM t FOR UPDATE;

innodb_autoinc_lock_mode 可以控制锁定的算法，自增锁比较简单，可以参考官网-14.8.1.5 AUTO_INCREMENT Handling in InnoDB。

本文参考文献
1.《MySQL技术内幕 InnoDB存储引擎 第2版》
2.《高性能MySQL 第3版 中文 》
3. MySQL官方5.6、5.7文档
4.《MYSQL内核：INNODB存储引擎 卷1》
·
本文参考资料：
1. MySQL数据库InnoDB存储引擎中的锁机制
2. Next-Key如何解决幻读问题
3. MYSQL Innodb 锁行还是锁表问题探讨
4.锁的意义
本文基于MySQL数据库 5.6版本进行测试