Use MySQL optimistic lock to solve concurrency problems

Case Description:

Two operators of the bank operate the same account simultaneously.
For example, A, B operators simultaneously read a balance of 1000 yuan account, a operator for the account increase of 100 yuan, b operator at the same time for the account deduction of 50 yuan, a first submitted, b after submission. The final actual account balance is 1000-50 = 950 yuan, but should be 1000+100-50=1050. This is a typical concurrency problem.

The optimistic locking mechanism solves this problem to some extent. Optimistic locking, mostly based on the data version (versions) recording mechanism implementation. What is a data version? is to add a version identity to the data, which is typically done by adding a "version" field to the database table in the version solution based on the database table.

When the data is read, the version number is read together, and then the version number is added one after the update. At this point, the version data of the submitted data is compared to the current version information of the database table corresponding to the record, and if the submitted version number is greater than the current version number of the database table, it is updated, otherwise it is considered to be outdated data.

For the example above to modify user account information, assume that there is a version field in the Account information table in the database, the current value is 1, and the Current Account balance field (balance) is $1000. Assuming that operator A is updated first, operator B is updated.
A, operator a reads it out at this time (version=1) and adds 100 (1000+100=1100) from its account balance.
b, during operator A's operation, operator B also reads this user information (version=1) and deducts 50 (1000-50=950) from its account balance.
C, operator a completed the modification work, the data version number plus one (version=2), together with the account increase balance (BALANCE=1100), submitted to the database update, at this time because the submission version of the data is larger than the current version of database records, the data is updated, The database record version is updated to 2.
D, operator B completed the operation, but also the version number plus one (version=2) attempt to submit data to the database (balance=950), but at this time than the database record version found that operator B submitted a version number of 2, the database records the current version is 2, does not meet the " The commit version must be larger than the current version of the record to perform the update "optimistic locking policy, so the submission of operator B is dismissed.
This avoids the possibility of operator B overwriting operator A's results with the results of old version=1-based data modifications.

Introduction to optimistic locking:

Optimistic lock (optimistic Locking) relative pessimistic lock, optimistic locking hypothesis that the data generally do not cause conflict, so when the data is submitted to update the data will be formally conflicting or not detected, if a conflict is found, then let the return of the user error information, Let the user decide how to do it. So how do we achieve optimistic locking, there are generally the following 2 ways:

1. Using the data version recording mechanism is implemented, which is the most common way to implement optimistic locking. What is a data version? is to add a version identifier to the data, typically by adding a "version" field of a numeric type to the database table. When the data is read, the value of the version field is read together, and the data is updated each time, adding one to this version value. When we submit an update, the current version of the corresponding record of the database table is judged to be compared with the first fetch, if the current version number of the database table is equal to the first one taken out, it is updated, otherwise it is considered to be outdated data. Use one of the following diagrams to illustrate:

As shown, if the update operation is executed sequentially, the version of the data is incremented sequentially, and no conflict occurs. However, if there are different business operations to modify the same version of the data, then the first commit operation (Figure B) will update the data version to 2, when a after B commits the update found that the data version has been modified, then A's update operation will fail.

2. The second way to achieve optimistic locking is similar to the first one, as well as adding a field to the table that requires optimistic lock control, the name does not matter, the field type uses the timestamp (timestamp), and the above version is like The time stamp of the data in the current database is also checked at the time of the update submission, and the timestamp is compared before the update, if the consistency is OK, otherwise it is the version conflict.

Examples of use : MySQL InnoDB as an example

Or take the previous example to lift: the commodity goods table has a field Status,status 1 for the product is not under the order, status is 2 for the product has been put orders, then we have to order a product to ensure that the product status is 1. Suppose the product has an ID of 1.

The order operation consists of 3 steps:

1. Check out the product information

Select (status,status,version) from T_goods where Id=#{id}

2. Generate orders based on product information

3. Change the item status to 2

Update T_goods

Set status=2,version=version+1

Where Id=#{id} and version=#{version};

So in order to use optimistic lock, we first modify the T_goods table, add a version field, the data default version value is 1.

T_goods table initial data is as follows:

Mysql> Select *  fromT_goods; +----+--------+------+---------+  |Id|Status|Name|Version|  +----+--------+------+---------+  |  1 |      1 |Props|       1 |  |  2 |      2 |Equipment|       2 |  +----+--------+------+---------+  2Rowsinch SetMySQL>  

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

Goods entity classes:

/*** Classname:goods <br/> * Function: Commodity entity. <br/> * date:2013-5-8 Morning 09:16:19 <br/> *@author[email protected]*/   Public classGoodsImplementsSerializable {/*** Serialversionuid: Serialization ID. */      Private Static Final LongSerialversionuid = 6803791908148880587L; /*** ID: primary key ID. */      Private intID; /*** Status: Commodity: 1 No orders, 2 orders. */      Private intstatus; /*** Name: Product name. */      PrivateString name; /*** Version: Product data version number. */      Private intversion; @Override PublicString toString () {return"Good ID:" +id+ ", goods Status:" +status+ ", Goods Name:" +name+ ", goods version:" +version; }        //Setter and Getter  }  

Goodsdao

/**   * Updategoodsusecas: Use CAS (Compare and set) to update product information.  <br/>  *@author  [email Protected]  @param  goods    the number    of rows affected by the @return commodity object *  /int updategoodsusecas (Goods Goods);  

Mapper.xml

<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 voidgoodsdaotest () {intGoodsid = 1; //Query the product information according to the same ID, assign to 2 objectsGoods GOODS1 = This. Goodsdao.getgoodsbyid (GOODSID); Goods Goods2= This. Goodsdao.getgoodsbyid (GOODSID); //Print Current Product informationSystem.out.println (GOODS1);            System.out.println (GOODS2); //Update product Information 1Goods1.setstatus (2);//Modify status to 2    intUPDATERESULT1 = This. Goodsdao.updategoodsusecas (GOODS1); System.out.println ("Modify product Information 1" + (updateresult1==1? ") Success ":" Failed ")); //Update product Information 2Goods1.setstatus (2);//Modify status to 2    intUPDATERESULT2 = This. Goodsdao.updategoodsusecas (GOODS2); System.out.println ("Modify product Information 2" + (updateresult2==1? ") Success ":" Failed ")); }  

Output Result:

Good id:1,goods status:1,goods Name: props, goods version:1  good id:1,goods status:1,goods name: props, goods version:1  Modify product Information 1 successfully  modified product information 2 failure  

Description

In the Goodsdaotest test method, we also identify the same version of the data, assign to different goods objects, and then modify the Good1 object and then perform the update operation, the execution succeeds. Then we modify the GOODS2, and the prompt operation fails when the update operation is performed. At this point the data in the T_goods table is as follows:

Mysql> Select *  fromT_goods; +----+--------+------+---------+  |Id|Status|Name|Version|  +----+--------+------+---------+  |  1 |      2 |Props|       2 |  |  2 |      2 |Equipment|       2 |  +----+--------+------+---------+  2Rowsinch SetMySQL>  

We can see that the data version with ID 1 has been modified to 2 on the first update. So when we update good2, the update where condition already does not match, so the updates will not succeed, the specific SQL is as follows:

Update t_goods    Set Status=2, version=version+1  where ID =  and version=#{version};  

So we can achieve an optimistic lock.

The above is my summary and practice of the optimistic lock MySQL, written relatively plain, there is no place to welcome the shooting bricks

Reference Source:

Https://www.cnblogs.com/linjiqin/p/5096206.html

http://blog.csdn.net/liyantianmin/article/details/50752102

Use MySQL optimistic lock to resolve concurrency problems