MySQL 表锁和行锁机制-转载

快来打我* 2022-12-03 05:30 156阅读 0赞

# 转载：[https://blog.csdn.net/xts5701046/article/details/81395958][https_blog.csdn.net_xts5701046_article_details_81395958] #

# MySQL 表锁和行锁机制 #

行锁变表锁，是福还是坑？如果你不清楚MySQL加锁的原理，你会被它整的很惨！不知坑在何方？没事，我来给你们标记几个坑。遇到了可别乱踩。通过本章内容，带你学习MySQL的行锁，表锁，两种锁的优缺点，行锁变表锁的原因，以及开发中需要注意的事项。还在等啥？经验等你来拿！

MySQL的存储引擎是从MyISAM到InnoDB，锁从表锁到行锁。后者的出现从某种程度上是弥补前者的不足。比如：MyISAM不支持事务，InnoDB支持事务。表锁虽然开销小，锁表快，但高并发下性能低。行锁虽然开销大，锁表慢，但高并发下相比之下性能更高。事务和行锁都是在确保数据准确的基础上提高并发的处理能力。本章重点介绍InnoDB的行锁。

## 案例分析 ##

目前，MySQL常用的存储引擎是InnoDB，相对于MyISAM而言。InnoDB更适合高并发场景，同时也支持事务处理。我们通过下面这个案例(坑)，来了解行锁和表锁。

业务：因为订单重复导入，需要用脚本将订单状态为”待客服确认”且平台是”xxx”的数据批量修改为”已关闭”。

说明：避免直接修改订单表造成数据异常。这里用innodb\_lock 表演示InnoDB的行锁。表中有三个字段：id，k(key值)，v(value值)。

步骤：

第一步：连接[数据库][Link 1]，这里为了方便区分命名为Transaction-A，设置autocommit为零，表示需手动提交事务。

第二步：Transaction-A，执行update修改id为1的命令。

第三步：新增一个连接，命名为Transaction-B，能正常修改id为2的数据。再执行修改id为1的数据命令时，却发现该命令一直处理阻塞等待中。

第四步：Transaction-A，执行commit命令。Transaction-B，修改id为1的命令自动执行，等待37.51秒。

总结：**多个事务操作同一行数据时，后来的事务处于阻塞等待状态。这样可以避免了脏读等数据一致性的问题。后来的事务可以操作其他行数据，解决了表锁高并发性能低的问题**。

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 </td> 
 <td> <code>#&nbsp;</code><code>Transaction</code><code>-A</code> <code>mysql&gt;&nbsp;</code><code>set</code>&nbsp;<code>autocommit = 0;</code> <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>innodb_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'1001'</code>&nbsp;<code>where</code>&nbsp;<code>id=1;</code> <code>mysql&gt;&nbsp;</code><code>commit</code><code>;</code> &nbsp; <code>#&nbsp;</code><code>Transaction</code><code>-B</code> <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>innodb_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'2001'</code>&nbsp;<code>where</code>&nbsp;<code>id=2;</code> <code>Query OK, 1 row affected (0.37 sec)</code> <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>innodb_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'1002'</code>&nbsp;<code>where</code>&nbsp;<code>id=1;</code> <code>Query OK, 1 row affected (37.51 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

有了上面的模拟操作，结果和理论又惊奇的一致，似乎可以放心大胆的实战。。。。。。但现实真的很残酷。

现实：当执行批量修改数据脚本的时候，行锁升级为表锁。其他对订单的操作都处于等待中，，，

原因：InnoDB只有在通过索引条件检索数据时使用行级锁，否则使用表锁！而模拟操作正是通过id去作为检索条件，而id又是MySQL自动创建的唯一索引，所以才忽略了行锁变表锁的情况。

步骤：

第一步：还原问题，Transaction-A，通过k=1更新v。Transaction-B，通过k=2更新v，命令处于阻塞等待状态。

第二步：处理问题，给需要作为查询条件的字段添加索引。用完后可以删掉。

总结：**InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁**。

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 </td> 
 <td> <code>Transaction</code><code>-A</code> <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>innodb_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'1002'</code>&nbsp;<code>where</code>&nbsp;<code>k=1;</code> <code>mysql&gt;&nbsp;</code><code>commit</code><code>;</code> <code>mysql&gt;&nbsp;</code><code>create</code>&nbsp;<code>index</code>&nbsp;<code>idx_k&nbsp;</code><code>on</code>&nbsp;<code>innodb_lock(k);</code> &nbsp; <code>Transaction</code><code>-B</code> <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>innodb_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'2002'</code>&nbsp;<code>where</code>&nbsp;<code>k=2;</code> <code>Query OK, 1 row affected (19.82 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

从上面的案例看出，行锁变表锁似乎是一个坑，可MySQL没有这么无聊给你挖坑。这是因为MySQL有自己的执行计划。

当你需要更新一张较大表的大部分甚至全表的数据时。而你又傻乎乎地用索引作为检索条件。一不小心开启了行锁(没毛病啊！保证数据的一致性！)。可MySQL却认为大量对一张表使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突问题，性能严重下降。所以MySQL会将行锁升级为表锁，即实际上并没有使用索引。

我们仔细想想也能理解，既然整张表的大部分数据都要更新数据，在一行一行地加锁效率则更低。其实我们可以通过explain命令查看MySQL的执行计划，你会发现key为null。表明MySQL实际上并没有使用索引，行锁升级为表锁也和上面的结论一致。

本章重点介绍InnoDB的行锁及其相关的事务知识。如果想了解MySQL的执行计划，请看上一章节。

## 行锁 ##

行锁的劣势：开销大；加锁慢；会出现死锁

行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：

共享锁：select \* from tableName where … + lock in share more

排他锁：select \* from tableName where … + for update

InnoDB和MyISAM的最大不同点有两个：一，InnoDB支持事务(transaction)；二，默认采用行级锁。加锁可以保证事务的一致性，可谓是有人(锁)的地方，就有江湖(事务)；我们先简单了解一下事务知识。

### MySQL 事务属性 ###

事务是由一组SQL语句组成的逻辑处理单元，事务具有ACID属性。

**原子性**（Atomicity）：事务是一个原子操作单元。在当时原子是不可分割的最小元素，其对数据的修改，要么全部成功，要么全部都不成功。

**一致性**（Consistent）：事务开始到结束的时间段内，数据都必须保持一致状态。

**隔离性**（Isolation）：数据库[系统][Link 3]提供一定的隔离机制，保证事务在不受外部并发操作影响的”独立”环境执行。

**持久性**（Durable）：事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

### 事务常见问题 ###

**更新丢失**（Lost Update）

原因：当多个事务选择同一行操作，并且都是基于最初选定的值，由于每个事务都不知道其他事务的存在，就会发生更新覆盖的问题。类比github提交冲突。

**脏读**（Dirty Reads）

原因：事务A读取了事务B已经修改但尚未提交的数据。若事务B回滚数据，事务A的数据存在不一致性的问题。

**不可重复读**（Non-Repeatable Reads）

原因：事务A第一次读取最初数据，第二次读取事务B已经提交的修改或删除数据。导致两次读取数据不一致。不符合事务的隔离性。

**幻读**（Phantom Reads）

原因：事务A根据相同条件第二次查询到事务B提交的新增数据，两次数据结果集不一致。不符合事务的隔离性。

幻读和脏读有点类似

脏读是事务B里面修改了数据，

幻读是事务B里面新增了数据。

### 事务的隔离级别 ###

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大。这是因为事务隔离实质上是将事务在一定程度上”串行”进行，这显然与”并发”是矛盾的。根据自己的业务逻辑，权衡能接受的最大副作用。从而平衡了”隔离” 和 “并发”的问题。MySQL默认隔离级别是可重复读。

脏读，不可重复读，幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 </td> 
 <td> <code>+</code><code>------------------------------+---------------------+--------------+--------------+--------------+</code> <code>| 隔离级别&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 读数据一致性&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 脏读&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 不可重复 读&nbsp;&nbsp; | 幻读&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>------------------------------+---------------------+--------------+--------------+--------------+</code> <code>| 未提交读(</code><code>Read</code>&nbsp;<code>uncommitted</code><code>)&nbsp;&nbsp;&nbsp; | 最低级别&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 是&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 是&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 是&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>------------------------------+---------------------+--------------+--------------+--------------+</code> <code>| 已提交读(</code><code>Read</code>&nbsp;<code>committed</code><code>)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 语句级&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 否&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 是&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 是&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>------------------------------+---------------------+--------------+--------------+--------------+</code> <code>| 可重复读(</code><code>Repeatable</code>&nbsp;<code>read</code><code>)&nbsp;&nbsp;&nbsp;&nbsp; | 事务级&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 否&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 否&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 是&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>------------------------------+---------------------+--------------+--------------+--------------+</code> <code>| 可序列化(</code><code>Serializable</code><code>)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 最高级别，事务级&nbsp;&nbsp;&nbsp;&nbsp; | 否&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 否&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 否&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>------------------------------+---------------------+--------------+--------------+--------------+</code> </td> 
 </tr> 
 </tbody> 
</table>

查看当前数据库的事务隔离级别：show variables like ‘tx\_isolation’;

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>mysql&gt; show variables&nbsp;</code><code>like</code>&nbsp;<code>'tx_isolation'</code><code>;</code> <code>+</code><code>---------------+-----------------+</code> <code>| Variable_name | Value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>---------------+-----------------+</code> <code>| tx_isolation&nbsp; |&nbsp;</code><code>REPEATABLE</code><code>-</code><code>READ</code>&nbsp;<code>|</code> <code>+</code><code>---------------+-----------------+</code> </td> 
 </tr> 
 </tbody> 
</table>

### 间隙锁 ###

当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做”间隙(GAP)”。InnoDB也会对这个”间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 </td> 
 <td> <code>Transaction</code><code>-A</code> <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>innodb_lock&nbsp;</code><code>set</code>&nbsp;<code>k=66&nbsp;</code><code>where</code>&nbsp;<code>id &gt;=6;</code> <code>Query OK, 1 row affected (0.63 sec)</code> <code>mysql&gt;&nbsp;</code><code>commit</code><code>;</code> &nbsp; <code>Transaction</code><code>-B</code> <code>mysql&gt;&nbsp;</code><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>innodb_lock (id,k,v)&nbsp;</code><code>values</code><code>(7,</code><code>'7'</code><code>,</code><code>'7000'</code><code>);</code> <code>Query OK, 1 row affected (18.99 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

危害(坑)：**若执行的条件是范围过大，则InnoDB会将整个范围内所有的索引键值全部锁定，很容易对性能造成影响**。

### 排他锁 ###

排他锁，也称写锁，独占锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

![排他锁][format_png]

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 </td> 
 <td> <code># Transaction_A</code> <code>mysql&gt;&nbsp;</code><code>set</code>&nbsp;<code>autocommit=0;</code> <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>innodb_lock&nbsp;</code><code>where</code>&nbsp;<code>id=4&nbsp;</code><code>for</code>&nbsp;<code>update</code><code>;</code> <code>+</code><code>----+------+------+</code> <code>| id | k&nbsp;&nbsp;&nbsp; | v&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>----+------+------+</code> <code>|&nbsp; 4 | 4&nbsp;&nbsp;&nbsp; | 4000 |</code> <code>+</code><code>----+------+------+</code> <code>1 row&nbsp;</code><code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00 sec)</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>innodb_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'4001'</code>&nbsp;<code>where</code>&nbsp;<code>id=4;</code> <code>Query OK, 1 row affected (0.00 sec)</code> <code>Rows</code>&nbsp;<code>matched: 1&nbsp; Changed: 1&nbsp; Warnings: 0</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>commit</code><code>;</code> <code>Query OK, 0&nbsp;</code><code>rows</code>&nbsp;<code>affected (0.04 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 </td> 
 <td> <code># Transaction_B</code> <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>innodb_lock&nbsp;</code><code>where</code>&nbsp;<code>id=4&nbsp;</code><code>for</code>&nbsp;<code>update</code><code>;</code> <code>+</code><code>----+------+------+</code> <code>| id | k&nbsp;&nbsp;&nbsp; | v&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>----+------+------+</code> <code>|&nbsp; 4 | 4&nbsp;&nbsp;&nbsp; | 4001 |</code> <code>+</code><code>----+------+------+</code> <code>1 row&nbsp;</code><code>in</code>&nbsp;<code>set</code>&nbsp;<code>(9.53 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

### 共享锁 ###

共享锁，也称读锁，多用于判断数据是否存在，多个读操作可以同时进行而不会互相影响。当如果事务对读锁进行修改操作，很可能会造成死锁。如下图所示。

![共享锁][format_png 1]

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code># Transaction_A</code> <code>mysql&gt;&nbsp;</code><code>set</code>&nbsp;<code>autocommit=0;</code> <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>innodb_lock&nbsp;</code><code>where</code>&nbsp;<code>id=4 lock&nbsp;</code><code>in</code>&nbsp;<code>share mode;</code> <code>+</code><code>----+------+------+</code> <code>| id | k&nbsp;&nbsp;&nbsp; | v&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>----+------+------+</code> <code>|&nbsp; 4 | 4&nbsp;&nbsp;&nbsp; | 4001 |</code> <code>+</code><code>----+------+------+</code> <code>1 row&nbsp;</code><code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00 sec)</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>innodb_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'4002'</code>&nbsp;<code>where</code>&nbsp;<code>id=4;</code> <code>Query OK, 1 row affected (31.29 sec)</code> <code>Rows</code>&nbsp;<code>matched: 1&nbsp; Changed: 1&nbsp; Warnings: 0</code> </td> 
 </tr> 
 </tbody> 
</table>

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 </td> 
 <td> <code># Transaction_B</code> <code>mysql&gt;&nbsp;</code><code>set</code>&nbsp;<code>autocommit=0;</code> <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>innodb_lock&nbsp;</code><code>where</code>&nbsp;<code>id=4 lock&nbsp;</code><code>in</code>&nbsp;<code>share mode;</code> <code>+</code><code>----+------+------+</code> <code>| id | k&nbsp;&nbsp;&nbsp; | v&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>----+------+------+</code> <code>|&nbsp; 4 | 4&nbsp;&nbsp;&nbsp; | 4001 |</code> <code>+</code><code>----+------+------+</code> <code>1 row&nbsp;</code><code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00 sec)</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>innodb_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'4002'</code>&nbsp;<code>where</code>&nbsp;<code>id=4;</code> <code>ERROR 1213 (40001): Deadlock found&nbsp;</code><code>when</code>&nbsp;<code>trying&nbsp;</code><code>to</code>&nbsp;<code>get lock; try restarting</code><code>transaction</code> </td> 
 </tr> 
 </tbody> 
</table>

### 分析行锁定 ###

通过检查InnoDB\_row\_lock 状态变量分析系统上的行锁的争夺情况 show status like ‘innodb\_row\_lock%’

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 </td> 
 <td> <code>mysql&gt; show status&nbsp;</code><code>like</code>&nbsp;<code>'innodb_row_lock%'</code><code>;</code> <code>+</code><code>-------------------------------+-------+</code> <code>| Variable_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Value |</code> <code>+</code><code>-------------------------------+-------+</code> <code>| Innodb_row_lock_current_waits | 0&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>| Innodb_row_lock_time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>| Innodb_row_lock_time_avg&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>| Innodb_row_lock_time_max&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>| Innodb_row_lock_waits&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>-------------------------------+-------+</code> </td> 
 </tr> 
 </tbody> 
</table>

innodb\_row\_lock\_current\_waits: 当前正在等待锁定的数量

innodb\_row\_lock\_time: 从系统启动到现在锁定总时间长度；非常重要的参数，

innodb\_row\_lock\_time\_avg: 每次等待所花平均时间；非常重要的参数，

innodb\_row\_lock\_time\_max: 从系统启动到现在等待最常的一次所花的时间；

innodb\_row\_lock\_waits: 系统启动后到现在总共等待的次数；非常重要的参数。直接决定优化的方向和策略。

### 行锁优化 ###

1 尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。

2 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。

3 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。

4 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

## 表锁 ##

表锁的优势：开销小；加锁快；无死锁

表锁的劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低

加锁的方式：自动加锁。查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显示加锁：

共享读锁：lock table tableName read;

独占写锁：lock table tableName write;

批量解锁：unlock tables;

### 共享读锁 ###

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读操作，但会阻塞对同一表的写操作。只有当读锁释放后，才能执行其他进程的写操作。在锁释放前不能取其他表。

![读锁][format_png 2]

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 </td> 
 <td> <code>Transaction</code><code>-A</code> <code>mysql&gt; lock&nbsp;</code><code>table</code>&nbsp;<code>myisam_lock&nbsp;</code><code>read</code><code>;</code> <code>Query OK, 0&nbsp;</code><code>rows</code>&nbsp;<code>affected (0.00 sec)</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>myisam_lock;</code> <code>9&nbsp;</code><code>rows</code>&nbsp;<code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00 sec)</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>innodb_lock;</code> <code>ERROR 1100 (HY000):&nbsp;</code><code>Table</code>&nbsp;<code>'innodb_lock'</code>&nbsp;<code>was&nbsp;</code><code>not</code>&nbsp;<code>locked&nbsp;</code><code>with</code>&nbsp;<code>LOCK TABLES</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>myisam_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'1001'</code>&nbsp;<code>where</code>&nbsp;<code>k=</code><code>'1'</code><code>;</code> <code>ERROR 1099 (HY000):&nbsp;</code><code>Table</code>&nbsp;<code>'myisam_lock'</code>&nbsp;<code>was locked&nbsp;</code><code>with</code>&nbsp;<code>a&nbsp;</code><code>READ</code>&nbsp;<code>lock&nbsp;</code><code>and</code>&nbsp;<code>can't be updated</code> &nbsp; <code>mysql&gt; unlock tables;</code> <code>Query OK, 0&nbsp;</code><code>rows</code>&nbsp;<code>affected (0.00 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 </td> 
 <td> <code>Transaction</code><code>-B</code> <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>myisam_lock;</code> <code>9&nbsp;</code><code>rows</code>&nbsp;<code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00 sec)</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>innodb_lock;</code> <code>8&nbsp;</code><code>rows</code>&nbsp;<code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.01 sec)</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>myisam_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'1001'</code>&nbsp;<code>where</code>&nbsp;<code>k=</code><code>'1'</code><code>;</code> <code>Query OK, 1 row affected (18.67 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

### 独占写锁 ###

对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。在锁释放前不能写其他表。

![写锁][format_png 3]

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 </td> 
 <td> <code>Transaction</code><code>-A</code> <code>mysql&gt;&nbsp;</code><code>set</code>&nbsp;<code>autocommit=0;</code> <code>Query OK, 0&nbsp;</code><code>rows</code>&nbsp;<code>affected (0.05 sec)</code> &nbsp; <code>mysql&gt; lock&nbsp;</code><code>table</code>&nbsp;<code>myisam_lock write;</code> <code>Query OK, 0&nbsp;</code><code>rows</code>&nbsp;<code>affected (0.03 sec)</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>myisam_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'2001'</code>&nbsp;<code>where</code>&nbsp;<code>k=</code><code>'2'</code><code>;</code> <code>Query OK, 1 row affected (0.00 sec)</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>myisam_lock;</code> <code>9&nbsp;</code><code>rows</code>&nbsp;<code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00 sec)</code> &nbsp; <code>mysql&gt;&nbsp;</code><code>update</code>&nbsp;<code>innodb_lock&nbsp;</code><code>set</code>&nbsp;<code>v=</code><code>'1001'</code>&nbsp;<code>where</code>&nbsp;<code>k=</code><code>'1'</code><code>;</code> <code>ERROR 1100 (HY000):&nbsp;</code><code>Table</code>&nbsp;<code>'innodb_lock'</code>&nbsp;<code>was&nbsp;</code><code>not</code>&nbsp;<code>locked&nbsp;</code><code>with</code>&nbsp;<code>LOCK TABLES</code> &nbsp; <code>mysql&gt; unlock tables;</code> <code>Query OK, 0&nbsp;</code><code>rows</code>&nbsp;<code>affected (0.00 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> <code>Transaction</code><code>-B</code> <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>myisam_lock;</code> <code>9&nbsp;</code><code>rows</code>&nbsp;<code>in</code>&nbsp;<code>set</code>&nbsp;<code>(42.83 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

总结：**表锁，读锁会阻塞写，不会阻塞读。而写锁则会把读写都阻塞**。

### 查看加锁情况 ###

show open tables; 1表示加锁，0表示未加锁。

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>mysql&gt; show&nbsp;</code><code>open</code>&nbsp;<code>tables&nbsp;</code><code>where</code>&nbsp;<code>in_use &gt; 0;</code> <code>+</code><code>----------+-------------+--------+-------------+</code> <code>|&nbsp;</code><code>Database</code>&nbsp;<code>|&nbsp;</code><code>Table</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>| In_use | Name_locked |</code> <code>+</code><code>----------+-------------+--------+-------------+</code> <code>| lock&nbsp;&nbsp;&nbsp;&nbsp; | myisam_lock |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |</code> <code>+</code><code>----------+-------------+--------+-------------+</code> </td> 
 </tr> 
 </tbody> 
</table>

### 分析表锁定 ###

可以通过检查table\_locks\_waited 和 table\_locks\_immediate 状态变量分析系统上的表锁定：show status like ‘table\_locks%’

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>mysql&gt; show status&nbsp;</code><code>like</code>&nbsp;<code>'table_locks%'</code><code>;</code> <code>+</code><code>----------------------------+-------+</code> <code>| Variable_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Value |</code> <code>+</code><code>----------------------------+-------+</code> <code>| Table_locks_immediate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 104&nbsp;&nbsp; |</code> <code>| Table_locks_waited&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+</code><code>----------------------------+-------+</code> </td> 
 </tr> 
 </tbody> 
</table>

table\_locks\_immediate: 表示立即释放表锁数。

table\_locks\_waited: 表示需要等待的表锁数。此值越高则说明存在着越严重的表级锁争用情况。

此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的存储引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞。

## 什么场景下用表锁 ##

InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。MySQL这样设计并不是给你挖坑。它有自己的设计目的。

即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible\_key 和 key)。如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

第一种情况：**全表更新**。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

第二种情况：**多表查询**。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

## 页锁 ##

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发处理能力一般。只需了解一下。

## 总结 ##

1 InnoDB 支持表锁和行锁，使用索引作为检索条件修改数据时采用行锁，否则采用表锁。

2 InnoDB 自动给修改操作加锁，给查询操作不自动加锁

3 行锁可能因为未使用索引而升级为表锁，所以除了检查索引是否创建的同时，也需要通过explain执行计划查询索引是否被实际使用。

4 行锁相对于表锁来说，优势在于高并发场景下表现更突出，毕竟锁的粒度小。

5 当表的大部分数据需要被修改，或者是多表复杂关联查询时，建议使用表锁优于行锁。

6 为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库的并发处理能力和性能。

[https_blog.csdn.net_xts5701046_article_details_81395958]: https://blog.csdn.net/xts5701046/article/details/81395958
[Link 1]: https://www.2cto.com/database/
[Link 2]: https://www.2cto.com/database/201801/710950.html#
[Link 3]: https://www.2cto.com/os/
[format_png]: /images/20221123/42387f1c11704441bb1b9045e79ca19c.png
[format_png 1]: /images/20221123/72eb3280c68445c296fcf77481dcf979.png
[format_png 2]: /images/20221123/e53faf50dbae4ba8a4b0671a43d87a97.png
[format_png 3]: /images/20221123/3db0bc962ab04f00a3244b03215f4b0f.png