Shulou(Shulou.com)06/01 Report--

How is the optimistic lock in mysql implemented? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

Optimistic locking (Optimistic Locking) adopts a looser locking mechanism than pessimistic locking. Pessimistic locks are mostly realized by the locking mechanism of the database to ensure the maximum exclusivity of the operation. But it is followed by a large amount of database performance overhead, especially for long transactions, which is often unbearable. The optimistic locking mechanism solves this problem to some extent. Optimistic locks are mostly implemented based on data version (Version) recording mechanism. What is the data version? That is, to add a version identity to the data, which is generally achieved by adding a "version" field to the database table in the version solution based on the database table. When reading out the data, read out the version number together, and then add one to the version number when it is updated. At this point, the version data of the submitted data is compared with the current version information recorded in the corresponding database table, and if the submitted data version number is greater than the current version number of the database table, it will be updated, otherwise it is regarded as out-of-date data.

Advantages:

As can be seen from the above example, the optimistic locking mechanism avoids the database locking overhead in long transactions (operator An and operator B do not lock the database data during the operation), and greatly improves the overall performance of the system under large concurrency.

Disadvantages:

It should be noted that the optimistic locking mechanism is often based on the data storage logic in the system, so it also has some limitations, such as in the above example, because the optimistic locking mechanism is implemented in our system, the user balance update operation from the external system is not controlled by our system, so it may cause dirty data to be updated to the database. In the system design stage, we should fully consider the possibility of these situations and make corresponding adjustments (for example, the optimistic locking strategy should be implemented in the database stored procedure, and only the data update path based on this stored procedure should be opened to the outside world. instead of exposing database tables directly).

Generally speaking, there are two ways to achieve optimistic locking:

1. Use the data version (Version) recording mechanism, which is the most commonly used implementation of optimistic locks. What is the data version? That is, to add a version ID to the data, usually by adding a numeric type "version" field to the database table. When the data is read, the value of the version field is read out together, and each time the data is updated, the version value is added.

When we submit the update, we judge that the current version information recorded in the database table is compared with the version value taken out for the first time, and if the current version number of the database table is equal to the version value taken out for the first time, it will be updated, otherwise it is considered to be out of date. Use the following picture to illustrate:

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

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

Examples of use:

Take MySQL InnoDB as an example

Let's take the previous example: in the goods goods table, there is a field status,status of 1 that indicates that the order has not been placed, and a status of 2 means that the order has been placed, so when we place an order for an item, we must make sure that the status of the item is 1. Assume that the id of the product is 1.

Placing an order consists of 3 steps:

1. Find out the commodity information

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

2. Generate orders according to commodity information

3. Change the status of goods to 2.

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

So in order to use optimistic locks, we first modify the t_goods table by adding a version field, with the default version value of 1.

The initial data of the t _ goods table is as follows:

Mysql > select * from t_goods +-+ | id | status | name | version | +-+ | 1 | 1 | Prop | 1 | 2 | 2 | equipment | 2 | +-+-- -+-+ 2 rows in set mysql >

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

Goods entity class:

/ * ClassName: Goods * Function: commodity entity. * date: 2013-5-8 09:16:19 * @ author chenzhou1025@126.com * / public class Goods implements Serializable {/ * serialVersionUID: serialize ID. * / private static final long serialVersionUID = 6803791908148880587L; / * * id: primary key id. * / private int id; / * status: product status: 1 has not placed an order, 2 has already placed an order. * / private int status; / * name: trade name. * / private String name; / * version: commodity data version number. * / 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: use CAS (Compare and set) to update product information. * * @ author chenzhou1025@126.com * @ param goods goods object * @ rows affected by return * / int updateGoodsUseCAS (Goods goods)

Mapper.xml

GoodsDaoTest test class

@ Test public void goodsDaoTest () {int goodsId = 1; / query the product information according to the same id and assign it to two objects: Goods goods1 = this.goodsDao.getGoodsById (goodsId); Goods goods2 = this.goodsDao.getGoodsById (goodsId); / / print the current commodity information System.out.println (goods1); System.out.println (goods2) / / update commodity information 1 goods1.setStatus (2); / / modify status to 2 int updateResult1= this.goodsDao.updateGoodsUseCAS (goods1); System.out.println ("modify commodity information 1" + (updateResult1==1? " Success: "failure"); / / Update commodity information 2 goods1.setStatus (2); / / modify status to 2 int updateResult2= this.goodsDao.updateGoodsUseCAS (goods1); System.out.println ("modify commodity information 2" + (updateResult2==1? " Success ":" failure ");}

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 commodity information 1 successfully modify commodity information 2 failed

Description:

In the GoodsDaoTest test method, we find out the same version of data at the same time, assign it to different goods objects, and then modify the good1 object first and then perform the update operation successfully. Then we modify the goods2 to indicate that the operation failed when we perform the update operation. At this point, the data in the t _ goods table is as follows:

Mysql > select * from t_goods +-+ | id | status | name | version | +-+ | 1 | 2 | props | 2 | 2 | 2 | equipment | 2 | +-+-- -+-+ 2 rows in set mysql >

We can see that the data version with an id of 1 has been modified to 2 in the first update. Therefore, when we update good2, the update where conditions no longer match, 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 realize the optimistic lock.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.