MySQL如何使用行锁?行锁怎么实现的?

2019-10-14 09:38栏目:编程

在我们日常工作开发中,不可避免的会和mysql的锁机制打交道,本文主要讲解mysql行锁实现原理和失效的场景。

比如我们现在有一张user表,使用desc user命令查看表的结构信息:

mysql 如何加锁

在这里我们先设置一个前提条件,mysql当前使用的存储引擎为innoDB,隔离级别采用的是不可重复读(Repeatable Read (RR));

首先我们需要明白,行锁(X锁)是基于索引建立的,简单点来说是对索引表加锁,当我们使用一个非索引字段进行查询用户信息并添加行锁时:select * from user where USER_NAME = "bibi"? for update时,我们可能会觉得我们是对这一行数据添加了行锁,事实上并非如此,我们通过执行explain select * from user where user_name = 'admin' 获取到sql的执行过程如下:

数据库锁机制原理

我们发现type类型为ALL,代表全表扫描,如果我们对其进行加行锁,会对整个表中的行加X锁,行与行之间添加了GAP锁(GAP锁是对不可重复读的一种实现,具体细节可查看文章最后的博文链接地址), 在这个情况下,MySQL也做了一些优化,就是所谓的semi-consistent read。semi-consistent read开启的情况下,对于不满足查询条件的记录,MySQL会提前放锁。mysql什么时候锁表锁行,当这样也增加了服务器的负担,降低了数据库的执行效率;如果semi-consistent read没有开启将会造成全表锁,除了添加的行锁,还有很多多余的GAP锁,如果当前事务一直不提交的情况下,其他事务对当前表进行修改时会全部阻塞,从而可能造成数据库宕机.
总结:

当我们在对一行或多行数据添加行锁时,我们需要注意以下几点:
哪些情况需要建索引:
(1) 主键,唯一索引
(2) 经常用作查询条件的字段需要创建索引
(3) 经常需要排序、分组和统计的字段需要建立索引
(4) 查询中与其他表关联的字段,外键关系建立索引
哪些情况不要建索引:
(1) 表的记录太少,百万级以下的数据不需要创建索引
(2) 经常增删改的表不需要创建索引
(3) 数据重复且分布平均的字段不需要创建索引,如 true,false 之类。
(4) 频发更新的字段不适合创建索引
(5) where条件里用不到的字段不需要创建索引;
哪些情况会导致索引失效:
(1) 查询中使用了or;
(2) 查询中未遵从最左前缀原则;
(3) 查询中使用了is null;
(4) 查询中使用like '%aa%';
(5) 查询中使用的in()中的参数数量超出了最大限定值;
(6) 查询中字符串未添加单引号

了解更多,关于mysql行锁的问题,可以留言大家一起探讨学习。

本文来自网络,不代表山斋月平台立场,转载请注明出处: https://www.shanzhaiyue.top