What is mysql optimistic locking?

mysql optimistic locking is relative to pessimistic locking. Optimistic locking assumes that the data will not cause conflicts under normal circumstances, so when the data is submitted for update, the conflict of the data will be officially detected. If a conflict is found, error information is returned to the user and the user can decide what to do.

mysql optimistic locking is (Optimistic Locking) Compared with pessimistic locking, optimistic locking assumes that data will generally not cause conflicts , so when the data is submitted for update, the conflict of the data will be officially detected. If a conflict is found, an error message will be returned to the user, allowing the user to decide what to do.

So how do we implement optimistic locking? Generally speaking, there are two ways:

1. Use the data version (Version) recording mechanism to achieve , which is optimistic One of the most commonly used implementation methods of locks. What is a data version? That is to add a version identifier to the data, usually by adding a numeric "version" field to the database table. When reading data, read the value of the version field together. Every time the data is updated, the version value is incremented by one.

When we submit an update, we judge the current version information of the corresponding record in the database table and compare it with the version value taken out for the first time. If the current version number of the database table is compared with the version value taken out for the first time If equal, it will be updated, otherwise it will be considered as expired data. Use the following picture to illustrate:

As shown in the above picture, if the update operations are performed sequentially, the data versions (versions) will increase sequentially without conflict. However, if different business operations modify the same version of data, then the operation submitted first (B in the figure) will update the data version to 2. When A submits the update after B, it is found that the data version has been modified. , then the update operation of A will fail.

2. The second implementation method of optimistic locking is similar to the first one. It also adds a field to the table that requires optimistic locking control. , the name does not matter, the field type uses timestamp, which is similar to the version above. When the update is submitted, the timestamp of the data in the current database is checked and compared with the timestamp obtained before the update. If they are consistent Then OK, otherwise it is a version conflict.

Related learning recommendations: mysql video tutorial

##Usage examples:

With

MySQL InnoDBAs an example

Let’s take the previous example: there is a field status in the goods table. A status of 1 means that the product has not been ordered, and a status of 2 means that the product has been ordered. Then when we place an order for a certain product, we must ensure that the status of the product is 1. Assume that the product id is 1.

The order operation includes 3 steps:

1. Query the product information

select (status,status,version) from t_goods where id=#{id}

2. Generate an order based on the product information

3. Modify the product status to 2

update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

In order to use optimistic locking, we first modify the t_goods table and add a version field. The default version value of the data is 1.

t_goodsThe initial data of the table is as follows:

mysql> select * from t_goods;  
+----+--------+------+---------+  
| id | status | name | version |  
+----+--------+------+---------+  
|  1 |      1 | 道具 |       1 |  
|  2 |      2 | 装备 |       2 |  
+----+--------+------+---------+  
2 rows in set  
  
mysql>

For the implementation of optimistic locking, I use MyBatis for practice, as follows:

Goods entity class:

/** 
 * ClassName: Goods <br/> 
 * Function: 商品实体. <br/> 
 * date: 2013-5-8 上午09:16:19 <br/> 
 * @author chenzhou1025@126.com 
 */  
public class Goods implements Serializable {  
  
    /** 
     * serialVersionUID:序列化ID. 
     */  
    private static final long serialVersionUID = 6803791908148880587L;  
      
    /** 
     * id:主键id. 
     */  
    private int id;  
      
    /** 
     * status:商品状态：1未下单、2已下单. 
     */  
    private int status;  
      
    /** 
     * name:商品名称. 
     */  
    private String name;  
      
    /** 
     * version:商品数据版本号. 
     */  
    private int version;  
      
    @Override  
    public String toString(){  
        return "good id:"+id+",goods status:"+status+",goods name:"+name+",goods version:"+version;  
    }  
  
    //setter and getter  
  
}

GoodsDao

/** 
 * updateGoodsUseCAS:使用CAS(Compare and set)更新商品信息. <br/> 
 * 
 * @author chenzhou1025@126.com 
 * @param goods 商品对象 
 * @return 影响的行数 
 */  
int updateGoodsUseCAS(Goods goods);

mapper.xml

<update id="updateGoodsUseCAS" parameterType="Goods">  
    <![CDATA[ 
        update t_goods 
        set status=#{status},name=#{name},version=version+1 
        where id=#{id} and version=#{version} 
    ]]>  
</update>

GoodsDaoTest test class

@Test  
public void goodsDaoTest(){  
    int goodsId = 1;  
    //根据相同的id查询出商品信息，赋给2个对象  
    Goods goods1 = this.goodsDao.getGoodsById(goodsId);  
    Goods goods2 = this.goodsDao.getGoodsById(goodsId);  
      
    //打印当前商品信息  
    System.out.println(goods1);  
    System.out.println(goods2);  
      
    //更新商品信息1  
    goods1.setStatus(2);//修改status为2  
    int updateResult1 = this.goodsDao.updateGoodsUseCAS(goods1);  
    System.out.println("修改商品信息1"+(updateResult1==1?"成功":"失败"));  
      
    //更新商品信息2  
    goods1.setStatus(2);//修改status为2  
    int updateResult2 = this.goodsDao.updateGoodsUseCAS(goods1);  
    System.out.println("修改商品信息2"+(updateResult2==1?"成功":"失败"));  
}

Output result:

good id:1,goods status:1,goods name:道具,goods version:1  
good id:1,goods status:1,goods name:道具,goods version:1  
修改商品信息1成功  
修改商品信息2失败

Explanation:

In the

GoodsDaoTest test method, we also found out The same version of data is assigned to different goods objects, and then the good1 object is modified first and then the update operation is performed, and the execution is successful. Then we modify goods2, and when performing the update operation, it prompts that the operation failed. At this time, the data in the t_goods table is as follows:

mysql> select * from t_goods;  
+----+--------+------+---------+  
| id | status | name | version |  
+----+--------+------+---------+  
|  1 |      2 | 道具 |       2 |  
|  2 |      2 | 装备 |       2 |  
+----+--------+------+---------+  
2 rows in set  
  
mysql>

We can see that the data version with id 1 has been modified to 2 during the first update. So when we update good2, the update where condition no longer matches, so the update will not succeed. The specific SQL is as follows:

update t_goods   
set status=2,version=version+1  
where id=#{id} and version=#{version};

In this way, we have achieved optimistic locking.

The above is the detailed content of What is mysql optimistic locking?. For more information, please follow other related articles on the PHP Chinese website!