mySql数据库 forUpdate 实战总结

发布时间:2023-04-29 09:36:10

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

以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 更新数据时,比较版本或时间戳不一致重试。

      ps 图灵课堂老师从近一百套最新一线互联网公司面试题中精选而出,涵盖Java架构面试 所有技术栈,包括JVM,Mysql,并发,Spring,Redis,MQ,Zookeeper,Netty, Dubbo,Spring Boot,Spring Cloud,数据结构与算法,设计模式等相关技术领域的大 厂面试题及详解。 详情咨询客服获取全套面经试题。

      上一篇 oracle 查询结果的排序,ASC/DESC,升序,降序,多列排序,ORDER BY,一些练习题
      下一篇 YOLOv8模型调试记录

      文章素材均来源于网络,如有侵权,请联系管理员删除。

      标签: