锁是数据库区别于文件系统的一个关键特性，锁用于管理共享资源的并发访问，加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。
Mysql中每个存储引擎因此对锁的支持都不太一致，这里只专注于InnoDB存储引擎中的锁，在InnoDB中支持的锁是比较多样的
从粒度上，InnoDB 支持行锁和表锁
- 行锁：锁定某一行
- 表锁：锁定整个表
只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
另外， InnoDB 实现了以下两种行锁
- 共享锁（S），允许事务读取一行数据
- 排他锁（X），允许事务删除或者更新一行数据
InnoDB内部还实现了意向共享锁（IS）和意向排他锁（IX）
InnoDB行锁有三种算法
-
Record Lock
-
Gap Lock
-
Next key Lock
在接下来的文章中将一一介绍
共享锁和排他锁
如果一个事务T1已经获得了行r的共享锁，那么另外一个事务T2可以获取行r的共享锁，这叫锁兼容，但是此时如果事务T3想获取行r的排他锁，那么必须等待T1和T2把行r的共享锁释放，这叫锁不兼容，兼容是指对同一记录锁的兼容情况，具体如下表1-1
意向共享锁和意向排他锁
InnoDB支持多粒度的锁机制，允许事务在行级别和表级别的锁同时存在。为了支持在不同粒度的锁操作，InnoDB支持了一种额外的锁方式，称为意向锁，意向锁分为意向共享锁和意向排他锁
若将上锁的对象看成一棵树，如果需要对最下层的对象上锁，即对最细粒度的对象上锁，首先需要对最粗粒度的对象上锁，如果1-1所示， 如果要对页上的记录r上X锁，那么首先需要对表，页上IX锁，最后才对记录r上X锁
为什么没有意向锁，表锁和行锁不能存?
举个例子(假设行锁和表锁能共存), 事务A锁住表的一行,事务B锁住整个表
但你就会发现一个很明显的问题，事务A既然锁住了某一行，其他事务就不可能修改这一行。这与”事务B锁住整个表就能修改表中的任意一行“形成了冲突。所以，没有意向锁的时候，行锁与表锁共存就会存在问题！
意向锁是如何让表锁和行锁共存的?
场景一: 事务A先申请行X锁, 事务B申请表X锁
有了意向锁之后，事务A在申请行X锁之前，数据库会自动先给事务A申请表的IX锁。当事务B去申请表的X锁时就会失败，因为表上有IX锁之后事务B申请表的X锁时会被阻塞
想想一下如果没有意向锁的话想要实现上述功能如何实现?
首先, 事务B在申请表锁时就需要遍历表中所有行记录/页记录判断是否有行/页已经被X锁锁定 , 如果锁定则不能加锁, 否则可以加锁，这效率就大大下降
场景二: 事务A先申请行S锁, 事务B申请表S锁
事务A在申请行S锁之前，数据库会自动先给事务A申请表的IS锁。当事务B去申请表的S锁时，先会申请表的IS锁,此时虽然表已经有IS锁了, 但是因为IS锁是兼容的, 事务B表的S锁成功
意向锁是表级别的锁, 锁兼容性如下表1-2所示
行锁的三种算法
InnoDB有三种行锁的算法
-
Record Lock: 单个行记录上的锁
-
Gap Lock: 间隙锁, 锁定一个范围但是不包含记录本身
-
Next-Key Lock: Gap Lock + Record Lock , 锁定一个范围并且包含记录本身
Record Lock总是会去锁住索引记录, 如果创建表的时候没有设置索引, 那么Record Lock会使用隐式的主键来锁定
Next-Key Lock是基于Gap Lock 和Record Lock的一种锁定算法,在该算法下, InnoDB的查询都是基于这种算法,例如一个索引有10,11,13和20这几个值,那么可能的锁定区间为
(-∞,10]
(10,11]
(11,13]
(13,20]
(20,+∞)
假如事务A已经用next key lock锁定了
(10,11] (11,13]
那么在插入新的记录12时,锁定的范围变成
(10,11] (11,12] (12,13]
当查询的索引含有唯一属性时, InnoDB会把Next-Key Lock降级为Record Lock作为一种优化
看下面的例子
mysql> drop table if exists t ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table t ( a int primary key );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
接下来按着下图3-1表格顺序执行sql语句
| 时间 | 会话A | 会话B |
|---|---|---|
| 1 | begin | |
| 2 | select * from t where a=5 for update | |
| 3 | begin | |
| 4 | insert into t select 4 | |
| 5 | commit | |
| 6 | commit |
在上面的例子中，在会话A先对a=5那行记录上X锁，由于是主键唯一索引，所以会话A只锁住了a=5那行记录，而不是(2，5]这个范围，所以会话B插入a=4不会阻塞，插入后可以立即返回，即Next-key-Lock降级为Record-Lock
Next-key Lock降级为Record Lock仅在查询的列是唯一索引的情况下才生效。如果是辅助索引，则情况会完全不一样，接下来看一个例子
mysql> create table z (a int ,b int , primary key(a), key(b));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into z select 1,1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into z select 3,1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into z select 5,3;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into z select 7,6;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into z select 10,8;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
表z中以b列加了辅助索引，如果在会话A执行下面的语句
select * from z where b=3 for update
这个sql语句通过b列进行查询, 因为有两个索引，因此需要分别锁定，对于a列，由于是主键唯一索引，那么会对a=5的索引加Record-Lock，对b列由于是辅助索引，而且没有唯一性，则加上了Next-key Lock, 锁定的范围是（1，3], 而且需要特别注意的是，InnoDB会对辅助索引下一个键值加Gap Lock, 那么范围（3，6）也被锁定，总体来说（1，6）这个范围都被锁定
如果会话A还没提交，此时在会话B执行下面语句，会话B则会被阻塞
select * from z where a =5 for share mode
insert into z select 4,2;
insert into z select 6,5;
第一个语句会被阻塞是因为索引a=5的列被X锁锁定（Record Lock）
第二，三个语句会被阻塞时因为(1,6）范围被X锁锁定（Next-key Lock）
如下，会话A先对辅助索引b列锁定，没有提交，之后会话B插入（4,2）记录被阻塞，超时之后打印错误语句
insert into z select 8,6;
insert into z select 2,0;
insert into z select 4,7;
对辅助索引加Gap lock的作用主要是为了防止Phantom Problem（幻读）问题的产生，例如在上面的例子中，会话A已经锁定了b=3列的记录，如果此时没有Gap Lock锁定（3，6）范围的记录，那么会话B可以插入b列为5的记录，会话A可能同一个会话两次执行了select *** where b =5拿到的结果不一致，就会违反ACID原则
用户可以通过两种方式来关闭Gap Lock
- 将事务的隔离级别设置为READ COMMITED
- 将参数innodb_locks_unsafe_for_binlog设置为1
在上述配置下，仅在外键约束检查和唯一性检查仍然需要Gap Lock,其余情况都使用Record Lock，但是取消Gap Lock违反了事务的隔离性，也会导致主从配置的不一致
在InnoDB存储引擎，对于insert操作，则会检查小一条语句是否已经被锁定，若已经被锁定，则不允许查询
InnoDB加锁方法
-
意向锁是 InnoDB 自动加的， 不需用户干预。
-
对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；
-
对于普通 SELECT 语句，InnoDB 不会加任何锁；
事务可以通过以下语句显式给记录集加共享锁或排他锁： -
- 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
- 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁
InnoDB 行锁实现方式
- InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
- 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
- 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，
别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。 - 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。
行锁，表锁，页面锁对比
-
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
-
- 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
- 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
-
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
-
- 最大程度的支持并发，同时也带来了最大的锁开销。
- 在 InnoDB 中，除单个 SQL 组成的事务外，
锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。 - 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
-
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
LOCK TABLES 和 UNLOCK TABLES
Mysql也支持lock tables和unlock tables，这都是在服务器层（MySQL Server层）实现的，和存储引擎无关，它们有自己的用途，并不能替代事务处理。 （除了禁用了autocommit后可以使用，其他情况不建议使用）：
- LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
- UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时，
或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁
LOCK TABLES语法：
- 在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则MySQL 不会给表加锁；
- 事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES会隐含地提交事务；
- COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用UNLOCK TABLES 释放表锁。
正确的方式见如下语句：
例如，如果需要写表 t1 并从表 t 读，可以按如下做：
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;
查看InnoDB锁争用情况
- show status like ‘innodb_row_lock%’
- 查看INFOMATION_SCHEMA架构下的表INNODB_TRX, INNODB_LOCK, INNODB_LOCKS_WAITS
1 show status like ‘innodb_row_lock%’
mysql> show status like "innodb_row_lock%";
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 112309 |
| Innodb_row_lock_time_avg | 28077 |
| Innodb_row_lock_time_max | 51202 |
| Innodb_row_lock_waits | 4 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
2 INNODB_TRX, INNODB_LOCK, INNODB_LOCKS_WAITS
首先看一下三个表的定义
以上文创建的表t为例，在事务A中先锁定a=5的行记录，然后再事务B申请a=b行的行锁，看这三个表的输出情况
事务A先锁定a=5的行记录
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where a=5 for update;
+---+
| a |
+---+
| 5 |
+---+
1 row in set (0.00 sec)
mysql>
事务B尝试去申请a=5的行锁被阻塞
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where a=5 for update;
查看三个表输出
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 2383
trx_state: LOCK WAIT
trx_started: 2020-11-08 14:30:28
trx_requested_lock_id: 2383:26:3:4
trx_wait_started: 2020-11-08 14:30:28
trx_weight: 2
trx_mysql_thread_id: 34
trx_query: select * from t where a=5 for update
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 2382
trx_state: RUNNING
trx_started: 2020-11-08 14:30:11
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 33
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.01 sec)
ERROR:
No query specified
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
lock_id: 2383:26:3:4
lock_trx_id: 2383
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: PRIMARY
lock_space: 26
lock_page: 3
lock_rec: 4
lock_data: 5
*************************** 2. row ***************************
lock_id: 2382:26:3:4
lock_trx_id: 2382
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: PRIMARY
lock_space: 26
lock_page: 3
lock_rec: 4
lock_data: 5
2 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 2383
requested_lock_id: 2383:26:3:4
blocking_trx_id: 2382
blocking_lock_id: 2382:26:3:4
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
从innodb_lock_waits的输出可以看到，事务2382阻塞了2383对锁2382:26:3:4的申请，再看innodb_locks的输出可以看到比较直观的看到锁住的是哪个表，哪个spaceId，锁住的页和记录的等等，从innodb_trx的输出可以看到是执行什么语句导致的阻塞，开始时间，状态等等，用于可以巧妙利用这几个表的输出查看锁的争用或者死锁问题。
锁问题
- 脏读，脏读是指事务读取到了其他事务未提交的数据，一般在事务隔离级别为READ UNCOMMITED才会出现
- 不可重复读，不可重复读是指事务多次读取同一数据集合的内容不一致，一般在事务隔离级别为READ COMMITED会出现
- 丢失更新，是指一个事务的操作会被另外一个事务覆盖，一般是逻辑业务产生，数据库理论上不会产生丢失更新问题
- 死锁，是指两个或者两个以上的事务在执行过程中，因争夺同样的资源造成互相等待的现象
InnoDB避免死锁
- 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT … FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
- 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
- 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
- 通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
- 改变事务隔离级别
如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。
参考
参考：
《MySQL技术内幕 InnoDB存储引擎 第2版.pdf》
https://zhuanlan.zhihu.com/p/29150809
不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
- 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
- 通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
- 改变事务隔离级别
如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。
参考
参考：
《MySQL技术内幕 InnoDB存储引擎 第2版.pdf》
https://zhuanlan.zhihu.com/p/29150809
406
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
