首页 / MYSQL / mysql InnoDB加锁测试

mysql InnoDB加锁测试

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql InnoDB加锁测试，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4028字，纯文字阅读大概需要6分钟。

内容图文

以下测试基于mysql5.7，隔离级别为：REPEATABLE READ
建表语句如下：

create table mytest (
  a int primary key AUTO_INCREMENT,
  b int ,
  c int,
  d int,
  KEY idx_b (b),
  KEY idx_c (c),
  UNIQUE idx_d (d)

) ENGINE=InnoDB;
insert into mytest (b, c,d) VALUES (1,1,1),(3,1,3),(5,3,6),(7,6,10),(10,8,12);

表数据如下：
mysql InnoDB加锁测试 - 文章图片

索引列加锁

以下测试省略开启事务的sql
设client 1，分别执行如下测试sql（只是写在一起但是分别执行，每条测试sql都会单独开启事务）

select * from mytest where b=3 for update ;#锁普通索引
select * from mytest where c=1 for update ;#锁普通索引
select * from mytest where d=3 for update ;#锁唯一索引
select * from mytest where b=10 for update ;#锁普通索引最大值
insert into mytest (b, c,d) value (11,4,13);#测试插入意向锁

client2，分别执行以下测试sql

1. select * from mytest where b=3 lock in share mode ;#冲突（select * from mytest where b=3 for update ;）
2. insert into mytest (b, c,d) value (2,9,17);#gap lock冲突(select * from mytest where b=3 for update ;),不冲突(select * from mytest where c=1 for update ;),说明只锁对应的辅助索引和主键索引
3. insert into mytest (b, c,d) value (3,9,17);#不冲突（select * from mytest where c=1 for update ;），说明只锁对应的辅助索引和主键索引
4. insert into mytest (a,b, c,d) value (2,3,9,18);#聚集索引冲突（select * from mytest where c=1 for update ;），说明只锁对应的辅助索引和主键索引
5. select * from mytest where d=3 for update ;#聚集索引冲突（select * from mytest where c=1 for update ;）
6. select * from mytest where b=3 for update ;#聚集索引冲突（select * from mytest where c=1 for update ;）
7. insert into mytest (b, c,d) value (4,9,13);#gap lock冲突（select * from mytest where b=3 for update ;）
8. insert into mytest (b, c,d) value (11,9,2);#不冲突（select * from mytest where b=3 for update ;），说明唯一索引没有gap lock 
9. insert into mytest (b, c,d) value (12,9,13);#gap lock冲突（select * from mytest where b=10 for update ;）说明对于正无穷范围的插入也有gap lock
 
10. insert into mytest (b, c,d) value (12,5,14);#不冲突(insert into mytest (b, c,d) value (11,4,13))，说明插入意向锁对于事务同时插入同一个gap，只要位置不同就不会引起冲突
11. insert into mytest (b, c,d) value (11,4,13);#冲突(insert into mytest (b, c,d) value (11,4,13))，插入意向锁对于事务同时插入同一个gap，位置相同会引起冲突

括号内是对应的client1中的测试sql
使用以下sql语句查询事务状态。

select l.*, t.trx_state,t.trx_weight,t.trx_query,w.* from information_schema.INNODB_TRX t left join information_schema.INNODB_LOCKS l
  on t.trx_id=l.lock_trx_id left join information_schema.INNODB_LOCK_WAITS w
    on t.trx_id=w.requesting_trx_id;

对应状态如下图（序号对应client2测试sql的序号）：

  1. mysql InnoDB加锁测试 - 文章图片

  2. mysql InnoDB加锁测试 - 文章图片

  3. mysql InnoDB加锁测试 - 文章图片

  4. mysql InnoDB加锁测试 - 文章图片

  5. mysql InnoDB加锁测试 - 文章图片

  6. mysql InnoDB加锁测试 - 文章图片

非索引列加锁

建表语句如上，drop 索引idx_b，对应client1测试sql如下：

select * from mytest where b=3 for update ;#drop索引后测试

clients测试sql:

1. select * from mytest where b=8 for update ;#聚集索引冲突（select * from mytest where b=3 for update ;）
2. select * from mytest where b=1 for update ;#聚集索引冲突（select * from mytest where b=3 for update ;）
3. select * from mytest where c=1 for update ;#聚集索引冲突（select * from mytest where b=3 for update ;）
4. insert into mytest (b, c,d) value (11,9,13);#聚集索引冲突（select * from mytest where b=3 for update ;）

对应状态如下图（序号对应client测试sql的序号）：

  1. mysql InnoDB加锁测试 - 文章图片

结论

对于有索引的列，加锁方式为：

  • 如果对辅助索引加锁，也会锁住对应的聚集索引，而不会锁住其他辅助索引
  • 对于主键和唯一索引，只会锁住这条record,不会锁住前后gap
  • 对于某个索引值加锁，实际上对这个record以及前后两个gap都会加锁。假设索引列有（a,b,c）三个值，对b加锁实际上是对（a,b]以及[b,c）都会加锁
  • 事务插入到一个gap时获取的锁为插入意向锁，插入意向锁可以被多个事务同时获得，不同事务同时插入到同一个gap只要插入位置不同就不会引起冲突。

如果对非索引列加锁，mysql会对整张表的聚集索引全部上锁，并对所有聚集索引之间所有gap加锁，任何插入或更新都会造成锁等待，这种情况千万要避免。

mysql InnoDB加锁测试

标签：测试   建表   left join   select   同事   orm   最大值   img   技术   

本文系统来源：https://www.cnblogs.com/bo2256321/p/10503545.html

内容总结

以上是互联网集市为您收集整理的mysql InnoDB加锁测试全部内容，希望文章能够帮你解决mysql InnoDB加锁测试所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/504900.html

来源：【匿名】