暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 高频面试考点--MySQL InnoDB 锁机制

高频面试考点--MySQL InnoDB 锁机制

GoGoCoder 2021-09-03
496

经典问题

Q：能说说MySQL InnoDB 有几种锁吗？能不能结合不同的事务隔离级别，说明一下数据库是如何加锁的？

Q：结合几种锁，在某种隔离级别下，能否举出一个死锁的例子？

A: 下文锁简介与实验，提供详细解答，选择一两种自己熟悉的情况记住，回答即可。


追根溯源


Mysql锁简介

1、共享锁/排他锁

InnoDB
 implements standard row-level locking where there are two types of locks, shared (S
) locks and exclusive (X
) locks.

  • A shared (S
    ) lock     permits the transaction that holds the lock to read a row.

  • An exclusive (X
    ) lock     permits the transaction that holds the lock to update or delete a row.

说明：
    1、共享锁和排它锁都是行级锁
    2、共享锁在事务进行读记录时触发
    3、排它锁在事务进行更新和删除记录时触发

排它锁与共享锁之间关系如下图：


SX
S✅❌
X❌❌

2、意向锁

InnoDB
 supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES ... WRITE
 takes an exclusive lock (an X
lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB
uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:

  • An intention shared lock (IS
    ) indicates that a transaction intends to set a shared lock on individual rows in a table.

  • An intention exclusive lock (IX
    ) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

For example, SELECT ... FOR SHARE
 sets an IS
lock, and SELECT ... FOR UPDATE
 sets an IX
lock.

The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

说明：
    1、 InnoDB可支持包括表级锁和行级锁在内的不同级别锁粒度控制。
    2、支持行锁与表锁共存，因而能够进行锁的粒度分级，提高并发读写的性能。

The intention locking protocol is as follows:  

  • Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS
    lock or stronger on the table.

  • Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX
    lock on the table.

说明：
    1、获得共享锁前必须获得共享意向锁或者更高级锁
    2、获得排它锁前必须获得意向排它锁或者更高级锁
具体意向锁、排它锁、共享锁之间的关系如下图：

XIXSIS
XConflictConflictConflictConflict
IXConflictCompatibleConflictCompatible
SConflictConflictCompatibleCompatible
ISConflictCompatibleCompatibleCompatible
#两个事务都要写的时候，冲突了锁等待。（不冲突 分别获取IX 与 对应行锁/间隙锁/临键锁 更新/删除/插入即可）
---TRANSACTION 103180, ACTIVE 29 sec
TABLE LOCK table `test`.`t` trx id 103180 lock mode IX
RECORD LOCKS space id 42 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 103180 lock_mode X waiting


---TRANSACTION 103179, ACTIVE 29 sec
TABLE LOCK table `test`.`t` trx id 103179 lock mode IX
RECORD LOCKS space id 42 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 103179 lock_mode X

3、行锁

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

说明：
    1、行锁一定是作用在索引上的
    2、当不存在索引时，默认创建索引，并在该索引上加锁

4、间隙锁

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record。

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (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.)

Gap locks in InnoDB
are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

说明：
    1、间隙锁是加在两条索引记录之间，来避免其他事务在间隙中添加记录
    2、当使用唯一索引能够确定某个特定记录时，并不需要间隙锁
    3、同一间隙，可以被不同事务多次加锁，且能够共存。
间隙锁共存情况：两事务均对（10,20）间隙加锁，并同时插入造成死锁，如下图所示：
+----+------+
| id | name |
+----+------+
| 10 | 10   |
+----+------+
| 20 | 20   |
+----+------+
| 30 | 30   |
+----+------+
锁类型：lock_mode X locks gap before rec
session1session2
begin;

begin;
select * from test where id = 12 for update;

select * from test where id = 13 for update;
insert into test(id, name) values(12, "test1");
锁等待..insert into test(id, name) values(13, "test2");
锁等待解除,插入成功死锁，session 2的事务被回滚

5、临键锁

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

行锁+间隙锁（before index）

建表语句
create table t(id int default null,name char(20) default null);
insert into t values(10,'10'),(20,'20'),(30,'30');
表记录
+----+------+
| id | name |
+----+------+
| 10 | 10   |
+----+------+
| 20 | 20   |
+----+------+
| 30 | 30   |
+----+------+
select * from t for update;
 lock_mode X 临键锁（间隙锁+行锁）
    (negative infinity, 10]
    (10, 20]
    (20, 30]
    (30, positive infinity)
#日志如下
TABLE LOCK table `test`.`t` trx id 103178 lock mode IX
RECORD LOCKS space id 42 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 103178 lock_mode X;;

6、插入意向锁

An insert intention lock is a type of gap lock set by INSERT
 operations prior to row insertion.

说明：
    1、插入意向锁是一种特殊的间隙锁，只用于并发插入操作。
    2、插入意向锁锁住的就是一个点，插入意向锁与间隙锁重叠，则互斥。
mysql> begin ;
mysql> select * from t2 where id >20 for update;
+----+------+
| id | name |
+----+------+
| 30 | c   |
+----+------+
1 row in set (0.00 sec)
mysql> begin;
mysql> insert into t2 values(25,'q');
#锁等待
RECORD LOCKS space id 43 page no 3 n bits 72 index PRIMARY of table `test`.`t2` trx id 103691 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001e; asc     ;;
1: len 6; hex 0000000192c6; asc       ;;
2: len 7; hex e5000001e00128; asc       ;;
3: len 20; hex 6320202020202020202020202020202020202020; asc c    ;;

总结：

1、增：插入意向锁、

     删/改：排他锁、

     查：共享锁

    涵盖了常用的增删改查四个动作。

2、设置排它锁时，根据事务隔离级别、查询条件（是否唯一主键与索引）分别设置间隙锁、临键锁、行锁。


实验：不同隔离级别、不同表结构、不同查询索引下，加锁情况

0、环境准备

#设置可查看锁的详细信息
set global innodb_status_output_locks=ON; // 输出的内容包含锁的详细信息
#查看当前事务ID
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX  WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();

1、REPEATABLE-READ隔离级别+表无显式主键和索引

创建表t,没有索引和主键，并插入测试数据

创建表t,没有索引和主键，并插入测试数据

create table t(id int default null,name char(20) default null);
insert into t values(10,'10'),(20,'20'),(30,'30');

手动开启事务，执行语句并采用for update方式（当前读）

begin;
select * from t for update;
show engine innodb status;

---TRANSACTION 103718, ACTIVE 2 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 2, OS thread handle 140043317577472, query id 341 localhost root starting
show engine innodb status
#事务103718 给test库t表加表级锁：意向排它锁IX
TABLE LOCK table `test`.`t` trx id 103718 lock mode IX
#事务103718 表空间47页号3对应锁位图为72bits(64bit预留+记录数+infimum&supremum 且被8取整的记录数） 给test库t表自动生成的索引GEN_CLUST_INDEX 添加临键锁
RECORD LOCKS space id 47 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 103718 lock_mode X
#记录锁，物理存储填充序号为1;n_fields 1 只有一列（supremum默认只有一列）;行记录格式为 变长的compact;info bits 0 表示数据未被删除
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;


#记录锁，物理存储填充序号为2（随着insert 递增）;n_fields 5 数据有5列;info bits 0 数据未被删除
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000000500; asc       ;; #GEN_CLUST_INDEX 生成索引列
1: len 6; hex 000000019521; asc     ;; #transaction id
2: len 7; hex b90000012d0110; asc     - ;; #roll pointer
3: len 4; hex 8000000a; asc     ;; #ID
4: len 20; hex 3130202020202020202020202020202020202020; asc10;;#name


Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000000501; asc       ;;
1: len 6; hex 000000019521; asc     !;;
2: len 7; hex b90000012d011e; asc     - ;;
3: len 4; hex 80000014; asc     ;;
4: len 20; hex 3230202020202020202020202020202020202020; asc 20   ;;


Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000000502; asc       ;;
1: len 6; hex 000000019521; asc     !;;
2: len 7; hex b90000012d012c; asc     - ,;;
3: len 4; hex 8000001e; asc     ;;
4: len 20; hex 3330202020202020202020202020202020202020; asc 30   ;;


相当于先通过lock_mode X 判断为临键锁，然后根据四个记录锁，判断加锁为
(negative infinity, 10]
(10, 20]
(20, 30]
(30, positive infinity]
其中包括4个间隙和4条记录

    表添加了IX锁和4个记录锁，表中的三行记录上分别添加了Next-key Lock锁，防止有数据变化发生幻读，例如进行了更新、删除操作。同时会出现“ 0: len 8; hex 73757072656d756d; asc supremum;”这样的描述信息，此操作也是为了防止幻读，会将最大索引值之后的间隙锁住并用supremum表示高于表中任何一个索引的值。

    同表下，如果加上where条件之后，是否就不会产生Next-key Lock呢？执行如下语句：

begin;
select * from t where id = 10 for update;
show engine innodb status;

---TRANSACTION 103719, ACTIVE 8 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 3, OS thread handle 140043317307136, query id 345 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t` trx id 103719 lock mode IX
RECORD LOCKS space id 47 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 103719 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 5; compact format; info bits 0
0: len 6; hex 000000000500; asc       ;;
1: len 6; hex 000000019521; asc     !;;
2: len 7; hex b90000012d0110; asc     - ;;
3: len 4; hex 8000000a; asc     ;;
4: len 20; hex 3130202020202020202020202020202020202020; asc 10 ;;


Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000000501; asc       ;;
1: len 6; hex 000000019521; asc     !;;
2: len 7; hex b90000012d011e; asc     - ;;
3: len 4; hex 80000014; asc     ;;
4: len 20; hex 3230202020202020202020202020202020202020; asc 20  ;;


Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000000502; asc       ;;
1: len 6; hex 000000019521; asc     !;;
2: len 7; hex b90000012d012c; asc     - ,;;
3: len 4; hex 8000001e; asc     ;;
4: len 20; hex 3330202020202020202020202020202020202020; asc 30  ;;

    从上述反馈信息中，可以发现加不加where条件的加锁情况是一样的，会同时出现多个行的临键锁和supremum。

    原因：虽然where的条件是10，但是每次插入记录时所需要生成的聚簇索引Row_id还是自增的，每次都会在表的最后插入，所以就有可能插入id=10这条记录，因此要添加一个supremum防止数据插入。

    出现其他行的临键锁的原因是：为了防止幻读，如果不添加Next-Key Lock锁，这时若有其他会话执行DELETE或者UPDATE语句，则都会造成幻读。

2、REPEATABLE-READ隔离级别+表有显式主键无索引

创建如下表并添加数据：

create table t2(id int primary key not null,name char(20) default null);
insert into t2 values(10,'10'),(20,'20'),(30,'30')

在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：

1、不带where条件

begin;
select * from t2 for update;
show engine innodb status;

通过上述信息可以看到，与之前的加锁方式是相同的。

2、where条件是主键字段

begin;
select * from t2 where id = 10 for update;
show engine innodb status;

TRANSACTIONS------------Trx id counter 103730
MySQL thread id 2, OS thread handle 140043317577472, query id 351 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t2` trx id 103729 lock mode IX
RECORD LOCKS space id 48 page no 3 n bits 72 index PRIMARY of table `test`.`t2` trx id 103729 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 00000001952c; asc     ,;;
2: len 7; hex a2000001150110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10   ;;

    通过上述信息可以看到，只会对表中添加IX锁和对主键添加了记录锁（X locks rec but not gap）,并且只锁住了where条件id=10这条记录，因为主键已经保证了唯一性，所以在插入时就不会是id=10这条记录。

3、where条件包含主键字段和非关键字段

begin;
select * from t2 where id = 10 and name = '10' for update;
show engine innodb status;

Trx id counter 103731
MySQL thread id 2, OS thread handle 140043317577472, query id 355 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t2` trx id 103730 lock mode IX
RECORD LOCKS space id 48 page no 3 n bits 72 index PRIMARY of table `test`.`t2` trx id 103730 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 00000001952c; asc     ,;;
2: len 7; hex a2000001150110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10 ;;

    通过看到，加锁方式与where条件是主键字段的加锁方式相同，因为根据主键字段可以直接定位一条记录。

3、REPEATABLE-READ隔离级别+表无显式主键有索引

1、不带where条件，跟之前的情况类似

2、where条件是普通索引字段或者（普通索引字段+非索引字段）

创建如下表：

create table t3(id int default null,name char(20) default null);
create index idx_id on t3(id);
insert into t3 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;
select * from t3 where id = 10 for update;
show engine innodb status;

Trx id counter 103749
MySQL thread id 2, OS thread handle 140043317577472, query id 362 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t3` trx id 103748 lock mode IX
RECORD LOCKS space id 49 page no 4 n bits 72 index idx_id of table `test`.`t3` trx id 103748 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 000000000503; asc       ;;


RECORD LOCKS space id 49 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t3` trx id 103748 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000000503; asc       ;;
1: len 6; hex 00000001953f; asc      ?;;
2: len 7; hex b1000001250110; asc     % ;;
3: len 4; hex 8000000a; asc     ;;
4: len 20; hex 3130202020202020202020202020202020202020; asc 10  ;;


RECORD LOCKS space id 49 page no 4 n bits 72 index idx_id of table `test`.`t3` trx id 103748 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000014; asc     ;;
1: len 6; hex 000000000504; asc       ;;

   通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了Next-Key Lock锁，区间是负无穷到10，对索引对应的聚集索引添加了X记录锁，为了防止幻读，对索引记录区间（10，20）添加间隙锁。

    此时，插入负无穷到id=19的全部记录都会被阻塞，而大于等于20 的值不会被阻塞,因而提高并发。

3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）

创建如下表：

create table t4(id int default null,name char(20) default null);
create unique index idx_id on t4(id);
insert into t4 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;
select * from t4 where id = 10 for update;
show engine innodb status;

Trx id counter 103767
TABLE LOCK table `test`.`t4` trx id 103766 lock mode IX
RECORD LOCKS space id 50 page no 4 n bits 72 index idx_id of table `test`.`t4` trx id 103766 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 000000000506; asc       ;;


RECORD LOCKS space id 50 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t4` trx id 103766 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000000506; asc       ;;
1: len 6; hex 000000019551; asc     Q;;
2: len 7; hex bf000001360110; asc     6 ;;
3: len 4; hex 8000000a; asc     ;;
4: len 20; hex 3130202020202020202020202020202020202020; asc 10  ;;

    通过上述信息可以看到，此方式与where条件是主键字段的加锁情况相同，表无显式主键则会把唯一索引加行锁，同时把默认自增主键进行加行锁，所以不能再插入id=10这条记录，因此不需要间隙锁。

4、REPEATABLE-READ隔离级别+表有显式主键和索引

此情况可以分为以下几种：

1、表有显式主键和普通索引

创建如下表：

create table t5(id int not null,name char(20) default null,primary key(id),key idx_name(name));
insert into t5 values(10,'10'),(20,'20'),(30,'30');

① 不带where条件

begin;
select * from t5 for update;
show engine innodb status;

Trx id counter 103777
show engine innodb status
TABLE LOCK table `test`.`t5` trx id 103776 lock mode IX
RECORD LOCKS space id 51 page no 4 n bits 72 index idx_name of table `test`.`t5` trx id 103776 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 2; compact format; info bits 0
0: len 20; hex 3130202020202020202020202020202020202020; asc 10    ;;
1: len 4; hex 8000000a; asc     ;;


Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 20; hex 3230202020202020202020202020202020202020; asc 20 ;;
1: len 4; hex 80000014; asc     ;;


Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 20; hex 3330202020202020202020202020202020202020; asc 30  ;;
1: len 4; hex 8000001e; asc     ;;


RECORD LOCKS space id 51 page no 3 n bits 72 index PRIMARY of table `test`.`t5` trx id 103776 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 00000001955b; asc     [;;
2: len 7; hex c7000001c70110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10  ;;


Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000014; asc     ;;
1: len 6; hex 00000001955b; asc     [;;
2: len 7; hex c7000001c7011c; asc       ;;
3: len 20; hex 3230202020202020202020202020202020202020; asc 20  ;;


Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001e; asc     ;;
1: len 6; hex 00000001955b; asc     [;;
2: len 7; hex c7000001c70128; asc       (;;
3: len 20; hex 3330202020202020202020202020202020202020; asc 30   ;;

    通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁,这样能够保证锁住全部的索引，防止幻读。

② where条件是普通索引字段

begin;
select * from t5 where name='10' for update;
show engine innodb status;

Trx id counter 103778
show engine innodb status
TABLE LOCK table `test`.`t5` trx id 103777 lock mode IX
RECORD LOCKS space id 51 page no 4 n bits 72 index idx_name of table `test`.`t5` trx id 103777 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 20; hex 3130202020202020202020202020202020202020; asc 10     ;;
1: len 4; hex 8000000a; asc     ;;


RECORD LOCKS space id 51 page no 3 n bits 72 index PRIMARY of table `test`.`t5` trx id 103777 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 00000001955b; asc     ;;
2: len 7; hex c7000001c70110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10  ;;


RECORD LOCKS space id 51 page no 4 n bits 72 index idx_name of table `test`.`t5` trx id 103777 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 20; hex 3230202020202020202020202020202020202020; asc 20  ;;
1: len 4; hex 80000014; asc     ;;

    通过上述信息可以看到，首先对表添加IX锁，然后对name添加临键锁，对主键索引列添加X记录锁，为了防止幻读，对name的（10，20）添加间隙锁(为了保证索引不重复。)

③ where条件是主键字段

begin;
select * from t5 where id=10 for update;
show engine innodb status;

Trx id counter 103779
MySQL thread id 2, OS thread handle 140043317577472, query id 383 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t5` trx id 103778 lock mode IX
RECORD LOCKS space id 51 page no 3 n bits 72 index PRIMARY of table `test`.`t5` trx id 103778 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 00000001955b; asc     [;;
2: len 7; hex c7000001c70110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10  ;;

    通过上述信息可以看到，对表添加了意向锁，对主键添加了记录锁。

(4)where条件同时包含普通索引字段和主键索引字段

begin;
select * from t5 where id=10 and name='10' for update;
show engine innodb status

     此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是普通索引，那么跟普通字段是类似的，其实根本就在于加锁的字段不同。

2、表有显式主键和唯一索引

创建如下表：

create table t6(id int not null,name char(20) default null,primary key(id),unique key idx_name(name));
insert into t6 values(10,'10'),(20,'20'),(30,'30');

① 不带where条件

begin;
select * from t6 for update;
show engine innodb status;

Trx id counter 103789
show engine innodb status
TABLE LOCK table `test`.`t6` trx id 103788 lock mode IX
RECORD LOCKS space id 52 page no 4 n bits 72 index idx_name of table `test`.`t6` trx id 103788 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 2; compact format; info bits 0
0: len 20; hex 3130202020202020202020202020202020202020; asc 10     ;;
1: len 4; hex 8000000a; asc     ;;


Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 20; hex 3230202020202020202020202020202020202020; asc 20   ;;
1: len 4; hex 80000014; asc     ;;


Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 20; hex 3330202020202020202020202020202020202020; asc 30   ;;
1: len 4; hex 8000001e; asc     ;;


RECORD LOCKS space id 52 page no 3 n bits 72 index PRIMARY of table `test`.`t6` trx id 103788 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 000000019567; asc     g;;
2: len 7; hex d1000001cf0110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10  ;;


Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000014; asc     ;;
1: len 6; hex 000000019567; asc     g;;
2: len 7; hex d1000001cf011c; asc       ;;
3: len 20; hex 3230202020202020202020202020202020202020; asc 20  ;;


Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001e; asc     ;;
1: len 6; hex 000000019567; asc     g;;
2: len 7; hex d1000001cf0128; asc       (;;
3: len 20; hex 3330202020202020202020202020202020202020; asc 30     ;;

   通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁

② where条件是唯一索引字段

begin;
select * from t6 where name='10' for update;
show engine innodb status;

Trx id counter 103790
show engine innodb status
TABLE LOCK table `test`.`t6` trx id 103789 lock mode IX
RECORD LOCKS space id 52 page no 4 n bits 72 index idx_name of table `test`.`t6` trx id 103789 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 20; hex 3130202020202020202020202020202020202020; asc 10   ;;
1: len 4; hex 8000000a; asc     ;;


RECORD LOCKS space id 52 page no 3 n bits 72 index PRIMARY of table `test`.`t6` trx id 103789 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 000000019567; asc     g;;
2: len 7; hex d1000001cf0110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10   ;;

    通过上述信息可以看到，首先对表添加IX锁，然后对name和主键添加行锁

③ where条件是主键字段

begin;
select * from t6 where id=10 for update;
show engine innodb status;

Trx id counter 103791
MySQL thread id 2, OS thread handle 140043317577472, query id 397 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t6` trx id 103790 lock mode IX
RECORD LOCKS space id 52 page no 3 n bits 72 index PRIMARY of table `test`.`t6` trx id 103790 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 000000019567; asc     g;;
2: len 7; hex d1000001cf0110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10                 ;;

    通过上述信息可以看到，首先对表添加IX锁，然后主键添加行锁

④ where条件是唯一索引字段和主键字段

begin;
select * from t6 where id=10 and name='10' for update;
show engine innodb status;

Trx id counter 103792
show engine innodb status
TABLE LOCK table `test`.`t6` trx id 103791 lock mode IX
RECORD LOCKS space id 52 page no 3 n bits 72 index PRIMARY of table `test`.`t6` trx id 103791 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 000000019567; asc     g;;
2: len 7; hex d1000001cf0110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10                 ;;  

  此处注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是唯一索引，那么跟唯一索引字段是一样的，其实根本点就在于加锁的字段不同而已。

5、READ-COMMITTED隔离级别+表无显式主键和索引

创建表t,没有索引和主键，并插入测试数据

create table t7(id int default null,name char(20) default null);
insert into t7 values(10,'10'),(20,'20'),(30,'30');

手动开启事务，执行语句并采用for update方式（当前读）

begin;
select * from t7 for update;
show engine innodb status;

    自行实验可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）

同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：

begin;
select * from t7 where id = 10 for update;
show engine innodb status;

    自行实验可以看到，首先对表添加IX锁，然后会对聚集索引添加记录锁，因为RC隔离级别无法解决幻读问题，所以不会添加临键锁。

6、READ-COMMITTED隔离级别+表有显式主键无索引

#设置为读已提交事务隔离级别
set global transaction isolation level read committed;
#设置为可重复读事务隔离级别
set global transaction isolation level REPEATABLE READ;

创建如下表并添加数据：

create table t8(id int primary key not null,name char(20) default null);
insert into t8 values(10,'10'),(20,'20'),(30,'30');

在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：

1、不带where条件

begin;
select * from t8 for update;
show engine innodb status;

Trx id counter 103802
show engine innodb status
TABLE LOCK table `test`.`t8` trx id 103801 lock mode IX
RECORD LOCKS space id 53 page no 3 n bits 72 index PRIMARY of table `test`.`t8` trx id 103801 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 4; hex 8000000a; asc     ;;
1: len 6; hex 000000019574; asc     t;;
2: len 7; hex dc000001d90110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10      ;;


Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000014; asc     ;;
1: len 6; hex 000000019574; asc     t;;
2: len 7; hex dc000001d9011c; asc       ;;
3: len 20; hex 3230202020202020202020202020202020202020; asc 20      ;;


Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001e; asc     ;;
1: len 6; hex 000000019574; asc     t;;
2: len 7; hex dc000001d90128; asc       (;;
3: len 20; hex 3330202020202020202020202020202020202020; asc 30    ;;

    通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（主键）

2、where条件是主键字段

begin;
select * from t8 where id = 10 for update;
show engine innodb status;

Trx id counter 103803
MySQL thread id 2, OS thread handle 140043317577472, query id 412 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t8` trx id 103802 lock mode IX
RECORD LOCKS space id 53 page no 3 n bits 72 index PRIMARY of table `test`.`t8` trx id 103802 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 6; hex 000000019574; asc     t;;
2: len 7; hex dc000001d90110; asc       ;;
3: len 20; hex 3130202020202020202020202020202020202020; asc 10                 ;; 

   通过上述信息可以看到，首先对表添加IX锁，然后对表id=10添加记录锁

3、where条件包含主键字段和非关键字段

begin;
select * from t8 where id = 10 and name = '10' for update;
show engine innodb status;

   自行实验可以看到，首先对表添加IX锁，然后对表id=10添加记录锁

7、READ-COMMITTED隔离级别+表无显式主键有索引

创建如下表：

create table t9(id int default null,name char(20) default null);
create index idx_id on t9(id);
insert into t9 values(10,'10'),(20,'20'),(30,'30');

1、不带where条件，跟之前的情况类似

begin;
select * from t9 for update;
show engine innodb status;

    自行实验可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）

2、where条件是普通索引字段或者（普通索引字段+非索引字段）

执行如下语句：

begin;
select * from t9 where id = 10 for update;
show engine innodb status;

    自行实验可以可以看到，对表添加了IX锁，对id=10的索引添加了行锁，对索引对应的聚集索引添加了行锁，

3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）

创建如下表：

create table t10(id int default null,name char(20) default null);
create unique index idx_id on t10(id);
insert into t10 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;
select * from t10 where id = 10 for update;
show engine innodb status;

    自行实验可以看到，对表添加了IX锁，对id=10的索引添加了行锁，对索引对应的聚集索引添加了行锁。


思考：

1、可重复读隔离级别下，能唯一确定某一条记录加行锁，否则加临键锁，目标是防止幻读；

2、读已提交隔离级别下，不解决幻读问题，故只需加行锁；

3、从锁方面考虑，主键与索引区分度不能过高也不能过低。过高，虽并发度高，但加锁代价大。过低，则虽并发度低，但加锁代价小。

4、更好理解底层加锁原理，从业务逻辑上尽量避免锁冲突，与死锁。

5、如事务中增减库存，添加日志，增减库存是加锁的，添加添加日志操作可以先行，将并发度高的表操作放在事务靠后部分，能够在事务结束尽快释放锁，提高并发度。

参考文档：

1、MySQL 8.0 参考手册 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

2、《MySQL技术内幕》4.3节4.4节



mysql面试
文章转载自GoGoCoder，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论