在项目中,我们经常使用到update
语句,那么update
语句会锁定表中的那些记录呢?此处我们通过一些简单的案例来模拟下。此处是我自己的一个理解,如果那个地方理解错了,欢迎指出
mysql> show variables like 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec)
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.28 | +-----------+ 1 row in set (0.00 sec)
mysql> show variables like '%storage_engine%'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | | internal_tmp_mem_storage_engine | TempTable | +---------------------------------+-----------+ 4 rows in set (0.01 sec)
锁是加在索引上
,那如果表中没有建立索引,是否就是加在表上的呢?其实不是,也是加在索引的,会存在一个默认的。
Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking
参考链接: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks
UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters
此处可以理解加锁的单位是: next-key
锁
记录锁
,即只会锁定一条记录。其实是锁定这条记录的索引。
A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.
间隙锁
,间隙锁是在索引记录之间的间隙上的锁,即锁定一个区间。前开后开区间
,不包括记录本身。
间隙锁
如果是使用单列唯一索引值
进行更新的话,是会退化
成Record Lock
。
间隙锁的目的
:
Gap locking is not needed for statements that lock rows using a unique index to search > for a unique row. (This
does not include
the case that the search condition includes only > some columns of amultiple-column unique index
; in that case, gap locking does occur.)
Next-Key Lock 是索引记录上
的记录锁
和索引记录之前
的间隙上的间隙锁
的组合。也是锁定一个区间,前开后闭区间
。包括记录本身。
如果索引值包括 1,5,10,30
,那么next key 锁可能涵盖如下区间
(negative infinity, 1] (1, 115 (5, 10] (10, 30] (30, positive infinity)
negative infinity
指的是负无穷。positive infinity
指的是正无穷。
create table test_record_lock ( id int not null comment '主键', age int null comment '年龄,普通索引', name varchar(10) null comment '姓名,无索引', constraint test_record_lock_pk primary key (id) ) comment '测试记录锁'; create index test_record_lock_age_index on test_record_lock (age);
mysql> select * from test_record_lock; +----+------+--------+ | id | age | name | +----+------+--------+ | 1 | 10 | 张三 | | 5 | 20 | 李四 | | 8 | 25 | 王五 | +----+------+--------+ 3 rows in set (0.00 sec)
select * from performance_schema.data_locks;
字段解释:
字段 | 值 | 解释 |
---|---|---|
lock_type |
TABLE | 锁是加在表上 |
RECORD | 锁加在记录上 | |
lock_mode |
IX | 意向排他锁 |
X或者S | next-key lock 锁定记录本身和记录之前的间隙 |
|
X,REC_NOT_GAP | Record Lock 只锁记录自身 | |
S,REC_NOT_GAP | Record Lock 只锁记录自身 | |
X,GAP | gap lock | |
X,INSERT_INTENTION | 插入意向锁 | |
lock_data |
具体的某个数字 | 表示主键的值 |
值,值 | 第一个值:普通索引的值 第二个值:主键值 |
疑问:X,GAP
是否可以理解成X
锁退化成了GAP
锁。
此处适用单个字段的唯一索引,不适合多个字段的唯一索引
解释:
解释:
此时可以发现表中扫描到的记录都加上了next key lock(锁加在索引上)
mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> update test_record_lock set name = 'aaa' where id >= 1; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select LOCK_TYPE,INDEX_NAME,LOCK_MODE,LOCK_DATA from performance_schema.data_locks; +-----------+------------+---------------+------------------------+ | LOCK_TYPE | INDEX_NAME | LOCK_MODE | LOCK_DATA | +-----------+------------+---------------+------------------------+ | TABLE | NULL | IX | NULL | | RECORD | PRIMARY | X,REC_NOT_GAP | 1 | | RECORD | PRIMARY | X | supremum pseudo-record | | RECORD | PRIMARY | X | 8 | | RECORD | PRIMARY | X | 5 | +-----------+------------+---------------+------------------------+ 5 rows in set (0.01 sec)
此时只可向表中插入比最小临界值小的记录。
解释:
age
加上next-key lock,锁定的范围是(10,20]解释:
解释:
从上图中可知,无索引更新数据表危险,需要谨慎处理
。无索引更新,会导致全表扫描,导致将扫描到的所有记录都加上next-key lock
。
1、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks
2、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html