Such as update table1 set filed1=field1-1 where the field - 1 & gt;=0 and id=1, could you tell me all this statement there will be risk high concurrency,
Appear this kind of circumstance, for example, filed1=1, two statements judgment field1=1 at the same time, this time will do subtraction 1-1, 0 and 1 at a time,
I think the success of this statement is to read you are exclusive lock the row or table, but there is no professional train of thought

CodePudding user response:

Don't worry, will line up,
When two of the same KEY update statement, it is the exclusive row locks.

You can use two clients, cross perform the following tests:
START the TRANSACTION
The update table1 set filed1=field1-1 where the field - 1 & gt;=0 and id=1
COMMIT

CodePudding user response:

There is something wrong with the statement in the top, where the field should be field1, or from the new write an unambiguous
The update mytable set myfiled=myfield - 1 where myfield 1 & gt;=0 and id=1

CodePudding user response:

The

reference 1/f, trainee response:

don't worry, will line up,
When two of the same KEY update statement, it is the exclusive row locks.

You can use two clients, cross perform the following tests:
START the TRANSACTION
The update table1 set filed1=field1-1 where the field - 1 & gt;=0 and id=1
COMMIT

Thank you, although the update will lock, but will likely perform internal reading and the assignment two process, like the
START the TRANSACTION
1, select myfield from mytable where id=1; To myfield value such as 1, calculated results myfiled - 1=0
2, update mytable set myfield=0 where 0 & gt;=0 and id=1;
COMMIT
If concurrent read, read it out of A 1, read the value is 1, B read value is 1, add A row lock the execution of the 2 update mytable set myfield=0 where 0 & gt;=0 and id=1 release exclusive locks, lock B to perform the update 2 mytable set myfield=0 where 0 & gt;=0 and id=1 release exclusive locks,
This virtually executed against the database two times update mytable set myfield=0 where 0 & gt;=0 and id=1, although the results for this line is not affected,
But if there are 3 in the transaction, the insert into,,,,,,,,, this is according to the success of the returned rows to operate 2, line 1 changes have taken place in both, then generates two inserts, in fact just want to be a,

CodePudding user response:

You worry about, should not exist, it is recommended that the revised statement, make straight efficiency improved,
Use id and the first field increase index, and then modify the statement is as follows:
The update table1 set filed1=field1-1 where field & gt;=1 and id=1;

CodePudding user response:

reference 4 floor AHUA1001 response:

you worry about a problem, should not exist, suggest a change on the statement, make straight efficiency improved,
Use id and the first field increase index, and then modify the statement is as follows:
The update table1 set filed1=field1-1 where field & gt;=1 and id=1;

Thank you, I'm going to practice a today, look at the phenomenon, while the principle of can't fix it, but if not, there is an error of the

CodePudding user response:

After mybatis + spring annotations transaction code, and use jmeter concurrent test (forgive me for not establish special table tests, using only the sys_user table of type int user_sex gender fields as test fields), and the results show that when user_sex down to zero, the corresponding insert a few is equal to the number of the target field, so there is no concurrent phenomenon shows that the statement on risk, in principle, please the great god deeply explain,
The reason why I ask the question is two days before go to interview a famous electric business enterprise, discuss the problems to reduce inventory, I said I use this kind of practice, he said that under the high concurrency will appear problem, he proposed to use optimistic locking, the timestamp directly to the where condition, but I think the optimistic locking in high concurrency will cause a lot of the where condition is not established,

@ Transactional
Public int updateSexCount User (User) {
//concurrent inventory reduction
Int a=userDao. UpdateSexCount (user);
If (a & gt; 0 {
//if the article on the success there is increase a data
ShopService. Insert (new Shop ());
}
return a;

}
The update sys_user set user_sex=user_sex - 1 where id=# {id} and user_sex 1 & gt;=0