The practice of "turn" MySQL optimistic lock in distributed scenario

Background

In the case of e-commerce shopping, when we click on the shopping, the backend service will reduce the inventory operation of the corresponding goods. In the case of single-instance deployments, we can simply use the lock mechanism provided by the JVM to lock down inventory operations and prevent multiple users from clicking on the inventory inconsistency caused by the purchase.

In practice, however, in order to improve the usability of the system, we usually carry out multi-instance deployment. While different instances have their own JVM, user requests that are load balanced to different instances cannot be mutually exclusive through the JVM's lock mechanism.

Therefore, in order to ensure the consistency of data in distributed scenarios, we generally have two practical ways: first, using the MySQL optimistic lock, second, the use of distributed locks.

This article mainly introduces the MySQL optimistic lock, about the distributed lock I introduced in the next blog post.

Introduction to Optimistic locking

Optimistic locking (optimistic Locking) corresponds to pessimistic locking, and when we use optimistic locking we assume that the data will not conflict in most cases, so it is only when the data is submitted that it is checked for conflicts. If a data conflict occurs, an error message is returned and processed accordingly.

So how do we achieve optimistic locking? This is typically achieved by using the version number mechanism, which is the most common implementation of optimistic locking.

Version number

What is the version number? The version number is to add a version flag for the data, and usually I add a "version" field of type int to the table in the database. When we read the data out, we read the version field together, and when the data is updated, it adds 1 to the version value of the data. When we submit the data, we will determine whether the current version number in the database and the first time the version number is consistent, if the two version number is equal, then update, otherwise think the data expires, return an error message. We can use it to illustrate the problem:

, if the update operation is executed in the same order as in the first diagram, the version number of the data is incremented sequentially and no conflict occurs. However, as in the second figure, different user actions read to the same version of the data, and then update the data separately, the user's update operation can be successful, and when User B updates, the version number of the data has changed, so the update fails.

Code practices

When we reduce the inventory of a product, the specific operation is divided into the following 3 steps:

1. Find out the specific information of the product

2. Generate corresponding update objects based on the specific amount of inventory reduction

3. Modify the inventory quantity for a product

In order to use the optimistic lock of MySQL, we need to add a version number field to the Product table goods, the table structure is as follows:

CREATE TABLE ' goods ' (  ' id ' int (one) not null auto_increment,  ' name ' varchar (+) ' NOT null ' DEFAULT ',  ' remainin G_number ' int (one) not NULL,  ' version ' int (one) not NULL,  PRIMARY KEY (' id ')) engine=innodb auto_increment=2 Defaul T Charset=utf8;

              

Java code for the Goods class:

* Product Name */    private String name;    /**     * Inventory quantity */    private Integer remainingnumber;    /**     * Version number */    private Integer version;    @Override public    String toString () {        return "goods{" +                "id=" + ID +                ", name= ' + name + ' \ ' +                ", Remainingnumber= "+ Remainingnumber +                ", version= "+ version +                '} ';    }}

Goodsmapper.java:

Public interface Goodsmapper {    Integer Updategoodcas (Goods good);}

Goodsmapper.xml as follows:

<update id= "Updategoodcas" parametertype= "Com.ztl.domain.Goods" >        <![ cdata[          Update goods          set ' name ' =#{name},           Remaining_number=#{remainingnumber},           version=version+1          where Id=#{id} and Version=#{version}        ]] >    </update>

The Goodsservice.java interface is as follows:

Public interface Goodsservice {    @Transactional    Boolean updategoodcas (integer ID, integer decreasenum);}

The Goodsserviceimpl.java class is as follows:

@Servicepublic class Goodsserviceimpl implements Goodsservice {    @Autowired    private goodsmapper goodsmapper;    @Override public    Boolean updategoodcas (integer ID, integer decreasenum) {        Goods good = Goodsmapper.selectgoodbyid (ID);        System.out.println (good);        try {            thread.sleep (+);     To simulate concurrency, different users read to the same data version        } catch (Interruptedexception e) {            e.printstacktrace ();        }        Good.setremainingnumber (Good.getremainingnumber ()-decreasenum);        int result = Goodsmapper.updategoodcas (good);        System.out.println (Result = = 1?) "Success": "Fail");        return result = = 1;    }}

Goodsserviceimpltest.java Test class

@RunWith (springrunner.class) @SpringBootTestpublic class Goodsserviceimpltest {    @Autowired    private Goodsservice Goodsservice;    @Test public    void Updategoodcastest () {        final Integer id = 1;        Thread thread = new Thread (new Runnable () {            @Override public            void Run () {                Goodsservice.updategoodcas (ID, 1);    //USER 1 Request            }        );        Thread.Start ();        Goodsservice.updategoodcas (ID, 2);            User 2 request        System.out.println (Goodsservice.selectgoodbyid (ID));}    }

Output Result:

Goods{id=1, name= ' mobile ', remainingnumber=10, version=9}goods{id=1, name= ' mobile ', remainingnumber=10, version=9} successfailgoods{id=1, name= ' cell phone ', remainingnumber=8, version=10}

Code Description:

In the test method of Updategoodcastest (), user 1 and User 2 simultaneously detect the same version information of the id=1 product, and then respectively inventory the goods by 1 and minus 2 operations. From the output of the results can be seen that the user 2 of the reduced inventory operation was successful, the product inventory success minus 2, while the user 1 commits the reduced inventory operation, the data version number has changed, so the data changes failed.

In this way, we can guarantee the consistency of data in distributed scenarios through the optimistic locking mechanism of MySQL.

Above.

Original link

11 ...

The practice of "turn" MySQL optimistic lock in distributed scenario