主要是看了小林coding发现对于非唯一索引的情况下跟正常的思路不大一样，特此实验一下

前要知识#

小林coding-非唯一索引加锁规则

select * from performance_schema.data_locks; 用来查询锁的情况

lock_data 表示锁住的数据，第一个是二级索引的数据，第二个是主键索引的数据

lock_mode 表示 IX = 意向锁 X = next-key lock ….

lock_type 表示 行级锁还是表锁

非唯一索引范围查询#

环境配置#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE test (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE
);

INSERT INTO test (id , name , age, email)
VALUES
(1, 'Alice', 25, 'alice@example.com'),
(3 ,'Bob', 30, 'bob@example.com'),
(5, 'Charlie', 22, 'charlie@example.com'),
(7,'Mike', 40, '23234234234@ee.com');

CREATE INDEX idx_age ON test (age);

记录存在#

  • 大于

    1
    2
    3
    4
    5
    6
    7
    START TRANSACTION;

    SELECT * FROM test WHERE age > 25  FOR UPDATE;

    commit ;

    select * from performance_schema.data_locks;
    LOCK_TYPE lock_mode lock_status lock_data
    table IX GRANTED null
    record x GRANTED supremum pseudo-record
    record x GRANTED 30, 3
    record x GRANTED 40, 7
    record X,REC_NOT_GAP GRANTED 3
    record X,REC_NOT_GAP GRANTED 7

  • 大于等于

    1
    2
    3
    4
    5
    6
    7
    START TRANSACTION;

    SELECT * FROM test WHERE age >= 25  FOR UPDATE;

    commit ;

    select * from performance_schema.data_locks;
    lock_type lock_mode lock_status lock_data
    table IX GRANTED null
    record X GRANTED supremum pseudo-record
    record X GRANTED 25, 1
    record X GRANTED 30, 3
    record X GRANTED 40, 7
    record X,REC_NOT_GAP GRANTED 1
    record X,REC_NOT_GAP GRANTED 3
    record X,REC_NOT_GAP GRANTED 7

  • 小于

    1
    2
    3
    4
    5
    6
    7
    START TRANSACTION;

    SELECT * FROM test WHERE age < 25  FOR UPDATE;

    commit ;

    select * from performance_schema.data_locks;
    lock_type lock_mode lock_status lock_data
    TABLE IX GRANTED null
    record X GRANTED 22, 5
    record X GRANTED 25, 1
    record X,REC_NOT_GAP GRANTED 5

  • 小于等于

    1
    2
    3
    4
    5
    6
    7
    START TRANSACTION;

    SELECT * FROM test WHERE age <= 25  FOR UPDATE;

    commit ;

    select * from performance_schema.data_locks;
    lock_type lock_mode lock_status lock_data
    TABLE IX GRANTED null
    record X GRANTED 22, 5
    record X GRANTED 25, 1
    record X GRANTED 30, 3
    record X,REC_NOT_GAP GRANTED 1
    record X,REC_NOT_GAP GRANTED 5

记录不存在#

  • 大于

    1
    2
    3
    4
    5
    START TRANSACTION;

    SELECT * FROM test WHERE age > 28  FOR UPDATE;

    commit ;
    lock_type lock_mode lock_status lock_data
    TABLE IX GRANTED null
    record X GRANTED supremum pseudo-record
    record X GRANTED 30, 3
    record X GRANTED 40, 7
    record X,REC_NOT_GAP GRANTED 3
    record X,REC_NOT_GAP GRANTED 7

  • 大于等于

    1
    2
    3
    4
    5
    START TRANSACTION;

    SELECT * FROM test WHERE age >= 28  FOR UPDATE;

    commit ;
    lock_type lock_mode lock_status lock_data
    TABLE IX GRANTED null
    record X GRANTED supremum pseudo-record
    record X GRANTED 30, 3
    record X GRANTED 40, 7
    record X,REC_NOT_GAP GRANTED 3
    record X,REC_NOT_GAP GRANTED 7

  • 小于

    1
    2
    3
    4
    5
    6
    7
    START TRANSACTION;

    SELECT * FROM test WHERE age < 28  FOR UPDATE;

    commit ;

    select * from performance_schema.data_locks;
    lock_type lock_mode lock_status lock_data
    TABLE IX GRANTED null
    record X GRANTED 22, 5
    record X GRANTED 25, 1
    record X GRANTED 30, 3
    record X,REC_NOT_GAP GRANTED 1
    record X,REC_NOT_GAP GRANTED 5

  • 小于等于

    1
    2
    3
    4
    5
    6
    7
    START TRANSACTION;

    SELECT * FROM test WHERE age <= 28  FOR UPDATE;

    commit ;

    select * from performance_schema.data_locks;
    lock_type lock_mode lock_status lock_data
    TABLE IX GRANTED null
    record X GRANTED 22, 5
    record X GRANTED 25, 1
    record X GRANTED 30, 3
    record X,REC_NOT_GAP GRANTED 1
    record X,REC_NOT_GAP GRANTED 5

结论#

当非唯一索引范围查询时，next-key lock 不会退化成 record lock 和 gap lock

一般来说他会锁住的范围会延伸到第一个不符合要求的记录（等于情况下找到了数据其实不算不符合要求的数据）

非唯一索引等值查询#

环境准备#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE test (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE
);

INSERT INTO test (id , name , age, email)
VALUES
(1, 'Alice', 25, 'alice@example.com'),
(3 ,'Bob', 30, 'bob@example.com'),
(5, 'Charlie', 22, 'charlie@example.com'),
(7,'Mike', 40, '23234234234@ee.com');

CREATE INDEX idx_age ON test (age);

记录存在#

1
2
3
4
5
6
7
START TRANSACTION;

SELECT * FROM test WHERE age = 25  FOR UPDATE;

commit ;

select * from performance_schema.data_locks;
lock_type lock_mode lock_status lock_data
TABLE IX GRANTED null
record X，GAP GRANTED 30，3
record X GRANTED 25, 1
record X，REC_NOT_GAP GRANTED 1

记录不存在#

1
2
3
4
5
6
7
START TRANSACTION;

SELECT * FROM test WHERE age = 28  FOR UPDATE;

commit ;

select * from performance_schema.data_locks;
lock_type lock_mode lock_status lock_data
TABLE IX GRANTED null
record X，GAP GRANTED 30，3

总结#

没什么好总结的，这个很好理解…..