How does MySQL return a value after updating a value in a statement?

How does MySQL return a value after updating a value in a statement?

What is a competitive issue?

Suppose there is a counter. First, the current value increases by itself, and then the current value is obtained by itself. The self-increasing value may be used as a unique identifier by some operations. Therefore, concurrent operations cannot obtain the same value.

Why can't I use the UPDATE statement to UPDATE the counter and then the SELECT statement to obtain the current value after auto-increment? The problem is that concurrent operations may obtain the same counter value.

Create table counters (id int not null unique, -- counter ID, multiple counters can exist in one TABLE, value INT -- current counter value); -- initialize COUNTER 1, insert into counters VALUES (1, 10) from 10; -- COUNTER 1 auto increment step 1 UPDATE counters SET value = value + 1 WHERE id = 1; -- Obtain the current value of counter 1 after auto-increment SELECT value FROM counters WHERE id = 1;

 

How can we avoid competition?

Method 1: Use Transaction and SELECT FOR UPDATE

If select for update is executed in a Transaction, this step locks the row record. Other concurrent operations on the record of this row will be blocked until the Transaction where the current select for update is located is committed or times out.

Start transaction; -- lock COUNTER 1 SELECT value FROM counters WHERE id = 1 for update; -- COUNTER 1 auto-increment step 1 UPDATE counters SET value = value + 1 WHERE id = 1; -- Get the current value after auto-increment SELECT value FROM counters WHERE id = 1; COMMIT;

 

Method 2: complete UPDATE and SELECT in a statement

Although solution 1 is feasible and reliable, adding a lock may affect some performance to some extent. Fortunately, we can use solution 2 to complete UPDATE and SELECT in a statement, which can be implemented in two ways.

 

Implementation 1: Use the Session variable.

-- COUNTER 1 Current value auto-increment step 1 UPDATE counters SET value = (@ newValue: = value + 1) WHERE id = 1; -- get the value after auto-increment SELECT @ newValue;

 

Implementation 2: Use the LAST_INSERT_ID method provided by MySQL

The LAST_INSERT_ID method is commonly used to obtain the last inserted value of an auto-incrementing column. It also has another usage. When a value is passed in, it will return the passed value, and the previous passed value will be returned the next time the LAST_INSERT_ID () method without parameters is called.

-- Step 1 of counter 1 is automatically increased, and the inserted value UPDATE counters SET value = LAST_INSERT_ID (Num) is recorded through the LAST_INSERT_ID (value + 1) WHERE id = 1; -- Obtain the last inserted value (current value after auto-increment) SELECT LAST_INSERT_ID ();