A very stupid question popped up today, let me record it
Can optimistic locking implemented through version numbers take effect in MySQL under the repeatable read transaction isolation level?
For example: two transactions one and two are opened, a certain piece of data is modified in transaction one, and the version number changes. But in transaction two, when querying the version number of this record, it has not changed. How to determine whether this data has been modified by other transactions?
The reason is that under the repeatable read isolation level, it is divided into snapshot read and current read. update is the current read, not the snapshot. Read, so the latest version number can be read.
Snapshot read reads the data of the snapshot at the moment when the transaction is started. The current read can read the data that has been submitted by other transactions. Under the repeatable read isolation level, select uses snapshot read. When other transactions have been submitted, the current transaction still reads the snapshot data. When the data is selected through where during update, the current read is used and the current read is read. The data. Therefore, in multiple transactions, when other transactions modify the data and the version number changes, even if the current transaction cannot read the change in the version number, when updating the data according to the version number, the data cannot be selected. The update will fail.
Why introduce locks? It is definitely to avoid the concurrency problems caused by multiple threads modifying a resource and destroying the data consistency problem. Let’s look at an example below. Money records the amount of a piggy bank, and the money in the piggy bank can be withdrawn by multiple users. Here is the preliminary data:
mysql> select * from money; + ---- + -------- + | id | money | + ---- + -------- + | 1 | 100.00 | + ---- + -------- + 1 row in set (0.00 sec)
Open two transactions, and when each transaction determines that the balance is sufficient, withdraw the amount:
Transaction 1
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from money; + ---- + -------- + | id | money | + ---- + -------- + | 1 | 100.00 | + ---- + -------- + 1 row in set (0.00 sec)
Transaction 2
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from money; + ---- + -------- + | id | money | + ---- + -------- + | 1 | 100.00 | + ---- + -------- + 1 row in set (0.00 sec)
The amount of each transaction is judged to be sufficient, and 90 is taken out as soon as the transaction is completed
Transaction 1
mysql> update money set money = money-90 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from money; + ---- + ------- + | id | money | + ---- + ------- + | 1 | 10.00 | + ---- + ------- + 1 row in set (0.00 sec)
Transaction 2 queries the database again and finds that the amount has not decreased. The reason is that Select reads snapshot data, and then transaction 2 also starts to withdraw money.
Transaction 2
mysql> select * from money; + ---- + -------- + | id | money | + ---- + -------- + | 1 | 100.00 | + ---- + -------- + 1 row in set (0.00 sec) mysql> update money set money = money-90 where id = 1;
However, during the update, the database was blocked because transaction one did not commit the transaction and row-level locks could not be obtained in transaction two. Next we submit transaction one and release the lock
Transaction 1
mysql> commit; Query OK, 0 rows affected (0.00 sec)
Transaction 2 queries the database again and the amount is still 100. Try withdrawing money again.
Transaction 2
mysql> select * from money; + ---- + -------- + | id | money | + ---- + -------- + | 1 | 100.00 | + ---- + -------- + 1 row in set (0.00 sec) mysql> update money set money = money-90 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Data updated successfully. Submit the transaction
Transaction 2
mysql> commit; Query OK, 0 rows affected (0.01 sec)
Let us query the final result of the data.
mysql> select * from money; + ---- + -------- + | id | money | + ---- + -------- + | 1 | -80.00 | + ---- + -------- + 1 row in set (0.00 sec)
As expected, concurrency issues occurred and data consistency was broken.
Let’s try adding an optimistic lock. Add version number field to record data version
mysql> alter table money add version int(11) after money; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> update money set version = 0, money = 100 where id =1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from money; + ---- + -------- + --------- + | id | money | version | + ---- + -------- + --------- + | 1 | 100.00 | 0 | + ---- + -------- + --------- + 1 row in set (0.00 sec)
Open two transactions at the same time
Transaction 1
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
Transaction 2
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
The following transaction starts with withdrawing money. When the data is updated, it is updated according to the snapshot data version number, and when the data is changed, the data version number is also updated.
Transaction 1
mysql> select * from money; + ---- + -------- + --------- + | id | money | version | + ---- + -------- + --------- + | 1 | 100.00 | 0 | + ---- + -------- + --------- + 1 row in set (0.00 sec) mysql> update money set money = money-90,version = version + 1 where id = 1 and version = 0; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from money; + ---- + ------- + --------- + | id | money | version | + ---- + ------- + --------- + | 1 | 10.00 | 1 | + ---- + ------- + --------- + 1 row in set (0.00 sec)
At this time, if transaction two also wants to modify the data, the modification will be unsuccessful because transaction one does not release the lock and transaction two is blocked.
Transaction 2
mysql> select * from money; + ---- + -------- + --------- + | id | money | version | + ---- + -------- + --------- + | 1 | 100.00 | 0 | + ---- + -------- + --------- + 1 row in set (0.00 sec) mysql> update money set money = money-90,version = version + 1 where id = 1 and version = 0;
Transaction one commit transaction
Transaction 1
mysql> commit; Query OK, 0 rows affected (0.00 sec)
At this time, the data is updated in transaction two, the number of rows is changed to 0, and the record with id=1 and version=0 cannot be found.
mysql> update money set money = money-90,version = version + 1 where id = 1 and version = 0; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
The reason is that update is the current read, the latest data is read, and the version has become 1.
What select reads is a snapshot read, and what it reads is version or 0.
The data version number has changed, the update failed, transaction two is rolled back, the data is queried again, and new data is read.
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from money; + ---- + ------- + --------- + | id | money | version | + ---- + ------- + --------- + | 1 | 10.00 | 1 | + ---- + ------- + --------- + 1 row in set (0.00 sec)
In MybatisPlus, the implementation principle of optimistic locking is also to add version number to the database. When we modify the data, version = version + 1 will be automatically added after the update statement set, and version = old version number will be added after where.