mysql InnoDB加锁测试

以下测试基于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 备份


下一篇:银联商务开放平台小程序尝试(weex)2