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

锁的概念
对一颗树进行加锁
文章图片

意向锁

• 揭示下一层请求的锁的类型
• 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; ascW; ; # 回滚指针idinfo bits 00: 非删除 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_schemaSELECT 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 Lock】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 4waiting_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 LockRecord 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\GMySQL 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; asc4; ;

分析过程： 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：测试区间插入

窗口cinsert 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\GMySQL 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：测试区间插入

窗口cinsert 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; ascZ; ;

结果分析：
锁定的区间范围：
(-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\GMySQL 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; ascZ :; ;

解释 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\GMySQL 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; ascB; ; 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; ascB; ; 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; ascC; ;

窗口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; ascB; ; 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 rec3、给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; ascB; ; 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)

推荐阅读

• 

  书，心画也
• 

  postgresql集群报错解决（SELECT pg_catalog.pg_try_advisory_xact_lock_shared(65535, 65535)）
• 

  统计 数据分析 直方图
• 

  操作系统|快@你的好兄弟好姐妹来内卷进程状态
• 

  瑞士河 莱茵河瀑布
• 

  艾伦方差分析法,spss单因素方差分析法
• 

  加密文件
• 

  如何用空气炸锅炸羊肉
• 

  redis解决跨域问题 redis跨域效率
• 

  郴州违章停车怎么处罚 郴州违章停车罚款多少
• 

  LCD 屏参数及计算
• 

  红酒中的焦亚硫酸钾对人体有害吗
• 

  对女朋友欲擒故纵,真的好吗？
• 

  海底地震引发的海啸,是来自大自然的震怒！它到底有多么恐怖？
• 

  新媒体营销有哪些平台 新媒体营销方式都有哪些
• 

  红薯跟鸡蛋能一起吃吗
• 

  小二寸照片的尺寸是多少像素
• 

  imindmap怎么导出图片 iMindMap导出单张幻灯片的方法步骤
• 

  书籍设计说明模板100字
• 

  开水冲鸡蛋加香油能起什么做用