mySql数据库 forUpdate 实战总结

悲观锁，就像它的名字一样，是指对数据被外界修改(包括系统当前的其他事务，以及外部系统的事务处理)保守的态度。因此，在整个数据处理过程中，数据被锁定。悲观锁的实现往往取决于数据库提供的锁机制（只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使锁机制在系统中实现，外部系统也不能修改数据）。

以MySQLInoDB为例

商品goods表中有一个字段status，status是1，代表商品没有下单，status是2，代表商品已经下单，所以我们必须确保status是1。假设商品的ID是1。

如果不使用锁，操作方法如下：

//1.查询商品信息selectt status from t_goods where id=1;//2.根据商品信息生成订单insertt订单 into t_orders (id,goods_id) values (null,1);//3.将商品status修改为2updatete t_goods set status=2;

在高并发访问的情况下，上述场景很可能会出现问题。

前面提到过，只有当goods status只有在1时才能订购商品。在上述第一步操作中，查询的商品status为1。然而，当我们执行第三步Update操作时，其他人可能会首先订购商品并将goods status修改为2，但我们不知道数据已经修改，这可能会导致同一产品下订单两次，导致数据不一致。所以这种方法是不安全的。

使用悲观锁来实现：

在上述场景中，商品信息从查询到修改，有一个处理订单的过程。使用悲观锁的原理是，当我们查询谷歌信息时，我们锁定当前的数据，直到我们修改它。所以在这个过程中，由于谷歌被锁定，没有第三方会修改它。

注意:要使用悲观锁，必须关闭mysql数据库的自动提交属性，因为mysql默认使用autocommit模式，也就是说，当您执行更新操作时，mysql会立即提交结果。

在非autocommit模式下，我们可以使用命令设置MySQL：

set autocommit=0;

设置autocommit后，我们可以执行我们的正常业务。具体如下:

//0.开始事务begin;/begin work;/start transaction; (三者选一)//1.查询商品信息selecttt status from t_goods where id=1 for update;//2.根据商品信息生成订单insertt订单 into t_orders (id,goods_id) values (null,1);//3.将商品status修改为2updatete t_goods set status=2;//4.提交committ提交事务;/commit work;

注:以上begin/commit是事务的开始和结束，因为在前一步，我们关闭了mysql的autocommit，所以我们需要手动控制事务的提交，这里就不细说了。

在上述第一步中，我们进行了查询操作：select status from t_goods where id=1 for update;

与普通查询不同，我们使用select...for update通过数据库实现悲观锁。此时，在t_gods表中，id为1 我们锁定了这些数据，其他事务必须在事务提交后才能执行。这样，我们就可以确保当前的数据不会被其他事务修改。

注:需要注意的是，事务中只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一个数据将在其他事务结束后执行。一般来说，SELECT ... 不受此影响。以上面的例子为例，当我执行select时 status from t_goods where id=1 for update;后。如果我在其他事务中再次执行selecttt status from t_goods where id=1 for update;第二个事务将始终等待第一个事务的提交，此时第二个查询处于阻塞状态，但如果我在第二件事上执行selectt， status from t_goods where id=1;数据可以在不受第一件事影响的情况下正常查询。

补充：MySQL select…for updateRow Lock与Table Lock

我们上面提到了使用select...for update会锁定数据，但我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地面指定主键，MySQL Row将执行 lock (只锁定所选数据) ，否则MySQL 将执行Table Lock (锁定整个数据表)。

举例说明：

T_数据库表T_数据库表goods，包括id,status,name三个字段，id为主键，数据库记录如下；

1. mysql> select * from2. +----+--------+------+3. | id | status | name4. +----+--------+------+5. |  1 |      1 | 道具 |  6. |  2 |      1 | 装备 |  7. +----+--------+------+8. 2 rows in set9.   10. mysql>

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

例1: (明确指定主键，并且有此数据，row lock)

console1：查询结果，但锁定了数据

1. mysql> select * from t_goods where id=1 for update;  2. +----+--------+------+3. | id | status | name4. +----+--------+------+5. |  1 |      1 | 道具 |  6. +----+--------+------+7. 1 row in set8.   9. mysql>

console2：查询被阻塞

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

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

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

例2: (明确指定主键，如果没有此数据，则没有lock)

console1：查询结果为空

1. mysql> select * from t_goods where id=3 for update;  2. Empty set

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

1. mysql> select * from t_goods where id=3 for update;  2. Empty set

例3: (无主键，table lock)

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

1. mysql> select * from t_goods where name='道具' for update;  2. +----+--------+------+3. | id | status | name4. +----+--------+------+5. |  1 |      1 | 道具 |  6. +----+--------+------+7. 1 row in set8.   9. mysql>

console2：查询name=设备 数据，查询堵塞，说明console1锁定了表格

1. mysql> select * from t_goods where name='装备' for update;

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

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

例4: (主键不清楚，table lock)

console1：查询正常

1. mysql> begin;  2. Query OK, 0 rows3.   4. mysql> select * from t_goods where id>0 for update;  5. +----+--------+------+6. | id | status | name7. +----+--------+------+8. |  1 |      1 | 道具 |  9. |  2 |      1 | 装备 |  10. +----+--------+------+11. 2 rows in set12.   13. mysql>

console2：查询被堵塞，说明console1锁定了表格

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

例5: (主键不清楚，table lock)

console1：

1. mysql> begin;  2. Query OK, 0 rows3.   4. mysql> select * from t_goods where id<>1 for update;  5. +----+--------+------+6. | id | status | name7. +----+--------+------+8. |  2 |      1 | 装备 |  9. +----+--------+------+10. 1 row in set11.   12. mysql>

console2：查询被堵塞，说明console1锁定了表格

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

console1：提交事务

1. mysql> commit;  2. Query OK, 0 rows

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

1. mysql> select * from t_goods where id<>2 for update;  2. +----+--------+------+3. | id | status | name4. +----+--------+------+5. |  1 |      1 | 道具 |  6. +----+--------+------+7. 1 row in set8.   9. mysql>

以上是关于数据库主键对MySQL锁级别的影响。需要注意的是，除了主键，索引的使用也会影响数据库的锁级

举例：

我们修改t_gods表，为status字段创建索引

修改id为2的status为2，此时表中的数据为：

1. mysql> select * from2. +----+--------+------+3. | id | status | name4. +----+--------+------+5. |  1 |      1 | 道具 |  6. |  2 |      2 | 装备 |  7. +----+--------+------+8. 2 rows in set9.   10. mysql>

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

console1：

1. mysql> select * from t_goods where status=1 for update;  2. +----+--------+------+3. | id | status | name4. +----+--------+------+5. |  1 |      1 | 道具 |  6. +----+--------+------+7. 1 row in set8.   9. mysql>

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

1. mysql> select * from t_goods where status=1 for update;  2. Query OK, -1 rows

console2：查询status=2的数据，可以正常查询，说明console1只锁了行，没有锁表

1. mysql> select * from t_goods where status=2 for update;  2. +----+--------+------+3. | id | status | name4. +----+--------+------+5. |  2 |      2 | 装备 |  6. +----+--------+------+7. 1 row in set8.   9. mysql>

例7: (明确指定索引，如果没有此数据，则没有lock)

console1：查询status=3的数据，返回空数据

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

console2：查询status=3的数据，返回空数据

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

说明:乐观锁是这里更好的解决方案。例如:使用version、timestemp 更新数据时，比较版本或时间戳不一致重试。