MySQL锁管理

博主： admin
发布时间：2019 年 11 月 21 日
787次浏览
7644字数
分类：

什么是锁？

对共享资源进行并发访问，提供数据的完整性和一致性。

每个数据库的锁实现完全不同：

MyISAM：表锁
InnoDB：行级锁
Oracle：行级锁
Microsoft SQL Server：行级锁 with 锁升级

lock 与 latch 的区别

.	lock	latch
对象	事务	线程
保护	数据库内容	内存数据结构
持续时间	整个事务过程	临界资源
模式	行锁、表锁、意向锁	读写锁、互斥量
死锁	通过waits-for graph、time out等机制进行死锁检测与处理	无死锁检测与处理机制。仅通过应用程序加锁的顺序（latch leveling)保证无死锁的情况发生
存在于	Lock Manager的哈希表中	每个数据结构的对象中

可以使用如下命令直观地看到 latch:

mysql> show engine innodb mutex;
+--------+------------------------+---------+
| Type   | Name                   | Status  |
+--------+------------------------+---------+
| InnoDB | rwlock: log0log.cc:838 | waits=4 |
+--------+------------------------+---------+
1 row in set (0.01 sec)

InnoDB 存储引擎中的锁

S 行级共享锁
X 行级排它锁
IS (意向锁)
IX (意向锁)
AI 自增锁

意向锁

IS/IX：事务想要获得一张表中某几行的共享锁/排他锁。

意向锁是揭示下一层级请求的锁类型。例如，一个支持多粒度锁的数据库模型，可以在数据库 ---> 表 ---> 页 ---> 行级别上都加锁，假如要在 rowid=5 的行上加 X 锁，则在上级数据库 ---> 表 ---> 页上面加上 IX 意向锁。任何意向锁之间都是相容的，另一个事务要在 rowid=6 的行上加 X 锁，也会在上级数据库 ---> 表 ---> 页上面加上 IX 意向锁。

有了意向锁，就可以实现多粒度的锁，例如，假如一个会话正在修改表中的记录，另一个会话要对表加 X 锁，则先对数据库加上 IX 锁，当对表加 X 时，因为 X 锁与表上已经存在的 IX 锁是互斥的，就发生等待。

InnoDB 存储引擎中意向锁都是加在表上的，没有加在数据库和页上的意向锁，即平时大家所说的 InnoDB 存储引擎中意向锁都是表锁的含义。对表加锁不需要意向锁，对行加锁才需要在上一级加意向锁。

直观地察看意向锁：

mysql> set global innodb_status_output_locks=1;
建议打开，在 show engine innodb status 时可以看到更加详细的锁信息，只有在 show 的时候才有影响，另一个弊端是如果锁太多，show 输出
将会过量信息。平时不打开，要查看详细时再临时打开也是可以的。

mysql> begin;
mysql> select * from cyt where id=1 for update;
mysql> show engine innodb status\G
TABLE LOCK table `employees`.`cyt` trx id 9502 lock mode IX

加锁方法

一是 SQL 语句自动加锁，另外一种方法就是手动加锁：

mysql> select * from employees.cyt where id = 1 lock in share mode;

锁的相容性

.	S	X	IS	IX
S	1	0	1	0
X	0	0	0	0

查看锁信息

可以使用如下几种方法查看锁相关信息：

show engine innodb status\G
innodb_trx
innodb_locks
innodb_lock_waits

show engine innodb status 可以查看到详细的锁信息，这个在后面讲 INNODB 锁算法时会讲解到。

innodb_trx 记录事务信息，产生一个活动事务，就可以通过 innodb_trx 查询到这个事务。在 innodb_trx 中记录的 trx_mysql_thread_id 就是 show processlist 中的 ID。

innodb_locks 记录了当会话之间发生锁等待时，会话各自申请锁的信息，注意，只有当会话之间发生锁等待时，该表中才有记录。让会话1持有锁，会话2等待锁，在会话3中就可以看到两个会话各自申请的锁信息。

查看锁持有与锁等待关系

方法一：

mysql> SELECT r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
 b.trx_id blocking_trx_id,
 b.trx_mysql_thread_id blocking_thread,
 b.trx_query blocking_query
 FROM information_schema.innodb_lock_waits w
 INNER JOIN information_schema.innodb_trx b
 ON b.trx_id=w.blocking_trx_id
 INNER JOIN information_schema.innodb_trx r
 ON r.trx_id=w.requesting_trx_id\G

*************************** 1. row ***************************
 waiting_trx_id: 10511
 waiting_thread: 4
  waiting_query: select * from cyt where id=1 for update
blocking_trx_id: 10510
blocking_thread: 3
 blocking_query: NULL
1 row in set, 1 warning (0.00 sec)

方法二：

mysql> select * from information_schema.innodb_lock_waits\G

*************************** 1. row ***************************
requesting_trx_id: 10512
requested_lock_id: 10512:79:3:2
  blocking_trx_id: 10510
 blocking_lock_id: 10510:79:3:2
1 row in set, 1 warning (0.00 sec)

requested_lock_id: 10512:79:3:2 中的 79 是 space no.，3 是 page no.)，2 是 heap no。

如果有 sys 库，查询 sys.innodb_lock_waits 信息会更加详细：

mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2019-11-21 08:54:24
                    wait_age: 00:00:31
               wait_age_secs: 31
                locked_table: `employees`.`cyt`
                locked_index: GEN_CLUST_INDEX
                 locked_type: RECORD
              waiting_trx_id: 10512
         waiting_trx_started: 2019-11-21 08:54:24
             waiting_trx_age: 00:00:31
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 4
               waiting_query: select * from cyt where id=1 for update
             waiting_lock_id: 10512:79:3:2
           waiting_lock_mode: X
             blocking_trx_id: 10510
                blocking_pid: 3
              blocking_query: NULL
            blocking_lock_id: 10510:79:3:2
          blocking_lock_mode: X
        blocking_trx_started: 2019-11-21 08:41:19
            blocking_trx_age: 00:13:36
    blocking_trx_rows_locked: 3
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 3
sql_kill_blocking_connection: KILL 3
1 row in set, 3 warnings (0.04 sec)

锁与并发

locking
concurrency control
isolation
serializability

这4个概念在数据库理论中其实是一个概念。
锁是用来实现并发控制，并发控制用来实现隔离级别，隔离级别是通过锁来进行控制的，锁的目的就是使得事务之间的执行是序列化的。

事务隔离级别

ANSI/ISO 事务隔离级别

事务的隔离性是指一个事务所做的修改对其它事务是不可见的，好似它们是串行执行的。

标准 SQL 通过 ANSI 和 ISO/IEC 定义了4种事务隔离级别。

READ UNCOMMITTED
READ COMMITTED
- DB2、Oracle、Microsoft SQL Server 的默认隔离级别
- 默认都是不符合隔离性的要求的。
REPEATABLE READ
- InnoDB 的默认隔离级别
SERIALIZABLE

隔离级别越低，事务请求的锁越少或者保持锁的时间就越短。
只有 SERIALIZABLE 才严格符合隔离性的要求（但是锁太大，并发不高）。
如果不满足事务的隔离性要求，则有如下问题：脏读、不可重复读、幻读。

脏读：一个事务读取到另一个事务修改但没有提交的数据。
不可重复读：同一个事务重复读取之前已经读取的数据，但发现另一个已经提交的事务修改了或者删除了该数据(两次执行 SELECT 语句得到的结果却不一样)。例如，一个用户查询一行，之后又查询相同行，发现这个数据已经被其它已经提交的事务更改。如果相同查询在同一工作单元中返回不同的结果，就发生了不可重复读。（姜老师把删除记录的场景放在了幻读）
幻读：同一个事务重新运行一个查询返回一个符合查询条件的行集合，发现另一个已提交的事务插入了额外的符合条件的行(多次执行同一SQL语句，后续执行会返回附加行)。例如，一个事务查询employees的记录数，5分钟之后这个事务执行相同的查询，但是记录因为另一个会话插入了一条新员工记录，相比之前返回了更多符合查询条件的数据。

READ COMMITTED 是用来解决脏读的
REPEATABLE READ 是用来解决脏读和不可重复读的
SERIALIZABLE 是用来解决脏读、不可重复读和幻读的的

隔离级别	脏读	不可重复读	幻读
未提交读	可能	可能	可能
已提交读	不可能	可能	可能
重复读	不可能	不可能	可能
串行	不可能	不可能	不可能

Oracle 的事务隔离级别

Oracle 提供了两种自己的事务隔离级别：已提交读和串行。

DB2 的事务隔离级别

可重复读（Repeatable Read, RR) 对应 SERIALIZABLE
读稳定性（Read Stability, RS) 对应 REPEATABLE READ
游标稳定性（Cursor Stability, CS）对应 READ COMMITTED
未提交读（Uncommitted Read, UR）对应 READ UNCOMMITTED

对于 DB2，当游标读取到一行时，该行上会加锁，其它事务不得修改。

INNODB 的事务隔离级别

INNODB 的默认事务隔离级别是 REPEATABLE-READ，但它通过其算法就解决了幻读的问题。
可以通过参数 tx_isolation 来设置隔离级别为 REPEATABLE-READ, read-committed, read-uncommitted。注意参数 tx_isolation 在参数文件中对应的是 transaction_isolation。

INNODB 锁的算法

Record Lock：单个行记录上的锁，只锁定加锁的记录本身。
Gap Lock：锁定一个范围，但不包含记录本身
- 假如一个表有 5、10、30、50 这4条记录，在记录30上加锁，表示锁定该范围(10, 30) ，不能插入在这个范围内的数据，但是可以对10和30做删除或者修改。Gap Lock 解决了幻读问题。
Next-Key Lock： Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身
- 假如一个表有 5、10、30、50 这4条记录，在记录30上加锁，表示锁定范围(10, 30) 的同时，对记录30表身也加锁，不能插入这个范围的数据，同时对记录30不能删除或修改，记录10可以删除或修改。

注意：这些锁还是加上行上的，只是意义各不相同。另外，这些锁，锁住的是索引。

Next Key lock

在会话1上产生锁：

mysql> create table cyt (id int primary key);
mysql> insert into cyt values (10),(11),(13),(20);
mysql> begin;
mysql> select * from cyt where id <= 13 for update;

在会话2查看锁：

mysql> show engine innodb status\G

TABLE LOCK table `employees`.`cyt` trx id 10551 lock mode IX   <-- IX 表示表上有一个IX意向锁
RECORD LOCKS space id 86 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10551 lock_mode X  <-- lock_mode X 表示是 Next Key lock
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;        <-- 记录10上有 Record lock
 1: len 6; hex 000000002932; asc     )2;;
 2: len 7; hex a7000001150110; asc        ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;        <-- 记录11上有 Record lock
 1: len 6; hex 000000002932; asc     )2;;
 2: len 7; hex a700000115011c; asc        ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;        <-- 记录13上有 Record lock
 1: len 6; hex 000000002932; asc     )2;;
 2: len 7; hex a7000001150128; asc       (;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;        <-- 记录20上有 Record lock
 1: len 6; hex 000000002932; asc     )2;;
 2: len 7; hex a7000001150134; asc       4;;

即在 RR 的隔离级别下，锁住的是 (负无穷,10], (10,11], (11,13], (13,20]。
RR 会对游标打开的所有的记录进行加锁，之所以锁住 20 是因为需要一直比对到第一个出现大于13的记录为止（可能有两个13，另外一个13被标记为删除）。
info bits 0 表示这条记录没有被删除，是非0的话，表示这条记录正在被修改或者可能被删除了。

唯一索引列的等值查询

当锁定的是一条记录且当索引含有唯一约束时，Next-Key lock 会降级为 Record lock（Next-Key lock 优化为 Record lock）。

会话1上产生锁：

mysql> begin;
mysql> select * from cyt where id=13 for update;

会话2上查看锁：

mysql> show engine innodb status\G

TABLE LOCK table `employees`.`cyt` trx id 10570 lock mode IX
RECORD LOCKS space id 87 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10570 lock_mode X locks rec but not gap <-- 没有gap的 X lock，即为记录锁
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;    <-- 只对13这个记录加了锁
 1: len 6; hex 000000002942; asc     )B;;
 2: len 7; hex b2000001120128; asc       (;;

这里可以降级为 Record Lock 是因为返回的记录具有唯一性，不会存在幻读问题。
当唯一索引是复合索引时，且查询条件只包含部分列的话，还是有 Gap lock。

Gap locking is not needed for statements that lock rows using a unique index to search for a unique rouw.(This does not include the case that the search condition includes only some columns of a multiple-column unique index;in that case,gap locking does occur.)

非索引列的等值查询

会话1产生锁：

create table cyt(id int not null);      <-- 注意这个表没有定义 id 列为主键列
insert cyt values(10),(11),(13),(20);
begin;
select * from cyt where id=13 for update;

在会话2上查看锁：

mysql> show engine innodb status\G

TABLE LOCK table `employees`.`cyt` trx id 10587 lock mode IX
RECORD LOCKS space id 88 page no 3 n bits 72 index GEN_CLUST_INDEX of table `employees`.`cyt` trx id 10587 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dc100; asc       ;;
 1: len 6; hex 000000002955; asc     )U;;
 2: len 7; hex c0000001190110; asc        ;;
 3: len 4; hex 8000000a; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dc101; asc       ;;
 1: len 6; hex 000000002955; asc     )U;;
 2: len 7; hex c000000119011e; asc        ;;
 3: len 4; hex 8000000b; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dc102; asc       ;;
 1: len 6; hex 000000002955; asc     )U;;
 2: len 7; hex c000000119012c; asc       ,;;
 3: len 4; hex 8000000d; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dc103; asc       ;;
 1: len 6; hex 000000002955; asc     )U;;
 2: len 7; hex c000000119013a; asc       :;;
 3: len 4; hex 80000014; asc     ;;

可见，锁住了所有记录，并且是 GAP 锁（相当于发生了表锁）。
为什么呢？因为这个查询要对全表所有记录都做一个扫描，才能找出所有的 id=13 的行，而 RR 会对游标打开的所有的记录进行加锁。

总结：在 RR 的隔离级别下，当涉及的列没有索引时，会锁住所有记录。

可以看出，默认 RR 隔离级别的锁是很重的。

为什么要保证隔离性

INNODB 之所以把 RR 隔离级别的锁设计的如此重，是为了保证事务的隔离性。

下面是一个不能保证事务隔离性的例子：

会话1：
delete where id <=7;

会话2：
insert into id =3;

会话1先执行但不提交，会话2后执行但先提交，则会话2会影响到会话1。
如果有主从，在从库重放日志时，因为是根据日志序号进行的重放，最终会导致没有数据。

锁的巩固练习

会话1产生锁：

CREATE TABLE cyt(a INT,b INT,PRIMARY KEY(a),KEY(b)); 
INSERT INTO cyt values (1,1),(3,1),(5,3),(7,6),(10,8); 

begin;
SELECT * FROM cyt WHERE b=3 FOR UPDATE;

会话2查看锁的情况：

TABLE LOCK table `employees`.`cyt` trx id 10618 lock mode IX
RECORD LOCKS space id 89 page no 4 n bits 72 index b of table `employees`.`cyt` trx id 10618 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 89 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10618 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000002974; asc     )t;;
 2: len 7; hex d5000001550128; asc     U (;;
 3: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 89 page no 4 n bits 72 index b of table `employees`.`cyt` trx id 10618 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000007; asc     ;;

第一段表示在索引b上加了一个 (1,3] 的锁
第二段表示在主键索引上加了一个记录锁(a=5)
第三段表示在索引b上加了一个(3,6)的锁

所以，如下语句的执行情况是：

SELECT * FROM cyt WHERE a=5 LOCK IN SHARE MODE;     <-- 不能执行成功
INSERT INTO cyt SELECT 4,2;                         <-- 不能执行成功
INSERT INTO cyt SELECT 4,3;                         <-- 不能执行成功
INSERT INTO cyt SELECT 6,3;                         <-- 不能执行成功
INSERT INTO cyt SELECT 6,5;                            <-- 不能执行成功
INSERT INTO cyt SELECT 8,6;                            <-- 执行成功
INSERT INTO cyt SELECT 2,0;                            <-- 执行成功
INSERT INTO cyt SELECT 6,7;                            <-- 执行成功
INSERT INTO cyt SELECT 6,6;                            <-- 不能执行成功

注意：二级索引保存的是 key 和主键，所以不能单独的只看 key 值，还要看主键的值，其实二级索引锁住的不仅仅是二级索引的Key本身，还有对应的value，也就是主键。

二级索引 "1 1 3 6 8" 准确地应该表示为：

「1:1」「1:3」「3:5」 (插入值(6,6)即「6:6」) 「6:7」 (插入值(8,6)即「6:8」) 「8:10」 < -- 锁住的(3,6)其实是「3:5」，「6:7」

(6, 6)不能插入成功，二级索引除了看key，还要看主键的值，(6, 6)在(7, 6)前面，在二级索引锁住的范围(3, 6)中，即(6, 6)中的b=6插在(3,6)中间，而(8, 6)中的b=6插在(3,6)之后。

注意前面 Next Key lock 一节中的例子中，是主键查询，所以在下一个记录上加 Next-Key Lock（与Purge有关，后续会讲），这儿是二级索引查询，所认在下一个记录上加的是 Gap Lock。

插入意向锁（insert intention lock）

插入意向锁的概念

这儿的意向锁并不是之前谈到的意向锁概念，插入意向锁本质上就是个 Gap Lock。普通 Gap Lock 不允许在（上一条记录，本记录）范围内插入数据，插入意向锁相反，允许在（上一条记录，本记录）范围内插入数据。
插入意向锁的作用是为了提高并发插入的性能，多个事务同时写入不同数据至同一索引范围（区间）内，并不需要等待其他事务完成，不会发生锁等待。

插入的过程

假设现在有记录 10，30，50，70 ；且为主键，需要插入记录 25。
1.找到小于等于25的记录，这里是10
2.找到记录10的下一条记录，这里是30
3.判断下一条记录30上是否有锁

判断 30 上面如果没有锁，则可以插入
判断 30 上面如果有 Record Lock ，则可以插入
判断 30 上面如果有 Gap Lock / Next-Key Lock，则无法插入，因为锁的范围是 (10, 30) / （10, 30] ；在30上增加 insert intention lock（此时处于 waiting 状态），当 Gap Lock / Next-Key Lock 释放时，等待的事物（transaction）将被唤醒，此时记录30上才能获得 insert intention lock ，然后再插入记录25

示例

会话1产生锁：

mysql> create table cyt(id int primary key);
mysql> insert into cyt values (10),(30),(50),(70);
mysql> begin;
mysql> select * from cyt where id<=50 for update;

会话2尝试插入值 25：

mysql> insert into cyt values(25);    <-- 发生等待

在会话3上查看锁信息：

TABLE LOCK table `employees`.`cyt` trx id 10726 lock mode IX
RECORD LOCKS space id 92 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10726 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be00000118011c; asc        ;;

---TRANSACTION 10725, ACTIVE 259 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 14, OS thread handle 140664276870912, query id 274 localhost root
TABLE LOCK table `employees`.`cyt` trx id 10725 lock mode IX
RECORD LOCKS space id 92 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10725 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be000001180110; asc        ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be00000118011c; asc        ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000032; asc    2;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be000001180128; asc       (;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000046; asc    F;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be000001180134; asc       4;;

可以看到，在 30 这条记录上加了一个插入意向锁。

将会话1的事务提交，它的锁释放，会话2将拿到插入意向锁，在会话3上查看：

TABLE LOCK table `employees`.`cyt` trx id 10726 lock mode IX
RECORD LOCKS space id 92 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10726 lock_mode X locks gap before rec insert intention
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be00000118011c; asc        ;;

此时，其它会话也可以插入记录。

Read Committed

将隔离级别改为 RC 后，就是我们平常对 Oracle 理解的行锁，下面语句返回三行，将在三行上面产生锁，lock_mode 为 X locks rec but notgap：

mysql> select * from cyt wherea <= 13 for update;

在大部分情况下，RC 隔离级别下没有 GAP 锁，但是一些场景下，线上仍可能出现。
设置了RC 隔离级别后，要设置 binlog_format = row ，否则会出现主从不一致的情况。

当查询条件是非索引列的等值查询：

RR 隔离级别下会锁住每个记录（形成表锁的效果）
RC 隔离级别下只锁住查询条件的记录本身

当查询条件是二级索引列的等值查询:

RC 模式下，二级索引查询的记录上有一个记录锁，对应的聚集索引上有一个记录锁
RR 模式下，二级索引查询的记录上有一个 Next-Key Lock，该记录的下一个记录上有一个 Gap-Lock （二级索引），对应的聚集索引上有一个记录锁

RR 与 RC 的性能对比

不要迷信 Oracle RC，Facebook 和 MySQL 的人都测试过 RR 比 RC 性能更好。

MySQL如何降低锁的开销

通过位图存放锁信息，内存占用少
没有锁升级
锁能够重用

锁重用示例：

在会话1产生锁：

mysql> begin;
mysql> select * from cyt where id=13 for update;
mysql> select * from cyt where id=13 lock in share mode;

在另一个会话通过 show engine 查看锁信息，会看到只有一把锁(X locks rec but notgap)，不会出现两个锁。

另外，MySQL还有隐式锁的概念，例如：

在会话1中对表插入一条记录，在另外一个会话中，只能看到在表上加了一个意向锁：

TABLE LOCK table `employees`.`cyt` trx id 10754 lock mode IX

但是该行上是有record lock的，当另一个会话尝试对该修改时，就可以通过show engine显式地看到锁了。
对于隐式锁，MySQL是如何知道该行上有锁的呢？MySQL 通过行上记录的trx_id是否存在于在线事物列表中来进行判断。在就说明事务活动未提交，有锁。

一致性的非锁定读(MVCC)

在隔离级别为 RU、RC、RR 时，读取(select)操作是不会加锁的，通过行多版本控制(MVCC)的方式读取当前执行时间点的记录。
通过UNDO指针的指向，可以读取前一个版本甚至前几个版本的记录（即通过UNDO来构造版本记录），从而实现快照读（Snapshot Read）。
通过trx_id判断该记录是否被锁住（在线事物列表），从而决定是否要读取之前的版本（UNDO）。

设置锁等待超时

innodb_lock_wait_timeout（默认 50s）

死锁

有两个参数与死锁有关：
innodb_deadlock_detect，默认ON
innodb_print_all_deadlocks，默认OFF，是否将 deadlock 信息打印到 error log。

MySQL 中的死锁，最后一条产生死锁的语句所在的会话会自动回滚事务。

死锁情况一：AB-BA死锁

案例：
用户1和用户2的购物车中都有AB两款产品，付款时库存表中相应行的库存数量减1，用户1的事务顺序是先A后B，用户2的事务顺序是先B后A，当两个用户同时提交时，就可能产生死锁。
解决办法：在应用程序端进行修改，提交购物车订单时，先对订单进行排序，然后再执行提交。这样只会出现锁等待，而不会出现死锁。

死锁情况二：唯一键引起的死锁

在开始之前，我们先了解一下 lock in share mode 产生的 S lock。

在会话1产生锁：

mysql> create table cyt (id int,key(id));  -- 二级索引
mysql> insert into cyt values(1),(10);
mysql> begin;
mysql> select * from cyt where id = 8 lock in share mode;
Empty set(0.00sec)

在会话2上可以看到锁：

TABLE LOCK table `test`.`cyt` trx id 3339 lock mode IS
RECORD LOCKS space id 6 page no 4 n bits 72 index `id` of table `test`.`cyt` trx id 3339 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000000201; asc       ;;

可见，在记录10上有一个Gap锁，锁住的范围是(1, 10)，因为是二级索引，所以是开区间。这样一来，其他事务就无法在这个范围内进行插入操作了(waiting)，而会话1是可以的(注意：请使用 5.6 版本测试，5.7 中没有该问题，哪怕是记录8也是可以插入的)。会话1返回的结果集为空，则表示查询条件不存在，相当于做了一次唯一性检查，此时再插入对应条件的记录，可以确保插入的记录的唯一性。所以，S lock可以用来做唯一性检查，但实际中如果要保证唯一性，我们很少使用 lock in share mode 的语法，而是设置为主键或者唯一索引。所以，唯一键的实现在很大可能上能产生(死)锁。

我们之前聊过插入的过程，当插入一条记录I时，找到小于该记录I的记录A及A的下一条记录B，当B上没有锁或者有Record Lock时，可以插入，如果B上有Gap lock/Next-key Lock，则无法插入，此时会在B上增加insert intention lock。其实在对B进行判断之前，还需要多一步检查：如果记录中有唯一约束，当存在一条记录等于当前插入的记录时，则需要在这个记录加上S Gap-Lock。

大家可能会话，当发现有记录等于当前插入的记录时，为什么不直接报错退出或者提示已存在呢？试想，如果已经存在的记录是标记为删除( delete-mark )，然后等待 purge呢？你还能直接提示已经存在而不让插入，因为被标记为删除的记录会随着事务的提交被真正删除，你也不能直接插入新值，因为之前的事务可能回滚。

下面，我们来看一下唯一键上产生的死锁：

首先，将事务隔离级别设置为 RC。

会话1中产生锁：

mysql> create table cyt (id int not null, unique key(id));
mysql> insert into cyt values(1),(10);
mysql> begin;
mysql> insert into cyt values(8);

会话2和传话3中都执行如下语句，等待锁：

mysql> insert into cyt values(8);

查看锁会话信息：

------------
TRANSACTIONS
------------
Trx id counter 11816
Purge done for trx's n:o < 11813 undo n:o < 0 state: running but idle
History list length 8
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421509924098560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 11815, ACTIVE 4 sec inserting    <--  会话3中的事务，等待对记录8的S Lock的授权
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 4, OS thread handle 140034660558592, query id 79 localhost root update
insert into cyt values(8)
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11815 lock mode S locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000002e25; asc     .%;;
 2: len 7; hex bd000001be0110; asc        ;;

------------------
TABLE LOCK table `employees`.`cyt` trx id 11815 lock mode IX
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11815 lock mode S locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000002e25; asc     .%;;
 2: len 7; hex bd000001be0110; asc        ;;

---TRANSACTION 11814, ACTIVE 5 sec inserting    <--  会话2中的事务，等待对记录8的S Lock的授权
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 140034660824832, query id 78 localhost root update
insert into cyt values(8)
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11814 lock mode S locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000002e25; asc     .%;;
 2: len 7; hex bd000001be0110; asc        ;;

------------------
TABLE LOCK table `employees`.`cyt` trx id 11814 lock mode IX
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11814 lock mode S locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000002e25; asc     .%;;
 2: len 7; hex bd000001be0110; asc        ;;

---TRANSACTION 11813, ACTIVE 315 sec    <--  终端会话1 中的事务，对记录8持有一个Record Lock
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 140034661091072, query id 56 localhost root
TABLE LOCK table `employees`.`cyt` trx id 11813 lock mode IX
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11813 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000002e25; asc     .%;;
 2: len 7; hex bd000001be0110; asc        ;;

将会话1中的事务rollback，会话2中的insert成功，会话3中报deadlock。

为什么不是会话3继续等待，而是产生死锁呢？怎么理解这儿产生的死锁呢？

此时，查看锁信息：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-11-25 16:57:31 0x7f5c5c32e700
*** (1) TRANSACTION:
TRANSACTION 11816, ACTIVE 11 sec inserting    <-- 会话2中的事务，在等待记录10上的插入意向锁（Gap Lock）
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 140034660824832, query id 82 localhost root update
insert into cyt values(8)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11816 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000002e20; asc     . ;;
 2: len 7; hex ba000001bc011c; asc        ;;

*** (2) TRANSACTION:
TRANSACTION 11817, ACTIVE 8 sec inserting    <-- 会话3中的事务，持有记录10的S-Gap Lock，在等待记录10上的插入意向锁（Gap Lock）
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 4, OS thread handle 140034660558592, query id 83 localhost root update
insert into cyt values(8)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11817 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000002e20; asc     . ;;
 2: len 7; hex ba000001bc011c; asc        ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11817 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000002e20; asc     . ;;
 2: len 7; hex ba000001bc011c; asc        ;;

*** WE ROLL BACK TRANSACTION (2)    <-- 回滚了会话3中的事务，所以会话2才插入成功

死锁分析：
因为会话1中的事务回滚了，则记录8标记为删除（delete-mark），而会话2和会话3中的事务都对记录8添加了S-Lock，此时下一个记录，即记录10会继承之前记录8上的锁，所以上面看到了锁的信息都在记录10 上了(锁继承)。

则此时的锁的状态是：
会话2持有记录10的S Gap-Lock，并且等待 X locks gap insert intention lock。
会话3持有记录10的S Gap-Lock，并且等待 X locks gap insert intention lock。

X Lock和S Lock是不兼容的，所以：
会话3的insert intention lock等待会话2的S Gap-Lock的释放；
会话2的insert intention lock等待会话3的S Gap-Lock的释放；
所以产生了死锁（其实还是AB-BA死锁）。

对于这种类型的列锁，线上是无法完全避免的，解决的办法就是程序端重试。

AI自增锁

一个表一个自增列，自增列必须是主键的第一个列，这样，数据库重启后，数据库可以直接找到最后一个自增值，然后 +1 操作后，就可以作为下一个自增列的值了（否则需要全表扫描，因为MySQL自增列的值不是持久化的：出于性能考虑）。

AI自增锁在事物提交前就释放（类似latch，使用完就释放了，如果AI锁在提交后才释放，那其他事物就没法插入了，无法实现并发）。这也是事物回滚后自增值不会跟着回滚，导致自增值不连续的原因（但是这个值连续也没什么意义）。

在看自增锁之前，我们先看一下插入类型。

插入类型

所有的插入都是insert-like
如果插入前能确定行数的，就是 simple inserts
- insert into table_t values(NULL, 1), (NULL, 2);
如果插入前不能确定行数的，就是 bulk inserts
- insert into table_t select * from table_2;
如果部分自增长，部分指定的，就是 mixed-mode inserts
- INSERT INTO t(c1,c2)VALUES(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d');

设置自增并发度

--innodb_autoinc_lock_mode={0|1|2}

0 传统方式
- 在SQL语句执行完之后，AI锁才释放，这样可以保证在这个SQL语句内插入的数据，自增值是连续的，因为在此期间，AI自增锁是被这个SQL持有的，且没有释放。
- 当 insert ... select ... 数据量很大时（比如执行10分钟），那在这个 SQL执行完毕前，其他事物是不能插入的（AI锁未释放）。
1 默认参数（大部分情况设置为1）
- bulk inserts，同传统方式一样，在SQL执行完之后，AI锁才释放
- simple inserts，并发方式，在SQL运行完之前，确定了自增值之后，就可以释放自增锁了
2 所有自增插入都可以并发
- 假如有一个事务A是批量插入多条记录，另一个事务B是插入一条记录，A的做法是：对于每一条记录，申请自增锁，然后插入，然后就释放自增锁，下一条记录继续申请锁、释放锁……，这样一来，B就可以并发插入了。这样做的好处是，对于批量的、耗时的插入，SQL不会长时间的持有AI自增锁，而是插入一条（有且仅插入一条，而 simple inserts 是确定好的M条）语句后就释放，这样可以给别的事物使用，实现并发。这种方式，并发度是增加了，但是性能不一定变好，要不断的申请和释放锁。另外，对于批量插入来说，自增就可能变的不连续了（需要和开发沟通，是否可以接受）。

关于自增的两个参数

auto_increment_increment：步长
auto_increment_offset：初始值

如果两个节点A和B，我们可以在 A 上设置auto_increment_offset为1，步长2，则可以产生1，3，5，7……；在B上设置auto_increment_offset为2，步长2，就可以产生2，4，6，8……
这样做的目的是保证每个节点上产生的自增值是全局唯一的，但这样做并不能用来做双主（比如一些额外的唯一索引能保证全局唯一么？）。

SERIALIZABLE和REPEATABLE READ的区别

SERIALIZABLE 采用两阶段锁来保证隔离性，且无论读还是写，都要加锁。但是这样做了以后，失去了MVCC的特性（非锁定的一致性读）。

现有一张表，记录如下：

mysql> select * from cyt;
+------+------+
| id   | name |
+------+------+
|    1 | cfop |
+------+------+

假如有一个事务1，内容如下：

mysql> begin;
mysql> select name from cyt where id=1;
.....
mysql> select name from cyt where id=1;
mysql> commit;

若有另个一个事务在两条 select 语句之间将该记录做了修改并且提交了，则该事务第2次的查询结果仍然为cfop，即读不阻止写。但是当将隔离级别设置为SERIALIZABLE后，事务2将被阻塞（写等待读）。

-- By 许望（RHCA、OCM、VCP）

最后修改：2020 年 10 月 03 日 02 : 54 PM

如果觉得我的文章对你有用，请随意赞赏

Tags: MySQL暂未分类

MySQL锁管理

admin • 2019 年 11 月 21 日

什么是锁？

对共享资源进行并发访问，提供数据的完整性和一致性。

每个数据库的锁实现完全不同：

MyISAM：表锁
InnoDB：行级锁
Oracle：行级锁
Microsoft SQL Server：行级锁 with 锁升级

lock 与 latch 的区别

.	lock	latch
对象	事务	线程
保护	数据库内容	内存数据结构
持续时间	整个事务过程	临界资源
模式	行锁、表锁、意向锁	读写锁、互斥量
死锁	通过waits-for graph、time out等机制进行死锁检测与处理	无死锁检测与处理机制。仅通过应用程序加锁的顺序（latch leveling)保证无死锁的情况发生
存在于	Lock Manager的哈希表中	每个数据结构的对象中

可以使用如下命令直观地看到 latch:

mysql> show engine innodb mutex;
+--------+------------------------+---------+
| Type   | Name                   | Status  |
+--------+------------------------+---------+
| InnoDB | rwlock: log0log.cc:838 | waits=4 |
+--------+------------------------+---------+
1 row in set (0.01 sec)

InnoDB 存储引擎中的锁

S 行级共享锁
X 行级排它锁
IS (意向锁)
IX (意向锁)
AI 自增锁

意向锁

IS/IX：事务想要获得一张表中某几行的共享锁/排他锁。

直观地察看意向锁：

mysql> set global innodb_status_output_locks=1;
建议打开，在 show engine innodb status 时可以看到更加详细的锁信息，只有在 show 的时候才有影响，另一个弊端是如果锁太多，show 输出
将会过量信息。平时不打开，要查看详细时再临时打开也是可以的。

mysql> begin;
mysql> select * from cyt where id=1 for update;
mysql> show engine innodb status\G
TABLE LOCK table `employees`.`cyt` trx id 9502 lock mode IX

加锁方法

一是 SQL 语句自动加锁，另外一种方法就是手动加锁：

mysql> select * from employees.cyt where id = 1 lock in share mode;

锁的相容性

.	S	X	IS	IX
S	1	0	1	0
X	0	0	0	0

查看锁信息

可以使用如下几种方法查看锁相关信息：

show engine innodb status\G
innodb_trx
innodb_locks
innodb_lock_waits

show engine innodb status 可以查看到详细的锁信息，这个在后面讲 INNODB 锁算法时会讲解到。

innodb_trx 记录事务信息，产生一个活动事务，就可以通过 innodb_trx 查询到这个事务。在 innodb_trx 中记录的 trx_mysql_thread_id 就是 show processlist 中的 ID。

查看锁持有与锁等待关系

方法一：

mysql> SELECT r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
 b.trx_id blocking_trx_id,
 b.trx_mysql_thread_id blocking_thread,
 b.trx_query blocking_query
 FROM information_schema.innodb_lock_waits w
 INNER JOIN information_schema.innodb_trx b
 ON b.trx_id=w.blocking_trx_id
 INNER JOIN information_schema.innodb_trx r
 ON r.trx_id=w.requesting_trx_id\G

*************************** 1. row ***************************
 waiting_trx_id: 10511
 waiting_thread: 4
  waiting_query: select * from cyt where id=1 for update
blocking_trx_id: 10510
blocking_thread: 3
 blocking_query: NULL
1 row in set, 1 warning (0.00 sec)

方法二：

mysql> select * from information_schema.innodb_lock_waits\G

*************************** 1. row ***************************
requesting_trx_id: 10512
requested_lock_id: 10512:79:3:2
  blocking_trx_id: 10510
 blocking_lock_id: 10510:79:3:2
1 row in set, 1 warning (0.00 sec)

requested_lock_id: 10512:79:3:2 中的 79 是 space no.，3 是 page no.)，2 是 heap no。

如果有 sys 库，查询 sys.innodb_lock_waits 信息会更加详细：

mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2019-11-21 08:54:24
                    wait_age: 00:00:31
               wait_age_secs: 31
                locked_table: `employees`.`cyt`
                locked_index: GEN_CLUST_INDEX
                 locked_type: RECORD
              waiting_trx_id: 10512
         waiting_trx_started: 2019-11-21 08:54:24
             waiting_trx_age: 00:00:31
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 4
               waiting_query: select * from cyt where id=1 for update
             waiting_lock_id: 10512:79:3:2
           waiting_lock_mode: X
             blocking_trx_id: 10510
                blocking_pid: 3
              blocking_query: NULL
            blocking_lock_id: 10510:79:3:2
          blocking_lock_mode: X
        blocking_trx_started: 2019-11-21 08:41:19
            blocking_trx_age: 00:13:36
    blocking_trx_rows_locked: 3
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 3
sql_kill_blocking_connection: KILL 3
1 row in set, 3 warnings (0.04 sec)

锁与并发

locking
concurrency control
isolation
serializability

事务隔离级别

ANSI/ISO 事务隔离级别

事务的隔离性是指一个事务所做的修改对其它事务是不可见的，好似它们是串行执行的。

标准 SQL 通过 ANSI 和 ISO/IEC 定义了4种事务隔离级别。

READ UNCOMMITTED
READ COMMITTED
- DB2、Oracle、Microsoft SQL Server 的默认隔离级别
- 默认都是不符合隔离性的要求的。
REPEATABLE READ
- InnoDB 的默认隔离级别
SERIALIZABLE

READ COMMITTED 是用来解决脏读的
REPEATABLE READ 是用来解决脏读和不可重复读的
SERIALIZABLE 是用来解决脏读、不可重复读和幻读的的

隔离级别	脏读	不可重复读	幻读
未提交读	可能	可能	可能
已提交读	不可能	可能	可能
重复读	不可能	不可能	可能
串行	不可能	不可能	不可能

Oracle 的事务隔离级别

Oracle 提供了两种自己的事务隔离级别：已提交读和串行。

DB2 的事务隔离级别

对于 DB2，当游标读取到一行时，该行上会加锁，其它事务不得修改。

INNODB 的事务隔离级别

INNODB 锁的算法

Record Lock：单个行记录上的锁，只锁定加锁的记录本身。
Gap Lock：锁定一个范围，但不包含记录本身
- 假如一个表有 5、10、30、50 这4条记录，在记录30上加锁，表示锁定该范围(10, 30) ，不能插入在这个范围内的数据，但是可以对10和30做删除或者修改。Gap Lock 解决了幻读问题。
Next-Key Lock： Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身
- 假如一个表有 5、10、30、50 这4条记录，在记录30上加锁，表示锁定范围(10, 30) 的同时，对记录30表身也加锁，不能插入这个范围的数据，同时对记录30不能删除或修改，记录10可以删除或修改。

注意：这些锁还是加上行上的，只是意义各不相同。另外，这些锁，锁住的是索引。

Next Key lock

在会话1上产生锁：

mysql> create table cyt (id int primary key);
mysql> insert into cyt values (10),(11),(13),(20);
mysql> begin;
mysql> select * from cyt where id <= 13 for update;

在会话2查看锁：

mysql> show engine innodb status\G

TABLE LOCK table `employees`.`cyt` trx id 10551 lock mode IX   <-- IX 表示表上有一个IX意向锁
RECORD LOCKS space id 86 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10551 lock_mode X  <-- lock_mode X 表示是 Next Key lock
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;        <-- 记录10上有 Record lock
 1: len 6; hex 000000002932; asc     )2;;
 2: len 7; hex a7000001150110; asc        ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;        <-- 记录11上有 Record lock
 1: len 6; hex 000000002932; asc     )2;;
 2: len 7; hex a700000115011c; asc        ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;        <-- 记录13上有 Record lock
 1: len 6; hex 000000002932; asc     )2;;
 2: len 7; hex a7000001150128; asc       (;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;        <-- 记录20上有 Record lock
 1: len 6; hex 000000002932; asc     )2;;
 2: len 7; hex a7000001150134; asc       4;;

唯一索引列的等值查询

当锁定的是一条记录且当索引含有唯一约束时，Next-Key lock 会降级为 Record lock（Next-Key lock 优化为 Record lock）。

会话1上产生锁：

mysql> begin;
mysql> select * from cyt where id=13 for update;

会话2上查看锁：

mysql> show engine innodb status\G

TABLE LOCK table `employees`.`cyt` trx id 10570 lock mode IX
RECORD LOCKS space id 87 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10570 lock_mode X locks rec but not gap <-- 没有gap的 X lock，即为记录锁
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;    <-- 只对13这个记录加了锁
 1: len 6; hex 000000002942; asc     )B;;
 2: len 7; hex b2000001120128; asc       (;;

Gap locking is not needed for statements that lock rows using a unique index to search for a unique rouw.(This does not include the case that the search condition includes only some columns of a multiple-column unique index;in that case,gap locking does occur.)

非索引列的等值查询

会话1产生锁：

create table cyt(id int not null);      <-- 注意这个表没有定义 id 列为主键列
insert cyt values(10),(11),(13),(20);
begin;
select * from cyt where id=13 for update;

在会话2上查看锁：

mysql> show engine innodb status\G

TABLE LOCK table `employees`.`cyt` trx id 10587 lock mode IX
RECORD LOCKS space id 88 page no 3 n bits 72 index GEN_CLUST_INDEX of table `employees`.`cyt` trx id 10587 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dc100; asc       ;;
 1: len 6; hex 000000002955; asc     )U;;
 2: len 7; hex c0000001190110; asc        ;;
 3: len 4; hex 8000000a; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dc101; asc       ;;
 1: len 6; hex 000000002955; asc     )U;;
 2: len 7; hex c000000119011e; asc        ;;
 3: len 4; hex 8000000b; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dc102; asc       ;;
 1: len 6; hex 000000002955; asc     )U;;
 2: len 7; hex c000000119012c; asc       ,;;
 3: len 4; hex 8000000d; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 0000000dc103; asc       ;;
 1: len 6; hex 000000002955; asc     )U;;
 2: len 7; hex c000000119013a; asc       :;;
 3: len 4; hex 80000014; asc     ;;

总结：在 RR 的隔离级别下，当涉及的列没有索引时，会锁住所有记录。

可以看出，默认 RR 隔离级别的锁是很重的。

为什么要保证隔离性

INNODB 之所以把 RR 隔离级别的锁设计的如此重，是为了保证事务的隔离性。

下面是一个不能保证事务隔离性的例子：

会话1：
delete where id <=7;

会话2：
insert into id =3;

锁的巩固练习

会话1产生锁：

CREATE TABLE cyt(a INT,b INT,PRIMARY KEY(a),KEY(b)); 
INSERT INTO cyt values (1,1),(3,1),(5,3),(7,6),(10,8); 

begin;
SELECT * FROM cyt WHERE b=3 FOR UPDATE;

会话2查看锁的情况：

TABLE LOCK table `employees`.`cyt` trx id 10618 lock mode IX
RECORD LOCKS space id 89 page no 4 n bits 72 index b of table `employees`.`cyt` trx id 10618 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 89 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10618 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000002974; asc     )t;;
 2: len 7; hex d5000001550128; asc     U (;;
 3: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 89 page no 4 n bits 72 index b of table `employees`.`cyt` trx id 10618 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000007; asc     ;;

第一段表示在索引b上加了一个 (1,3] 的锁
第二段表示在主键索引上加了一个记录锁(a=5)
第三段表示在索引b上加了一个(3,6)的锁

所以，如下语句的执行情况是：

SELECT * FROM cyt WHERE a=5 LOCK IN SHARE MODE;     <-- 不能执行成功
INSERT INTO cyt SELECT 4,2;                         <-- 不能执行成功
INSERT INTO cyt SELECT 4,3;                         <-- 不能执行成功
INSERT INTO cyt SELECT 6,3;                         <-- 不能执行成功
INSERT INTO cyt SELECT 6,5;                            <-- 不能执行成功
INSERT INTO cyt SELECT 8,6;                            <-- 执行成功
INSERT INTO cyt SELECT 2,0;                            <-- 执行成功
INSERT INTO cyt SELECT 6,7;                            <-- 执行成功
INSERT INTO cyt SELECT 6,6;                            <-- 不能执行成功

二级索引 "1 1 3 6 8" 准确地应该表示为：

「1:1」「1:3」「3:5」 (插入值(6,6)即「6:6」) 「6:7」 (插入值(8,6)即「6:8」) 「8:10」 < -- 锁住的(3,6)其实是「3:5」，「6:7」

插入意向锁（insert intention lock）

插入意向锁的概念

插入的过程

判断 30 上面如果没有锁，则可以插入
判断 30 上面如果有 Record Lock ，则可以插入
判断 30 上面如果有 Gap Lock / Next-Key Lock，则无法插入，因为锁的范围是 (10, 30) / （10, 30] ；在30上增加 insert intention lock（此时处于 waiting 状态），当 Gap Lock / Next-Key Lock 释放时，等待的事物（transaction）将被唤醒，此时记录30上才能获得 insert intention lock ，然后再插入记录25

示例

会话1产生锁：

mysql> create table cyt(id int primary key);
mysql> insert into cyt values (10),(30),(50),(70);
mysql> begin;
mysql> select * from cyt where id<=50 for update;

会话2尝试插入值 25：

mysql> insert into cyt values(25);    <-- 发生等待

在会话3上查看锁信息：

TABLE LOCK table `employees`.`cyt` trx id 10726 lock mode IX
RECORD LOCKS space id 92 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10726 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be00000118011c; asc        ;;

---TRANSACTION 10725, ACTIVE 259 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 14, OS thread handle 140664276870912, query id 274 localhost root
TABLE LOCK table `employees`.`cyt` trx id 10725 lock mode IX
RECORD LOCKS space id 92 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10725 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be000001180110; asc        ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be00000118011c; asc        ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000032; asc    2;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be000001180128; asc       (;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000046; asc    F;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be000001180134; asc       4;;

可以看到，在 30 这条记录上加了一个插入意向锁。

将会话1的事务提交，它的锁释放，会话2将拿到插入意向锁，在会话3上查看：

TABLE LOCK table `employees`.`cyt` trx id 10726 lock mode IX
RECORD LOCKS space id 92 page no 3 n bits 72 index PRIMARY of table `employees`.`cyt` trx id 10726 lock_mode X locks gap before rec insert intention
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 6; hex 0000000029df; asc     ) ;;
 2: len 7; hex be00000118011c; asc        ;;

此时，其它会话也可以插入记录。

Read Committed

将隔离级别改为 RC 后，就是我们平常对 Oracle 理解的行锁，下面语句返回三行，将在三行上面产生锁，lock_mode 为 X locks rec but notgap：

mysql> select * from cyt wherea <= 13 for update;

当查询条件是非索引列的等值查询：

RR 隔离级别下会锁住每个记录（形成表锁的效果）
RC 隔离级别下只锁住查询条件的记录本身

当查询条件是二级索引列的等值查询:

RC 模式下，二级索引查询的记录上有一个记录锁，对应的聚集索引上有一个记录锁
RR 模式下，二级索引查询的记录上有一个 Next-Key Lock，该记录的下一个记录上有一个 Gap-Lock （二级索引），对应的聚集索引上有一个记录锁

RR 与 RC 的性能对比

不要迷信 Oracle RC，Facebook 和 MySQL 的人都测试过 RR 比 RC 性能更好。

MySQL如何降低锁的开销

通过位图存放锁信息，内存占用少
没有锁升级
锁能够重用

锁重用示例：

在会话1产生锁：

mysql> begin;
mysql> select * from cyt where id=13 for update;
mysql> select * from cyt where id=13 lock in share mode;

在另一个会话通过 show engine 查看锁信息，会看到只有一把锁(X locks rec but notgap)，不会出现两个锁。

另外，MySQL还有隐式锁的概念，例如：

在会话1中对表插入一条记录，在另外一个会话中，只能看到在表上加了一个意向锁：

TABLE LOCK table `employees`.`cyt` trx id 10754 lock mode IX

一致性的非锁定读(MVCC)

设置锁等待超时

innodb_lock_wait_timeout（默认 50s）

死锁

有两个参数与死锁有关：
innodb_deadlock_detect，默认ON
innodb_print_all_deadlocks，默认OFF，是否将 deadlock 信息打印到 error log。

MySQL 中的死锁，最后一条产生死锁的语句所在的会话会自动回滚事务。

死锁情况一：AB-BA死锁

死锁情况二：唯一键引起的死锁

在开始之前，我们先了解一下 lock in share mode 产生的 S lock。

在会话1产生锁：

mysql> create table cyt (id int,key(id));  -- 二级索引
mysql> insert into cyt values(1),(10);
mysql> begin;
mysql> select * from cyt where id = 8 lock in share mode;
Empty set(0.00sec)

在会话2上可以看到锁：

TABLE LOCK table `test`.`cyt` trx id 3339 lock mode IS
RECORD LOCKS space id 6 page no 4 n bits 72 index `id` of table `test`.`cyt` trx id 3339 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000000201; asc       ;;

下面，我们来看一下唯一键上产生的死锁：

首先，将事务隔离级别设置为 RC。

会话1中产生锁：

mysql> create table cyt (id int not null, unique key(id));
mysql> insert into cyt values(1),(10);
mysql> begin;
mysql> insert into cyt values(8);

会话2和传话3中都执行如下语句，等待锁：

mysql> insert into cyt values(8);

查看锁会话信息：

------------
TRANSACTIONS
------------
Trx id counter 11816
Purge done for trx's n:o < 11813 undo n:o < 0 state: running but idle
History list length 8
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421509924098560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 11815, ACTIVE 4 sec inserting    <--  会话3中的事务，等待对记录8的S Lock的授权
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 4, OS thread handle 140034660558592, query id 79 localhost root update
insert into cyt values(8)
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11815 lock mode S locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000002e25; asc     .%;;
 2: len 7; hex bd000001be0110; asc        ;;

------------------
TABLE LOCK table `employees`.`cyt` trx id 11815 lock mode IX
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11815 lock mode S locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000002e25; asc     .%;;
 2: len 7; hex bd000001be0110; asc        ;;

---TRANSACTION 11814, ACTIVE 5 sec inserting    <--  会话2中的事务，等待对记录8的S Lock的授权
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 140034660824832, query id 78 localhost root update
insert into cyt values(8)
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11814 lock mode S locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000002e25; asc     .%;;
 2: len 7; hex bd000001be0110; asc        ;;

------------------
TABLE LOCK table `employees`.`cyt` trx id 11814 lock mode IX
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11814 lock mode S locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000002e25; asc     .%;;
 2: len 7; hex bd000001be0110; asc        ;;

---TRANSACTION 11813, ACTIVE 315 sec    <--  终端会话1 中的事务，对记录8持有一个Record Lock
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 140034661091072, query id 56 localhost root
TABLE LOCK table `employees`.`cyt` trx id 11813 lock mode IX
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11813 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000002e25; asc     .%;;
 2: len 7; hex bd000001be0110; asc        ;;

将会话1中的事务rollback，会话2中的insert成功，会话3中报deadlock。

为什么不是会话3继续等待，而是产生死锁呢？怎么理解这儿产生的死锁呢？

此时，查看锁信息：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-11-25 16:57:31 0x7f5c5c32e700
*** (1) TRANSACTION:
TRANSACTION 11816, ACTIVE 11 sec inserting    <-- 会话2中的事务，在等待记录10上的插入意向锁（Gap Lock）
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 140034660824832, query id 82 localhost root update
insert into cyt values(8)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11816 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000002e20; asc     . ;;
 2: len 7; hex ba000001bc011c; asc        ;;

*** (2) TRANSACTION:
TRANSACTION 11817, ACTIVE 8 sec inserting    <-- 会话3中的事务，持有记录10的S-Gap Lock，在等待记录10上的插入意向锁（Gap Lock）
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 4, OS thread handle 140034660558592, query id 83 localhost root update
insert into cyt values(8)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11817 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000002e20; asc     . ;;
 2: len 7; hex ba000001bc011c; asc        ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 96 page no 3 n bits 72 index id of table `employees`.`cyt` trx id 11817 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000002e20; asc     . ;;
 2: len 7; hex ba000001bc011c; asc        ;;

*** WE ROLL BACK TRANSACTION (2)    <-- 回滚了会话3中的事务，所以会话2才插入成功

对于这种类型的列锁，线上是无法完全避免的，解决的办法就是程序端重试。

AI自增锁

在看自增锁之前，我们先看一下插入类型。

插入类型

所有的插入都是insert-like
如果插入前能确定行数的，就是 simple inserts
- insert into table_t values(NULL, 1), (NULL, 2);
如果插入前不能确定行数的，就是 bulk inserts
- insert into table_t select * from table_2;
如果部分自增长，部分指定的，就是 mixed-mode inserts
- INSERT INTO t(c1,c2)VALUES(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d');

设置自增并发度

--innodb_autoinc_lock_mode={0|1|2}

0 传统方式
- 在SQL语句执行完之后，AI锁才释放，这样可以保证在这个SQL语句内插入的数据，自增值是连续的，因为在此期间，AI自增锁是被这个SQL持有的，且没有释放。
- 当 insert ... select ... 数据量很大时（比如执行10分钟），那在这个 SQL执行完毕前，其他事物是不能插入的（AI锁未释放）。
1 默认参数（大部分情况设置为1）
- bulk inserts，同传统方式一样，在SQL执行完之后，AI锁才释放
- simple inserts，并发方式，在SQL运行完之前，确定了自增值之后，就可以释放自增锁了
2 所有自增插入都可以并发
- 假如有一个事务A是批量插入多条记录，另一个事务B是插入一条记录，A的做法是：对于每一条记录，申请自增锁，然后插入，然后就释放自增锁，下一条记录继续申请锁、释放锁……，这样一来，B就可以并发插入了。这样做的好处是，对于批量的、耗时的插入，SQL不会长时间的持有AI自增锁，而是插入一条（有且仅插入一条，而 simple inserts 是确定好的M条）语句后就释放，这样可以给别的事物使用，实现并发。这种方式，并发度是增加了，但是性能不一定变好，要不断的申请和释放锁。另外，对于批量插入来说，自增就可能变的不连续了（需要和开发沟通，是否可以接受）。

关于自增的两个参数

auto_increment_increment：步长
auto_increment_offset：初始值

SERIALIZABLE和REPEATABLE READ的区别

SERIALIZABLE 采用两阶段锁来保证隔离性，且无论读还是写，都要加锁。但是这样做了以后，失去了MVCC的特性（非锁定的一致性读）。

现有一张表，记录如下：

mysql> select * from cyt;
+------+------+
| id   | name |
+------+------+
|    1 | cfop |
+------+------+

假如有一个事务1，内容如下：

mysql> begin;
mysql> select name from cyt where id=1;
.....
mysql> select name from cyt where id=1;
mysql> commit;