PHP Optimistic Locking principle detailed

Why do I need a lock (concurrency control)?

In a multiuser environment, multiple users may update the same record at the same time, which can create a conflict. This is the famous concurrency problem.
Typical conflicts are:
1. Missing updates: One transaction update overwrites the update result of other transactions, which is called update loss. For example: User A changes the value from 6 to 2, and User B changes the value from 2 to 6, then user a loses his update.
2. Dirty read: Dirty reads occur when a transaction reads a record of another half-completed transaction. For example, the user A, B sees a value of 6, the value is changed to 2, and the value read by user A is still 6.

In order to solve the problems caused by these concurrency. We need to introduce concurrency control mechanisms.

Second, concurrency control mechanism

Lock, which locks the target data we have selected so that it cannot be modified by other programs.

1. Pessimistic lock: Refers to the data by the outside (including the system's current other transactions, as well as the transaction from the external system) is conservative attitude, so throughout the process of data processing, to lock the state
2. Optimistic lock: Assume that there will be no concurrency conflicts and only check for violation of data integrity when committing the operation. Optimistic locking does not solve the problem of dirty reading.

Three, the realization of optimistic lock
This is the most common way to implement optimistic locking, using the data version record mechanism. 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 taken out, it is updated, otherwise it is considered to be outdated data

1. database table Design

Task

There are three fields, namely Id,value, version

2. Implement

1) Read the data of the task table first (actually the table has only one record), get the value of version Versionvalue

2) You need to do this to prevent collisions each time you update the Value field in the Task table

Update task Set value = Newvalue,version = Versionvalue + 1 where version = Versionvalue;

Only this statement is executed to indicate that the value of the Update Value field was successful

If there are two nodes A and B are to update the value field values in the Task table, almost at the same time, A and B nodes from the task table read the version value of 2, then the a node and the B node when updating the Value field values, the Operation update task set Value = Newvalue,version = 3 where version = 2; actually only 1 nodes execute the SQL statement successfully, assuming the a node succeeds, then the value of the version field of the task table is the 3,B node and then the update task Set value = Newvalue,version = 3 where version = 2; This SQL statement is not executed, thus guaranteeing that no conflict will occur when updating the task table.

Related recommendations:

Yii2.0 optimistic lock and pessimistic lock instance detailed

A method of implementing transaction mechanism and optimistic locking in Redis

Database things optimistic lock and pessimistic lock