MySQL 杂记 —— 读写锁与库存设计

假设有A、B两个用户同时各购买一件 id=1 的商品，用户A获取到的库存量为 1000，用户B获取到的库存量也为 1000，用户A完成购买后修改该商品的库存量为 999，用户B完成购买后修改该商品的库存量为 999，此时库存量数据产生了不一致。

 

分析：

MySQL 默认的事务隔离级别是可重复读，但是 MySQL 通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，即实现的的“快照读”（读的历史数据），要实现当前读（读的数据库当前版本的数据）则通常需要加排它锁或者重新实现 MVCC 方案。

Mysql 自带的 MVCC 只保证单行的可重复读，但不保证全局数据的一致性，即多个并发事务可能在不加锁的情况下读到相同的历史数据，然后执行更新，导致数据不一致。这可能会导致 库存超卖、余额透支 等问题。

 

注：

① 快照读 (snapshot read)，即读取的是历史数据，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读。而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在Mysql中：

• 快照读：就是select
  • select * from table ….;
• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
  • select * from table where ? lock in share mode;
  • select * from table where ? for update;
  • insert;
  • update ;
  • delete;

② 乐观锁，大多是基于数据版本记录机制（MVCC）实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

 

③ 读锁，又称共享锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

 

④ 写锁，又称为排他锁，简称X锁，顾名思义，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，只有当前持有排它锁的事务可以对数据就行读取和修改。

mysql InnoDB引擎默认的修改数据语句，update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，如果 select 时需要加排他锁可以使用 select ...for update 语句。

所有加过排他锁的数据行在其他事务中是不能修改数据的，这个锁会 一直持有到事务提交或回滚，不会提前释放。由于普通查询没有任何锁机制，所以可以直接通过 select ...from... 查询数据，只不过读的是历史数据。 

关于事务的理解可参考

 

解决方案：

① 悲观锁

每次获取商品时，对该商品加排它锁（x锁）。也就是在用户A获取获取 id=1 的商品信息时对该行记录加锁，期间其他用户阻塞等待访问该记录。悲观锁适合写入频繁的场景。

begin;

select * from goods where id = 1 for update;

update goods set stock = stock - 1 where id = 1;

commit;

 

② 乐观锁

每次获取商品时，不对该商品加锁。在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等，如果相等则进行更新，反之程序重新获取库存量，再次进行比较，直到两个库存量的数值相等才进行数据更新。乐观锁适合读取频繁的场景。

-- 不加锁获取 id=1 的商品对象
select * from goods where id = 1

begin;

-- 更新 stock 值，这里需要注意 where 条件 “stock = cur_stock”，只有程序中获取到的库存量与数据库中的库存量相等才执行更新
update goods set stock = stock - 1 where id = 1 and stock = cur_stock;

commit;

 

 

参考文档

---

https://zhuanlan.zhihu.com/p/143866444

https://tech.meituan.com/2014/08/20/innodb-lock.html

 

233