MySQL 中的乐观锁和悲观锁
MySQL 锁 面试 About 4,012 words创建测试表
创建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
缩写。
Views: 2,658 · Posted: 2021-03-11
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...