MySQL 乐观锁与悲观锁介绍及代码示例

在数据库并发控制中，乐观锁和悲观锁是两种重要的机制，用于处理多用户同时访问和修改数据时可能出现的问题。MySQL作为广泛使用的关系型数据库，也支持这两种锁机制。

悲观锁 悲观锁的设计理念是，总是假设最坏的情况，即认为在数据处理过程中，很可能会有其他事务同时对数据进行修改。在获取数据时，就会立即锁定数据，防止其他事务对其进行修改。在MySQL中，悲观锁主要通过 SELECT...FOR UPDATE 语句来实现。

例如，假设有一个商品库存表 product_stock，包含 product_id 和 stock 字段。要使用悲观锁更新库存：

START TRANSACTION;
SELECT stock FROM product_stock WHERE product_id = 1 FOR UPDATE;
-- 假设查询到的库存为 $stock
SET @new_stock = $stock - 1;
UPDATE product_stock SET stock = @new_stock WHERE product_id = 1;
COMMIT;

在这个例子中，SELECT...FOR UPDATE 语句会锁定 product_id 为1的记录，直到事务结束。其他事务如果要对这条记录进行修改，必须等待当前事务提交或回滚。

乐观锁 乐观锁则持乐观态度，它假设在大多数情况下，数据在被读取和修改的过程中不会被其他事务修改。乐观锁通过版本号（version）或时间戳（timestamp）来实现。

以版本号为例，在 product_stock 表中添加一个 version 字段。当读取数据时，同时读取 version 值。在更新数据时，检查 version 是否与读取时一致，如果一致则进行更新，并将 version 加1。

-- 读取数据
SELECT stock, version FROM product_stock WHERE product_id = 1;
-- 假设读取到的库存为 $stock，版本号为 $version
SET @new_stock = $stock - 1;
SET @new_version = $version + 1;
-- 更新数据，条件中检查版本号
UPDATE product_stock 
SET stock = @new_stock, version = @new_version 
WHERE product_id = 1 AND version = $version;

如果在读取和更新之间，其他事务修改了数据，version 会发生变化，更新语句将不会影响任何记录，此时可以根据返回结果决定是否重新读取和尝试更新。

乐观锁和悲观锁各有优缺点和适用场景。悲观锁能确保数据的一致性，但在高并发场景下，可能会导致性能下降；乐观锁则在并发性能上有优势，但可能会出现更新失败的情况。开发者需要根据具体的业务需求来选择合适的锁机制。