MySQL Select for update

Select for update定义

for update 是一种行级锁，又叫排它锁。一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行。

如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁。即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。

行锁永远是独占方式锁。只有当出现如下之一的条件，才会释放共享更新锁：

执行提交（COMMIT）语句
退出数据库（LOG　OFF）
程序停止运行

InnoDB共享锁与排它锁

InnoDB行锁类型

共享锁（S）： 允许一个事务去读取一行，阻止其他事务获取相同数据集的排他锁。

排他锁（X）： 允许获得排他锁的事务更新数据，组织其他事务获取相同数据集的共享锁和排他锁。

加锁方式

共享锁（S）：

select * from table_name where ... lock in share mode;

排他锁（S）：

select * from table_name where ... for update;

for update 是在数据库中上锁用的，可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新。

for update的使用场景

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

比如涉及到金钱、库存等。一般这些操作都是很长一串并且是开启事务的。如果库存刚开始读的时候是 1，而立马另一个进程进行了 update 将库存更新为 0 了，而事务还没有结束，会将错的数据一直执行下去，就会有问题。所以需要 for upate 进行数据加锁防止高并发时候数据出错。

记住一个原则：一锁二判三更新。

for update悲观锁

悲观锁： 总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它解锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。就像 for update，再比如 Java 里面的同步原语 synchronized 关键字的实现也是悲观锁。

乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于 write_condition 机制，其实都是提供的乐观锁。

场景分析

假设有一张商品表 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;

如果我们需要设计一个商城系统，该选择以上的哪种方案呢？查询商品的频率比下单支付的频次高，基于以上我可能会优先考虑第二种方案（当然还有其他的方案，这里只考虑以上两种方案）。

行锁与表锁

只根据主键进行查询，并且查询到数据，主键字段产生行锁
```
begin;
select * from goods where id = 1 for update;
commit;
```
只根据主键进行查询，没有查询到数据，不产生锁
```
begin;
select * from goods where id = 1 for update;
commit;
```
根据主键、非主键含索引（name）进行查询，并且查询到数据，主键字段产生行锁，name 字段产生行锁
```
begin;
select * from goods where id = 1 and name='prod11' for update;
commit;
```
根据主键、非主键含索引（name）进行查询，没有查询到数据，不产生锁
```
begin;
select * from goods where id = 1 and name='prod12' for update;
commit;
```
根据主键、非主键不含索引（name）进行查询，并且查询到数据，如果其他线程按主键字段进行再次查询，则主键字段产生行锁，如果其他线程按非主键不含索引字段进行查询，则非主键不含索引字段产生表锁，如果其他线程按非主键含索引字段进行查询，则非主键含索引字段产生行锁，如果索引值是枚举类型，mysql 也会进行表锁
```
begin;
select * from goods where id = 1 and name='prod11' for update;
commit;
```
根据主键、非主键不含索引（name）进行查询，没有查询到数据，不产生锁
```
begin;
select * from goods where id = 1 and name='prod12' for update;
commit;
```
根据非主键含索引（name）进行查询，并且查询到数据，name 字段产生行锁
```
begin;
select * from goods where name='prod11' for update;
commit;
```
根据非主键含索引（name）进行查询，没有查询到数据，不产生锁
```
begin;
select * from goods where name='prod11' for update;
commit;
```
根据非主键不含索引（name）进行查询，并且查询到数据，name 字段产生表锁
```
begin;
select * from goods where name='prod11' for update;
commit;
```
根据非主键不含索引（name）进行查询，没有查询到数据，name 字段产生表锁
```
begin;
select * from goods where name='prod11' for update;
commit;
```
只根据主键进行查询，查询条件为不等于，并且查询到数据，主键字段产生表锁
```
begin;
select * from goods where id <> 1 for update;
commit;
```
只根据主键进行查询，查询条件为不等于，没有查询到数据，主键字段产生表锁
```
begin;
select * from goods where id <> 1 for update;
commit;
```
只根据主键进行查询，查询条件为 like，并且查询到数据，主键字段产生表锁
```
begin;
select * from goods where id like '1' for update;
commit;
```
只根据主键进行查询，查询条件为 like，没有查询到数据，主键字段产生表锁
```
begin;
select * from goods where id like '1' for update;
commit;
```

总结

InnoDB 行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。
检索值的数据类型与索引字段不同，虽然 MySQL 能够进行数据类型转换，但却不会使用索引，从而导致 InnoDB 使用表锁。通过用 explain 检查两条 SQL 的执行计划，我们可以清楚地看到了这一点。