RR与RC隔离级别下索引和锁的测试脚本示例代码

select * from table where ?;

select * from table where ? lock in share mode;select * from table where ? for update;insert into table values ();update table set ? where ?;delete from table where ?;

-- 基本操作 ---- 查询事务隔离级别，默认是RRshow variables like '%isolation%';-- 设置事务隔离级别为RCset session transaction isolation level read committed;-- 数据初始化 --begin;drop table if exists user;CREATE TABLE `user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(64) NOT NULL, `age` int(11) NOT NULL, `address` varchar(64) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_email` (`email`), KEY `idx_age` (`age`));insert into user (email, age, address) values ("[email protected]", 18, "address1");insert into user (email, age, address) values ("[email protected]", 20, "address2");insert into user (email, age, address) values ("[email protected]", 20, "address3");commit;select * from user;-- 一、trx_id示例begin;SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();select * from user;SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();SHOW ENGINE INNODB STATUS;update user set age = 22 where id = 3;-- 查询事务idSELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();-- INNODB 引擎状态SHOW ENGINE INNODB STATUS;commit;-- 二、可重复读、不可重复读示例-- session1set session transaction isolation level read committed;begin;-- session2set session transaction isolation level repeatable read;begin;-- session1select * from user;-- session2select * from user;-- session3begin;insert into user (email, age, address) values ("[email protected]", 30, "address4");commit;-- session1 这里因为是RC，所以可以读到trx3提交的新数据，这里如果是证明不可重复读的话应该使用update而不是insertselect * from user;commit;-- session2 这里因为是RR，所以不会读到trx3提交的新数据select * from user;commit;-- 三、快照读幻读示例-- session1set session transaction isolation level repeatable read;begin;-- 这里使用快照读select * from user;-- session2begin;insert into user (email, age, address) values ("[email protected]", 30, "address4");commit;select * from user;-- session1select * from user; -- 这里读不到test4@的数据，因为是RR-- 这里发生了幻读insert into user (email, age, address) values ("[email protected]", 30, "address4"); -- 插入失败因为email唯一索引冲突commit;-- 四、当前读幻读示例-- RC-- session1set session transaction isolation level read committed;begin;-- 这里会对所有满足条件的age=20的记录加锁，因为是RC，所以没有GAP锁delete from user where age = 20;select * from user;-- session2set session transaction isolation level read committed;begin;-- 因为trx1没有加GAP锁，所以之类可以插入age=20的记录insert into user (email, age, address) values ("[email protected]", 20, "address4");select * from user; -- 可以查到4条数据，可以读到trx1的删除数据，因为是RC，trx1未提交所以没影响trx2commit;-- session1select * from user; -- 可以读到trx2新插入的数据，虽然trx1是当前读，但是并未添加相应的next-key锁，没有阻止trx2的新数据插入commit;--RR-- session1set session transaction isolation level repeatable read;begin;delete from user where age = 20;select * from user;-- session2begin;-- 这里会阻塞，因为trx1在age=20周围加了GAP锁-- 非唯一索引，首先，通过索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁；-- 然后读取下一条，重复进行。直至进行到第一条不满足条件的记录，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。insert into user (email, age, address) values ("[email protected]", 20, "address4");-- 直到超时，ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction-- 此时如果查询可以看到3条记录commit;-- session1-- 此时只能看到1条记录，另外两条被删除了select * from user;commit;-- 唯一索引+RC-- session1set session transaction isolation level read committed;begin;delete from user where email = "[email protected]";-- session2begin;-- 可以读到，因为trx1是RCselect * from user where email = "[email protected]";-- 尝试更新这个记录的age，会阻塞直到超时，因为email是唯一索引已经被trx1锁住了，同时也会在对应的主键索引上加锁-- 注意这里操作的id=3就是trx1中操作的email的同一行记录update user set age = 40 where id = 3;-- session1commit;-- session2commit;-- 无索引+RC-- session1set session transaction isolation level read committed;begin;-- 由于address字段无索引，所以Innodb会对所有行进行加锁，由MySQL server进行判断并释放锁delete from user where address = "address3";-- session2set session transaction isolation level read committed;begin;-- 这一行会成功，因为这一行没有加锁（先加了后释放了）update user set age = 10 where address = "address2";-- 这一行同样会被阻塞，原因是它已经被trx1的语句加了锁了，全部符合条件的都加锁了update user set age = 10 where address = "address3";-- session1commit;-- session2commit;-- 非唯一索引+RR-- session1set session transaction isolation level repeatable read;begin;delete from user where age = 20;-- session2set session transaction isolation level repeatable read;begin;-- 这里会阻塞，因为trx1中已经锁住了age=20的记录以及加上了GAP锁，所以这里18已经落入锁区间insert into user (email, age, address) values ("[email protected]", 18, "address4");-- session1commit;-- session2commit;-- 无索引RR-- session1set session transaction isolation level repeatable read;begin;-- 没有索引，那么会锁上表中的所有记录，同时会锁上主键索引上的所有GAP，杜绝所有的并发更新操作delete from user where address = "address3";-- session2set session transaction isolation level repeatable read;begin;-- 这里会阻塞，原因是主键已经被加上了GAP锁，所以新的插入不能执行成功insert into user (email, age, address) values ("[email protected]", 18, "address4");-- session1commit;-- session2commit;-- 死锁 简单示例-- session1begin;delete from user where id = 1;-- session2begin;delete from user where id = 3;-- session1delete from user where id = 3;-- seession2-- 这里MySQL判断发生了死锁，中断了一个trx-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactiondelete from user where id = 1;-- session1rollback;-- session2;rollback;-- 五、死锁 insert示例drop table if exists t1;begin;create table t1 ( `id` bigint not null auto_increment, primary key (`id`));insert into t1 values(1);insert into t1 values(5);commit;select * from t1;-- session1begin;insert into t1 values (2);-- sessioin2begin;-- 这里会阻塞insert into t1 values (2);-- session3begin;-- 这里会阻塞insert into t1 values (2);-- session1;-- 此时回滚，trx2和trx3收到通知，MySQL自动中断一个trx，因为发生了死锁-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionrollback;--session2;rollback;--session3;rollback;