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);

配置项

$\"Mysql$

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 Lock

锁的概念

对一颗树进行加锁

意向锁

加锁语法

加锁举例

建表

配置项

测试锁

窗口A

窗口B

锁的查看

INNODB_TRX

INNODB_LOCK_WAITS

事务隔离级别

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

脏读举例

InnoDB 锁的算法

锁住的是索引（重要）

案例分析1

分析过程：

结论

案例分析2

分析过程：

结论

Next Key Lock 优化为record lock

案例分析2

添加唯一索引

解释

添加普通索引

分析

相关推荐

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