13.mysql optimistic Lock

MySQL optimistic lock summary and practice Original address: http://chenzhou123520.iteye.com/blog/1863407

The previous article, "MySQL pessimistic lock summary and practice" talked about the MySQL pessimistic lock, but pessimistic lock is not suitable for any scenario, it also has some shortcomings, because pessimistic lock in most cases rely on the lock mechanism of the database implementation, to ensure 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 pessimistic locking, we have an optimistic lock, see below for details:

Introduction to optimistic Locking:

Optimistic lock (optimistic Locking) relative pessimistic lock, optimistic locking hypothesis that the data generally do not cause conflict, so when the data is submitted to update the data will be formally conflicting or not detected, if a conflict is found, then let the return of the user error information, Let the user decide how to do it. So how do we achieve optimistic locking, there are generally the following 2 ways:

1. Using the data version recording mechanism is implemented, which is the most common way to implement optimistic locking. What is a data version? is to add a version identifier to the data, typically 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, the current version of the corresponding record of the database table is judged to be compared with the first fetch, if the current version number of the database table is equal to the first one taken out, it is updated, otherwise it is considered to be outdated data. Use one of the following diagrams to illustrate:

As shown, if the update operation is executed sequentially, the version of the data is incremented sequentially, and no conflict occurs. However, if there are different business operations to modify the same version of the data, then the first commit operation (Figure B) will update the data version to 2, when a after B commits the update found that the data version has been modified, then A's update operation will fail.

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. (The concept is relatively simple, not an example.) ）

13.mysql optimistic Lock