MySql for update 【排它锁】

最新推荐文章于 2024-09-02 10:19:10 发布

givmchickenblood

最新推荐文章于 2024-09-02 10:19:10 发布

阅读量912

点赞数 1

分类专栏： mysql

mysql 专栏收录该内容

37 篇文章 1 订阅

订阅专栏

https://blog.csdn.net/claram/article/details/54023216
https://blog.csdn.net/u011957758/article/details/75212222

行锁、表锁、页锁的比较

开销、加锁速度、死锁、粒度、并发性能

表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

for update的特点（排他锁）

for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
要测试for update的锁表情况，可以利用MySQL的Command Mode，开启二个视窗来做测试。
通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。
排他锁的申请前提：没有线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞。

for update用行锁还是表锁？

InnoDB默认是行级别的锁，当有明确指定的主键时候，是行级锁，否则是表级别。

例子: 假设表foods ，存在有id跟name、status三个字段，id是主键，status有索引。

例1: (where中明确指定主键，并且有此记录，行级锁)
SELECT * FROM foods WHERE id=1 FOR UPDATE;
SELECT * FROM foods WHERE id=1 and name=’咖啡色的羊驼’ FOR UPDATE;

例2: (明确指定主键/索引，若查无此记录，无锁)
SELECT * FROM foods WHERE id=-1 FOR UPDATE;

例3: (无主键/索引，表级锁)
SELECT * FROM foods WHERE name=’咖啡色的羊驼’ FOR UPDATE;

例4: (主键/索引不明确，表级锁)
SELECT * FROM foods WHERE id<>’3’ FOR UPDATE;
SELECT * FROM foods WHERE id LIKE ‘3’ FOR UPDATE;

for update的疑问点

当开启一个事务进行for update的时候，另一个事务也有for update的时候会一直等着，直到第一个事务结束吗？
答：会的。除非第一个事务commit或者rollback或者断开连接，第二个事务会立马拿到锁进行后面操作。
如果没查到记录会锁表吗？
答：会的。表级锁时，不管是否查询到记录，都会锁定表。

for update的使用场景

如果遇到存在高并发并且对于数据的准确性很有要求的场景，是需要了解和使用for update的。比如涉及到金钱、库存等。

假设有一张商品表 goods，它包含 id，商品名称，库存量三个字段，表结构如下：

CREATE TABLE `goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `stock` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`) USING HASH
) ENGINE=InnoDB

插入如下数据：

INSERT INTO `goods` VALUES ('1', 'prod11', '1000');
INSERT INTO `goods` VALUES ('2', 'prod12', '1000');
INSERT INTO `goods` VALUES ('3', 'prod13', '1000');
INSERT INTO `goods` VALUES ('4', 'prod14', '1000');
INSERT INTO `goods` VALUES ('5', 'prod15', '1000');
INSERT INTO `goods` VALUES ('6', 'prod16', '1000');
INSERT INTO `goods` VALUES ('7', 'prod17', '1000');
INSERT INTO `goods` VALUES ('8', 'prod18', '1000');
INSERT INTO `goods` VALUES ('9', 'prod19', '1000');

一、数据一致性

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

有两种解决方案：

悲观锁方案：每次获取商品时，对该商品加排他锁。也就是在用户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;

如果我们需要设计一个商城系统，该选择以上的哪种方案呢？

查询商品的频率比下单支付的频次高，基于以上我可能会优先考虑第二种方案（当然还有其他的方案，这里只考虑以上两种方案）。

总结

1、由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

2、当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

3、即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁（还有种情况就是，如果索引重复很多的话，选择性不高的时候，MySql也会使用表锁）。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

4、检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。