Mysql中行级锁的示例分析

共计 4358 个字符，预计需要花费 11 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 Mysql 中行级锁的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

我们首先需要知道的一个大前提是：mysql 的锁是由具体的存储引擎实现的。所以像 Mysql 的默认引擎 MyISAM 和第三方插件引擎 InnoDB 的锁实现机制是有区别的。

Mysql 有三种级别的锁定：表级锁定、页级锁定、行级锁定

一、定义

每次锁定的是一行数据的锁机制就是行级别锁定 (row-level)。行级锁定不是 MySQL 自己实现的锁定方式，而是由其他存储引擎自己所实现的

二、优缺点

1. 优点

由于锁粒度小，争用率低，并发高。

2. 缺点

实现复杂，开销大。

加锁慢、容易出现死锁

三、支持存储引擎

使用行级锁定的主要有 InnoDB 存储引擎，以及 MySQL 的分布式存储引擎 NDBCluster

四、行级锁类型

InnoDB 的行级锁定同样分为两种类型：共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB 也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。

意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说 InnoDB 的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX）

锁模式的兼容性：

五、行级锁定实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。其他注意事项：

在不通过索引条件查询的时候，InnoDB 使用的是表锁，而不是行锁。

由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以即使是访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的。

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

即便在条件中使用了索引字段，但具体是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。

隐式加锁：

InnoDB 自动加意向锁。

对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（X)；

对于普通 SELECT 语句，InnoDB 不会加任何锁；

显示加锁：

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE

排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE

用 SELECT … IN SHARE MODE 获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 UPDATE 或者 DELETE 操作。

但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 SELECT… FOR UPDATE 方式获得排他锁。

InnoDB 如何加表锁：

在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则 MySQL 不会给表加锁；事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES 会隐含地提交事务；COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用 UNLOCK TABLES 释放表锁。

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

既然都用表锁了，怎么不选择 MyISAM 引擎呢！

六、间隙锁（Next-Key 锁）1. 间隙锁定义：

Innodb 的锁定规则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。Innodb 的这种锁定实现方式被称为“NEXT-KEY locking”（间隙锁），因为 Query 执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。

例：假如 emp 表中只有 101 条记录，其 empid 的值分别是 1,2,…,100,101，下面的 SQL：

mysql  select * from emp where empid   100 for update;

是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁，也会对 empid 大于 101（这些记录并不存在）的“间隙”加锁。

2. 间隙锁的缺点：

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

当 Query 无法利用索引的时候，Innodb 会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；

当 Quuery 使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该 Query 的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；

当 Query 在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定

3 . 间隙锁的作用：

防止幻读，以满足相关隔离级别的要求。

为了数据恢复和复制的需要。

4. 注意

在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

InnoDB 除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用间隙锁。

七、查看行级锁争用情况

执行 SQL:mysql show status like InnoDB_row_lock%

mysql  show status like  InnoDB_row_lock% 
+-------------------------------+-------+| Variable_name | Value |
+-------------------------------+-------+| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |+-------------------------------+-------+

如果发现锁争用比较严重，还可以通过设置 InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。如：

设置监视器：mysql create table InnoDB_monitor(a INT) engine=InnoDB;

查看：mysql show engine InnoDB status;

停止查看：mysql drop table InnoDB_monitor;

具体参考：InnoDB Monitor

八、死锁

什么是死锁：你等我释放锁，我等你释放锁就会形成死锁。

如何发现死锁：在 InnoDB 的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在

解决办法：

回滚较小的那个事务

在 REPEATABLE-READ 隔离级别下，如果两个线程同时对相同条件记录用 SELECT…FOR UPDATE 加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成 READ COMMITTED，就可避免问题。

判断事务大小：事务各自插入、更新或者删除的数据量

注意：

当产生死锁的场景中涉及到不止 InnoDB 存储引擎的时候，InnoDB 是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数 InnoDB_lock_wait_timeout 来解决。

九、优化行级锁定

InnoDB 存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于 MyISAM 的表级锁定的。当系统并发量较高的时候，InnoDB 的整体性能和 MyISAM 相比就会有比较明显的优势了。但是，InnoDB 的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让 InnoDB 的整体性能表现不仅不能比 MyISAM 高，甚至可能会更差。

（1）要想合理利用 InnoDB 的行级锁定，做到扬长避短，我们必须做好以下工作：

尽可能让所有的数据检索都通过索引来完成，从而避免 InnoDB 因为无法通过索引键加锁而升级为表级锁定；

合理设计索引，让 InnoDB 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他 Query 的执行；

尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；

尽量控制事务的大小，减少锁定的资源量和锁定时间长度；

在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少 MySQL 因为实现事务隔离级别所带来的附加成本。

（2）由于 InnoDB 的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：

类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；

在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

以上是“Mysql 中行级锁的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！