锁的概念
- 对一颗树进行加锁
- 意向锁
锁的概念
对一颗树进行加锁
![\"Mysql](\"https://img.it610.com/image/info9/a65f0cc3a9ff450aa32e23058df82309.jpg\" "\\"锁树\\"")
意向锁
- 揭示下一层请求的锁的类型
- 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](\"https://img.it610.com/image/info9/0be3ce48cd0748c7a8b60e1e29049a99.png\" "\\"\\"")
set global innodb_status_output_locks = 1 测试锁
窗口A
begin;
select * from demo where a = 3 for update;窗口B
show engine innodb status\\G![\"\"](\"https://img.it610.com/image/info9/f348d661e34f42ada6cd9b6ae285bad1.jpg\" "\\"\\"")
# 先添加了表意向锁 意向排它锁 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_idmysql 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 = 203：测试区间插入
窗口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 = 133：测试区间插入
窗口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)