mysql-锁-锁类型

mysql-锁-锁类型

record lock

A record lock is a lock on an index record(record lock 是一个加在索引记录上的锁),如果一个表定义时没有索引，innodb会隐式的创建聚簇索引，名称为GEN_CLUST_INDEX,然后给这个隐式的锁引加record lock

显示的有索引

现有表b, 其中 id为主键:

CREATE TABLE `b` (
  `id` int NOT NULL,
  `num` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据:

INSERT INTO `b` (`id`, `num`) VALUES ('4', '3'), VALUES ('10', '5'), ('15', '6');

事务查询与修改:

事务A                                            |  事务B
mysql> begin;                                    |  mysql> begin;
Query OK, 0 rows affected                        |  Query OK, 0 rows affected
                                                 |
mysql> select * from b where id=4 for update;    |
+----+-----+                                     |
| id | num |                                     |
+----+-----+                                     |
|  4 |   3 |                                     |
+----+-----+                                     |
1 row in set                                     |
                                                 |  mysql> update b set num=8 where id=4;
												 |  waiting....

查看innodb状态:

show engine innodb status

update b set num=8 where id=4
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of 
        table `spring`.`b` trx id 3149 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 128
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 000000000c44; asc      D;;
 2: len 7; hex 010000011c0151; asc       Q;;
 3: len 4; hex 80000003; asc     ;;

注意: lock_mode X locks rec but not gap, 没有使用gap lock

没有加任何索引

如果去掉 id 列的主键索引:

CREATE TABLE `b` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

执行相同的事务查询和修改，此时查看innodb状态为:

RECORD LOCKS space id 4 page no 4 n bits 72 
    index GEN_CLUST_INDEX of table `spring`.`b` trx id 3182 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000000020b; asc       ;;
 1: len 6; hex 000000000c4e; asc      N;;
 2: len 7; hex 80000000000000; asc        ;;
------------------

Gap lock

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.(gap lock是锁在索引记录之间的间隙的锁，或者是锁在第一索引记录之前或者最后一条索引记录之后间隙间的锁o)

现有表:

CREATE TABLE `t` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  KEY `index_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据:

INSERT INTO `spring`.`t` (`id`, `num`) VALUES 
('10', '1'), ('11', '3'), ('13', '5'),('20', '5');

事务执行，在事务A中锁住id在13和20之间的数据，事务B插入id为15的数据:

mysql> begin;                                    | mysql> begin;
Query OK, 0 rows affected                        | Query OK, 0 rows affected
                                                 |
mysql> select * from t where 
			id between 13 and 20 for update;	 |
+----+-----+                                     |
| id | num |                                     |
+----+-----+                                     |
| 13 |   5 |                                     |
| 20 |   5 |                                     |
+----+-----+                                     |
2 rows in set                                    |
                                                 |
mysql>                                           |
                                                 |
												 |	mysql> INSERT INTO `t` (`id`, `num`) 
												 | 		VALUES ('15', '5');
												 |  1205 - Lock wait timeout exceeded; 
												 | 	try restarting transaction

查看innodb状态:

RECORD LOCKS space id 2 page no 5 n bits 72 
index index_id of table `spring`.`t` trx id 3184 lock_mode X locks gap 
before rec insert intention waiting
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 000000000203; asc       ;;

Next-Key Locks

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.(next-key lock,是索引记录上的record lock和加在在这个索引记录之前间隙之间的gap lock的组合，即左开右闭,(…,index record])

• next-key解决了事务中的幻读现象(事务A读取到事务B新增的数据)
• 可以通过设置事务隔离级别为read committed来取消 next-key lock

现有表,其中 id 为普通索引:

mysql> select * from t;
+----+-----+
| id | num |
+----+-----+
| 10 |   1 |
| 11 |   3 |
| 13 |   5 |
| 20 |   5 |
+----+-----+
4 rows in set

那么这个索引所包含的可能 next-key lock为:

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

简单的来讲,事务A查询如下:

select * from t where id > 11 for update

事务B进行一次插入操作:

insert into t(id,num)values(18,4);

然后事务A进行一次查询:

select * from t

好了，问题来了，如果没有这个 next-key lock,而传统的 RR 隔离级别是不能解决幻读的问题，所以事务A就会读取到时事务B提交的新增数据

即事务A锁到的行数据就只有id=13,20,即在这些范围之内是不能被插入的，但是其他空隙是可以插入，就比如这个id=18,这条数据是允许插入的，即出现幻读现象，

但是，正因为这个 next-key lock,使得事务A就会读取不到时事务B提交的新增数据，解决了幻读

因为这个 next-key lock, select * from t where id > 11 for update,这个sql锁住的范围是 (11, positive infinity),即锁住了大于11之后的所有值，在这个范围之内新增数据都是不可以的，比如这个18，也是被禁的，即解决了幻读现象