MySQL 如何在一個語句中更新一個數值後返回該值 -- 自增長種子競態問題處理

標籤：comm   col   sql   font   sel   code   有一個   nsa   方法   

什麼是競態問題？

　　假設有一個計數器，首先當前值自增長，然後擷取到自增長之後的當前值。自增長後的值有可能被有些操作用來當做唯一性標識，因此並發的操作不能允許取得相同的值。

　　為什麼不能使用使用UPDATE語句更新計數器，然後SELECT語句擷取自增長後的當前值？問題在於並發的操作有可能擷取到相同的計數器值。

CREATE TABLE counters  (     id INT NOT NULL UNIQUE,   -- 計數器ID，多個計數器可以存在一個表中,      value INT                 -- 計數器當前值  );   -- 初始化計數器1，從10開始計數  INSERT INTO counters VALUES (1, 10);   -- 計數器1自增步長1  UPDATE counters SET value = value + 1 WHERE id = 1;  -- 擷取計數器1自增長後的當前值  SELECT value FROM counters WHERE id = 1; 

 

如何避免競態問題？

方法一：使用Transaction和SELECT FOR UPDATE

　　如果一個Transaction中執行SELECT FOR UPDATE，該步操作會鎖住該行記錄。其它對該行記錄的並行作業會被阻塞，直到當前SELECT FOR UPDATE所在Transaction提交或逾時。

  START TRANSACTION;   -- 鎖定計數器1  SELECT value FROM counters WHERE id = 1 FOR UPDATE;    -- 計數器1自增長步長1  UPDATE counters SET value = value + 1 WHERE id = 1;  -- 擷取自增長後的當前值  SELECT value FROM counters WHERE id = 1;      COMMIT;

 

方法二：在一個語句中完成UPDATE和SELECT

　　方案一雖然可行並且可靠，但是加上了鎖後一定程度上可能會影響一些效能。幸運的是我們可以使用方式情節二，在一個語句中完成UPDATE和SELECT，可以有兩種方法實現。

 

實現1：通過Session變數。

-- 計數器1當前值自增長步長1UPDATE counters   SET value = (@newValue := value + 1)WHERE id = 1;-- 擷取自增長之後的值SELECT @newValue;

 

實現2：通過MySQL內建的LAST_INSERT_ID方法

　　LAST_INSERT_ID方法常用的情境是擷取自增長列最後一次插入的值。它還有另外一個用法，當傳入一個值時它會返回傳入的值，並且在下一次調用不含參數的LAST_INSERT_ID（）方法時，還是會返回先前傳入的值。

-- 計數器1自增長步長1，並通過LAST_INSERT_ID(Num)方法記錄插入的值UPDATE counters   SET value = LAST_INSERT_ID(value + 1)WHERE id = 1;-- 擷取最後一次插入的值 （自增長之後的當前值）SELECT LAST_INSERT_ID();

 

MySQL 如何在一個語句中更新一個數值後返回該值 -- 自增長種子競態問題處理