MySQL optimistic lock

MySQL pessimistic lock is based on the lock mechanism of the database to achieve the maximum degree of exclusivity. However, because the modern web system is generally high concurrency, so pessimistic lock in such circumstances, the applicability is not high, so we have a pessimistic lock corresponding to the optimistic lock.

Optimistic lock, that is, when the data to commit the update, the data will be conflicting or not detected, if there is a conflict, then return the wrong information, let the user decide how to do.

The implementation of optimistic locking is generally controlled in the program. There are generally two general implementations: use the data version, which is implemented using timestamps.

Implementation Details:

1. Add a field of version or timestamp field in the table;

2. At the time of the query, return the field, the update, the first check whether the Version field or timestamp field is consistent with the field in the table, if the update is consistent, and the version field plus 1 (timestamp words updated to the current time), if inconsistent indicates that the field has been updated, Prompt for update failure, and then return the error message to the user;

Through some pseudo-code to achieve:

--Pseudo Code:--1. Concurrent query, all query id=1 recordsMysql> SELECT *  fromTest_orderWHEREId= 1;+----+--------+------+----------+---------+-------------+|Id|Status|Name|goods_id|Version|Update_date|+----+--------+------+----------+---------+-------------+|  1 |      1 |Maotai| 123      |       1 | NULL        |+----+--------+------+----------+---------+-------------+1Rowinch Set--2. User 1 Perform the update operationUPDATETest_orderSETStatus=1, version=Version+1 WHEREId=1  andVersion=1 --3. At this point version is 2, user 2 performs the update again, but the update is unsuccessful at this timeUPDATETest_orderSETStatus=1, version=Version+1 WHEREId=1  andVersion=1

The usual SVN is done in a similar way.

MySQL optimistic lock