MysqL_select for update锁详解

先来举一个在某些应用场景下会出现数据不一致的例子，当然存储引擎是InnoDB（至于为什么，后面再告诉你）。

电商平台常见的下单场景：

一般商品表(goods)有基本的四个字段，id（主键），goods_name （商品名），goods_status是商品状态（下架还是在售），goods_stock（库存）。

用户在购买商品id为1的商品，虽然一般展示层会筛选出在售状态的商品，在严谨的流程中我们还需要判断一下是否在售，商品的库存数量等条件是否正常，来避免出现问题。但并发情况下，可能会出现用户在购买将要付费的时候，商品管理人员抢先把商品下架，就会出现不一致了。

select goods_status,goods_stock from goods where id=1;  //查出商品状态和库存

inset into orders (goods_id,goods_count) values (1,3);      //如果库存和状态正常，把购买的商品和数量写入订单表

接下来还会有付费减库存等操作......

这里商品管理人员突然对商品的状态或者库存做了调整

update goods set goods_status = 0 where id =3;  //修改状态为例//

在上面的场景中，商品信息从查询出来到修改，中间有一个处理订单的过程。那么可以使用悲观锁来解决此问题。

使用悲观锁的原理就是，当我们在查询出goods信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为goods被锁定了，就不会出现有第三者来对其进行修改了。

要注意的是，使用悲观锁，必须关闭Mysql的自动提交机制：http://www.cnblogs.com/wt645631686/p/7986696.html

//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select goods_status from goods where id=1 for update;
//2.根据商品信息生成订单
insert into orders (goods_id,goods_count) values (3,5);
//3.修改商品status为2
update goods set status=2;
//4.提交事务
commit;/commit work;

拿上面的实例来说，当我执行select status from goods where id=3 for update;后。我在另外的事务中如果再次执行select status from goods where id=3 for update;则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，但是如果我是在第二个事务中执行select status from goods where id=3;则能正常查询出数据，不会受第一个事务的影响。

举例说明
数据库表goods，包括id,status,name三个字段，id为主键，数据库中记录如下;

mysql> select * from goods; 
+----+--------+------+ 
| id | status | name | 
+----+--------+------+ 
| 1  | 1 　　  | 道具 | 
| 2  | 1 　　  | 装备 | 
+----+--------+------+ 

注：为了测试数据库锁，我使用两个console来模拟不同的事务操作，分别用console1、console2来表示。

例1: (明确指定主键，并且有此数据，row lock)
console1：查询出结果，但是把该条数据锁定了

mysql> select * from goods where id=1 for update; 
+----+--------+------+ 
| id | status | name | 
+----+--------+------+ 
| 1  | 1 　　  | 道具 | 
+----+--------+------+ 

console2：查询被阻塞

mysql> select * from goods where id=1 for update; 

console2：如果console1长时间未提交，则会报错

mysql> select * from goods where id=1 for update; 
ERROR 1205 : Lock wait timeout exceeded; try restarting transaction 

例2: (明确指定主键，若查无此数据，无lock)
console1：查询结果为空

mysql> select * from goods where id=3 for update; 
Empty set 

console2：查询结果为空，查询无阻塞，说明console1没有对数据执行锁定

mysql> select * from goods where id=3 for update; 
Empty set 

例3: (无主键，table lock)

console1：查询name=道具 的数据，查询正常

mysql> select * from goods where name='道具' for update; 
+----+--------+------+ 
| id | status | name | 
+----+--------+------+ 
| 1  | 1 　　　| 道具 | 
+----+--------+------+ 

console2：查询name=装备 的数据，查询阻塞，说明console1把表给锁住了

mysql> select * from goods where name='装备' for update; 

console2：若console1长时间未提交，则查询返回为空

mysql> select * from goods where name='装备' for update; 
Query OK, -1 rows affected 

例4: (主键不明确，table lock)
console1：查询正常

mysql> begin; 
Query OK, 0 rows affected 
mysql> select * from goods where id>0 for update; 
+----+--------+------+ 
| id | status | name | 
+----+--------+------+ 
| 1  | 1 　　  | 道具 | 
| 2  | 1 　　  | 装备 | 
+----+--------+------+ 

console2：查询被阻塞，说明console1把表给锁住了

mysql> select * from t_goods where id>1 for update; 

例5: (主键不明确，table lock)

console1：

mysql> begin; 
Query OK, 0 rows affected 

mysql> select * from t_goods where id<>1 for update; 
+----+--------+------+ 
| id | status | name | 
+----+--------+------+ 
| 2  | 1      | 装备 | 
+----+--------+------+ 

console2：查询被阻塞，说明console1把表给锁住了

mysql> select * from t_goods where id<>2 for update; 

console1：提交事务

mysql> commit; 
Query OK, 0 rows affected 

console2：console1事务提交后，console2查询结果正常

mysql> select * from t_goods where id<>2 for update; 
+----+--------+------+ 
| id | status | name | 
+----+--------+------+ 
| 1  | 1      | 道具 | 
+----+--------+------+ 

 

以上就是关于数据库主键对MySQL锁级别的影响实例，需要注意的是，除了主键外，使用索引也会影响数据库的锁定级别
举例：
我们修改goods表，给status字段创建一个索引
修改id为2的数据的status为2，此时表中数据为：

mysql> select * from goods; 
+----+--------+------+ 
| id | status | name | 
+----+--------+------+ 
| 1  | 1      | 道具 | 
| 2  | 2      | 装备 | 
+----+--------+------+ 

例6: (明确指定索引，并且有此数据，row lock)

console1：

mysql> select * from goods where status=1 for update; 
+----+--------+------+ 
| id | status | name | 
+----+--------+------+ 
| 1  | 1      | 道具 | 
+----+--------+------+ 

console2：查询status=1的数据时阻塞，超时后返回为空，说明数据被console1锁定了

mysql> select * from goods where status=1 for update; 
Query OK, -1 rows affected 

console2：查询status=2的数据，能正常查询，说明console1只锁住了行，未锁表

mysql> select * from goods where status=2 for update; 
+----+--------+------+ 
| id | status | name | 
+----+--------+------+ 
| 2  | 2      | 装备 | 
+----+--------+------+ 

例7: (明确指定索引，若查无此数据，无lock)
console1：查询status=3的数据，返回空数据

mysql> select * from t_goods where status=3 for update; 
Empty set 

console2：查询status=3的数据，返回空数据
Sql代码 收藏代码

mysql> select * from t_goods where status=3 for update; 
Empty set

 完毕。。。