Mysql锁实战

已于 2023-11-26 20:47:10 修改
阅读量365 收藏
点赞数 1
分类专栏： 数据库 文章标签： mysql 数据库 MVCC 幻读 mysql锁
于 2023-08-13 15:14:47 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010597819/article/details/132259875
版权

mysql版本：8.0.32
通过实战验证mysql的Record lock 与 Gap lock原理

锁类型

Shared and Exclusive Locks

类似读写锁，S锁为读锁，X锁为写锁

Intention Locks

意向锁，例如：

  1. SELECT … FOR SHARE sets an IS lock,
  2. and SELECT … FOR UPDATE sets an IX lock.
    为什么有意向锁？
    假设事务A锁定1行数据，事务B需要表扫描，那么加表锁需要判断每行是否存在锁？意向锁可以避免全扫描

Record Locks

行锁日志案例：lock_mode X locks rec but not gap

RECORD LOCKS space id 15 page no 477 n bits 128 index PRIMARY of table `gallant`.`my_test_user` trx id 18793 lock_mode X locks rec but not gap
Record lock, heap no 55 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00164233; asc   B3;;
 1: len 6; hex 000000004969; asc     Ii;;
 2: len 7; hex 820000009d0110; asc        ;;
 3: len 5; hex 616c696365; asc alice;;
 4: len 4; hex 80000013; asc     ;;

Gap Locks

gap锁日志案例：lock_mode X locks gap before rec

Next-Key Locks

日志案例：lock_mode X，注意next-key lock是前开后闭集合
例如：索引包含值： 10, 11, 13, and 20. next-key lock的范围如下

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

官方日志案例

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 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 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

Insert Intention Locks

日志案例：lock_mode X locks gap before rec insert intention waiting

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

准备工作

设置隔离级别为：RR，以及innodb状态输出锁相关信息

show variables like '%innodb_status_output_locks%';
show variables like '%isolation%';

SET GLOBAL innodb_status_output_locks=ON;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

建立测试表

CREATE TABLE `my_test_user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) DEFAULT NULL,
  `user_age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_age` (`user_age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

关闭自动提交

set autocommit=OFF;

实战

数据库数据分布

# user_agecount(0)
121
131
141
151
161
171
181
191
401
411

insert-存在唯一索引

session 1：

INSERT INTO `gallant`.`my_test_user`
(`id`,
`user_name`,
`user_age`)
values
(1,'jack1','12'),
(2,'jack2','13'),
(3,'jack3','14'),
(4,'jack4','15'),
(5,'jack5','16'),
(6,'jack6','17'),
(7,'jack7','18'),
(8,'jack8','19'),
(9,'jack9','40'),
(10,'jack10','41')
;

查看锁信息

show engine innodb status\G

没有写入冲突，加：意向排他锁：IX

---TRANSACTION 18795, ACTIVE 3 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 6253998080, query id 229 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18795 lock mode IX
---TRANSACTION 18794, ACTIVE 11 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 6252883968, query id 228 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18794 lock mode IX

开启第2个session，同时写入一个id数据，IX锁额外增加排他锁X，行锁，并且session2将会因为IX锁等待超时：TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED
session 2：

INSERT INTO `gallant`.`my_test_user`
(`id`,
`user_name`,
`user_age`)
values
(1,'jack1','12');

锁日志

---TRANSACTION 246806, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 371, OS thread handle 123145543245824, query id 5984255 localhost root update
INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (1,'jack1','12')
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 438 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 246806 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000003c40d; asc       ;;
 2: len 7; hex 82000000e50110; asc        ;;
 3: len 5; hex 6a61636b31; asc jack1;;
 4: len 4; hex 8000000c; asc     ;;

------------------
TABLE LOCK table `gallant`.`my_test_user` trx id 246806 lock mode IX
RECORD LOCKS space id 438 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 246806 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000003c40d; asc       ;;
 2: len 7; hex 82000000e50110; asc        ;;
 3: len 5; hex 6a61636b31; asc jack1;;
 4: len 4; hex 8000000c; asc     ;;

---TRANSACTION 246797, ACTIVE 66 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 10
MySQL thread id 370, OS thread handle 123145545375744, query id 5984253 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 246797 lock mode IX
RECORD LOCKS space id 438 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 246797 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 4; hex 00000001; asc     ;;
 1: len 6; hex 00000003c40d; asc       ;;
 2: len 7; hex 82000000e50110; asc        ;;
 3: len 5; hex 6a61636b31; asc jack1;;
 4: len 4; hex 8000000c; asc     ;;

insert-不存在唯一索引

加：意向排他锁，无锁冲突，可以并行写入
session 1:

INSERT INTO `my_test_user`
(`user_name`,`user_age`)
VALUES
('alice','40');

session 2:

INSERT INTO `my_test_user`
(`user_name`,`user_age`)
VALUES
('alice_1','40');

锁日志

---TRANSACTION 18797, ACTIVE 3 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 6253998080, query id 235 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18797 lock mode IX
---TRANSACTION 18796, ACTIVE 11 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 6252883968, query id 234 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18796 lock mode IX

update-根据唯一索引

where条件数据不存在

session1：

update `my_test_user` set user_age=41 where id = 1867767;

锁日志

---TRANSACTION 18798, ACTIVE 3 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 9, OS thread handle 6252883968, query id 241 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18798 lock mode IX
RECORD LOCKS space id 15 page no 477 n bits 128 index PRIMARY of table `gallant`.`my_test_user` trx id 18798 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;;

锁定范围
[最大主键,+∞）。与非主键场景完全一致，详情看后面的场景案例

where条件数据存在

session1：

update `my_test_user` set user_age=41 where id = 1;

锁日志

---TRANSACTION 246810, ACTIVE 4 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 370, OS thread handle 123145545375744, query id 5984278 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 246810 lock mode IX
RECORD LOCKS space id 438 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 246810 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 4; hex 00000001; asc     ;;
 1: len 6; hex 00000003c41a; asc       ;;
 2: len 7; hex 01000002d31b13; asc        ;;
 3: len 5; hex 6a61636b31; asc jack1;;
 4: len 4; hex 80000029; asc    );;

锁定范围
record lock：id=1->hex 00000001; asc ;;

update-根据非唯一索引

user_age=11

session1:

update `my_test_user` set user_age=41 where user_age=11;

锁日志：

---TRANSACTION 246875, ACTIVE 3 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 370, OS thread handle 123145545375744, query id 5984433 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 246875 lock mode IX
RECORD LOCKS space id 438 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 246875 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000c; asc     ;;
 1: len 4; hex 00000001; asc     ;;

锁定范围
gap lock：(negative infinity,12)->hex 8000000c; asc ;;
验证
session2:

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (116,'jack111','10'); -- 失败
INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (116,'jack111','11'); -- 失败
INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (116,'jack111','12'); -- 成功
user_age=12

session1:

update `my_test_user` set user_age=41 where user_age=12;

锁日志：

---TRANSACTION 246873, ACTIVE 5 sec
4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 370, OS thread handle 123145545375744, query id 5984430 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 246873 lock mode IX
RECORD LOCKS space id 438 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 246873 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000000c; asc     ;;
 1: len 4; hex 00000001; asc     ;;

RECORD LOCKS space id 438 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 246873 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 4; hex 00000001; asc     ;;
 1: len 6; hex 00000003c459; asc      Y;;
 2: len 7; hex 01000001421f94; asc     B  ;;
 3: len 5; hex 6a61636b31; asc jack1;;
 4: len 4; hex 80000029; asc    );;

RECORD LOCKS space id 438 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 246873 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000e; asc     ;;
 1: len 4; hex 00000003; asc     ;;

锁定范围
gap lock：(13,14)->hex 8000000e; asc ;;
record lock：=12（ID=1）->hex 00000001; asc ;;
next-key-lock：(-∞, 12]->hex 8000000c; asc ;;
综上所述：(-∞,14)
验证
session2:

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (116,'jack111','11'); -- 失败
INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (116,'jack111','12'); -- 失败
INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (116,'jack111','13'); -- 失败
INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (116,'jack111','14'); -- 成功
user_age=13

session1:

update `my_test_user` set user_age=41 where user_age=13;

锁日志：

---TRANSACTION 246980, ACTIVE 358 sec
4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 370, OS thread handle 123145545375744, query id 5984667 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 246980 lock mode IX
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 246980 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000000d; asc     ;;
 1: len 4; hex 00000002; asc     ;;

RECORD LOCKS space id 439 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 246980 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 00000003c4c4; asc       ;;
 2: len 7; hex 020000140621e9; asc      ! ;;
 3: len 5; hex 6a61636b32; asc jack2;;
 4: len 4; hex 80000029; asc    );;

RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 246980 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000e; asc     ;;
 1: len 4; hex 00000003; asc     ;;

锁定范围
gap lock：(13,14)->hex 8000000e; asc ;;
record lock：=13（ID=2）->hex 00000002; asc ;;
next-key-lock：(12, 13]->hex 8000000c; asc ;;
综上所述：(12,14)
验证
session2:

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (113,'jack111','11'); -- 成功
INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (114,'jack111','12'); -- 失败 原因：session2的插入意向锁IX与session1的next-key lock（X）冲突（user_age=13），日志如下
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 246992 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000000d; asc     ;;
 1: len 4; hex 00000002; asc     ;;

下面的日志案例不应该是行锁冲突吗？

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (115,'jack111','13');  -- 失败 原因：session2的插入意向锁IX与session1的gap lock（X）冲突（user_age=13），日志如下
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 246992 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000e; asc     ;;
 1: len 4; hex 00000003; asc     ;;

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (116,'jack111','14'); -- 成功
user_age=40

session1:

update `my_test_user` set user_age=18 where user_age=40;

锁日志：

---TRANSACTION 246994, ACTIVE 2 sec
4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 370, OS thread handle 123145545375744, query id 5984701 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 246994 lock mode IX
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 246994 lock_mode X
Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000028; asc    (;;
 1: len 4; hex 00000009; asc     ;;

RECORD LOCKS space id 439 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 246994 lock_mode X locks rec but not gap
Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000009; asc     ;;
 1: len 6; hex 00000003c4d2; asc       ;;
 2: len 7; hex 02000000a704d9; asc        ;;
 3: len 5; hex 6a61636b39; asc jack9;;
 4: len 4; hex 80000012; asc     ;;

RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 246994 lock_mode X locks gap before rec
Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000029; asc    );;
 1: len 4; hex 0000000a; asc     ;;

锁定范围
gap lock：(40,41)->hex 80000029; asc );;
record lock：=40（ID=9）->hex 00000009; asc ;;
next-key-lock：(39, 40]->hex 80000028; asc (;;
综上所述：(39,41)
验证
session2:
"("符号表示左侧也是闭区间吗？包含39？

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (215,'jack111','39'); --失败 原因：session2的插入意向锁IX与session1的next-key lock冲突(user_age=39)
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 247001 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000028; asc    (;;
 1: len 4; hex 00000009; asc     ;;

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (216,'jack111','40'); --失败 原因：session2的插入意向锁IX与session1的gap lock冲突(user_age=40)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 247001 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000029; asc    );;
 1: len 4; hex 0000000a; asc     ;;

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (216,'jack111','41'); -- 成功
user_age=41

session1:

update `my_test_user` set user_age=18 where user_age=41;

锁日志：

---TRANSACTION 247011, ACTIVE 5 sec
3 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 370, OS thread handle 123145545375744, query id 5984741 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 247011 lock mode IX
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 247011 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 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000029; asc    );;
 1: len 4; hex 0000000a; asc     ;;

RECORD LOCKS space id 439 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 247011 lock_mode X locks rec but not gap
Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 00000003c4e3; asc       ;;
 2: len 7; hex 02000000d10dc2; asc        ;;
 3: len 6; hex 6a61636b3130; asc jack10;;
 4: len 4; hex 80000012; asc     ;;

锁定范围
next-key lock：(40,41]->hex 80000029; asc );;，[41,+∞)->hex 73757072656d756d; asc supremum;;
record lock：id=10->hex 0000000a; asc ;;
综上所述：(40,+∞)
验证
session2:

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (215,'jack111','39'); -- 成功

左闭?[40,41]

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (216,'jack111','40'); -- 失败 原因：session2的插入意向锁IX与session1的next-key lock冲突(user_age=40)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 247012 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000029; asc    );;
 1: len 4; hex 0000000a; asc     ;;

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (217,'jack111','41'); -- 失败 原因：session2的插入意向锁IX与session1的next-key lock冲突(user_age=41)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 247017 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (219,'jack111','66'); -- 失败 原因 同user_age=41更新时的冲突
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 247018 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
user_age=66

session1:

update `my_test_user` set user_age=41 where user_age=66;

锁日志：

---TRANSACTION 247020, ACTIVE 3 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 370, OS thread handle 123145545375744, query id 5984761 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 247020 lock mode IX
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 247020 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;;

锁定范围
next-key lock：[41,+∞)->hex 73757072656d756d; asc supremum;;
验证
session2:

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (216,'jack111','40'); -- 成功
INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (217,'jack111','41'); -- 失败 原因：session2的插入意向锁IX与session1的next-key lock冲突(user_age=41)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 247022 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (219,'jack111','66'); -- 失败 原因：session2的插入意向锁IX与session1的next-key lock冲突(user_age=66)
------- TRX HAS BEEN WAITING 21 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 247023 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (215,'jack111','77'); -- 失败 原因：session2的插入意向锁IX与session1的next-key lock冲突(user_age=77)
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 439 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 247024 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

幻读

RR隔离级别下会有幻读吗？
会：MVCC快照读，未加锁，其他事务写入/更新新数据，再次使用当前读（for update）。均会出现幻读

场景1

session 1session 2
select * from my_test_user where user_age>41;（无数据返回）
INSERT INTO gallant.my_test_user (id, user_name, user_age) values (14,‘jack10’,‘42’);
commit（执行成功）
select * from my_test_user where user_age>41 for update;（返回数据）

场景2

session 1session 2
select * from my_test_user where user_age>41;（无数据返回）
update my_test_user set user_age=42 where user_age=41;
commit（执行成功）
select * from my_test_user where user_age>41 for update;（返回数据）

总结

  1. 基于唯一索引更新，行锁
  2. 基于非唯一索引更新，next-key-lock，行锁+gap锁，锁范围前后均为闭区间，以user_age索引字段为例：
    1. between 18 and 40, 锁范围：[18,40]
    2. 大于> 19，锁范围：(19,+∞)
    3. <13，锁范围：(-∞,13)
    4. =40，锁范围：[19,40]，锁的是距离user_age=40最近的一条数据至40的范围
    5. =44，数据不存在，加意向排他锁IX，此时如果要并发更新数据库中存在的数据会锁超时
  3. 基于唯一索引插入，IX锁，可以并行写入，只有索引冲突时会失败
  4. 基于非唯一索引插入，IX锁，可以并行写入，无冲突
  5. MVCC或者next-key-lock均可以解决幻读，但是RR隔离级别下，某些场景可以脱离MVCC或者next-key-lock，因此RR隔离级别下可能会出现幻读
  6. 很多博客强调幻读是针对insert场景，其实不然，幻读场景2中就是update产生的幻读，幻读是指在同一个事务相同条件下的两次读的数据结果不一致
  7. 普通索引加record锁，每次都需要两把锁，普通索引一把锁（可以看作为读写锁中的读锁），primary key一把锁（可以看作为读写锁中的写锁），普通索引的锁可以重入不排斥，写锁不可以
太阳伞下的阿呆
关注
专栏目录
【MySQL实战05】锁
李维山的博客
04-12 1248
MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。数据库锁设计的初衷是处理并发问题。 全局锁 命令：Flush tables with read lock (FTWRL)，使整个库处于只读状态，可以在客户端断开的时候自动释放。 使用场景：全库逻辑备份。 官方自带的备份工具 mysqldump，使用参数–single-transaction 时，导数据之前会启动一个事务，来确保拿到一致性视图，但只适用于所有的表都使用事务引擎的库，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，
【项目实战】基于MySQL数据库实现分布式锁
最新发布
本本本添哥
07-28 138
在分布式系统中，实现分布式锁是确保资源或数据在多进程或多服务实例间安全访问的关键机制。MySQL数据库虽然主要用于数据存储，但也可以用来实现基本的分布式锁。利用数据库（如MySQL）的事务或行级锁机制来实现。
MySQL实战 | 06/07 简单说说MySQL中的锁
cizhuichen7500452的博客
12-19 175
锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲...
mysql锁 实战测试代码
weixin_34060741的博客
12-18 116
存储引擎 支持的锁定 MyISAM 表级锁 MEMORY 表级锁 InnoDB 行级锁 BDB 页面锁 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。页面锁：开销和加锁时间界于表锁和行锁之间；会出现死...
MySQL锁实战
weixin_44499429的博客
07-13 237
带你认识并实践MySQL的锁
MySql锁实践
weixin_46220076的博客
11-21 359
mysql行锁，mysql是怎么加锁的？
超详细的Mysql锁 实战分析，你想知道的都在这里~
一枝花算不算浪漫的专栏
11-27 1097
1.mysql回表查询 在这里提起主要是用于说明mysql数据和索引的结构，有助于理解后续加锁过程中的一些问题。 mysql索引结构和表数据结构是相互独立的，根据索引查询，只能找到索引列和主键聚簇索引。如果select语句中不包含索引列，mysql会根据主键聚簇索引二次回表查询所需要的数据，查询出来的数据是根据聚簇索引排序的。如果select中只包含索引列和聚簇索引，那么mysql不会再根据聚簇...
数据库MySQL进阶实战
03-31
《数据库MySQL进阶实战》 MySQL是一个广泛应用的关系型数据库管理系统，源自瑞典MySQL AB公司，现归Oracle公司所有。作为RDBMS（关系数据库管理系统）的杰出代表，MySQL因其开源、小巧、高效的特点，在Web应用领域...
mysql实战和优化视频教程
01-04
根据提供的文件信息，我们可以从以下几个方面来探讨与“MySQL实战和优化视频教程”相关的知识点： ### MySQL实战 #### 1. 数据库设计基础 - **规范化理论**：介绍数据库设计中的第一范式到第三范式的基本概念，...
MySQL锁介绍
小英雄
03-10 245
该文章是对极客时间专栏《MySQL实战45讲》相关部分的总结 全局锁 全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL) 当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。 ...
MySQL数据库实战例子（存储引擎、视图、锁机制、分表）
06-30
本例子主要以存储引擎、视图、存储函数、锁机制、分表为主，并且配以对应的博客全面解析。 这个例子的博客系列在这里：http://blog.csdn.net/Jack__Frost/article/category/6998564
MySQL实战：MySQL有哪些锁？
小识的博客
03-25 1991
并发场景 最近做了一些分布式事务的项目，对事务的隔离性有了更深的认识，后续写文章聊分布式事务。今天就复盘一下单机事务的隔离性是如何实现的？ 隔离的本质就是控制并发，如果SQL语句就是串行执行的。那么数据库的四大特性中就不会有隔离性这个概念了，也就不会有脏读，不可重复读，幻读等各种问题了 对数据库的各种并发操作，只有如下四种，写写，读读，读写和写读 写-写 事务A更新一条记录的时候，事务B能同时更新同一条记录吗？ 答案肯定是不能的，不然就会造成脏写问题，那如何避免脏写呢？答案就是加锁 读-读 MySQL读操.
MYSQL--锁实例
qq_56892136的博客
07-16 471
MYISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。MYSQL的表级锁有两种模式。
MySQL事务和锁实战篇
zly03的博客
10-05 596
本文主要介绍了事务的隔离等级和事务的锁机制，偏向于实战理解
Mysql锁实战详细分析
xdpcxq的专栏
11-28 1201
对于上面数据插入的案例，MySQL中采用了自增锁的方式来实现，AUTO-INC锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。
MySQL实战06の锁
weixin_30781433的博客
12-25 72
1. 锁的用处？ MySQL的锁就是为了处理并发问题 2. 锁的分类 全局锁：对整个数据库实例加锁。 加全局锁命令：Flush tables with read lock;（FTWRL） 加全局锁后，数据更新语句，数据定义语句和更新类事务语句都会被阻塞。 适用场景：做全库逻辑备份，就是把每个表selsct出来存成文本。备份为什么加全局锁？不加锁会有什么问题？假...
mysql实战45讲---6 全局锁和表锁
kevin_pull的专栏
06-28 472
6全局锁和表锁 数据库的锁设计是为了处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源访问的规则，而锁就是用来实现这些访问规则的重要数据结构。 根据加锁的范围，mysql里面的锁大致可以分为全局锁、表锁和行锁三类。 全局锁 全局锁就是对整个数据库实例加锁，mysql提供了一个加全局读锁的方法，命令是flush tables with read lock(F...
MySQL产生死锁的根本原因及解决方法
热门推荐
Kevinvcc200的博客
09-16 3万+
一、 什么是死锁 死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等的进程称为死锁进程. 二、 死锁产生的四个必要条件 •互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至
mysql-死锁
m0_46598535的博客
09-23 3984
mmm
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值