当前位置:网站首页>On the concurrency of update operation

On the concurrency of update operation

2020-11-08 13:52:21 osc_7eqzxl4g

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.

版权声明
本文为[osc_7eqzxl4g]所创,转载请带上原文链接,感谢