MyBatis How to use optimistic locks

Pessimistic lock problem:

Because pessimistic locks are implemented in most cases by the lock mechanism of the database , to guarantee the maximum degree of exclusivity of the operation. If the lock time is too long, other users can not access for a long time, affecting the program's concurrent access, but also the database performance cost impact is very large , especially for long transactions, such overhead is often unbearable. So, in contrast to the pessimistic lock, we have an optimistic lock.

The principle of optimistic locking is roughly the same, here I provide two kinds of ideas:

1. use the version record mechanism implementation 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, we determine that the current version of the database table corresponding to the record is compared to the first one, and if the current version number of the database table is equal to the first one, it is updated.

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.

Below is an example of MyBatis, the key code is as follows:

2. <update id="Updategoodsusecas" parametertype="Goods">
4. <! [cdata[
6. Set status=#{status},name=#{name},version=version+1
8. ]]>
10. </Update>
    

A typical example is a bank with two operators operating the same account at the same time.
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.

Operator a operates as follows:

 
Select id, balance, version from account where id="1";
Query results: id=1, balance=1000, version=1

Update the account
Set the balance = the balance + 100, version = version + 1
Where id = "1" and version = 1

Select id, balance, version from account where id="1";
Query results: id=1, balance=1100, version=2

Operator B operates as follows:

Select id, balance, version from account where id="1";

Query results: id=1, balance=1000, version=1

# operator, A modified success. The actual account balance = 1100, the version = 2, the operator will also B version number plus one (version = 2) attempts to submit data to the database (the balance = 950), but at the moment than the database record version found that when the operator B submitted data version number is 2, the database record the current version of 2, also does not meet the "submitted version records must be greater than the current version to update" optimistic locking strategy, therefore, the operator B submission was rejected.

Update the account

Set the balance = the balance - 50, version = version + 1

Where id = "1" and version = 1

Select id, balance, version from account where id="1";

Query results: id=1, balance=1100, version=2

An ORM framework, such as Hibernate, JPA, or implementation, that uses the version number to determine the value returned after the update

2. Public void Goodsdaotest () {
4. int goodsid = 1;
6. Query the product information according to the same ID, assign to 2 objects
8. This.goodsDao.getGoodsById (GOODSID);
10. This.goodsDao.getGoodsById (GOODSID);
12. //Print current product information
14. System.out.println (GOODS2);
16. //Update product information 1
18. 2); //Modify status to 2
20. int updateResult1 = This.goodsDao.updateGoodsUseCAS (GOODS1);
22. "Modify product Information 1" + (updateresult1==1?")  Success ":" Failure "));
24. //Update product Information 2
26. 2); //Modify status to 2
28. int updateResult2 = This.goodsDao.updateGoodsUseCAS (GOODS1);
30. "Modify product Information 2" + (updateresult2==1?")  Success ":" Failure "));
32. }
    

2. 1,goods Status:1,goods name: props, goods version:1
4. 1,goods Status:1,goods name: props, goods version:1
6. Modify product Information 2 failed
   

MyBatis How to use optimistic locks