关于java:记录一下MySql-update会锁定哪些范围的数据

1、背景

在我的项目中,咱们常常应用到update语句,那么update语句会锁定表中的那些记录呢?此处咱们通过一些简略的案例来模仿下。此处是我本人的一个了解,如果那个中央了解错了,欢送指出

2、前置常识

2.1 数据库的隔离级别

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

2.2 数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

2.3 数据库的存储引擎

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)

2.4 锁是加在记录上还是索引上

锁是加在索引上,那如果表中没有建设索引,是否就是加在表上的呢?其实不是,也是加在索引的,会存在一个默认的。

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

2.5 update…where加锁的根本单位是

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters
此处能够了解加锁的单位是: next-key

2.6 行级锁

2.6.1 Record Locks

记录锁,即只会锁定一条记录。其实是锁定这条记录的索引。
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.

2.6.2 Gap Locks

间隙锁,间隙锁是在索引记录之间的间隙上的锁,即锁定一个区间。前开后开区间,不包含记录自身。

间隙锁如果是应用单列惟一索引值进行更新的话,是会进化Record Lock

间隙锁的目标

  1. 避免新的数据插入到间隙中
  2. 避免曾经存在的数据被更新到间隙中。

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 a multiple-column unique index; in that case, gap locking does occur.)

2.6.3 Next-Key Locks

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指的是正无穷。

2.6.4 测试锁表的表构造

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);

2.6.5 表中的测试数据

mysql> select * from test_record_lock;
+----+------+--------+
| id | age  | name   |
+----+------+--------+
|  1 |   10 | 张三   |
|  5 |   20 | 李四   |
|  8 |   25 | 王五   |
+----+------+--------+
3 rows in set (0.00 sec)

2.7 查看数据库中以后的锁

select * from performance_schema.data_locks;

字段解释:

字段 解释
lock_type TABLE 锁是加在表上
RECORD 锁加在记录上
lock_mode IX 动向排他锁
X或者S next-key lock <br/>锁定记录自身和记录之前的间隙
X,REC_NOT_GAP Record Lock 只锁记录本身
S,REC_NOT_GAP Record Lock 只锁记录本身
X,GAP gap lock
X,INSERT_INTENTION 插入意向锁
lock_data 具体的某个数字 示意主键的值
值,值 第一个值:一般索引的值<br/>第二个值:主键值

疑难:X,GAP是否能够了解成X锁进化成了GAP锁。

3、测试数据加锁

3.1 惟一索引测试

此处实用单个字段的惟一索引,不适宜多个字段的惟一索引

3.1.1 等值更新-记录存在

解释:

  1. 加next-key lock,那么锁定的记录范畴为 (1,5]。
  2. 因为是惟一索引,且查问的值存在,next-key lock进化成record lock,即最终只锁定了id=5的这一行数据。其余的数据不影响。

    3.1.2 等值查问-记录不存在-01

解释:

  1. 加next-key lock,那么锁定的记录范畴为 (5,8]。
  2. 因为是惟一索引,且查问的值不存在,next-key lock进化成gap,即最终锁定的数据范畴为(5,8)。其余的数据不影响。
3.1.3 等值更新-记录不存在-02

3.1.4 范畴更新
1、小于或等于最大临界值

此时能够发现表中扫描到的记录都加上了next key lock(锁加在索引上)

2、大于或等于最小临界值
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)

此时只可向表中插入比最小临界值小的记录。

3、失常范畴

3.2 一般索引测试

3.2.1 等值更新-记录存在

解释:

  1. 先对一般索引age加上next-key lock,锁定的范畴是(10,20]
  2. next-key lock还会锁住本记录,因而在id索引的值等于5上加了Record Lock
  3. 因为是一般索引并且值还存在,因而还会对本记录的下一个区间减少间隙锁 Gap Lock,锁定的范畴为 (20,25)
3.2.2 等值更新-记录不存在

解释:

  1. 获取next-key lock 锁定的范畴为 (10,20]
  2. 因为须要更新的记录不存在,next-key lock进化成 gap lock,所以锁定的范畴为(10,20)
  3. 因为是一般索引且记录不存在,所以不须要再次查找下一个区间。
3.2.3 范畴更新

解释:

  1. 一般索引的范畴更新,next-key-lock不回进化成 gap lock。
3.3 无索引更新

从上图中可知,无索引更新数据表危险,须要审慎解决。无索引更新,会导致全表扫描,导致将扫描到的所有记录都加上next-key lock

3、参考链接

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

【腾讯云】轻量 2核2G4M,首年65元

阿里云限时活动-云数据库 RDS MySQL  1核2G配置 1.88/月 速抢

本文由乐趣区整理发布,转载请注明出处,谢谢。

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据