mysql的insert锁解析

已于 2023-11-22 16:29:29 修改
阅读量601 收藏
点赞数 5
文章标签： mysql 数据库
于 2023-11-22 14:25:58 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_31661765/article/details/134545872
版权

       本文是读了<mysql是怎么运行的>作者加锁文章之后，想弄清楚insert的加的什么锁，搞的一个测试记录。

准备条件

1.操作的表(借用小孩子的表，侵权，我删)
CREATE TABLE hero (
number INT AUTO_INCREMENT,
name VARCHAR(100),
country varchar(100),
PRIMARY KEY (number),
UNIQUE KEY uk_name (name)
) Engine=InnoDB CHARSET=utf8;
INSERT INTO hero VALUES
(1, 'l刘备', '蜀'),
(3, 'z诸葛亮', '蜀'),
(8, 'c曹操', '魏'),
(15, 'x荀彧', '魏'),
(20, 's孙权', '吴');

最后形成的格式（借用作者小孩子的图）

2.mysql版本和隔离级别
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

mysql> select @@global.tx_isolation;
ERROR 1193 (HY000): Unknown system variable 'tx_isolation'
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)
3.开启锁的日志信息和死锁信息
SHOW GLOBAL VARIABLES LIKE 'innodb_status_output_locks';
SET GLOBAL innodb_status_output_locks = ON;

show variables like "%innodb_print_all_deadlocks%";
set global innodb_print_all_deadlocks = 1;
4.开启多个会话，并用SHOW ENGINE INNODB STATUS \G查看加锁信息

加锁分析

1.记录不存在，插入带主键

       开启两个事务

事务1，插入主键

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO world.hero VALUES(21, 'g关羽', '蜀');
Query OK, 1 row affected (0.00 sec)

SHOW ENGINE INNODB STATUS \G查看锁信息，什么都没有

事务2，也插入同样的主键

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO world.hero VALUES(21, 'g关羽', '蜀');

事务2会阻塞

SHOW ENGINE INNODB STATUS \G查看锁信息，只截取锁的部分，其余省略

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11042 lock mode S locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000015; asc     ;;
 1: len 6; hex 000000002b21; asc     +!;;
 2: len 7; hex 810000010e0110; asc        ;;
 3: len 7; hex 67e585b3e7bebd; asc g      ;;
 4: len 3; hex e89c80; asc    ;;

------------------
TABLE LOCK table `world`.`hero` trx id 11042 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11042 lock mode S locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000015; asc     ;;
 1: len 6; hex 000000002b21; asc     +!;;
 2: len 7; hex 810000010e0110; asc        ;;
 3: len 7; hex 67e585b3e7bebd; asc g      ;;
 4: len 3; hex e89c80; asc    ;;

---TRANSACTION 11041, ACTIVE 24 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 2028, query id 121 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11041 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11041 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000015; asc     ;;
 1: len 6; hex 000000002b21; asc     +!;;
 2: len 7; hex 810000010e0110; asc        ;;
 3: len 7; hex 67e585b3e7bebd; asc g      ;;
 4: len 3; hex e89c80; asc    ;;

事务1给主键记录是number=21的加了x的记录锁，事务2要等待给当前记录加上s的记录锁，所以阻塞

    index PRIMARY 代表的是number主键的名字是PRIMARY

    hex 80000015  16进制代表number的值是21

    hex 67e585b3e7bebd;  utf8的值是g关羽

    hex e89c80; utf8的值是蜀

如果什么都不做，事务2会自动等待超时

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 通过这个分析，看mysql的官网死锁的例子分析

然后看死锁日志

LATEST DETECTED DEADLOCK
------------------------
2023-11-22 16:03:29 0x1e14
*** (1) TRANSACTION:
TRANSACTION 11081, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 16, OS thread handle 15580, query id 301 localhost ::1 root update
INSERT INTO world.hero VALUES(65, 'g????', '??')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11081 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11081 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;;


*** (2) TRANSACTION:
TRANSACTION 11080, ACTIVE 20 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 15, OS thread handle 12456, query id 299 localhost ::1 root update
INSERT INTO world.hero VALUES(65, 'g????', '??')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11080 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11080 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;;

 事务1的第一个操作为该行获取x的记录锁。事务2和事务3的操作都会导致重复键错误，并且它们都请求该行的s的记录锁。当事务1提交时，它释放该行上的x的记录锁，并授予事务2和事务3的排队共享锁请求。此时，会话2和3死锁:由于双方持有s的记录锁，这两个事务都不能获得该行的x的记录锁

2.记录不存在，插入不带主键

同上，事务1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO world.hero(name, country) VALUES( 'g关羽', '蜀');
Query OK, 1 row affected (0.00 sec)

SHOW ENGINE INNODB STATUS \G查看锁信息，什么都没有

事务2也同样的插入，会阻塞（图略，同上）

然后再SHOW ENGINE INNODB STATUS \G查看锁信息

mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 12456, query id 113 localhost ::1 root update
INSERT INTO world.hero VALUES(21, 'g????', '??')
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 5 n bits 80 index uk_name of table `world`.`hero` trx id 11040 lock mode S waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 7; hex 67e585b3e7bebd; asc g      ;;
 1: len 4; hex 80000036; asc    6;;

------------------
TABLE LOCK table `world`.`hero` trx id 11040 lock mode IX
RECORD LOCKS space id 77 page no 5 n bits 80 index uk_name of table `world`.`hero` trx id 11040 lock mode S waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 7; hex 67e585b3e7bebd; asc g      ;;
 1: len 4; hex 80000036; asc    6;;

---TRANSACTION 11035, ACTIVE 14 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 2028, query id 111 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11035 lock mode IX
RECORD LOCKS space id 77 page no 5 n bits 80 index uk_name of table `world`.`hero` trx id 11035 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 7; hex 67e585b3e7bebd; asc g      ;;
 1: len 4; hex 80000036; asc    6;;

index uk_name 的country的唯一索引的名字是uk_name，事务1的唯一索引的uk_name的g关羽记录被加上了x的记录锁，事务2等待在此记录上加入s的next-key锁，所以一直阻塞，下面的唯一索引g关羽的记录

       hex 67e585b3e7bebd;  utf8的值是g关羽

       hex 80000036;  唯一索引带的自增的主键id

3.记录存在，插入带主键

事务1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO world.hero VALUES(15, 'x荀彧', '吴') ;
ERROR 1062 (23000): Duplicate entry '15' for key 'hero.PRIMARY'

SHOW ENGINE INNODB STATUS \G查看锁信息

2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 11, OS thread handle 2028, query id 131 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11043 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11043 lock mode S locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000f; asc     ;;
 1: len 6; hex 000000002122; asc     !";;
 2: len 7; hex 820000011a0137; asc       7;;
 3: len 7; hex 78e88d80e5bda7; asc x      ;;
 4: len 3; hex e9ad8f; asc    ;;

给number=15的主键上了s的记录锁，所以不会阻塞

事务2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO world.hero VALUES(15, 'x荀彧', '吴') ;
ERROR 1062 (23000): Duplicate entry '15' for key 'hero.PRIMARY'

SHOW ENGINE INNODB STATUS \G查看锁信息

2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 15, OS thread handle 12456, query id 133 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11044 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11044 lock mode S locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000f; asc     ;;
 1: len 6; hex 000000002122; asc     !";;
 2: len 7; hex 820000011a0137; asc       7;;
 3: len 7; hex 78e88d80e5bda7; asc x      ;;
 4: len 3; hex e9ad8f; asc    ;;

---TRANSACTION 11043, ACTIVE 39 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 11, OS thread handle 2028, query id 131 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11043 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11043 lock mode S locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000f; asc     ;;
 1: len 6; hex 000000002122; asc     !";;
 2: len 7; hex 820000011a0137; asc       7;;
 3: len 7; hex 78e88d80e5bda7; asc x      ;;
 4: len 3; hex e9ad8f; asc    ;;

事务2也给number=15的主键记录上了s的记录锁，因为s的记录锁是兼容的，所以不会阻塞

******上面的语句换成insert ignore world.hero VALUES(15, 'x荀彧', '吴');是同样的效果，可以自己测试一下。

4.记录存在，插入不带主键

      事务1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO world.hero(name, country) VALUES( 'x荀彧', '吴') ;
ERROR 1062 (23000): Duplicate entry 'x荀彧' for key 'hero.uk_name'

SHOW ENGINE INNODB STATUS \G查看锁信息

4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 11, OS thread handle 2028, query id 141 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11045 lock mode IX
RECORD LOCKS space id 77 page no 5 n bits 80 index uk_name of table `world`.`hero` trx id 11045 lock mode S
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 7; hex 78e88d80e5bda7; asc x      ;;
 1: len 4; hex 8000000f; asc     ;;

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11045 lock_mode X locks rec but not gap
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11045 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;;

事务1给唯一索引记录（x荀彧）加上了s的next-key的锁，给主键记录supremum加上了x的锁和x的记录锁，所以不会阻塞，只会报唯一索引重复的错误

事务2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO world.hero(name, country) VALUES( 'x荀彧', '吴') ;

SHOW ENGINE INNODB STATUS \G查看锁信息

------------------
TABLE LOCK table `world`.`hero` trx id 11046 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11046 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;;

---TRANSACTION 11045, ACTIVE 27 sec
4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 11, OS thread handle 2028, query id 141 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11045 lock mode IX
RECORD LOCKS space id 77 page no 5 n bits 80 index uk_name of table `world`.`hero` trx id 11045 lock mode S
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 7; hex 78e88d80e5bda7; asc x      ;;
 1: len 4; hex 8000000f; asc     ;;

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11045 lock_mode X locks rec but not gap
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11045 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;;

事务2要给主键supremum加x的next-key锁,所以一直阻塞等待，因为事务1已经上了一个x的next-key锁了

***********INSERT ignore INTO world.hero(name, country) VALUES( 'x荀彧', '吴') ;
                INSERT INTO world.hero VALUES(30, 'x荀彧', '吴') ; 

上面的两条语句也能达到同样的效果

如果换成INSERT INTO world.hero VALUES(16, 'x荀彧', '吴') ;，又会有怎样的效果的

效果和上面的一样，看事务1的加锁情况

4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 11, OS thread handle 2028, query id 153 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11047 lock mode IX
RECORD LOCKS space id 77 page no 5 n bits 80 index uk_name of table `world`.`hero` trx id 11047 lock mode S
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 7; hex 78e88d80e5bda7; asc x      ;;
 1: len 4; hex 8000000f; asc     ;;

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11047 lock_mode X locks rec but not gap
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11047 lock_mode X locks gap before rec
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 00000000273e; asc     '>;;
 2: len 7; hex 0200000089065f; asc       _;;
 3: len 7; hex 73e5ad99e69d83; asc s      ;;
 4: len 4; hex e9ad8f37; asc    7;;

上面主键supremum加的锁，换到了主键number=20的了，即number=16的下一个

事务2的锁也是替换了上面的了

------------------
TABLE LOCK table `world`.`hero` trx id 11048 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11048 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 00000000273e; asc     '>;;
 2: len 7; hex 0200000089065f; asc       _;;
 3: len 7; hex 73e5ad99e69d83; asc s      ;;
 4: len 4; hex e9ad8f37; asc    7;;

---TRANSACTION 11047, ACTIVE 19 sec
4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 11, OS thread handle 2028, query id 153 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11047 lock mode IX
RECORD LOCKS space id 77 page no 5 n bits 80 index uk_name of table `world`.`hero` trx id 11047 lock mode S
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 7; hex 78e88d80e5bda7; asc x      ;;
 1: len 4; hex 8000000f; asc     ;;

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11047 lock_mode X locks rec but not gap
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11047 lock_mode X locks gap before rec
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 00000000273e; asc     '>;;
 2: len 7; hex 0200000089065f; asc       _;;
 3: len 7; hex 73e5ad99e69d83; asc s      ;;
 4: len 4; hex e9ad8f37; asc    7;;
5.insert ignore into 和 insert into 加的锁大致一样
6.ON DUPLICATE KEY UPDATE，插入带主键

事务1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO world.hero VALUES(20, 's孙权', '吴') ON DUPLICATE KEY UPDATE country='魏';
Query OK, 2 rows affected (0.00 sec)

加锁分析

MySQL thread id 11, OS thread handle 2028, query id 163 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11049 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11049 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000002b29; asc     +);;
 2: len 7; hex 01000001310e3f; asc     1 ?;;
 3: len 7; hex 73e5ad99e69d83; asc s      ;;
 4: len 3; hex e9ad8f; asc    ;;

事务1，给主键number=20的加了一个x的记录锁，所以执行成功

事务2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO world.hero VALUES(20, 's孙权', '吴') ON DUPLICATE KEY UPDATE country='魏';

加锁分析

------------------
TABLE LOCK table `world`.`hero` trx id 11050 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11050 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000002b29; asc     +);;
 2: len 7; hex 01000001310e3f; asc     1 ?;;
 3: len 7; hex 73e5ad99e69d83; asc s      ;;
 4: len 3; hex e9ad8f; asc    ;;

---TRANSACTION 11049, ACTIVE 18 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 2028, query id 163 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11049 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11049 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000002b29; asc     +);;
 2: len 7; hex 01000001310e3f; asc     1 ?;;
 3: len 7; hex 73e5ad99e69d83; asc s      ;;
 4: len 3; hex e9ad8f; asc    ;;

事务2也要给number=20的加了一个x的记录的锁，所以等待

7.ON DUPLICATE KEY UPDATE，插入不带主键

事务1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO world.hero(name, country) VALUES('s孙权', '吴') ON DUPLICATE KEY UPDATE country='魏5';
Query OK, 2 rows affected (0.00 sec)
4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 2028, query id 173 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11052 lock mode IX
RECORD LOCKS space id 77 page no 5 n bits 80 index uk_name of table `world`.`hero` trx id 11052 lock_mode X
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 7; hex 73e5ad99e69d83; asc s      ;;
 1: len 4; hex 80000014; asc     ;;

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11052 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 80000014; asc     ;;
 1: len 6; hex 000000002b2c; asc     +,;;
 2: len 7; hex 01000001320cc1; asc     2  ;;
 3: len 7; hex 73e5ad99e69d83; asc s      ;;
 4: len 4; hex e9ad8f35; asc    5;;

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11052 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;;

事务1 给唯一索引（s孙权）加上了x的next-key的锁，给number=20的主键加上了x的记录锁，并给supremum加上了x的next-key的锁

同理事务也按上面的处理，加锁分析

------------------
TABLE LOCK table `world`.`hero` trx id 11053 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11053 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;;

---TRANSACTION 11052, ACTIVE 18 sec
4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 2028, query id 173 localhost ::1 root
TABLE LOCK table `world`.`hero` trx id 11052 lock mode IX
RECORD LOCKS space id 77 page no 5 n bits 80 index uk_name of table `world`.`hero` trx id 11052 lock_mode X
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 7; hex 73e5ad99e69d83; asc s      ;;
 1: len 4; hex 80000014; asc     ;;

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11052 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 80000014; asc     ;;
 1: len 6; hex 000000002b2c; asc     +,;;
 2: len 7; hex 01000001320cc1; asc     2  ;;
 3: len 7; hex 73e5ad99e69d83; asc s      ;;
 4: len 4; hex e9ad8f35; asc    5;;

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `world`.`hero` trx id 11052 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;;

事务2要给supremum加上x的next-key的锁的，所以一直等待

结论

        对于同一语句来说，加不加主键，其实都是加了的，不加主键的主键默认是自增的id+1，在RR隔离级别下，

如果插入带主键，主键存在，insert和insert ignore会加s型的记录锁，ON DUPLICATE KEY UPDATE会加x的记录锁，主键不存在，insert和insert ignore会给前一个加入一个x的记录锁。

如果插入不带主键，insert和insert ignore正在插入的，会给下一个记录加一个x的next-key锁，ON DUPLICATE KEY UPDATE会当前记录一个x的记录锁和下一个记录的x的next-key的锁。

对于唯一的二级索引下，insert和insert ignore会加s的next-key的锁，ON DUPLICATE KEY UPDATE会加x的next-key的锁

对于RC级别，大家可以测试一下

最后，本人能力有限，如果上面的有纰漏和问题，欢迎大佬指出

qq_31661765
关注
MySQL insert的锁问题
编程探索与数字构建 | Python与Java之旅
07-12 669
MySQL insert的锁问题
读MySQL源码再看 insert 加锁流程，一个字：牛
hnjsjsac的博客
04-20 733
在之前的博客中，我写了一系列的文章，比较系统的学习了 MySQL 的事务、隔离级别、加锁流程以及死锁，我自认为对常见 SQL 语句的加锁原理已经掌握的足够了，但看到热心网友在评论中提出的一...
MySQL 中的 INSERT 是怎么加锁的？
m0_38048955的博客
12-25 2256
热心网友在评论中提出的一个问题，我还是彻底被问蒙了。
mysql insert手动加锁_mysql insert 加锁流程
weixin_42349182的博客
01-19 555
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the ga...
mysql insert 锁
vincent_duan的专栏
11-30 139
撸了今年阿里、头条和美团的面试，我有一个重要发现.......>>> ...
解析MySQL中INSERT INTO SELECT的使用
09-10
MySQL中的`INSERT INTO SELECT`语句是一种非常实用的数据库操作，它允许你从一个或多个数据源中选择数据，并将其插入到另一个表中。这种操作在数据迁移、数据整合或者生成汇总报告时非常常见。下面将详细介绍`INSERT...
MySQL 锁机制全面解析
一名后端程序猿
04-18 3179
mysql中常见的锁：全局锁、表锁、行锁、共享锁、排它锁、乐观锁、悲观锁、意向锁、间隙锁、临键锁
如何优化MySQL insert性能
12-14
在优化MySQL的INSERT性能时，面对大量数据的系统，我们需要考虑如何有效地减少插入时间，以降低潜在的风险和提高系统的稳定性。以下是一些经过测试的有效策略： 1. **批量插入**： - 传统的方式是逐行插入数据，...
MySQL特殊INSERT语句的锁机制解析
"MySQL45讲读书笔记40讲主要探讨了插入语句（insert）在特定情况下的锁机制，特别是自增主键的优化以及insert...select语句的锁行为。文章指出，MySQL尽力在获取自增ID后立即释放自增锁，但有些insert操作可能需要对...
mysql insert加锁机制
duoyasong5907的博客
08-11 1059
insert锁
MySQL 数据库中 Insert 语句的锁机制
良月柒
05-11 1957
在数据库系统中，Insert 语句的锁机制是保证数据一致性和完整性的重要手段之一。通过合理设计和优化数据库操作，我们可以提高系统的并发性能，减少锁竞争和等待时间，从而提升系统的稳定性和可靠性。
故障分析 | MySQL Insert 加锁与死锁分析
chiweiliu4439的博客
07-03 399
原创：胡呈清 前 言 本文是由爱可生运维团队出品的「MySQL专栏」系列文章，内容来自于运维团队一线实战经验，涵盖MySQL各种特性的实践，优化案例，数据库架构，HA，监控等，有扫雷功效。 爱可生开源社区持续运营维护的小目标： 每周至少推送一篇高质量技术文...
MySQL innodb中各种SQL语句加锁分析
weixin_34266504的博客
07-07 267
Locking read（ SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE），UPDATE以及DELETE语句通常会在他扫描的索引所有范围上加锁，忽略没有用到索引的那部分where语句。举个例子： CREATE TABLE `test` ( `id` int(11) NOT NULL DEFAULT '0', `nam...
mysql insert锁机制
lgxzzz的博客
12-29 3799
一、前言 上周遇到一个因insert而引发的死锁问题，其成因比较令人费解。 于是想要了解一下insert加锁机制，但是发现网上介绍的文章比较少且零散，挖掘过程比较忙乱。 本以为只需要系统学习一个较完全的逻辑，但是实际牵扯很多innodb锁相关知识及加锁方式。我好像并没有那么大的能耐，把各种场景的加锁过程一一列举并加之分析；亦没有太多的精力验证网上的言论的准确性。 只好根据现在了解的内容，参考官方文档，...
MySQL INSERT语句的锁
辛明辉的专栏
07-22 558
INSERT…SELECT 表初始化 CREATE TABLE `t` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `c` INT(11) DEFAULT NULL, `d` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c` (`c`) ) ENGINE=InnoDB; INSERT INTO t VALUES (null,1,1); INSERT INTO t VALUES (null
mysql INSERT INTO时的锁
最新发布
ithongchou的博客
05-28 1315
操作，也只有成功提交的操作才会对表产生影响。而且，在事务内部，其他线程只能查看事务开始前的数据状态，无法看到正在进行的中间状态。将不会触发，因为这将导致一个错误，因为自增主键值已经存在。在这种情况下，你需要先检查记录是否存在，如果不存在，再执行插入操作。提交事务）来确保在事务执行过程中，只有持有事务的线程可以对表进行写操作。操作时直接锁定整个表，确保在同一时间只有一个线程可以对表进行写操作。如果表中有一个自增的主键，并且你尝试插入一个已经存在的值，那么。是唯一的，并且已经存在，那么。
mysql insert锁机制(insert死锁)
爱笑的k11的博客
03-09 2193
一、前言 上周遇到一个因insert而引发的死锁问题，其成因比较令人费解。 于是想要了解一下insert加锁机制，但是发现网上介绍的文章比较少且零散，挖掘过程比较忙乱。 本以为只需要系统学习一个较完全的逻辑，但是实际牵扯很多innodb锁相关知识及加锁方式。我好像并没有那么大的能耐，把各种场景的加锁过程一一列举并加之分析；亦没有太多的精力验证网上的言论的准确性。 只好根据现在了解的内容，参考官方文...
MySQL——插入加锁/唯一索引插入死锁/批量插入效率
娜娜米的博客
03-08 9791
本篇主要介绍MySQL跟加锁相关的一些概念、MySQL执行插入Insert时的加锁过程、唯一索引下批量插入可能导致的死锁情况，以及分别从业务角度和MySQL配置角度介绍提升批量插入的效率的方法；
mysql insert语句 锁_MySQL实战45讲Day39----insert语句的锁
weixin_30181209的博客
02-07 285
一、insert … select 语句：对于表t，t2：CREATE TABLE `t` (`id` int(11) NOT NULL AUTO_INCREMENT,`c` int(11) DEFAULT NULL,`d` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `c` (`c`)) ENGINE=InnoDB;insert into...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值