Mysql的行锁，改一行锁一行

前言

在 MySQL 数据库中，提供锁机制这个功能的是存储引擎，如果需要使用行锁就必须使用支持行锁的存储引擎。在 MySQL 数据库中，MyISAM 存储引擎是不支持行级锁的，支持行锁的是 InnoDB 存储引擎，这也是 MySQL 数据库后来选择 InnoDB 存储引擎为默认存储引擎的原因之一。

行级锁的功能是跟表级锁类似，都是用来锁定数据的，用来防止并发导致数据修改失败这种情况的出现。所不同的是表锁是锁定整个表的数据，而行级锁是操作一行、锁定一行，并且行级锁也是 MySQL 中粒度最小的锁。

当操作某个数据表中的数据使用表级锁时，整个数据表中所有的数据都会被锁定而无法操作，也就是同一时间只能有一个修改操作，这是非常影响数据库并发的，不适合访问量大的场景。而行级锁是操作一行、锁定一行，所以在最大程度上减少了数据表中多个操作之间的冲突，进而提升了数据库数据更新的并发。

行级锁

行级锁就是将修改一行、锁住一行。但这里有一点我们需要注意：行级锁是产生于一个事务之中的，当事务提交或者回滚之后，行级锁立即自动释放。

在 MySQL 数据库中行级锁主要有两种，分别是：共享锁与排他锁。

1. 共享锁（Shared Lock）

共享锁又称为读锁，也可以简称为：S 锁。添加共享锁的语句为：lock in share mode。当多个事务同一时间修改同一条数据时，共享锁只允许其中的一个事务修改数据，读数据则不限制。

通常情况下，共享锁的使用场景是保证数据库中数据与数据之间的关系。举个例子：我们在购物时，总是会让我们添加地址；在填写地址之时，首先会让我们选择省份，其次是市，最后是详细的地址。在这个场景中，如果我们需要添加浦东新区的话，就意味着上海市这个上级选项必须存在。假设，我们添加浦东新区时，恰好其他人把上海市删除；那此时，我们添加的浦东新区就失去了它的意义。

为了防止这个情况的发生，我们最好添加一个锁机制，这个锁机制既不影响数据库的正常读，又能保证数据关系一致。在这种场景下，我们正好用到共享锁。

下面我们利用上面填写地址的例子，来描述一下共享锁的使用场景。

步骤 1，在事务一中开启一个共享锁，在事务二中测试共享锁是否影响查询：

-- 在事务二中查询该条数据，发现添加了共享锁的数据仍然可以正常查询。
mysql> select * from province where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 上海市     |
+----+-----------+
1 row in set (0.08 sec)

可以看出，增加了共享锁之后，其他事务中该条数据是可以正常查询的。

步骤 2，当加上共享锁之后，添加浦东新区，同时删除上海市：

在事务1中增加共享锁，同时增加一条数据

mysql> insert into city (name, fid) values (‘浦东新区’, 1); Query OK, 1 row affected (0.00 sec)

在事务2中删除一条数据

mysql> delete from province where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

我们可以看到，当一个事务中增加了共享锁之后，上海市这条数据无法删除，并且处于阻塞状态。

注意：长时间不处理数据库阻塞时，会报一个ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction的错误，这是因为阻塞超时了。

在 MySQL数据库中，锁机制阻塞的超时时间受innodb_lock_wait_timeout的影响，我们可以以下语句来临时修改锁机制阻塞的时间：SET GLOBAL innodb_lock_wait_timeout=100;，单位秒（s）。

综上，我们可以得出，共享锁的主要功能是：在某个事务中某条数据只要加上了共享锁，那么对于其他的事务来说该条数据将可读但是无法修改。

现在你知道为什么添加上共享锁之后，该条数据就无法正常修改了吧？接下来，我们再一起来探讨一下另一种行级锁——排他锁。

2. 排他锁（Exclusive Lock）

排他锁又称为写锁，也可以简称为：X 锁。在某个事务中给某些数据添加了排他锁，那么这部分数据将无法添加其他锁机制。添加排他锁的语句是：for update。

排他锁和共享锁一样，是需要在事务中开启，当事务提交或者回滚之后将自动释放。另外，还需要注意的是，在一个事务中一个更新的操作会自动添加排他锁。

为了更好地理解排他锁，这里我还是给你举个例子。

春运时，我们需要购买从上海到北京的车票，你跟另一个朋友同时抢购上海到北京的车票，恰好这个时候只剩下一张车票（每购买一张车票之后，剩余的车票数减 1）。假设我们在不考虑并发（同时修改同一条数据就称为并发）的情况下，如果两个人同时抢票就会把原来剩下的 1 修改成 -1，这个时候就会出现重复购票这种情况。

为了防止这种情况的发生，在购买车票时，可以采用 MySQL 数据库为我们提供的锁机制。如果采用表锁，将会将整个表中的所有数据全部锁定，此时除了该车次的车票无法购买之外，其他所有车次的车票全部无法购买，很大程度上影响购买的速度。所以，MySQL 又为我们提供了一个行级锁，当我们购买哪一个车次的车票就会锁定该车次的车票数据，这样做既保证安全又降低了复杂度。

下面我用具体的案例来帮助你理解下。

步骤 1，开启两个事务，来测试排他锁，开启事务的 SQL 参考上面，这里就不再重复列举。
步骤 2，修改票数：
查看火车票上海至北京的票数为1（该表数据只适合用来模拟排他锁的场景）

mysql> select * from train_tickets; 
+——+————————-+———+ | id | tickets | num | 
+——+————————-+———+ | 1 | 上海至北京 | 1 | 
+——+————————-+———+ | 1 row in set (0.00 sec)

修改票数

mysql> update train_tickets set num=num-1 where id = 1; 
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

根据上面的案例，我们可以看出两个事务同时修改同一条数据只能有一个修改成功，这也就保证了数据的安全性。

此时，我们假设一下，如果两个事务同时包含了对方所需要的锁，会有什么效果呢？我们接着往下看。

行级锁中的死锁（Dead Lock）现象

死锁，并不是 MySQL 数据库提供的一种锁机制，而是在使用 MySQL 数据库锁机制的过程中出现的因争夺锁资源而导致一直处于等待阻塞状态的一种错误现象。表锁是不可能出现死锁现象的，死锁只产生于行锁之间。

为了方便理解死锁，我还是通过一个例子来讲解。

步骤 1，事务 1 修改 info 表中 id 为 1 的这条数据，事务 2 修改 info 表中 id 为 3 的这条数据：

事务1中修改id为1的这条数据

mysql> update info set name = ‘女神’ where id = 1; 
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

事务2中修改id为3的这条数据

mysql> update info set name = ‘翠花妹妹’ where id = 3; 
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

步骤 2，事务 1 修改 info 表中 id 为 3 的这条数据，事务 2 中修改 info 表中 id 为 1 的这条数据：

事务1中修改info表中id为3这条数据

mysql> update info set name = ‘铁锤妹妹’ where id = 3; 
Query OK, 0 rows affected (22.91 sec) Rows matched: 1 Changed: 0 Warnings: 0

事务2中修改info表中id为1这条数据（报错）

mysql> update info set name = ‘翠花’ where id = 1; 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

如下图

此时，就造成了一种事务交叉，进而造成了死锁现象。如下图：

因此，在实际应用过程中，当需要多个锁时，要尽可能地把那些可能引发锁冲突的锁进行拆分或者往后放，进而最大限度地避免锁冲突。举个例子：在马路上经过十字路口时，如果没有红绿灯，很快就会堵死；为了解决这个问题，红绿灯让一部分车提前，一部分稍微延后一点行驶即可。死锁也是这样，既然有冲突，就让一部分锁先执行，等这部分执行完毕之后，另一部分后执行，就可以避免这个错误了。

行级锁虽好，但有时候会升级成表级锁

在实际应用中，有些情况下行级锁会升级成为表级锁，进而导致数据库的并发能力下降。那具体哪些情况下会升级为表级锁呢？

第一种情况，当未命中索引时，行级锁会升级成表级锁。😢

-- 在事务1中不使用索引查询数据时，加排他锁（行级锁）
mysql> select * from info where name like '%xxx%' for update;
Empty set (0.00 sec)
-- 在事务2中查询数据时，也加排他锁，发现无法查询
mysql> select * from info where id = 2 for update ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

根据上面案例，我们可以得出：在未使用索引的情况下，使用行锁会引发表锁，这是因为在 MySQL 数据库中行级锁并不是直接锁每一行的数据，而是锁字段的索引。

在 MySQL 数据库中有两种索引，分别是：主键索引和非主键索引。当一条 SQL 使用主键索引时，会直接在主键索引上增加锁；而当一条 SQL 使用非主键索引时，会首先通过非主键索引找到数据表中的主键索引，然后再给主键索引加锁。

第二种情况，当更新所有数据时，会从行级锁升级成表级锁。😒

在实际应用过程中，在一个数据量比较小的表中更新大量数据时，行锁会升级成为表锁。示例如下：

-- 在事务1中更新所有的数据。
mysql> update city set fid = 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
-- 在事务2中随机找一个数据加排他锁，发现无法添加。
mysql> select * from city where id = 1 for update ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

总结😊

• 共享锁主要是用来解决数据一致性问题的。加上共享锁之后，该条数据在其他事务中只读但不可以修改。
• 排他锁就像它的名字一样，排斥其他的锁机制，也就是说加上排他锁之后，将无法再添加任何其他锁。

这样就保证了在大量的数据并发情况下不会出现重复修改这种情况，而且行级锁是操作一行锁定一行的，解决了表级锁锁住数据太多而导致的并发问题。

但在实际的应用中，并不是加的锁越多越好。加锁和释放锁都是有时间消耗的，例如行锁，更新一行加一行的锁，是会很消耗性能的，会很慢的。所以，要尽量避免大量更新数据的情况。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们处理，核实后本网站将在24小时内删除侵权内容。