MySQL 中的乐观锁和悲观锁

MySQL Lock 面试 大约 4012 字

创建测试表

创建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

乐观锁

使用versionupdate_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执行更新amountamount_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缩写。

阅读 258 · 发布于 2021-03-11

————        END        ————

扫描下方二维码关注公众号和小程序↓↓↓

扫描二维码关注我
昵称:
随便看看 换一批