mysql中行级锁的一些实验

最新推荐文章于 2024-10-19 20:05:20 发布
最新推荐文章于 2024-10-19 20:05:20 发布
阅读量479 收藏 1
点赞数
文章标签： mysql 间隙锁 行级锁 next-key锁 记录锁
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Waves___/article/details/105416862
版权

目录

看此文前先看 MySQL 中的锁机制，本文是看了该文后做的一些实验记录和总结，仅够参考，更多的是需要自己在mysql中做实验，理解。

总结

1、通过主键索引操作单个记录，只会对该主键索引记录加record lock；因此，此时只是不能修改该条记录。

2、通过唯一索引操作单个记录，会对改唯一索引记录加record lock，对该唯一索引对应的 主键索引记录加record lock；因此，此时只是不能修改该条记录

3、通过普通索引操作单个记录，会对该范围进行左延伸/右延伸 后，延伸后得到一个新范围；

  • 对新范围里的普通索引记录，加Next-key锁（record lock+gap lock）；
  • 对该新范围里的主键索引记录加record lock；

因此，此时不能修改该条记录，也不能在新范围里插入新（普通索引）纪录

4、通过主键索引操作范围多个记录，会对该范围进行左延伸/右延伸，延伸后得到一个新范围；

  • 对新范围里的主键索引记录，加Next-key锁（record lock+gap lock）

因此，此时不能修改范围里的记录，也不能在新范围里插入新（主键索引）纪录

5、通过唯一索引操作范围多个记录，会对该范围进行左延伸/右延伸，延伸后得到一个新范围；

  • 对新范围里的唯一索引记录，加Next-key锁（record lock+gap lock）
  • 对新范围里的主键索引记录加record lock；

因此，此时不能修改范围里的记录，也不能在新范围里插入新（唯一索引）纪录

6、通过普通索引操作范围多个记录，同 5，因此，此时不能修改范围里的记录，也不能在新范围里插入新（普通索引）纪录。

7、通过无索引操作单个记录或范围多个记录，会对所有的主键索引记录加即Next-key锁（record lock+gap lock）。因此，此时无法修改任何值、插入任何行。

这也就是为什么 MySQL 推荐通过索引操作数据，最好是主键。

实验前的准备

mysql> create table t(
	  id int primary key,
	  c1 int,
	  c2 int,
	  c3 int
	);
mysql> create unique index idx_t_c1 on t(c1);
mysql> create index idx_t_c2 on t(c2);
mysql> insert into t(id,c1,c2,c3) values (1,1,2,3),(5,4,5,6),(9,7,8,9),(13,10,11,12);
mysql> select * from t;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |    2 |    3 |
|  5 |    4 |    5 |    6 |
|  9 |    7 |    8 |    9 |
| 13 |   10 |   11 |   12 |
+----+------+------+------+
4 rows in set (0.00 sec)
mysql> desc t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| c1    | int(11) | YES  | UNI | NULL    |       |
| c2    | int(11) | YES  | MUL | NULL    |       |
| c3    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> SET GLOBAL innodb_status_output=ON;
mysql> SET GLOBAL innodb_status_output_locks=ON;

对于select的语句中有范围

如果select的范围为 [a,b], 则会向左延伸，找第一个<=a的值；向右延伸，找第一个值>b的值，形成新的范围[a,d]或(c,d]，锁住这之间的间隙；
举例：假设当前表中一个索引为id, 该索引的值有 1 5 9 13 ，其实就是前面创建的那张表。

select * from t;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |    2 |    3 |
|  5 |    4 |    5 |    6 |
|  9 |    7 |    8 |    9 |
| 13 |   10 |   11 |   12 |
+----+------+------+------+
select * from t where id between 5 and 9 for update;  锁定[5],(5,9],(9,13], 		 		即[5,13]
select * from t where id between 3 and 9 for update;  锁定(1,5],(5,9],(9,13],		 		即(1,13]
select * from t where id between 3 and 13 for update; 锁定(1,5],(5,9],(9,13],(13,+∞)		即(1,+∞)
select * from t where id between 3 and 15 for update; 锁定(1,5],(5,9],(9,13],(13,+∞)		即(1,+∞)
select * from t where id between 1 and 9 for update;  锁定[1],(1,5],(5,9],(9,13]	 		即[1,13]
select * from t where id between 1 and 13 for update; 锁定[1],(1,5],(5,9],(9,13],(13,+∞)	即[1,+∞]
select * from t where id between 1 and 15 for update; 锁定[1],(1,5],(5,9],(9,13],(13,+∞)	即[1,+∞]
select * from t where id between 0 and 15 for update; 锁定(-∞,1],(1,5],(5,9],(9,13],(13,+∞)	即[-∞,+∞]

这个是为后面做准备的，看不懂没关系，先往下看，看到（4）的时候可以回来看这里。

（1）通过主键索引操作单个记录

mysql> begin;
mysql> SELECT * FROM t WHERE id = 5 FOR UPDATE;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  5 |    4 |    5 |    6 |
+----+------+------+------+
1 row in set (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS;
---TRANSACTION 43298, ACTIVE 11 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 30, OS thread handle 11440, query id 429 localhost ::1 root starting
SHOW ENGINE INNODB STATUS
TABLE LOCK table `test`.`t` trx id 43298 lock mode IX
RECORD LOCKS space id 317 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 43298 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000a91d; asc       ;;
 2: len 7; hex d500000152011d; asc     R  ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000006; asc     ;;

结果分析：存在 2 个锁结构（表IX锁、主键索引上的X记录锁），锁定了1条索引记录；

  • 表t上存在IX锁；
  • 主键索引上存在一个X记录锁，锁定了id为5的那条主键索引记录（主键索引记录包括该行全部数据）。

（2）通过唯一索引操作单个记录

  • c1是唯一索引
mysql> begin;
mysql> SELECT * FROM t WHERE c1 = 4 FOR UPDATE;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  5 |    4 |    5 |    6 |
+----+------+------+------+
1 row in set (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS;
---TRANSACTION 43299, ACTIVE 10 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 30, OS thread handle 11440, query id 433 localhost ::1 root starting
SHOW ENGINE INNODB STATUS
TABLE LOCK table `test`.`t` trx id 43299 lock mode IX
RECORD LOCKS space id 317 page no 4 n bits 72 index idx_t_c1 of table `test`.`t` trx id 43299 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 317 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 43299 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000a91d; asc       ;;
 2: len 7; hex d500000152011d; asc     R  ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000006; asc     ;;

结果分析：存在 3 个锁结构（表IX锁、主键索引上X记录锁、唯一索引上的X记录锁），锁定了2条索引记录；

  • 表t上存在IX锁；
  • 主键索引上存在一个X记录锁，锁定了id为5的主键索引记录（主键索引记录包括该行全部数据）；
  • 索引idx_t_c1上存在一个X记录锁，锁定了c1为4的唯一索引记录（唯一索引记录中只包括id和c1两列）。

噢！它是先通过唯一索引idx_t_c1，由c1=4找到id=5，知道了id=5，再通过主键索引找到id=5的记录。
记录锁永远都是锁定索引记录，锁定非聚簇索引时会先锁定聚簇索引。
如果表中没有定义索引，InnoDB 默认为表创建一个隐藏的聚簇索引，并且使用该索引锁定记录。

（3）通过普通索引操作单个记录

  • c2为普通索引
mysql> begin;
mysql> SELECT * FROM t WHERE c2 = 5 FOR UPDATE;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  5 |    4 |    5 |    6 |
+----+------+------+------+
1 row in set (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS;
---TRANSACTION 43480, ACTIVE 15 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 53, OS thread handle 11440, query id 656 localhost ::1 root starting
SHOW ENGINE INNODB STATUS
TABLE LOCK table `test`.`t` trx id 43480 lock mode IX
RECORD LOCKS space id 318 page no 5 n bits 80 index idx_t_c2 of table `test`.`t` trx id 43480 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 318 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 43480 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000a9c8; asc       ;;
 2: len 7; hex c600000142011d; asc     B  ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000006; asc     ;;

RECORD LOCKS space id 318 page no 5 n bits 80 index idx_t_c2 of table `test`.`t` trx id 43480 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 80000008; asc     ;;
 1: len 4; hex 80000009; asc     ;;

结果分析：存在4个锁结构（表IX锁、主键索引上X记录锁、普通索引上的X next-key锁、普通索引上的X间隙锁），锁定了3个索引记录；

  • 表 t 上存在 IX 锁，
  • 索引 idx_t_c2 上存在一个 next-key 锁（锁定了c2 (2, 5]）
    索引 idx_t_c2 还有一个 X 间隙锁（c2 (5,8] 被锁定 ）；
  • 主键索引上存在一个 X 记录锁（id = 5的主键索引记录被锁定）。
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |    2 |    3 |
|  5 |    4 |    5 |    6 |
|  9 |    7 |    8 |    9 |
| 13 |   10 |   11 |   12 |
+----+------+------+------+

--另一个事务
mysql> begin;
mysql> insert into t(id,c1,c2,c3) values (-1,-1,1,0);  			成功
mysql> insert into t(id,c1,c2,c3) values (-7,-7,2,0);  			成功
mysql> insert into t(id,c1,c2,c3) values (-3,-3,3,0);  			失败
mysql> insert into t(id,c1,c2,c3) values (-4,-4,4,0);  			失败
mysql> insert into t(id,c1,c2,c3) values (-5,-5,5,0);  			失败
mysql> insert into t(id,c1,c2,c3) values (-6,-6,7,0);  			失败
mysql> insert into t(id,c1,c2,c3) values (-2,-2,8,0);  			失败
mysql> insert into t(id,c1,c2,c3) values (7,-7,12,0);  			成功
mysql> rollback;

反正就是c2的值在(2,5],(5,8]之间的，都不能插入，对于c2为5的记录不能更新。

（4）通过主键索引操作范围多个记录

mysql> begin;
mysql> SELECT * FROM t WHERE id BETWEEN 3 and 10 FOR UPDATE;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  5 |    4 |    5 |    6 |
|  9 |    7 |    8 |    9 |
+----+------+------+------+
2 rows in set (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS;
---TRANSACTION 43300, ACTIVE 9 sec
2 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 30, OS thread handle 11440, query id 437 localhost ::1 root starting
SHOW ENGINE INNODB STATUS
TABLE LOCK table `test`.`t` trx id 43300 lock mode IX
RECORD LOCKS space id 317 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 43300 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000a91d; asc       ;;
 2: len 7; hex d500000152011d; asc     R  ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000006; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 6; hex 00000000a91d; asc       ;;
 2: len 7; hex d500000152012a; asc     R *;;
 3: len 4; hex 80000007; asc     ;;
 4: len 4; hex 80000008; asc     ;;
 5: len 4; hex 80000009; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000000a91d; asc       ;;
 2: len 7; hex d5000001520137; asc     R 7;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000b; asc     ;;
 5: len 4; hex 8000000c; asc     ;;

结果分析：存在2个锁结构（表IX锁、主键索引上X next-key锁），锁定了3条记录；

  • 表t上存在IX锁；
  • 主键索引上存在3个X Next-key锁（ (1, 5]、(5, 9]、(9,13]被锁定 ）；
    （此时，只可以插入id小于1和大于13的数据；）
-- 另一个事务
mysql> begin;
mysql> insert into t(id,c1,c2,c3) values (0,0,0,0);  			成功
mysql> update t set c1 = 111 where id = 1; 						成功
mysql> insert into t(id,c1,c2,c3) values (2,222,222,222);  		失败
mysql> insert into t(id,c1,c2,c3) values (3,333,333,333); 	 	失败
mysql> insert into t(id,c1,c2,c3) values (4,444,444,444); 		失败
mysql> update t set c1 = 555 where id = 5; 						失败
mysql> insert into t(id,c1,c2,c3) values (8,888,888,888);  		失败
mysql> insert into t(id,c1,c2,c3) values (10,100,100,100);  	失败
mysql> insert into t(id,c1,c2,c3) values (11,1111,1111,1111); 	失败
mysql> insert into t(id,c1,c2,c3) values (12,1222,1222,1222); 	失败
mysql> update t set c1 = 1333 where id = 13; 					失败
mysql> insert into t(id,c1,c2,c3) values (14,1444,1444,1444); 	成功
mysql> rollback;

再举一个例子：

mysql> begin;
mysql> SELECT * FROM t WHERE id BETWEEN 3 and 15 FOR UPDATE;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  5 |    4 |    5 |    6 |
|  9 |    7 |    8 |    9 |
| 13 |   10 |   11 |   12 |
+----+------+------+------+
3 rows in set (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS;
---TRANSACTION 43336, ACTIVE 11 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 49, OS thread handle 19320, query id 495 localhost ::1 root starting
SHOW ENGINE INNODB STATUS
TABLE LOCK table `test`.`t` trx id 43336 lock mode IX
RECORD LOCKS space id 317 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 43336 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 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000a91d; asc       ;;
 2: len 7; hex d500000152011d; asc     R  ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000006; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 6; hex 00000000a91d; asc       ;;
 2: len 7; hex d500000152012a; asc     R *;;
 3: len 4; hex 80000007; asc     ;;
 4: len 4; hex 80000008; asc     ;;
 5: len 4; hex 80000009; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000000a91d; asc       ;;
 2: len 7; hex d5000001520137; asc     R 7;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000b; asc     ;;
 5: len 4; hex 8000000c; asc     ;;
  • 主键索引上存在4个X Next-key锁（id (1,5]、(5, 9]、(9,13]、(13, +无穷)被锁定）；
-- 另一个事务
mysql> begin;
mysql> insert into t(id,c1,c2,c3) values (0,0,0,0);  			成功
mysql> update t set c1 = 111 where id = 1; 						成功
mysql> insert into t(id,c1,c2,c3) values (2,222,222,222);  		失败
mysql> insert into t(id,c1,c2,c3) values (22,2222,2222,2222);  	失败
mysql> rollback;

（5）通过唯一索引操作范围多个记录

mysql> begin;
mysql> SELECT * FROM t WHERE c1 BETWEEN 3 and 9 FOR UPDATE;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  5 |    4 |    5 |    6 |
|  9 |    7 |    8 |    9 |
+----+------+------+------+
2 rows in set (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS;
---TRANSACTION 43378, ACTIVE 11 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 53, OS thread handle 11440, query id 554 localhost ::1 root starting
SHOW ENGINE INNODB STATUS
TABLE LOCK table `test`.`t` trx id 43378 lock mode IX
RECORD LOCKS space id 317 page no 4 n bits 80 index idx_t_c1 of table `test`.`t` trx id 43378 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000005; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000009; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000d; asc     ;;

RECORD LOCKS space id 317 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 43378 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000a91d; asc       ;;
 2: len 7; hex d500000152011d; asc     R  ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000006; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 6; hex 00000000a91d; asc       ;;
 2: len 7; hex d500000152012a; asc     R *;;
 3: len 4; hex 80000007; asc     ;;
 4: len 4; hex 80000008; asc     ;;
 5: len 4; hex 80000009; asc     ;;

结果分析：存在3个锁结构，锁定了5条索引记录；

  • 表t上存在IX锁；
  • 主键索引上存在2个X 记录锁（id为5、9的主键索引记录被锁定，即c2为4和7的记录不能被修改了）
  • 唯一索引上存在3个X Next-key锁（c1 (1, 4]、(4, 7]、(7, 10]被锁定），
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |    2 |    3 |
|  5 |    4 |    5 |    6 |
|  9 |    7 |    8 |    9 |
| 13 |   10 |   11 |   12 |
+----+------+------+------+

--另一个事务
mysql> begin;
mysql> insert into t(id,c1,c2,c3) values (-1,0,0,0);  			成功
mysql> insert into t(id,c1,c2,c3) values (0,3,0,0);  			失败
mysql> insert into t(id,c1,c2,c3) values (6,-1,0,0);  			成功
mysql> insert into t(id,c1,c2,c3) values (8,22,0,0);  			成功
mysql> insert into t(id,c1,c2,c3) values (22,8,0,0);  			失败
mysql> update t set c2 = 0 where c1 = 1; 						成功  
mysql> update t set c2 = 0 where c1 = 4; 						失败  
mysql> update t set c2 = 0 where c1 = 2; 						成功，因为锁的是间隙，不是索引记录（因为并没有这条记录，所以实际上什么都没有改变）
mysql> update t set c2 = 0 where c1 = 10; 						失败，不能通过c1改变c1=10这条记录的其他值
mysql> update t set c2 = 0 where id = 13; 						成功，但是可以通过id改变c1=10的这条记录
mysql> rollback;

反正就是c1的值在(1,4],(4,7],(7,10]之间的，都不能插入，对于c1为4、7的记录不能更新。
c1的10的记录可以更新，但是不能通过c1改变c1=10这条记录的其他值，可以通过id改变c1=10的这条记录

（6）通过普通索引操作范围多个记录

同（5）

（7）通过无索引操作单个记录或范围多个记录

mysql> begin;
mysql> SELECT * FROM t WHERE c3 = 6 FOR UPDATE;  （SELECT * FROM t WHERE c3 BETWEEN 4 and 10 FOR UPDATE;）
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  5 |    4 |    5 |    6 |
+----+------+------+------+
mysql> SHOW ENGINE INNODB STATUS;
---TRANSACTION 43513, ACTIVE 14 sec
2 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 53, OS thread handle 11440, query id 730 localhost ::1 root starting
SHOW ENGINE INNODB STATUS
TABLE LOCK table `test`.`t` trx id 43513 lock mode IX
RECORD LOCKS space id 318 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 43513 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 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000a9c8; asc       ;;
 2: len 7; hex c6000001420110; asc     B  ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000002; asc     ;;
 5: len 4; hex 80000003; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000a9c8; asc       ;;
 2: len 7; hex c600000142011d; asc     B  ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000006; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 6; hex 00000000a9c8; asc       ;;
 2: len 7; hex c600000142012a; asc     B *;;
 3: len 4; hex 80000007; asc     ;;
 4: len 4; hex 80000008; asc     ;;
 5: len 4; hex 80000009; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 00000000a9c8; asc       ;;
 2: len 7; hex c6000001420137; asc     B 7;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000b; asc     ;;
 5: len 4; hex 8000000c; asc     ;;

结果分析：存在2个锁结构，锁定了5个索引项；

  • 表t上存在IX锁，
  • 主键索引上存在5个next-key锁，锁定了所有的主键范围（(-∞,1], (1,5],(5,9],(9,13],(13,+∞）。

此时其他事务无法插入任何数据。 不仅无法修改c3=6的那条记录，其他所有记录都无法修改。

--另一个事务
mysql> begin;
mysql> insert into t(id,c1,c2,c3) values (-1,-1,1,0);  			失败
mysql> insert into t(id,c1,c2,c3) values (20,-2,2,0);  			失败
mysql> update t set c2 = 0 where c3 = 6; 						失败
mysql> update t set c2 = 0 where c3 = 9; 						失败
mysql> update t set c2 = 0 where id = 9; 						失败
mysql> rollback;

也就是说这个时候，不能够执行任何写操作。

死锁

mysql> select * from t;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |    2 |    3 |
|  5 |    4 |    5 |    6 |
|  9 |    7 |    8 |    9 |
| 13 |   10 |   11 |   12 |
+----+------+------+------+
4 rows in set (0.00 sec)

--事务T1												--事务T2
mysql> begin;											mysql> begin;			
mysql> select * from t where id=22 for update;
Empty set (0.00 sec) 		

														mysql> select * from t where id=23 for update;
														Empty set (0.00 sec)
												
														mysql> insert into t(id,c1,c2,c3) values (23,-2,2,0); 	
												
mysql> insert into t(id,c1,c2,c3) values (22,-1,1,0); 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
														
														Query OK, 1 row affected (10.77 sec)

因为事务T1获得了(13,+∞)的间隙锁，事务T2也获得(13,+无穷大)的间隙锁（因为最大ID是13，锁13之后的间隙，间隙锁之间不互斥）。T2想要插入id=23的记录，但是间隙被T1锁了；T1想要插入id=22的记录，但是间隙被T2锁了；就造成了死锁。
从上面实验可以看出，mysql检测到死锁后，就把是T1给关闭了，然后T2才能成功插入数据。

Waves___
关注
MySQL——数据库锁原理
庄小焱
12-10 1675
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。例如，操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素，为了保证一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性。人们认为行级锁总会增加开销。实际上，只有当实现本身会增加开销时，行级锁才会增加开销。InnoDB存储引擎不需要锁升级，因为一个锁和多个锁的开销是相同的。InnoDB存储引擎锁的实现和Oracle数据库非常类似，提供一致性的
一篇文章让你搞懂mysql的三种锁及数据库表上做查询慢的原因[图]
minemi的博客
12-15 448
一、一篇文章让你搞懂mysql的三种锁 前言 锁的重要性想必不用多说了吧，作为面试造火箭中最重要的一个点之一，可谓是不得不会，说出来都是一把辛酸泪，什么悲观锁，乐观锁，自旋锁，偏向锁等等等等，虽然说在我们平常写代码的时候很少会用到它们，但是实现的思想是很需要我们去研究的。 之前和大家聊过分布式锁的实现方式以及其解决的问题。 那么今天moon就和大家聊聊mysql中的锁，读完这篇文章你将会学到： 全局锁 全局锁就是说对整个数据库进行加锁。 具体加锁的方式可以用以下代码： flushtableswithr
TX锁是区别于行级锁的（实验）
haiross的专栏
11-26 1658
1、会话A：                                                                   //A先获得TM（RX），再得TX（X），最后在id=1的数据行（的锁位）上加上行级锁（RLS）。      savepoint a；      Update table_name   ......where id=1； 2
mysql行锁、表锁小实验------select for update
认知 行动 坚持
07-26 2857
来建一个表： CREATE TABLE `a` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `x` int(10) unsigned NOT NULL, `y` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `idx_x` (`x`) ) ENGINE...
跑了4个实验，实战讲解 MySQL的行锁、间隙锁...​
weixin_44742132的博客
12-14 163
大家好，我是Tom哥~今天跟大家聊一聊MySQL的事务隔离，并通过一些实验做了些总结。光说不练，假把式，没有经过实践就没有话语权。我们都知道数据库有四种隔离级别，分别是：读未提交（READ...
mysql 锁实验_engineer feng 的实验报告 （mysql锁）
weixin_42525178的博客
01-19 156
本篇文章说明mysql的几个锁的情况。锁MyISAM锁问题我们使用存储引擎 MyISAM进行锁测试。MyISAM锁分为两种模式，第一种 表共享锁(Table Read Lock)和表独占写锁(Table Write Lock)。在多个线程同时需要写入的情况下，当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。MyISAM引擎中，锁的...
mysql 事务锁实验
天高海阔 扬帆远行
10-29 721
mysql get_lock(), release_lock(), is_free_lock().
MySQL锁，锁的到底是什么？
m0_71777195的博客
12-07 322
只要学计算机，「」永远是一个绕不过的话题。MySQL锁也是一样。一句话解释MySQL锁：MySQL锁是解决资源竞争的一种方案。短短一句话却包含了3点值得我们注意的事情：这篇文章开始带你循序渐进地理解这几个问题。MySQL对资源的操作无非就是读、写两种方式，但是由于事务并发执行的存在，因此对同一资源的并发访问存在3种形式：假设一种情形，一个事务先对某个资源进行读操作，然后另一个事务再对该资源进行写操作，如果两个事务到此为止，必然不会导致并发问题。可是事务这种东西，一般情况下就是包含有很多个子操作啊。想象一下啊
MySQL实战45讲 ---数据库中的锁
weixin_41563161的博客
10-10 364
数据库中的锁 概述 数据库中的锁 再到MySQL中的锁，对于MySQL来说，锁是一个很重要的特性，数据库的锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性，这样才能保证在高并发的情况下，访问数据库的时候，数据不会出现问题。 1 锁的两个概念 在数据库中，lock和latch都...
InnoDB锁机制实验
oneslide
10-30 241
Insert Intention Locks 这种锁称为“插入意向锁”，插入意向锁对索引进行加锁（即使表没有索引，MySQL也会自动加入一个临时的）。 在下面的例子中，建立一个表，在大于100的索引上加个锁，其他事务在本事务持续期间，不得插入大于100的ID： CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=Inn...
Mysql行级锁的使用
primarykey123的博客
03-19 348
mysql的InnoDB,支持事务和行级锁，可以使用行锁来处理用户提现，库存扣减等业务 mysql的行级锁又分共享锁和排他锁 共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。 用法：SELECT id FROM table WHERE　id in(1,2) LOCK...
【Mysql】-锁，行级锁
最新发布
binqian的专栏
10-19 507
行级锁通常不是直接加在数据行上的，而是加在与数据行相关的索引项上。这是因为索引提供了快速定位数据的方式，同时也可以高效地管理锁。通过这种方式，InnoDB 可以最小化锁的影响范围，提高并发性能。如果你希望避免不必要的锁，确保你的查询尽可能使用合适的索引是非常重要的。*** Mysql IX IS锁是表级锁，如果表中有数据行索引加了索引行锁x s 锁 就会在表上加上iX 或 IS 表明 表内部的数据正被加锁了。
Mysql行级锁
梁诗傑的博客
11-08 1379
等值查询命中加记录锁，辅助索引两侧加间隙锁；范围查询命中加临键锁，主键索引加记录锁；索引未命中加间隙锁。
MySQL锁机制，行锁竟然加在索引上！！
qq_40174198的博客
12-28 1万+
锁概述 锁是计算机协调多个进程或线程并发访问某一资源的机制，应该都不陌生。​但在这之前我们先来看看并发控制，理清MVCC多版本并发控制和锁的关系，这也是之前我很迷惑的一个点 并发控制技术 在数据库中，数据可以允许多个用户同时访问，因此在并发场景下需要确保数据的一致性，可以简单梳理一下，并发场景有三种： 从宽泛意义上讲，目前有三种并发控制技术 悲观并发控制(PCC)：心态悲观，假定多用户并发的事物在处理时都会引起并发冲突，每次操作数据的时候都会上锁 先取锁再访问的策略，为数据的安全提供了保证，但是加锁会
[转]MySQL锁机制，行锁竟然加在索引上！！！
沮丧的南瓜
06-02 2242
原文作者qq_40174198 原文连接：MySQL锁机制，行锁竟然加在索引上！- https://blog.youkuaiyun.com/qq_40174198/article/details/111835482#comments_16752317 锁概述 锁是计算机协调多个进程或线程并发访问某一资源的机制，应该都不陌生。​但在这之前我们先来看看并发控制，理清MVCC多版本并发控制和锁的关系，这也是之前我很迷惑的一个点。 并发控制技术 在数据库中，数据可以允许多个用户同时访问，因此在并发场景下需要确保数据的一.
mysql的行锁加在哪个位置_MySQL的数据锁 加在哪？
weixin_28778341的博客
01-26 2779
MySQL的数据锁 加在哪？MySQL的数据锁 加在哪？此篇文章探讨MySQL数据库的锁，讨论MySQL各种语句将如何加锁，以及加锁的“效果”，主要针对默认的InnoDb引擎。基于MySQL5.6之后的版本。有心力的可以直接看MySQL官方文档，说的更为详细：14.7.3由InnoDB中的不同SQL语句设置的锁锁分类按类型分，MySQL有锁：行锁，最普通的锁，其实是加在索引上的锁。表锁，直接加在整...
MySQL的表级锁、行级锁、排它锁和共享锁
qq_41721746的博客
06-13 972
一、表级锁&行级锁 二、排它锁&共享锁 1. 测试不同事务之间排它锁和共享锁的兼容性 2. 测试行锁加在索引项上 三、串行化隔离级别测试
记录一次MySQL死锁的分析与解决过程
热门推荐
noaman_wgs的博客
09-08 4万+
一、问题描述 上周开发过程中，线下环境遇到一个死锁问题，借此机会正好分析下MySQL死锁的原因和解决方案，本篇文章会带你去如何查看死锁日志和分析、解决。 二、MySQL死锁介绍 1、MySQL 锁种类 MySQL InnoDB存储引擎提供了如下几种锁： （1）共享/排他锁（S/X锁） 共享锁（S Lock）：允许事务读取一行数据，多个事务可以拿到一把S锁（即读读并行）； 排他...
心灵拷问：MySQL 到底是怎么加行级锁、间隙锁、临键锁的？
祝我飞黄腾达
04-13 5812
Next-Key Lock 是一张间隙锁，相较于行锁只锁定一行，它会锁定一个区间范围，加锁的范围区间内的索引键值会被锁定 MySQL InnoDB支持三种行锁定方式 InnoDB的默认加锁方式是next-key。如某个加锁操作未使用到索引，则该锁会退化为表锁。 行锁 (Record Lock)：存在唯一索引中 (包含主键索引)，锁定单条索引记录，即key。 间隙锁 (Gap Lock)：存在非唯一索引中，锁定索引记录间隙，确保索引记录的间隙不变。 临键锁 (Next-Key Lo.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值