MySQL 中的乐观锁和悲观锁

创建测试表

创建product表：

CREATE TABLE `product` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `amount` int(10) NOT NULL,
  `amount_cc` bigint(20) unsigned NOT NULL DEFAULT '0',
  `update_ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `version` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入测试数据：

mysql> select * from product;
+----+-----------+--------+-----------+---------------------+---------+
| id | name      | amount | amount_cc | update_ts           | version |
+----+-----------+--------+-----------+---------------------+---------+
|  1 | 自行车    |    100 |         0 | 2021-03-11 09:31:12 |       0 |
|  2 | 小汽车    |    200 |         0 | 2021-03-11 09:30:02 |       0 |
+----+-----------+--------+-----------+---------------------+---------+
2 rows in set (0.00 sec)

悲观锁

客户端A注意开启事务，默认事务是自动提交的。不开事务则for update语句执行完就提交了，客户端B就可以执行更新。

select * from product where id=2 for update;

输出：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from product where id=2 for update;
+----+-----------+--------+-----------+---------------------+---------+
| id | name      | amount | amount_cc | update_ts           | version |
+----+-----------+--------+-----------+---------------------+---------+
|  2 | 小汽车    |    200 |         0 | 2021-03-10 16:40:14 |       0 |
+----+-----------+--------+-----------+---------------------+---------+
1 row in set (0.00 sec)

客户端B进行更新，会阻塞等待for update锁住的客户端释放锁，阻塞一定时候后抛出超时异常：

mysql> update product set amount=amount-1 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

乐观锁

使用version、update_ts等字段对比控制，考虑到version等字段锁的粒度较大，如更新amount字段时，其他客户端需要更新name字段，失败率会非常高。可以添加一个频繁更新的字段相关联的并发控制字段amount_cc。

客户端A，先select查询到当前的amount_cc，在执行update语句时amount_cc自增1，且where条件中添加一开始select出来的amount_cc的值作为联合条件。当amount_cc被其他客户端修改了不再等于之前select出来的值时，返回更新失败。

start transaction;
select amount_cc from product where id=2;
update product set amount=amount-1, amount_cc=amount_cc+1 where id=2 and amount_cc=0;
commit;

开启事务后，查询得到当前amount_cc等于0，而在执行update前，被客户端B更新了amount_cc的值，则update语句将不做任何修改。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from product where id = 2;
+----+-----------+--------+-----------+---------------------+---------+
| id | name      | amount | amount_cc | update_ts           | version |
+----+-----------+--------+-----------+---------------------+---------+
|  2 | 小汽车    |    200 |         0 | 2021-03-11 09:30:02 |       0 |
+----+-----------+--------+-----------+---------------------+---------+
1 row in set (0.00 sec)

mysql> update product set amount=amount-1, amount_cc=amount_cc+1 where id=2 and amount_cc=0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from product where id=2;
+----+-----------+--------+-----------+---------------------+---------+
| id | name      | amount | amount_cc | update_ts           | version |
+----+-----------+--------+-----------+---------------------+---------+
|  2 | 小汽车    |    199 |         1 | 2021-03-11 10:04:24 |       0 |
+----+-----------+--------+-----------+---------------------+---------+
1 row in set (0.00 sec)

客户端B执行更新amount和amount_cc字段，并且添加额外amount_cc条件（假设已经select出来且值等于0）。

mysql> update product set amount=amount-1, amount_cc=amount_cc+1 where id=2 and amount_cc=0;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from product where id=2;
+----+-----------+--------+-----------+---------------------+---------+
| id | name      | amount | amount_cc | update_ts           | version |
+----+-----------+--------+-----------+---------------------+---------+
|  2 | 小汽车    |    199 |         1 | 2021-03-11 10:04:24 |       0 |
+----+-----------+--------+-----------+---------------------+---------+
1 row in set (0.00 sec)

备注

后缀cc取自：Concurrency Control缩写。