MySQL锁

目录

一、lock与latch

二、锁的类型

1. 两种标准行级锁

2. 意向锁

三、锁的算法        

1. Record Lock

2. Gap Lock

3. Next-Key Lock

四、多版本并发控制（MVCC）

五、锁使用

1. 一致性非锁定读

2. 一致性锁定读

3. 自增长与锁

4. 外键与锁

六、死锁

1. 阻塞

2. 死锁

七、参考资料

---

一、lock与latch

        锁的目的是确保对共享资源的并发访问的正确。MySQL中lock和latch，都可以称为“锁”，这里主要关注lock锁。

        latch一般称为闩锁（轻量级锁），其目的保证并发线程操作的正确性，且没有死锁检测机制。要求锁定时间短，若是锁定时间长则性能非常差。InnoDB存储引擎中，latch分为mutex（互斥量）、rwlock（读写锁）。

        lock锁住是事务对象，即：表、页、行，而且有死锁检测机制。一般释放锁是在事务提交或回滚（不同隔离级别释放时间可能不同）。下图是lock与latch的对比。

lock与latch的对比

二、锁的类型

1. 两种标准行级锁

        InnoDB存储引擎中有两种标准行级锁：

• 共享锁（S Lock）：允许事务读一行数据
• 排他锁（X Lock）：允许事务删除或更新一行数据，防止其他事务访问

        一个事务T1已经获取行R的锁，而另一个事务T2可以获取行R的锁，这种情况下称为锁的兼容性。而锁的不兼容是指两个事务不能同时对行R加锁。如下图所示是两种标准行级锁的兼容性。看出，X锁（排他锁）与任何锁都不兼容，而S锁（共享锁）仅与S锁（共享锁）兼容。

        MySQL中与锁有关系的表在information_schema库下：

• INNODB_TRX（事务表）：当前运行的事务，但此表不能判断锁的情况
• INNODB_LOCKS（锁表）：有关锁的情况，如：事务ID、锁ID、锁类型、锁住的索引等
• INNODB_LOCK_WAITS（锁等待表）：反映当前事务的等待，即：事务与锁的关系

2. 意向锁

        InnoDB存储引擎支持多粒度（granular）锁定，其允许事务在不同级别上锁同时存在。为了支持这种多粒度锁定，InnoDB存储引擎有种额外的锁方式，称之为“意向锁”，是指锁定的对象分为多个层次，意向锁意味着希望在更细粒度上加锁。

        MySQL支持意向锁设计比较简单，为表锁，其目的是在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

• 意向共享锁（IS Lock）：事务想获取一张表中某几行的共享锁
• 意向排他锁（IX Lock）：事务想获取一张表中某几行的排他享锁

        下图所示是表级锁（意向锁）与行锁的兼容性。看出：X与任何锁都不兼容；IX与S不兼容；IX与IS相互兼容。需要注意的是InnoDB存储引擎是行级别的锁，因此意向锁不会阻塞除全表扫描以外的任何请求。

表级锁（意向锁）与行锁的兼容性

        InnoDB不存在锁升级问题，原因是事务访问的页对锁进行管理，采用位图的形式。所以不管是锁住一个页中的一个记录还是多个，其开销一样。 

三、锁的算法        

        InnoDB存储引擎有三种行锁的算法，如下：

• Record Lock：单行加锁
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
• Next-Key Lock：Record Lock + Gap Lock，即：锁定一个范围且包含记录本身

        不同的隔离级别，采用不同的行算法。MySQL默认隔离级别REPEATABLE READ时，采用Next-Key Lock；READ COMMITTED，则采用Record Lock。如下不同情况下的加锁情况。

1. Record Lock

        Record Lock总是锁定聚集索引记录（主键锁定），InnoDB表没有任何索引时，默认隐式主键rowId主键来进行锁定。

mysql> show create table test_lock\G;
*************************** 1. row ***************************
       Table: test_lock
Create Table: CREATE TABLE `test_lock` (
  `id` int(11) DEFAULT NULL COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

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

mysql> select * from test_lock where id = 2 for update;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    2 | lisi |   19 |
+------+------+------+
1 row in set (0.00 sec)

mysql> 

mysql> update test_lock set age=25 where id = 2;
1205 - Lock wait timeout exceeded; try restarting transaction

2. Gap Lock

        Gap Lock的作用是阻止多事务将记录插入到同一个范围内（导致Phantom Problem问题 _ 不可重复读）。 InnoDB存储引擎中，对于INSERT操作时，会检查插入记录的下一条记录是否被锁定，若已经被锁定，则不允许其他事务查询。

        用户两种方式显示关闭Gap Lock。关闭Gap Lock后除外键约束和唯一性检查仍需使用Gap Lock，其余使用Record Lock。注意，关闭Gap Lock破坏了事务的隔离性，对于主从复制会导致数据不一致。

• 隔离级别设置为READ COMMITTED
• innodb_locks_unsafe_for_binlog设置1

3. Next-Key Lock

        Next-Key Lock结合了Record Lock和Gap Lock的一种锁定算法，该锁定技术也称Next-Key Locking，其设计目的是解决Phantom Problem （不可重复读）问题。其加锁格式，如：(-∞,10]，(10,20]，(20,+∞)。

        Phantom Problem问题是指同一事务下，执行相同SQL查询可能导致不同的数据结果，即：不可重复读。        

mysql> show create table test_lock\G;
*************************** 1. row ***************************
       Table: test_lock
Create Table: CREATE TABLE `test_lock` (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from test_lock;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  18 |
|  2 | lisi     |  19 |
|  3 | wangwu   |  20 |
|  4 | zhaoliu  |  35 |
+----+----------+-----+
4 rows in set (0.06 sec)

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

mysql> select * from test_lock where age = 35 for update;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  4 | zhaoliu |   35 |
+----+---------+------+
1 row in set (0.01 sec)

mysql> 

        上述代码，表test_lock的id为聚集索引列，age为辅助索引列。执行SQL语句：

select * from test_lock where age = 35 for update;

        通过辅助索引age查询，根据Next-Key Lock，表test_lock有两个索引，需要分别进行锁定。对于聚集索引时，则Next-Key Lock降级为Record Lock，即：锁住索引本身。对于辅助索引时，则Next-Key Lock锁定一个范围(20,35]，同时会对下一个键值(35,+∞)加上一个Gap Lock。如下测试所示。

-- 聚集索引时，则Next-Key Lock降级为Record Lock，即：锁住索引本身
mysql> update test_lock set name='tcmdf' where id = 4;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> 

-- 辅助索引时，则Next-Key Lock锁定一个范围(20,35]
mysql> insert into test_lock select 6,'aisuo',34;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> 
mysql> update test_lock set name='tcmdf' where age=35;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> 
mysql> 

-- 对下一个键值(35,+∞)加上一个Gap Lock
mysql> insert into test_lock select 6,'aisuo',36;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> insert into test_lock select 6,'aisuo',1892440;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> 

-- 当前范围(20,35]的上一个键值，插入成功
mysql> insert into test_lock select 6,'aisuo',19;
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_lock select 7,'aisuo',20;
1205 - Lock wait timeout exceeded; try restarting transaction

四、多版本并发控制（MVCC）

        多版本并发控制（Multi Version Concurrency Control _ MVCC）是指行记录可能不止一个快照数据，由此带来的并发控制。

        快照数据是指行之前版本的数据，其实现通过undo段来完成。undo段是事务回滚数据，因此快照数据本身并没有额外的开销，也无需加锁。

        不同隔离级别，快照数据获取是不同的。MySQL默认隔离级别REPEATABLE READ时，快照数据是事务开始前的行数据版本；READ COMMITTED，快照数据是读取被锁定行的最新一份快照数据（多事务提交）。

五、锁使用

1. 一致性非锁定读

        一致性非锁定读（consistent nonlocking read）是指InnoDB通过MVCC技术来读取当前执行时间数据库中的行数据。而非锁定读是指无需等待行上的X锁释放，来保证数据一致性。如下图所示。

一致性非锁定读

2. 一致性锁定读

        一致性锁定读是指用户显式对读取行进行加锁来保证数据一致性。InnoDB存储引擎对于SELECT操作有两种一致性锁定读：

• SELECT ... FOR UPDATE：读取行加X锁（其他事务不能加任何锁）
• SELECT ... LOCK IN SHARE MODE：读取行加S锁（其他事务加S锁）

        上述两种锁定，都必须事务提交或回滚后锁才释放。需要注意的是，对于一致性非锁定读，即使读取的行被执行了SELECT ... FOR UPDATE语句，也可以进行读取。

3. 自增长与锁

        InnoDB存储引擎中，每个表有且只有一个自增长列，同时自增长列必须是索引且还是索引定义第一列（主键）。因此，每个含有自增长列的表都有一个自增长计数器（auto-increment counter）。

# 查询自增长列当前值

select max(id) from test_lock for update;

        获取自增长值的两种方式，而互斥量实现获取高并发情况下，值可能不连续。

• AUTO-INC Locking（表锁）：插入时，自增长计数器加1后赋值给自增长列（并发低）
• mutex（互斥量）：自增长值的插入SQL语句完成后立即释放，无需事务完成释放（并发高）

        MySQL中插入的情况有如下4种：

         由参数innodb_autoinc_lock_mode控制自增长的模式，其值如下：

• 0：采用表锁使用AUTO-INC Locking
• 1（默认）：插入类型为simple inserts采用互斥量；bulk inserts采用AUTO-INC Locking
• 2： 插入类型为insert-like，都采用互斥量（性能最高，但是值可能不连续）

4. 外键与锁

        对于外键值的插入或更新时，首先查询父表中的记录，即：SELECT父表。对父表SELECT操作时，不采用一致性非锁定读，这样会导致数据不一致问题。则采用一致性锁定读方式，使用SELECT ... LOCK IN SHARE MODE方式主动对父表加S锁。因此若是父表加上X锁时，子表会被阻塞。

mysql> show create table t_child\G;
*************************** 1. row ***************************
       Table: t_child
Create Table: CREATE TABLE `t_child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test-foreign` (`parent_id`),
  CONSTRAINT `test-foreign` FOREIGN KEY (`parent_id`) REFERENCES `t_parent` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create table t_parent\G;
*************************** 1. row ***************************
       Table: t_parent
Create Table: CREATE TABLE `t_parent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from t_child;
+----+--------+-----------+
| id | name   | parent_id |
+----+--------+-----------+
|  1 | 熊大   |         2 |
|  2 | 熊二   |         2 |
+----+--------+-----------+
2 rows in set (0.00 sec)

mysql> select * from t_parent where id = '2';
+----+--------+
| id | name   |
+----+--------+
|  2 | 熊爸   |
+----+--------+
1 row in set (0.04 sec)

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

mysql> update t_parent set name='熊爹' where id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> insert into t_child select 3,'熊三',2;
1205 - Lock wait timeout exceeded; try restarting transaction

六、死锁

1. 阻塞

        阻塞是指一个事务中加锁需要等待另一个事务的锁释放它占用的对象，则该事务被阻塞。阻塞并不是一件坏事，可以确保其他事务正确执行。

        InnoDB存储引擎中，静态参数innodb_lock_wait_timeout控制阻塞等待的时间，默认50s。而动态参数innodb_rollback_on_timeout控制等待超时时是否对事务进行回滚，默认OFF（抛出异常不回滚）。

        注意的是，默认情况下，InnoDB不回滚超时引发的错误异常，除死锁外。

mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
+----------------------------+-------+
1 row in set (0.02 sec)

2. 死锁

        死锁是指多个事务争夺资源而相互等待，而易发生死锁发生在事务量大、每个事务操作数量大、操作数据大。

        每个事务请求锁发生等待时，需要主动检测是否存在死锁。主动检查死锁的机制是等待图（wait-for graph），要求数据库保存两种信息：

• 锁的信息链表
• 事务等待链表

        两种链表信息存在回路，即：存在死锁。检测到死锁存在时，超出阻塞等待时间（innodb_lock_wait_timeout），则InnoDB引擎会选择回滚undo量最小的事务回滚。注意，InnoDB不回滚超时引发的错误异常，除死锁外。

        下图所示时等待图，T1与T2事务存在回路，存在死锁。

wait-for graph

七、参考资料

Innodb行锁_songtaiwu的博客-CSDN博客_innodb行锁

多版本并发控制（MVCC）_zuodaoyong的博客-CSDN博客_多版本并发控制

MySQL 学习之 innodb_locks_unsafe_for_binlog 参数设置对是否幻读影响_cyb_17302190874的博客-CSDN博客mysql死锁检测原理_mysql死锁问题分析_聪明的孩纸的博客-CSDN博客