你知道吗?使用 MySQL 中InnoDB行锁需要避免这些坑?

一、InnoDB行锁的实现方式

通过给索引上的索引项加锁来实现的,也就意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。这一点在实际应用中特别需要注意,不然的话可能导致大量的锁冲突,从而影响引发并发性能。

实验一:对没有索引的加锁,导致表锁

1)准备工作:建tab_no_index表,表中无任何索引,并插入数据

2)Session_1: 我们给id=1的行加上排它锁(for update),由于id没有索引,实际上是表级锁;

3)Session_2:我们给id=2的行加上排它锁(for update),由于id没有索引,所以去申请表级锁,但是却出现了锁等待!原因就是在没有索引的情况下,InnoDB只能使用表锁。

备注:MySQL中的for update 仅适用于InnoDB(因为是只有此引擎才有行级锁),并且必须开启事务,在begin与commit之间才生效。for update是在数据库中上锁用的,可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候,其他事务可以对这行读取但是不能写入或更新,只能等该事务Rollback, Commit, Lost connection…

实验二:对有索引的键值加锁,会对所有涉及到的数据行加锁

1)准备工作:对id建索引如下

2)Session_1:此时id是有索引的,我们对id=1 and name=1的一行加排它锁;

3)Session_2:访问不同于Session_1的id=1, name=5行,但是索引键值是一样的,照样等待锁,锁冲突了。

实验三:多个索引时,不同的事务可以使用不同的索引锁定不同的行,不论什么索引,InnoDB都会使用行锁对数据加锁(对有索引的行数据)。

1)准备工作:对tab_no_index追加name索引:alter table tab_no_index add index name(name);

2)Session_1:开启事务对id=1的行加排它锁,即对name=1与name=5两个数据加锁。

3)Session_2:开启事务对name=2行加锁,因为该数据没有被加锁,索引可以获得锁

4)Session_3:再对name=5的数据进行加锁,由于该数据记录已被Session_1锁定,所以等待获得锁。

注意事项:即便使用了索引,但还是要看MySQL具体对SQL的执行计划,不一定能使用到

如我们对实验三对name='2'进行加锁,误以为name是int类型,本来name是有索引的,但是最后结果导致表锁:

二、间隙锁(Next-Key锁)

当用范围条件而不是相等条件检索数据,并请求共享或者排它锁的时候,InnoDB会给符合条件的已有数据记录的索引项加锁;对于不在范围内的但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个间隙加锁,这就是所谓的间隙锁。

如:select * from where id>100 for update 对id大于100的数据对加锁,但是此时数据中id只有1,2….100,101,不仅对存在的101的记录加锁,还会对大于101不存在的数据的间隙加锁。

此外,对使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁,如下:

Session_1:对不存在的id=6的记录加锁

Session_2:插入id=6的记录,也会出现锁等待

三、什么时候使用表锁?

对于InnoDB表,在绝大部分情况下都应该使用行锁,因为事务和行锁往往是我们之所以选择InnoDB表的理由,但在个别情况下也使用表级锁;

  • 事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间等待和锁冲突;
  • 事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。

使用表锁需要注意几点:

1)使用LOCK TABLES虽然可以给InnoDB加表级锁,表级锁不是InnoDB存储引擎层管理的,而是由其上一层MySQL Server负责的

2)在用LOCK TABLES对InnoDB表加锁时需要注意,要将Autocommit设置为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCK TABLES释放表锁,因为UNLOCK_TABLES隐含提交事务;COMMIT或ROLLBACK并不能释放用LOCK TABLES加表级锁。

SET AUTOCOMMIT=0;

LOCK TABLES table1 WRITE, table2 READ,...;

[do something....]

COMMIT;

UNLOCK TABLES;

总结

  • 从设计之初,就应该建立良好的索引机制,避免对关键字段搜索时造成表锁;
  • 避免长时间事务未提交等情况,导致锁冲突,死锁等情况;
  • 不要老是抱怨数据库有问题,应该从自身写的SQL分析出发,学会分析(数据库不行大部分是因为SQL写的有问题,没错,是自身问题);
  • 不要总是觉得这是DBA该做的事,开发者应该学会基本的SQL常识(如MySQL的最左索引,回表,索引覆盖等知识),学会基本的优化步骤。

 

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读