How MySQL returns a value after updating a value in a statement-processing of self-growing seed race problem

What is a race condition?

Suppose you have a counter that starts with the current value from growing, and then gets the current value since the growth. Since the growth value may be used by some operations as a unique identity, concurrent operations cannot allow the same value to be obtained.

Why can't I update the counter using the UPDATE statement, and then the SELECT statement gets the current value since it grew? The problem is that concurrent operations are likely to get the same counter value.

CREATE TABLECounters (IDINT  not NULL UNIQUE,--counter ID, multiple counters can exist in a table,ValueINT                 --Counter Current value  ); --Initialize counter 1, counting starting from 10  INSERT  intoCountersVALUES(1,Ten); --counter 1 self-increment step 1  UPDATECountersSETValue=Value+ 1 WHEREId= 1; --gets the current value of counter 1 since growth  SELECTValue fromCountersWHEREId= 1; 

How to avoid race problems?

Method One: Use transaction and select for UPDATE

If a select for UPDATE is executed in a transaction, the step will lock the row record. Other concurrent operations that are logged to the row are blocked until the current select for update transaction commits or times out.

STARTTRANSACTION; --Lock counter 1  SELECTValue fromCountersWHEREId= 1  for UPDATE; --counter 1 Self-growing step 1  UPDATECountersSETValue=Value+ 1 WHEREId= 1; --gets the current value since the growth  SELECTValue fromCountersWHEREId= 1; COMMIT;

Method Two: Complete the update and select in a single statement

Scenario one is feasible and reliable, but adding a lock may affect some performance somewhat. Fortunately, we can use scenario two to complete the update and select in a single statement, which can be implemented in two ways.

Implementation 1: Pass the session variable.

-- counter 1 Current value from growth Step 1 UPDATE counters    SET = (@newValue :=+1) WHERE = 1 ; -- gets the value since the growth SELECT @newValue;

Implementation 2: The last_insert_id method that comes with MySQL

The common scenario for the last_insert_id method is to get the last inserted value from the growth column. It also has another usage that returns the value passed in when a value is passed in, and returns the previously passed-in value when the next call to the last_insert_id () method with no parameters is called.

-- counter 1 Self-growing step 1, and record the inserted value via the last_insert_id (Num) method UPDATE counters    SET = + 1 )WHERE=1; -- gets the last inserted value (the current value since the growth) SELECT LAST_INSERT_ID ();

How MySQL returns a value after it has been updated in a statement-handling of the race problem of self-growing seeds