Mysql Lock

锁的概念
- 对一颗树进行加锁
- 意向锁

锁的概念

对一颗树进行加锁

意向锁

揭示下一层请求的锁的类型
IS: 事务想要获得一张表中某几行的共享锁
IX: 事务想要获得一张表中某几行的排它锁
InnoDB 存储引擎中的意向锁都是表锁

兼容性	S	X
S	兼容	不兼容
X	不兼容	不兼容

兼容性	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

加锁语法

select * from table for update                     # 排它锁(X)
select * from table id = xxx lock in share mode    # 共享锁(S)

加锁举例

建表

create table demo (
  a int primary key
) engine=innodb;

insert into demo values (1),(3),(5),(7),(9);

配置项

set global innodb_status_output_locks = 1

测试锁

窗口A

begin;
select * from demo where a = 3 for update;

窗口B

show engine innodb status\G

# 先添加了表意向锁 意向排它锁 IX
TABLE LOCK table `test`.`demo` trx id 7583 lock mode IX 

# 再添加了记录锁 lock_mode X locks rec but not gap
RECORD LOCKS ... index PRIMARY of table `test`.`demo` ... lock_mode X locks rec but not gap 

0: len 4; hex 80000003; asc     ;;                   # a = 3 (80000003 十六进制3)
1: len 6; hex 000000001d9a; asc       ;;             # 事务id
2: len 7; hex f000000157011c; asc     W  ;;          # 回滚指针id

info bits 0  0: 非删除 32：删除操作

锁的查看

show engine innodb status
INNODB_TRX
INNODB_LOCKS
INNODB_LOCK_WAITS

INNODB_TRX

use information_schema
select * from INNODB_TRX\G;

*************************** 1. row ***************************
                    trx_id: 7583
                 trx_state: RUNNING
               trx_started: 2022-05-09 22:05:12
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 53
                 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

表 INNODB_TRX 的结构说明

字段名	说明
trx_id	InnoDB 存储引擎内部唯一事务ID
trx_state	当前事务状态
trx_started	事务的开始时间
trx_requested_lock_id	等待事务的锁ID，如trx_status的状态为LOCK WAIT，那么该值代表当前的事务等待之前事务占锁资源的ID。若trx_stata 不是LOCK WAIT,该值为NULL
trx_wait_started	事务等待开始的时间
trx_weight	事务的权重，反映了一个事务修改和锁住的行数。在InnoDB 存储引擎中，当发生死锁需要回滚时，InnoDB存储引擎会选择该值最小的进行回滚
trx_mysql_thread_id	Mysql 中线程ID，SHOW PROCESSLIST 显示结果
trx_query	事务运行的SQL语句

INNODB_LOCK_WAITS

窗口A

begin;
select * from demo where id = 3 for update;

窗口B

begin;
select * from demo where a = 3 lock in share mode;

窗口C

set innodb_lock_wait_timeout=60 # 锁等待时间

select * from INNODB_LOCKs\G
*************************** 1. row ***************************
    lock_id: 283107623620360:47:3:3
lock_trx_id: 283107623620360
  lock_mode: S
  lock_type: RECORD
 lock_table: `test`.`demo`
 lock_index: PRIMARY
 lock_space: 47
  lock_page: 3
   lock_rec: 3
  lock_data: 3
*************************** 2. row ***************************
    lock_id: 7583:47:3:3
lock_trx_id: 7583
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`demo`
 lock_index: PRIMARY
 lock_space: 47

表 INNODB_LOCKS 的结构说明

字段名	说明
lock_id	锁的ID
lock_trx_id	事务ID
lock_mode	锁的模式
lock_type	锁的类型，表锁还是行锁
lock_table	要加锁的表
lock_index	锁住的索引
lock_space	锁住对象的space id
lock_page	事务锁定页的数量，若是表锁，则该值为NULL
lock_rec	事务锁定行的数量，若是表锁，则该值为NULL
lock_data	事务锁定记录的主键值，若是表锁，则该值为NULL

INNODB_LOCK_WAITS

*************************** 1. row ***************************
requesting_trx_id: 7941
requested_lock_id: 7941:47:3:3
  blocking_trx_id: 7940
 blocking_lock_id: 7940:47:3:3
1 row in set, 1 warning (0.00 sec)

表 INNODB_LOCK_WAITS 的结构说明

字段名	说明
requesting_trx_id	请求事务ID
requested_lock_id	请求锁对象
blocking_trx_id	等待事务ID
blocking_lock_id	等待锁对象

查看死锁信息

use information_schema

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

mysql 5.7

use sys
select * from innodb_lock_waits

*************************** 1. row ***************************
                wait_started: 2022-05-10 09:48:27
                    wait_age: 00:00:02
               wait_age_secs: 2
                locked_table: `test`.`demo`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 7941
         waiting_trx_started: 2022-05-10 09:34:04
             waiting_trx_age: 00:14:25
     waiting_trx_rows_locked: 4
   waiting_trx_rows_modified: 0
                 waiting_pid: 5
               waiting_query: select * from demo where a = 3 lock in share mode
             waiting_lock_id: 7941:47:3:3
           waiting_lock_mode: S
             blocking_trx_id: 7940
                blocking_pid: 4
              blocking_query: NULL
            blocking_lock_id: 7940:47:3:3
          blocking_lock_mode: X
        blocking_trx_started: 2022-05-10 09:33:45
            blocking_trx_age: 00:14:44
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 4
sql_kill_blocking_connection: KILL 4

waiting_lock_id: 7941:47:3:3
7941：事务id
47：space id
3：page_no
3：heap_no

事务隔离级别

读未提交（READ UNCOMITTED）
读提交（READ COMMITTED）
可重复读（REPEATABLE READ）
串行化（SERIALIZABLE）

事务的隔离性解释：

一个事务所做的修改，对其他的事务是不可见的，好似是串行执行的

事务隔离性要解决的几种问题

脏读
不可重复读
幻读

问题	解释
脏读	读到事务未提交数据
不可重复读	同一条记录，不同事务读到不一样的结果
幻读	读到不存在的记录

隔离级别	脏读	不可重复读	幻读
RU	×	×	×
RC	√	×	×
RR	√	√	√
SR	√	√	√

查看本机事务隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

脏读举例

事务隔离离别调整为 read-uncommitted

create table b (
a int,
b int
);

窗口A

set tx_isolation='read-uncommitted';

begin;
insert into b select 1,1;

窗口B

set tx_isolation='read-uncommitted';

begin;
mysql> select * from b;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
+------+------+

事务隔离离别调整为 read-committed

窗口A

set tx_isolation='read-committed';

begin;
insert into b select 1,1;

窗口B

set tx_isolation='read-committed';

begin;
mysql> select * from b;
Empty set (0.00 sec)

InnoDB 锁的算法

Record Lock 单行记录上的锁
Gap Lock 锁定一个范围，但不包含记录本身
Next Key Lock Record Lock + Gap Lock,锁定一个范围，并且锁定记录本身

锁住的是索引（重要）

案例分析1

CREATE TABLE c (a INT PRIMARY KEY) 
INSERT INTO c VALUES(10),(11),(13),(20)

窗口A

set tx_isolation='repeatable-read';

begin;
select * from c where a <= 13 for update;

窗口B

set global innodb_status_output_locks = 1 
show engine innodb status\G

MySQL thread id 24, OS thread handle 5792, query id 195 localhost ::1 ODBC
TABLE LOCK table `test`.`c` trx id 7971 lock mode IX
RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 7971 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 000000001f1d; asc       ;;
 2: len 7; hex b8000001170110; asc        ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000000001f1d; asc       ;;
 2: len 7; hex b800000117011c; asc        ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001f1d; asc       ;;
 2: len 7; hex b8000001170128; asc       (;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000001f1d; asc       ;;
 2: len 7; hex b8000001170134; asc       4;;

分析过程：

1：表先添加意向排它锁 IX

TABLE LOCK table `test`.`c` ... lock mode IX

2：添加4条记录的排它锁 X

0: len 4; hex 8000000a; asc     ;;    # a = 10
0: len 4; hex 8000000b; asc     ;;    # a = 11
0: len 4; hex 8000000d; asc     ;;    # a = 13
0: len 4; hex 80000014; asc     ;;    # a = 20

3：测试区间插入

窗口c

insert into c select 9;  # 阻塞
insert into c select 15; # 阻塞
insert into c select 20; # 阻塞
insert into c select 21; # 成功

update c set a = 100 where a = 11; # 阻塞
update c set a = 100 where a = 13; # 阻塞
update c set a = 100 where a = 15; # 成功 记录不存在
update c set a = 100 where a = 20; # 阻塞

4：区间范围
(-infinity,10]
(10,11]
(11,13]
(13,20]

结论

在 repeatable-read 事务级别下，默认采用是Next Key Lock 锁算法

案例分析2

CREATE TABLE c (a INT PRIMARY KEY) 
INSERT INTO c VALUES(10),(11),(13),(20)

窗口A

set tx_isolation='read-committed';

begin;
select * from c where a <= 13 for update;

窗口B

set global innodb_status_output_locks = 1 
show engine innodb status\G

MySQL thread id 32, OS thread handle 6452, query id 239 localhost ::1 ODBC
TABLE LOCK table `test`.`c` trx id 7975 lock mode IX
RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 7975 lock_mode X locks rec but not gap
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 000000001f1d; asc       ;;
 2: len 7; hex b8000001170110; asc        ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000000001f1d; asc       ;;
 2: len 7; hex b800000117011c; asc        ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001f1d; asc       ;;
 2: len 7; hex b8000001170128; asc       (;;

分析过程：

1：表先添加意向排它锁 IX

TABLE LOCK table `test`.`c` ... lock mode IX

2：添加3条记录的 lock_mode X locks rec but not gap(记录锁 )

0: len 4; hex 8000000a; asc     ;;    # a = 10
0: len 4; hex 8000000b; asc     ;;    # a = 11
0: len 4; hex 8000000d; asc     ;;    # a = 13

3：测试区间插入

窗口c

insert into c select 9;  # 成功
insert into c select 15; # 成功
insert into c select 20; # 失败 主键重复
insert into c select 21; # 成功

update c set a = 100 where a = 11; # 阻塞
update c set a = 100 where a = 13; # 阻塞
update c set a = 100 where a = 15; # 成功 记录不存在
update c set a = 100 where a = 20; # 阻塞

结论

lock_mode X locks rec but not gap，是记录锁，锁住的仅仅是记录本身，并非区间范围

Next Key Lock 优化为record lock

当索引含有唯一约束时
锁定一条记录

案例分析2

CREATE TABLE `d` (
  `a` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `d` VALUES ('10'),('11'),('13'),('20'),('30'),('31'),('33'),('40'),('60'),('61'),('63'),('70'),('80'),('81'),('83'),('90');

Mysql 默认事务级别 repeatable-read,锁算法也就是Next Key Lock

窗口A

begin;
select * from d where a= 20 for update;

窗口B

set global innodb_status_output_locks = 1 ;

TABLE LOCK table `test`.`d` trx id 8150 lock mode IX
RECORD LOCKS space id 54 page no 3 n bits 88 index GEN_CLUST_INDEX of table `test`.`d` trx id 8150 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 000000000316; asc       ;;
 1: len 6; hex 000000001faf; asc       ;;
 2: len 7; hex a40000011b0110; asc        ;;
 3: len 4; hex 8000000a; asc     ;;

...

Record lock, heap no 17 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000325; asc      %;;
 1: len 6; hex 000000001faf; asc       ;;
 2: len 7; hex a40000011b01e2; asc        ;;
 3: len 4; hex 8000005a; asc    Z;;

结果分析：
锁定的区间范围：
(-infinity,10]
(10,11]
(11,13]
(13,20]
(20,30]
(31,33]
(33,40]
(40,60]
(60,61]
(61,63]
(63,70]
(80,81]
(81,83]
(83,90]

因为a 列没有索引，把所有记录当做GAP Lock

添加唯一索引

窗口A

alter table d add unique idx_a(`a`)

begin;
select * from d where a= 20 for update;

窗口B

show engine innodb status\G

MySQL thread id 57, OS thread handle 1572, query id 871 localhost ::1 ODBC
TABLE LOCK table `test`.`d` trx id 8215 lock mode IX
RECORD LOCKS space id 57 page no 3 n bits 88 index idx_a of table `test`.`d` trx id 8215 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000001feb; asc       ;;
 2: len 7; hex ac0000015a013a; asc     Z :;;

解释

1、lock_mode X locks rec but not gap 记录锁
2、a列Unique key索引
3、在有唯一索引情况下，Next Key Lock 优化为 Record Lock

添加普通索引

窗口A

set tx_isolation='repeatable-read';

alter table d drop index idx_a
alter table d add index idx_a(`a`)

begin;
select * from d where a= 20 for update;

窗口B

show engine innodb status\G

MySQL thread id 57, OS thread handle 1572, query id 920 localhost ::1 ODBC
TABLE LOCK table `test`.`d` trx id 8256 lock mode IX
RECORD LOCKS space id 58 page no 4 n bits 88 index idx_a of table `test`.`d` trx id 8256 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000342; asc      B;;

RECORD LOCKS space id 58 page no 3 n bits 88 index GEN_CLUST_INDEX of table `test`.`d` trx id 8256 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000342; asc      B;;
 1: len 6; hex 00000000201c; asc       ;;
 2: len 7; hex cc000004c8013a; asc       :;;
 3: len 4; hex 80000014; asc     ;;

RECORD LOCKS space id 58 page no 4 n bits 88 index idx_a of table `test`.`d` trx id 8256 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 6; hex 000000000343; asc      C;;

窗口C

set tx_isolation='repeatable-read';

begin;
insert into d select 15; #阻塞
insert into d select 16; #阻塞
insert into d select 17; #阻塞
insert into d select 18; #阻塞
insert into d select 19; #阻塞
insert into d select 13; #阻塞
insert into d select 12; #成功
insert into d select 10; #成功
insert into d select 9;  #成功
insert into d select 21  #阻塞
insert into d select 31 #成功

分析

1、BLE LOCK table `test`.`d` trx id 8256 lock mode IX 表先添加意向排它锁

RECORD LOCKS space id 58 page no 4 n bits 88 index idx_a of table `test`.`d` trx id 8256 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000342; asc      B;;

index idx_a of table `test`.`d` trx id 8256 lock_mode X
0: len 4; hex 80000014; asc     ;;

2、在idx_a 索引上添加了 GAP 锁，确定了80000014，30这条记录范围

RECORD LOCKS space id 58 page no 4 n bits 88 index idx_a of table `test`.`d` trx id 8256 lock_mode X locks gap before rec

3、给30这条记录添加，Gap 插入意向锁

RECORD LOCKS space id 58 page no 3 n bits 88 index GEN_CLUST_INDEX of table `test`.`d` trx id 8265 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000342; asc      B;;
 1: len 6; hex 00000000201c; asc       ;;
 2: len 7; hex cc000004c8013a; asc       :;;
 3: len 4; hex 80000014; asc     ;;

4、当表中没有显示创建主键，innodb默认内部创建一个6个字节的 index GEN_CLUST_INDEX 主键，lock_mode X locks rec but not gap，给20这条记录加上了，记录锁

锁的区间
(13,20]
(20,30)

未完待续...

引用

《Mysql技术内幕 Innodb存储引擎》
https://baijiahao.baidu.com/s...
https://www.bilibili.com/vide...

Mysql Lock

锁的概念

对一颗树进行加锁

意向锁

加锁语法

加锁举例

建表

配置项

测试锁

窗口A

窗口B

锁的查看

INNODB_TRX

INNODB_LOCK_WAITS

事务隔离级别

事务隔离性要解决的几种问题

脏读举例

InnoDB 锁的算法

锁住的是索引（重要）

案例分析1

分析过程：

结论

案例分析2

分析过程：

结论

Next Key Lock 优化为record lock

案例分析2

添加唯一索引

解释

添加普通索引

分析

zzMeow

引用和评论

Mysql Lock Update

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

浅谈Java中的锁

MySQL 回收表碎片实践教程

高效工程师的七个习惯

sqlmap注入语句学习，帮助你排查SQL注入漏洞

Mysql Lock

锁的概念

对一颗树进行加锁

意向锁

加锁语法

加锁举例

建表

配置项

测试锁

窗口A

窗口B

锁的查看

INNODB_TRX

INNODB_LOCK_WAITS

事务隔离级别

事务隔离性 要解决的几种问题

脏读举例

InnoDB 锁的算法

锁住的是索引（重要）

案例分析1

分析过程：

结论

案例分析2

分析过程：

结论

Next Key Lock 优化为record lock

案例分析2

添加唯一索引

解释

添加普通索引

分析

zzMeow

引用和评论

Mysql Lock Update

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

浅谈Java中的锁

MySQL 回收表碎片实践教程

高效工程师的七个习惯

sqlmap注入语句学习，帮助你排查SQL注入漏洞

事务隔离性要解决的几种问题