如何處理下面的並發問題

1. 描述你的問題

工作中存在如下表：
表table_2，其中存在 unique key(device, seq) 為唯一索引。 其中seq欄位是我們程式中維護的一個自增序列。
業務的需求就是：每次推送一條訊息，會根據device擷取表中最大的seq。
select seq from table_2 where device = ? order by seq desc
然後將擷取的 seq+1 插入到table_2中作為當前訊息的記錄
insert into table_2 (device, seq) values(?, ?)

2 . 貼上相關代碼。代碼是我簡化的結果，方便大家閱讀

$device = "x-x";$this->_db->startTrans();//從table_2表中讀取該device最大的seq，//然後將該seq+1之後，重新將該device和seq插入的資料表中。//現在的問題是當並發的時候，並發的select語句擷取到了相同的seq，//所以insert的時候衝突發生了。$result = $this->_db->getRow(    “SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1”, $device)$Seq = $result['seq'] + 1;$this->_db->execute(    "INSERT INTO table_2(device, seq) VALUES(?, ?)", array($device, $Seq));$this->_db->commit();

3. 貼上報錯資訊

失敗方法1：
不合理的原因是：直接加上異常處理機制。當衝突時，並發的請求因為拋出異常，直接被捕獲，程式繼續運行，不做任何處理。結果就是：導致用戶端請求失敗，用戶端需要重新發起請求。

try{    //代碼放這裡} catch(Exception $e){    if ($e->getCode() == 1062){        //    }}

失敗方法2
嘗試重試機制.當請求第一次失敗時，會多次嘗試請求。當資料庫唯一索引衝突的時候，catch捕獲該異常，然後重試。重試的條件必須是：捕獲的異常是資料庫插入衝突，返回1062異常code。

for($i=0; $i < 10; $i++)try{    //代碼放這裡} catch(DBException $e){    if($e->code() == 1062){        continue;    }    throw $e;}

失敗方法3
通過redis來控制。因為衝突的根源就是於並發導致多個請求讀取到了相同的seq導致的。所以考慮使用redis‘鎖’的機制來實現。第一個擷取到seq的請求會設定一個key,之後的請求都會在這個key的基礎上進行擷取。

$device = "";$result = $this->_db->getRow(    “SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1”, $device)$Seq = $result['seq'] + 1;//因為並發其實就是因為上面的sql語句查到了相同的seq,所以這裡//就只擷取第一條執行的seq，之後的全部通過對該redis加1操作。使用setnx和incr是為了保證原子操作。//這個處理方式仍然存在問題，比如在索引值到期的時刻存在並發，這個時候$Seq就可能從1開始。$lock = $redis->setnx($device, $Seq)$redis->expire($device, 2);if (!$lock){    $Seq = $redis->incr($device);}$this->_db->execute(    "INSERT INTO table_2(device, seq) VALUES(?, ?)", array($device, $Seq));$this->_db->commit();

失敗方式4
另一種重試機制。上面的重試機制是靠MySQL資料庫的插入衝突，現在的重試是通過Redis在Select語句層面實現。

$device = "";for($i=0;$i<6; $i++){    if($i!=0){        usleep(10000);    } else {        $result = $this->_db->getRow(            “SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1”, $device        )        $Seq = $result['seq'] + 1;        $lock = $redis->setnx($device . $Seq, rand());        if ($lock){             $success = true;             $redis->expire($device . $Seq, 2);             break;        }    }    if (!$success) {            throw new Exception();    }    $this->_db->execute(        "INSERT INTO table_2(device, seq) VALUES(?, ?)", array($device, $Seq)    );    $this->_db->commit();}

失敗方法5
使用事務。使用MySQL機制的事務來實現。首先在Select語句中加上意向獨佔鎖定IX, 修改之後的SQL為 “SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1 FOR UPDATE”， 後面的語句不變。

官方文檔是這樣介紹的：兩個意向排他說並沒有衝突，所以並發請求A和並發請求B同時會擷取IX，insert操作需要一個X鎖。

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

我的猜測：A請求在擷取X鎖的時候，B請求也在擷取X鎖。導致A請求在他遍曆的行上加鎖，B請求也同時在加鎖，造成了迴圈等待鎖釋放的情況，產生死結。

//前一句使用select for update//然後和insert包裹起來。多進程執行的話，會出現死結。//通過降低mysql的隔離水平確實可以實現，但是可能會產生不可重複讀取的問題.通俗的介紹就是：A請求在操作事務中兩次讀到的資料可能不一致。

失敗方法6
將兩條sql放在一條sql裡。 INSERT INTO table_2(device, seq) VALUES(?, (select seq + 1 from table_2) where device = ? )", array($device)

//因為前一條sql是我簡化版，其實他是一個分表的查詢操作。//這裡的分表是一個router操作，所以不適合進行一條sql語句//就是會依次查詢我們這裡分的幾個表，最近的一個表查不到，查第二個

方法7
修改資料表結構，將seq儲存到獨立的一個其他表中，每個device對應一個最大的seq。這樣在select的時候就可以保證select for update 不會產生間隙鎖，而是指在一條資料行上加鎖。

1. 貼上相關

圖片就如上所示。大部分的代碼都是自己手工敲的，所以有些地方大家不用深究，瞭解清楚意思就可以。
希望大家可以有建設性建議

大神來指點指點吧

我實在是想不出什麼好的辦法了。將並發請求轉化為串列，這個想法沒有很好的實現。大神在哪裡？

回複內容：

1. 描述你的問題

工作中存在如下表：
表table_2，其中存在 unique key(device, seq) 為唯一索引。 其中seq欄位是我們程式中維護的一個自增序列。
業務的需求就是：每次推送一條訊息，會根據device擷取表中最大的seq。
select seq from table_2 where device = ? order by seq desc
然後將擷取的 seq+1 插入到table_2中作為當前訊息的記錄
insert into table_2 (device, seq) values(?, ?)

2 . 貼上相關代碼。代碼是我簡化的結果，方便大家閱讀

$device = "x-x";$this->_db->startTrans();//從table_2表中讀取該device最大的seq，//然後將該seq+1之後，重新將該device和seq插入的資料表中。//現在的問題是當並發的時候，並發的select語句擷取到了相同的seq，//所以insert的時候衝突發生了。$result = $this->_db->getRow(    “SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1”, $device)$Seq = $result['seq'] + 1;$this->_db->execute(    "INSERT INTO table_2(device, seq) VALUES(?, ?)", array($device, $Seq));$this->_db->commit();

3. 貼上報錯資訊

失敗方法1：
不合理的原因是：直接加上異常處理機制。當衝突時，並發的請求因為拋出異常，直接被捕獲，程式繼續運行，不做任何處理。結果就是：導致用戶端請求失敗，用戶端需要重新發起請求。

try{    //代碼放這裡} catch(Exception $e){    if ($e->getCode() == 1062){        //    }}

失敗方法2
嘗試重試機制.當請求第一次失敗時，會多次嘗試請求。當資料庫唯一索引衝突的時候，catch捕獲該異常，然後重試。重試的條件必須是：捕獲的異常是資料庫插入衝突，返回1062異常code。

for($i=0; $i < 10; $i++)try{    //代碼放這裡} catch(DBException $e){    if($e->code() == 1062){        continue;    }    throw $e;}

失敗方法3
通過redis來控制。因為衝突的根源就是於並發導致多個請求讀取到了相同的seq導致的。所以考慮使用redis‘鎖’的機制來實現。第一個擷取到seq的請求會設定一個key,之後的請求都會在這個key的基礎上進行擷取。

$device = "";$result = $this->_db->getRow(    “SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1”, $device)$Seq = $result['seq'] + 1;//因為並發其實就是因為上面的sql語句查到了相同的seq,所以這裡//就只擷取第一條執行的seq，之後的全部通過對該redis加1操作。使用setnx和incr是為了保證原子操作。//這個處理方式仍然存在問題，比如在索引值到期的時刻存在並發，這個時候$Seq就可能從1開始。$lock = $redis->setnx($device, $Seq)$redis->expire($device, 2);if (!$lock){    $Seq = $redis->incr($device);}$this->_db->execute(    "INSERT INTO table_2(device, seq) VALUES(?, ?)", array($device, $Seq));$this->_db->commit();

失敗方式4
另一種重試機制。上面的重試機制是靠MySQL資料庫的插入衝突，現在的重試是通過Redis在Select語句層面實現。

$device = "";for($i=0;$i<6; $i++){    if($i!=0){        usleep(10000);    } else {        $result = $this->_db->getRow(            “SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1”, $device        )        $Seq = $result['seq'] + 1;        $lock = $redis->setnx($device . $Seq, rand());        if ($lock){             $success = true;             $redis->expire($device . $Seq, 2);             break;        }    }    if (!$success) {            throw new Exception();    }    $this->_db->execute(        "INSERT INTO table_2(device, seq) VALUES(?, ?)", array($device, $Seq)    );    $this->_db->commit();}

失敗方法5
使用事務。使用MySQL機制的事務來實現。首先在Select語句中加上意向獨佔鎖定IX, 修改之後的SQL為 “SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1 FOR UPDATE”， 後面的語句不變。

官方文檔是這樣介紹的：兩個意向排他說並沒有衝突，所以並發請求A和並發請求B同時會擷取IX，insert操作需要一個X鎖。

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

我的猜測：A請求在擷取X鎖的時候，B請求也在擷取X鎖。導致A請求在他遍曆的行上加鎖，B請求也同時在加鎖，造成了迴圈等待鎖釋放的情況，產生死結。

//前一句使用select for update//然後和insert包裹起來。多進程執行的話，會出現死結。//通過降低mysql的隔離水平確實可以實現，但是可能會產生不可重複讀取的問題.通俗的介紹就是：A請求在操作事務中兩次讀到的資料可能不一致。

失敗方法6
將兩條sql放在一條sql裡。 INSERT INTO table_2(device, seq) VALUES(?, (select seq + 1 from table_2) where device = ? )", array($device)

//因為前一條sql是我簡化版，其實他是一個分表的查詢操作。//這裡的分表是一個router操作，所以不適合進行一條sql語句//就是會依次查詢我們這裡分的幾個表，最近的一個表查不到，查第二個

方法7
修改資料表結構，將seq儲存到獨立的一個其他表中，每個device對應一個最大的seq。這樣在select的時候就可以保證select for update 不會產生間隙鎖，而是指在一條資料行上加鎖。

1. 貼上相關

圖片就如上所示。大部分的代碼都是自己手工敲的，所以有些地方大家不用深究，瞭解清楚意思就可以。
希望大家可以有建設性建議

大神來指點指點吧

我實在是想不出什麼好的辦法了。將並發請求轉化為串列，這個想法沒有很好的實現。大神在哪裡？

insert into table_2 select max(seq)+1,device from table_2 WHERE device = ?;

直接使用redis的incr來產生自增id，incr是原子操作，不會有並發問題。

你的問題歸納一下就是：如何?同一個device下seq自增長問題。
解決方案：使用redis的hash儲存device與seq的關係，當需要為指定device生產一個唯一seq時使用HINCRBY命令即可。

