MySQL optimistic concurrency controls optimistic concurrency control

By default, the INNODB transaction isolation level for MySQL is read repeatedly repeatable read,

SELECT @ @GLOBAL @ @tx_isolation;
Repeatable-read Repeatable-read

Perform the following tests, and open two sessions, S1 and S2, and turn autocommit off.

set autocommit=0;

The test uses a simple table with a single row of data

CREATE TABLE' T1 ' (' v1 ' )tinyint(2) not NULL DEFAULT '0', ' v2 'tinyint(2) not NULL DEFAULT '0', ' Version ' Mediumint (8) not NULL DEFAULT '0',  PRIMARY KEY(' v1 ')) ENGINE=InnoDBDEFAULTCHARSET=Utf8;row:1    1    0

01-S1 Execute SELECT * from T1; See 1, 1, 0
02-S2 Execute SELECT * from T1; See 1, 1, 0
03-S2 Execute update t1 set v2=2, version=version+1 where v1=1 and version=0; (Affected rows:1)
04-S2 Execute SELECT * from T1; See 1, 2, 1
05-S1 Execute SELECT * from T1; See 1, 1, 0-No change
06-S1 Execute update t1 set v2=2, version=version+1 where v1=1 and version=0; be hung up and waiting
07-s2 execution commit; No. 06 Step S1 Query End, display (affected rows:0)
08-S1 Execute SELECT * from T1; Still see 1, 1, 0
09-S1 execution commit;
10-S1 Execute SELECT * from T1; See 1, 2, 1-This time the data is updated

It can be verified that, under the isolation level of Repeatable-read, a transaction is not aware of data changes outside the transaction, and all the data read is unchanged from the beginning of the transaction, but the operation of the update type is affected by changes in the external data of the transaction. First, if the same row of data has an external transaction uncommitted, the current operation needs to be queued, followed by an update operation that is affected if the same row of data has been changed by the outside, such as in this example affected rows:0

Thus, it is possible to do concurrency control in the same way as update T1 set v2=2, version=version+1 where V1=1 and version=0.

Optimistic concurrency control for MySQL optimistic concurrency controls