Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to realize mysql optimistic lock". In daily operation, I believe many people have doubts about how to realize mysql optimistic lock. Xiaobian consulted various materials and sorted out simple and easy operation methods. I hope to help you answer the doubts about "how to realize mysql optimistic lock"! Next, please follow the small series to learn together!

In mysql, you can use the data version recording mechanism to implement optimistic locking, add a version field to the data table, add one to the version number of each record, determine whether the version value is equal to the value just queried, if equal, update, if not equal, do not update.

Operating environment of this tutorial: Windows 10 system, mysql version 8.0.22, Dell G3 computer.

How do I achieve mysql optimistic lock?

implementation method

1. It is implemented by the data version recording mechanism, which is the most common implementation method of optimistic lock.

Data version, that is, adding a version identifier to the data, is generally achieved by adding a version field of numeric type to the database table. When reading data, read the value of the version field together, and add 1 to this version value every time the data is updated. When we submit the update, compare the current version information of the corresponding record of the database table with the version value extracted for the first time. If the current version number of the database table is equal to the version value extracted for the first time, update it, otherwise it is considered as expired data.

examples

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

Optimistic locks are not built-in with the database and need to be implemented by ourselves.

Optimistic lock refers to the operation of the database (update operation), the idea is very optimistic, that this operation will not lead to conflict, in the operation of data, and do not carry out any other special processing (that is, do not lock), and after the update, and then to determine whether there is a conflict. The whole idea is CAS thought.

The usual implementation is this: when performing an operation (update) on the data in the table, first add a version field to the data table, and each time the operation is performed, increase the version number of that record by 1. That is to say, first query the record and obtain the version field. If you want to operate (update) that record, first judge whether the version value at this moment is equal to the version value just queried. If they are equal, it means that no other program operates on it during this period. Then you can perform the update and add 1 to the value of the version field. If you find that the version value at this time is not equal to the version value just obtained during the update, it means that other programs have operated on it during this period, and the update operation is not performed.

eg:

Order operation includes 3 steps:

1. Query inventory information:

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

2. Deduct 2 inventories:

The calculation in the program: count = count - 2;

3. Update inventory:

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

The version found in step 1 is actually a snapshot (MVCC mechanism under read-committed and read-repeatable isolation mechanisms). In this case, when updating in step 3, obtain the lock. Version=#{version} in the judgment in the where condition is actually a comparison between the current version and the snapshot version in step 1.

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

If the comparison fails, it means that the data has been updated during this period, then update fails, and the error is rolled back or spun.

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

Update inventory:

update t_goodskuset count=count -2where id=#{id}; At this point, the study of "how to implement mysql optimistic lock" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.