RR与RC隔离级别下索引和锁的测试脚本示例代码
基本概念
当前读与快照读
在MVCC中，读操作可以分成两类：快照读(snapshotread)与当前读(currentread)。快照读，读取的是记录的可见版本(有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且对返回的记录，都会加上锁，保证在事务结束前，这条数据都是最新版本。
快照读：简单的select操作，属于快照读，不加锁(Serializable除外)。
select*fromtablewhere?;
当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
select*fromtablewhere?lockinsharemode; select*fromtablewhere?forupdate; insertintotablevalues(); updatetableset?where?; deletefromtablewhere?;
隔离级别与加锁机制
- ReadUncommitted会发生脏读，不考虑。
- ReadCommitted(RC)针对当前读，RC隔离级别保证对读取到的记录加锁(GapLocking)，存在幻读现象。
- RepeatableRead(RR)针对当前读，RR隔离级别保证对读取到的记录加锁(RecordLocking)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入(GapLocking)，不存在幻读现象。
- Serializable所有的读操作均为退化为当前读，读写冲突，因此并发度急剧下降，不考虑。
测试脚本
--基本操作--
--查询事务隔离级别，默认是RR
showvariableslike'%isolation%';
--设置事务隔离级别为RC
setsessiontransactionisolationlevelreadcommitted;
--数据初始化--
begin;
droptableifexistsuser;
CREATETABLE`user`(
`id`bigint(20)unsignedNOTNULLAUTO_INCREMENT,
`email`varchar(64)NOTNULL,
`age`int(11)NOTNULL,
`address`varchar(64)NOTNULL,
PRIMARYKEY(`id`),
UNIQUEKEY`uniq_email`(`email`),
KEY`idx_age`(`age`)
);
insertintouser(email,age,address)values("test1@elsef.com",18,"address1");
insertintouser(email,age,address)values("test2@elsef.com",20,"address2");
insertintouser(email,age,address)values("test3@elsef.com",20,"address3");
commit;
select*fromuser;
--一、trx_id示例
begin;
SELECTTRX_IDFROMINFORMATION_SCHEMA.INNODB_TRXWHERETRX_MYSQL_THREAD_ID=CONNECTION_ID();
select*fromuser;
SELECTTRX_IDFROMINFORMATION_SCHEMA.INNODB_TRXWHERETRX_MYSQL_THREAD_ID=CONNECTION_ID();
SHOWENGINEINNODBSTATUS;
updateusersetage=22whereid=3;
--查询事务id
SELECTTRX_IDFROMINFORMATION_SCHEMA.INNODB_TRXWHERETRX_MYSQL_THREAD_ID=CONNECTION_ID();
--INNODB引擎状态
SHOWENGINEINNODBSTATUS;
commit;
--二、可重复读、不可重复读示例
--session1
setsessiontransactionisolationlevelreadcommitted;
begin;
--session2
setsessiontransactionisolationlevelrepeatableread;
begin;
--session1
select*fromuser;
--session2
select*fromuser;
--session3
begin;
insertintouser(email,age,address)values("test4@elsef.com",30,"address4");
commit;
--session1这里因为是RC，所以可以读到trx3提交的新数据，这里如果是证明不可重复读的话应该使用update而不是insert
select*fromuser;
commit;
--session2这里因为是RR，所以不会读到trx3提交的新数据
select*fromuser;
commit;
--三、快照读幻读示例
--session1
setsessiontransactionisolationlevelrepeatableread;
begin;
--这里使用快照读
select*fromuser;
--session2
begin;
insertintouser(email,age,address)values("test4@elsef.com",30,"address4");
commit;
select*fromuser;
--session1
select*fromuser;--这里读不到test4@的数据，因为是RR
--这里发生了幻读
insertintouser(email,age,address)values("test4@elsef.com",30,"address4");--插入失败因为email唯一索引冲突
commit;
--四、当前读幻读示例
--RC
--session1
setsessiontransactionisolationlevelreadcommitted;
begin;
--这里会对所有满足条件的age=20的记录加锁，因为是RC，所以没有GAP锁
deletefromuserwhereage=20;
select*fromuser;
--session2
setsessiontransactionisolationlevelreadcommitted;
begin;
--因为trx1没有加GAP锁，所以之类可以插入age=20的记录
insertintouser(email,age,address)values("test4@elsef.com",20,"address4");
select*fromuser;--可以查到4条数据，可以读到trx1的删除数据，因为是RC，trx1未提交所以没影响trx2
commit;
--session1
select*fromuser;--可以读到trx2新插入的数据，虽然trx1是当前读，但是并未添加相应的next-key锁，没有阻止trx2的新数据插入
commit;
--RR
--session1
setsessiontransactionisolationlevelrepeatableread;
begin;
deletefromuserwhereage=20;
select*fromuser;
--session2
begin;
--这里会阻塞，因为trx1在age=20周围加了GAP锁
--非唯一索引，首先，通过索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁；
--然后读取下一条，重复进行。直至进行到第一条不满足条件的记录，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
insertintouser(email,age,address)values("test4@elsef.com",20,"address4");
--直到超时，ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
--此时如果查询可以看到3条记录
commit;
--session1
--此时只能看到1条记录，另外两条被删除了
select*fromuser;
commit;
--唯一索引+RC
--session1
setsessiontransactionisolationlevelreadcommitted;
begin;
deletefromuserwhereemail="test3@elsef.com";
--session2
begin;
--可以读到，因为trx1是RC
select*fromuserwhereemail="test3@elsef.com";
--尝试更新这个记录的age，会阻塞直到超时，因为email是唯一索引已经被trx1锁住了，同时也会在对应的主键索引上加锁
--注意这里操作的id=3就是trx1中操作的email的同一行记录
updateusersetage=40whereid=3;
--session1
commit;
--session2
commit;
--无索引+RC
--session1
setsessiontransactionisolationlevelreadcommitted;
begin;
--由于address字段无索引，所以Innodb会对所有行进行加锁，由MySQLserver进行判断并释放锁
deletefromuserwhereaddress="address3";
--session2
setsessiontransactionisolationlevelreadcommitted;
begin;
--这一行会成功，因为这一行没有加锁（先加了后释放了）
updateusersetage=10whereaddress="address2";
--这一行同样会被阻塞，原因是它已经被trx1的语句加了锁了，全部符合条件的都加锁了
updateusersetage=10whereaddress="address3";
--session1
commit;
--session2
commit;
--非唯一索引+RR
--session1
setsessiontransactionisolationlevelrepeatableread;
begin;
deletefromuserwhereage=20;
--session2
setsessiontransactionisolationlevelrepeatableread;
begin;
--这里会阻塞，因为trx1中已经锁住了age=20的记录以及加上了GAP锁，所以这里18已经落入锁区间
insertintouser(email,age,address)values("test4@elsef.com",18,"address4");
--session1
commit;
--session2
commit;
--无索引RR
--session1
setsessiontransactionisolationlevelrepeatableread;
begin;
--没有索引，那么会锁上表中的所有记录，同时会锁上主键索引上的所有GAP，杜绝所有的并发更新操作
deletefromuserwhereaddress="address3";
--session2
setsessiontransactionisolationlevelrepeatableread;
begin;
--这里会阻塞，原因是主键已经被加上了GAP锁，所以新的插入不能执行成功
insertintouser(email,age,address)values("test4@elsef.com",18,"address4");
--session1
commit;
--session2
commit;
--死锁简单示例
--session1
begin;
deletefromuserwhereid=1;
--session2
begin;
deletefromuserwhereid=3;
--session1
deletefromuserwhereid=3;
--seession2
--这里MySQL判断发生了死锁，中断了一个trx
--ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction
deletefromuserwhereid=1;
--session1
rollback;
--session2;
rollback;
--五、死锁insert示例
droptableifexistst1;
begin;
createtablet1(
`id`bigintnotnullauto_increment,
primarykey(`id`)
);
insertintot1values(1);
insertintot1values(5);
commit;
select*fromt1;
--session1
begin;
insertintot1values(2);
--sessioin2
begin;
--这里会阻塞
insertintot1values(2);
--session3
begin;
--这里会阻塞
insertintot1values(2);
--session1;
--此时回滚，trx2和trx3收到通知，MySQL自动中断一个trx，因为发生了死锁
--ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction
rollback;
--session2;
rollback;
--session3;
rollback;
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。