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;
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对毛票票的支持。