MySQL中的SELECT ...for update（悲观锁和乐观锁）

最新推荐文章于 2024-09-18 11:15:58 发布

lanxingbudui

最新推荐文章于 2024-09-18 11:15:58 发布

阅读量4.8k

点赞数 3

分类专栏： MySQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/lanxingbudui/article/details/117230799

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL悲观锁（如forupdate）的工作原理和在产品下单场景中的应用，以及对比之下乐观锁（versioning）的实现方式，包括数据版本机制。通过实例展示了不同情况下的锁级别和其对并发性能的影响。

摘要由CSDN通过智能技术生成

一、MySQL悲观锁：

SELECT ...for update是在MySQL悲观锁的应用。

1、何为MySQL的悲观锁？（有悲观锁，就有乐观锁，下面介绍）

悲观锁是对数据被修改时持悲观态度（认为数据在被修改的时候一定会存在并发问题），因此在整个数据处理过程中将数据锁定。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在应用层中实现了加锁机制，也无法保证外部系统不会修改数据）。

　　在产品表t_product中有个字段p_status，p_status为1代表产品未被下单，p_status为2代表产品已经被下单，那么我们对某个产品下单时必须确保该产品p_status为1。假设产品的p_id为1。如果不采用锁，那么操作方法如下：

--1.查询出产品信息
select p_status from t_product where p_id = 1;
--2.根据产品信息生成订单
insert into t_order (id, p_id) values ('001', 1);
--3.修改产品p_status为2
update t_product set status=2 where p_id = 1;

此类场景在高并发访问的情况下很可能会出现问题。前面已经提到，只有当p_status为1时才能对该产品下单，上面第一步操作中，查询出来的商品status为1。但是当我们执行第三步Update操作的时候，有可能出现其他人先一步对产品下单把p_status状态修改为2了，但是我们并不知道数据已经被修改了，这样就可能造成同一个产品被下单2次，使得数据不一致。所以说这种方式是不安全的。

2、使用悲观锁来实现

　　在上面的场景中，产品信息从查询出来到修改，中间有一个处理订单的过程，使用悲观锁的原理就是，当我们在查询出产品信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为产品被锁定了，就不会出现有第三者来对其进行修改了。要使用悲观锁，我们必须关闭mysql数据库的自动提交属性。

--设置MySQL为非autocommit模式：
set autocommit=0;
--设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
--0.开始事务
begin;/begin work;/start transaction; (三者选一就可以，推荐start transaction是SQL-99标准启动一个事务)
--1.查询出产品信息
select p_status from t_product where p_id=1 for update;
--2.根据产品信息生成订单
insert into t_order (id,p_id) values ('001',1);
--3.修改产品status为2
update t_product set p_status=2;
--4.提交事务
commit;/commit work;

假设有个产品表t_product，里面有p_id跟p_name二个字段，p_id是主键。
例1: (明确指定主键，并且有此数据，row lock)
SELECT * FROM t_product WHERE p_id='3' FOR UPDATE;
例2: (明确指定主键，若查无此数据，无lock)
SELECT * FROM t_product WHERE p_id='-1' FOR UPDATE;
例2: (无主键，table lock)
SELECT * FROM t_product WHERE p_name='电脑' FOR UPDATE;
例3: (主键不明确，table lock)
SELECT * FROM t_product WHERE p_id<>'3' FOR UPDATE;
例4: (主键不明确，table lock)
SELECT * FROM t_product WHERE p_id LIKE '3' FOR UPDATE;
注1: FOR UPDATE 仅适用于InnoDB，且必须在事务区块(start transaction/COMMIT)中才能生效。
注2: 要测试锁定的状况，可以利用MySQL 的Command Mode ，开二个视窗来做测试。

以上就是关于数据库主键对MySQL锁级别的影响实例，需要注意的是，除了主键外，使用索引也会影响数据库的锁定级别，因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受。所以与悲观锁相对的是乐观锁，具体参见下面介绍：

二、MySQL乐观锁：

乐观锁（ Optimistic Locking ）相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。那么我们如何实现乐观锁呢，一般来说有以下2种方式：

1.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。

何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。用下面的一张图来说明：