How to handle the following concurrency issues

1. Describe your problem

The following table exists in the work:
Table table_2, which exists unique key(device, seq) as a unique index . Where the SEQ field is a self-increment sequence maintained in our program.
The business requirement is that each time a message is pushed, it gets the largest seq in the table based on the device.
select seq from table_2 where device = ? order by seq desc
The obtained seq+1 is then inserted into the table_2 as a record of the current message
insert into table_2 (device, seq) values(?, ?)

2. Paste the relevant code. The code is the result of my simplification, easy for everyone to read

$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. Post-escalation error messages

Failure Method 1:
Unreasonable reason is: Directly add exception handling mechanism. When a conflict occurs, the concurrent request is caught directly by throwing an exception, and the program continues to run without any processing. The result is that the client request fails and the client needs to re-initiate the request.

try{    //代码放这里} catch(Exception $e){    if ($e->getCode() == 1062){        //    }}

Failure Method 2
Try the retry mechanism. When a request fails for the first time, the request is attempted multiple times. When a database unique index conflict occurs, the catch catches the exception and then tries again. The condition of the retry must be: The caught exception is a database insert conflict and returns 1062 exception code.

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

Failure Method 3
Controlled by Redis. Because the source of the conflict is that concurrency causes multiple requests to be read to the same seq. So consider using the Redis ' lock ' mechanism to implement. The first request to get to the SEQ will set a key, after which the request will be obtained on the basis of this 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();

Failure Mode 4
Another retry mechanism. The retry mechanism above is based on the MySQL database insert conflict, and now the retry is done through Redis at the SELECT statement level.

$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();}

Failure Method 5
Use transactions. Implemented using the MySQL mechanism's transaction. First add the intent exclusive lock IX in the SELECT statement, the modified SQL is “SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1 FOR UPDATE” , and the subsequent statements are unchanged.

The official documentation is as follows: Two intent exclusive says there is no conflict, so concurrent request A and concurrent request B will get the Ix,insert operation requires an X lock.

is

	X	IX	S
X	Conflict	Conflict	Conflict	Conflict
Ix	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
Is	Conflict	Compatible	Compatible	Compatible

my guess : A request is in the X lock, and the B request is also getting the X lock. Causes a request to lock on the line he traverses, and the B request is also locked at the same time, causing the loop to wait for the lock to release, resulting in a deadlock.

//前一句使用select for update//然后和insert包裹起来。多进程执行的话，会出现死锁。//通过降低mysql的隔离水平确实可以实现，但是可能会产生不可重复读的问题.通俗的介绍就是：A请求在操作事务中两次读到的数据可能不一致。

Failure Method 6
Put two SQL in one SQL.INSERT INTO table_2(device, seq) VALUES(?, (select seq + 1 from table_2) where device = ? )", array($device)

//因为前一条sql是我简化版，其实他是一个分表的查询操作。//这里的分表是一个router操作，所以不适合进行一条sql语句//就是会依次查询我们这里分的几个表，最近的一个表查不到，查第二个

Method 7
Modifies the data table structure, saves the SEQ to a separate table, and each device corresponds to a maximum seq. This ensures that the select for update does not create a gap lock at select time, but rather locks on a single data row.

1. Paste the relevant

The picture is as shown above. Most of the code is self-knocking, so there are some places you do not have to delve into, understand the meaning can be clear.
I hope we can have constructive suggestions.

The great God, show me the finger.

我实在是想不出什么好的办法了。将并发请求转化为串行，这个想法没有很好的实现。大神在哪里？

Reply content:

1. Describe your problem

The following table exists in the work:
Table table_2, which exists unique key(device, seq) as a unique index . Where the SEQ field is a self-increment sequence maintained in our program.
The business requirement is that each time a message is pushed, it gets the largest seq in the table based on the device.
select seq from table_2 where device = ? order by seq desc
The obtained seq+1 is then inserted into the table_2 as a record of the current message
insert into table_2 (device, seq) values(?, ?)

2. Paste the relevant code. The code is the result of my simplification, easy for everyone to read

$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. Post-escalation error messages

Failure Method 1:
Unreasonable reason is: Directly add exception handling mechanism. When a conflict occurs, the concurrent request is caught directly by throwing an exception, and the program continues to run without any processing. The result is that the client request fails and the client needs to re-initiate the request.

try{    //代码放这里} catch(Exception $e){    if ($e->getCode() == 1062){        //    }}

Failure Method 2
Try the retry mechanism. When a request fails for the first time, the request is attempted multiple times. When a database unique index conflict occurs, the catch catches the exception and then tries again. The condition of the retry must be: The caught exception is a database insert conflict and returns 1062 exception code.

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

Failure Method 3
Controlled by Redis. Because the source of the conflict is that concurrency causes multiple requests to be read to the same seq. So consider using the Redis ' lock ' mechanism to implement. The first request to get to the SEQ will set a key, after which the request will be obtained on the basis of this 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();

Failure Mode 4
Another retry mechanism. The retry mechanism above is based on the MySQL database insert conflict, and now the retry is done through Redis at the SELECT statement level.

$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();}

Failure Method 5
Use transactions. Implemented using the MySQL mechanism's transaction. First add the intent exclusive lock IX in the SELECT statement, the modified SQL is “SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1 FOR UPDATE” , and the subsequent statements are unchanged.

The official documentation is as follows: Two intent exclusive says there is no conflict, so concurrent request A and concurrent request B will get the Ix,insert operation requires an X lock.

is

	X	IX	S
X	Conflict	Conflict	Conflict	Conflict
Ix	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
Is	Conflict	Compatible	Compatible	Compatible

my guess : A request is in the X lock, and the B request is also getting the X lock. Causes a request to lock on the line he traverses, and the B request is also locked at the same time, causing the loop to wait for the lock to release, resulting in a deadlock.

//前一句使用select for update//然后和insert包裹起来。多进程执行的话，会出现死锁。//通过降低mysql的隔离水平确实可以实现，但是可能会产生不可重复读的问题.通俗的介绍就是：A请求在操作事务中两次读到的数据可能不一致。

Failure Method 6
Put two SQL in one SQL.INSERT INTO table_2(device, seq) VALUES(?, (select seq + 1 from table_2) where device = ? )", array($device)

//因为前一条sql是我简化版，其实他是一个分表的查询操作。//这里的分表是一个router操作，所以不适合进行一条sql语句//就是会依次查询我们这里分的几个表，最近的一个表查不到，查第二个

Method 7
Modifies the data table structure, saves the SEQ to a separate table, and each device corresponds to a maximum seq. This ensures that the select for update does not create a gap lock at select time, but rather locks on a single data row.

1. Paste the relevant

The picture is as shown above. Most of the code is self-knocking, so there are some places you do not have to delve into, understand the meaning can be clear.
I hope we can have constructive suggestions.

The great God, show me the finger.

我实在是想不出什么好的办法了。将并发请求转化为串行，这个想法没有很好的实现。大神在哪里？

INSERT INTO table_2 select Max (seq) +1,device from table_2 WHERE device =?;

Using Redis's incr directly to generate ID,INCR is atomic and there is no concurrency problem.

Your question summed up is: how to achieve the same device under the SEQ self-growth problem.
Workaround: Use Redis's hash to preserve the relationship between device and SEQ, using the Hincrby command when you need to produce a unique SEQ for the specified device.

