mysql场景测试_mysql在RR级别下各场景的锁定测试

一.基本信息

版本&＃xff1a;10.0.20-MariaDB-log

事务隔离级别: REPEATABLE-READ

innodb_lock_wait_timeout&＃61;60

innodb_locks_unsafe_for_binlog&＃61;off

innodb_rollback_on_timeout&＃61;off

二. 表信息

CREATE TABLE t1

(

id int unsigned not null auto_increment,

vid int unsigned not null default 0,

name varchar(10) not null default &＃39;&＃39;,

other varchar(20) not null default &＃39;&＃39;,

primary key(id),

key vid(vid)

) engine &＃61;InnoDB;

Insert into t1 values(1,1,&＃39;zengxw1&＃39;,&＃39;I am a DBA&＃39;),(2,2,&＃39;zengxw2&＃39;,&＃39;I am a DBA 2&＃39;);

select * from t1;

&＃43;----&＃43;-----&＃43;---------&＃43;--------------&＃43;

| id | vid | name    | other        |

&＃43;----&＃43;-----&＃43;---------&＃43;--------------&＃43;

|  1 |   1 | zengxw1 | I am a DBA   |

|  2 |   2 | zengxw2 | I am a DBA 2 |

三&＃xff0e;场景一(排它锁与共享锁测试)

因为排它锁与共享锁不兼容&＃xff0c;肯定会锁。

四.场景二(无索引测试)

因为name字段上没有索引&＃xff0c;所以锁会升级为表锁。

五.场景三(一个条件有索引&＃xff0c;一个条件无索引测试)

因为vid字段上有索引&＃xff0c;都会锁住vid&＃61;1对应的记录。

因为vid&＃61;1和vid&＃61;2锁定的记录不同。

五.场景四(vid为非唯一索引的插入测试一)

Session 2同样插入&＃xff1a;Insert into t1 values (4,4,&＃39;zengxw4&＃39;,&＃39;I am a DBA 4&＃39;)&＃xff0c;也会锁定&＃xff0c;查找原因&＃xff0c;是由于表中只有两条记录:vid&＃61;1和vid&＃61;2,

session 2的update操作,会在表上产生gap&＃xff0c;

Gap范围为:[1,无穷大)&＃xff0c;此范围内的值都会锁&＃xff0c;也就是上一个值到下一个值之间(同时需要考虑主键)。若vid的值为:1,2,3时&＃xff0c;对应主键id分别为&＃xff1a;1,2,5&＃xff0c;其gap为[id任何值,1]到[2,2]再到[5,3]之间。

一句话vid要小于上一个值&＃xff0c;vid和id都要大于或等于下一个值才不会锁。

Select * from t1;

----&＃43;-----&＃43;---------&＃43;--------------&＃43;

| id | vid | name    | other        |

&＃43;----&＃43;-----&＃43;---------&＃43;--------------&＃43;

|  1 |   1 | zengxw1 | I am a DBA   |

|  2 |   2 | ??      | I am a DBA 2 |

|  5 |   3 | ??      | I am a DBA 3 |

举例来说&＃xff1a;

会话1执行&＃xff1a;update t1 set name &＃61;&＃39;??&＃39; where vid&＃61;2;

会话2执行&＃xff1a;

Insert into t1 values (4,3,&＃39;zengxw4&＃39;,&＃39;I am a DBA 4&＃39;); ---会锁

Insert into t1 values (6,3,&＃39;zengxw4&＃39;,&＃39;I am a DBA 4&＃39;); ---不会锁

Insert into t1 values (0,1,&＃39;zengxw3&＃39;,&＃39;I am a DBA 3&＃39;); --会锁

Insert into t1 values (10,1,&＃39;zengxw3&＃39;,&＃39;I am a DBA 3&＃39;);--会锁

六.场景五(b为非唯一索引的插入测试二)

CREATE TABLE tt1( a INT, b INT, PRIMARY KEY (a), KEY(b));

insert into tt1 values(1,1), (3,1), (5,3), (7,6),(10,8);

select * from tt1;

&＃43;----&＃43;------&＃43;

| a  | b    |

&＃43;----&＃43;------&＃43;

|  1 |    1 |

|  3 |    1 |

|  5 |    3 |

|  7 |    6 |

| 10 |    8 |

&＃43;----&＃43;------&＃43;

会话一执行&＃xff1a;

SELECT* FROM tt1 WHERE b &＃61;3 FOR UPDATE;

同样&＃xff1a;

INSERT INTO tt1 SELECT 6,5; ---会锁

INSERT INTO tt1 SELECT 2,2; ---会锁

INSERT INTO tt1 SELECT 4,6; ---会锁

而INSERT INTO tt1 SELECT 8,6; --不锁

七.场景六(b为唯一索引的插入测试)

把vid改成唯一索引测试:

CREATE TABLE tt2( a INT, b INT, PRIMARY KEY (a), unique KEY(b));

insert into tt2 values(1,1), (3,3), (5,4), (7,7),(10,8);

select * from tt2;

&＃43;----&＃43;------&＃43;

| a  | b    |

&＃43;----&＃43;------&＃43;

|  1 |    1 |

|  3 |    3 |

|  5 |    4 |

|  7 |    7 |

| 10 |    8 |

会话一执行&＃xff1a;

SELECT* FROM tt1 WHERE b &＃61;3 FOR UPDATE;

同样&＃xff1a;

INSERT INTO tt2 SELECT 6,5; ---不锁

INSERT INTO tt2 SELECT 2,2; ---不锁

INSERT INTO tt2 SELECT 4,6; ---不锁

结论&＃xff1a;当b为唯一索引时&＃xff0c;RR级别下不存在gap锁&＃xff0c;只有record锁。

总结&＃xff1a;

1.  主键索引有record lock

2.  唯一辅助索引有record lock

3.  非唯一辅助索引有next-key lock

4.  没有索引的话&＃xff0c;则是全表范围的next-key lock

5.  RC下只有record lock

6.  RR&innodb_locks_unsafe_for_binlog&＃61;1&＃xff0c;只有record lock.