On the concurrency of update operation

In a high concurrency scenario , This is often the case ：
A Please come here , Look up a piece of data , Conduct update operation , meanwhile B Please come here at this time , Query this data , And operate . And then there will be B stay A After that, the query operation will be carried out , But the actual B But the data is A Cover .

Table name A, Field called number, As follows SQL sentence :

A operation sentence 1：select num from store where id='1';

Suppose that at this point a gets num= 99

B operation sentence 2：select num from store where id='1';

Because Party A has not yet update operation , B also got num= 99
Now A Conduct update operation

update store set num =${num} +1 where id='1';

At this time, it is written to the database num That is to say 100
here B The request also initiated the update operation ：

update store set num =${num} +1 where id='1';

Our expectation at this time should have been 101 Of , But actually B I wrote in the database again 100

Solution ：

（1） Introduce the concept of a version number , In the table A Add one version Field

A operation sentence 1：select num,version from store where id='1';

Suppose that at this point a gets num= 99 version =1

B operation sentence 2：select num,version from store where id='1';

Because Party A has not yet update operation , B also got num= 99 version=1
Now A Conduct update operation

update store set num =${num} +1 where id='1' and version = ${version};

At this time, it is written to the database num That is to say 100, version =2
here B The request also initiated the update operation ：

update store set num =${num} +1 where id='1' and version = ${version} ;

At this time, we found the condition version = 1 Don't set up , Because the last step is version Have been to 2 了 , therefore update Cannot update .

（2） Solution ：update A set number=number+1 where id=1; Statements are processed directly

Table name A, Field called number, As follows SQL sentence :

sentence 1：update A set number=number+1 where id=1;
sentence 2：update A set number=number+2 where id=1;

Suppose these two SQL The statement is simultaneously mysql perform ,id=1 In the record number The original value of the field is 99, So is it possible that ：
sentence 1 and 2 Because at the same time , They got number The values are all 99, It's all in 10 On the basis of 1 and 2, Lead to the final number Is updated to 100 or 101, instead of 102

This is actually Relational database itself needs to solve the problem . First , They were also MySQL perform , What you mean is that they are executed concurrently , The concurrent transaction is supported by special theory in relational database - ACID, The theory of transaction parallelism , All relational databases implement , Include Oracle, MySQL We need to follow this principle .
The simple understanding is the principle of lock . This is the first time update Will hold id=1 This line records Exclusive lock , the second update You need to have an exclusive lock on this record to modify it , Normal word , the second update It will block , Until the first one update Submit successfully , He'll get the lock , To modify the data .
in other words , According to the theory of relational database , these two items. update If they all succeed ,id=1 Of number It must be modified to 22. If not 22, That's a serious problem with database implementation bug.