How to implement optimistic locking in mysql

In mysql, you can use the data version Version recording mechanism to implement optimistic locking. Add a version field to the data table. The recorded version number is incremented by one for each operation to determine whether the version value is equal to the value just queried. , if equal, update is performed, if not equal, no update is performed.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to implement optimistic locking in mysql

Implementation method

1. Use the data version Version recording mechanism to implement it. This is the most commonly used implementation method of optimistic locking.

2. Data version, that is, adding a version identifier to the data. This is usually achieved by adding a numeric version field to the database table. When reading data, the value of the version field is read together. Each time the data is updated, the version value is increased by 1. When we submit an update, we compare the current version information of the corresponding record in the database table with the version value taken out for the first time. 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 expired data.

Example

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version}

Optimistic locking does not come with the database and needs to be implemented by ourselves.

Optimistic locking means that when operating the database (update operation), the idea is very optimistic that this operation will not cause conflicts. When operating the data, no other special processing (that is, no additional lock), and after updating, determine whether there is a conflict. The overall idea is CAS idea.

The usual implementation is as follows: when the data in the table is operated (updated), a version (version) field is first added to the data table, and each time the operation is performed, the version number of that record is incremented by 1. That is to say, first query the record and obtain the version field. If you want to operate (update) that record, first determine whether the value of the version at this moment is equal to the value of the version just queried. If they are equal, then explain During this period, if no other program operates on it, you can perform an update and add 1 to the value of the version field; if it is found during the update that the version value at this moment is not equal to the value of the version just obtained, it means that this period has been If other programs operate on it, the update operation will not be performed.

eg:

The order operation includes 3 steps:

1. Query the inventory information:

select (id,count,version) from t_goodsku where id=#{id}

2. Deduct 2 stocks:

Calculation in the program: count = count - 2;

3. Update inventory:

update t_goodsku
set count={count},version=version+1
where id=#{id} and version=#{version};

The version found in step 1 is actually a snapshot (read-commited and read -MVCC mechanism under the repeatable isolation mechanism), in this case, when going to update in step 3, acquire the lock, and use the version=#{version} in the where condition to judge, which actually takes the current version and the value in step 1 Compare the snapshot versions

If the comparison is successful, it means that this data has not been updated by other threads during this period, and the update is successful;

If the comparison fails, it means that this period of time If this piece of data has been updated, the update will fail and an error will be reported and rolled back or spun.

Of course, this is to simulate the optimistic locking scenario. In fact, updating the inventory can be achieved in one step:

1. Update the inventory:

update t_goodsku
set count=count -2
where id=#{id};

Recommended learning: mysql video tutorial

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