How to deal with the following concurrency Problems

1. Describe your problem. The following table exists in the work: Table table_2, where uniquekey (device, seq) is a unique index. The seq field is an auto-incrementing sequence maintained in our program. The business requirement is: each time a message is pushed, the maximum seq in the table will be obtained according to the device... 1. Describe your problem

The following table exists at work:
Table table_2, which existsunique key(device, seq)IsUnique Index. The seq field is an auto-incrementing sequence maintained in our program.
Business NeedsThat is, each time a message is pushed, the maximum seq in the table is obtained based on the device.
select seq from table_2 where device = ? order by seq desc
Insert the obtained seq + 1 to table_2 as the record of the current message.
insert into table_2 (device, seq) values(?, ?)

2. paste the relevant code. The code is simplified for your convenience.

$ Device = "x-x"; $ this-> _ db-> startTrans (); // read the largest seq of the device from the table_2 table, // after this seq + 1, insert the device and seq into the data table again. // The problem now is that when concurrency occurs, the concurrent select statement gets the same seq, // so the insert conflict occurs. $ 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. paste the error message

Method 1 of failure:
The reason is that the exception handling mechanism is directly added. When a conflict occurs, concurrent requests are directly captured because of an exception thrown. The program continues to run without any processing. The result is: the client request fails and the client needs to initiate a new request.

Try {// code here} catch (Exception $ e) {if ($ e-> getCode () = 1062 ){//}}

Method 2 of failure
Retry Mechanism. When a request fails for the first time, it will be tried multiple times. When the unique index of the database conflicts, catch the exception and try again. The condition for retry must be: The caught exception is a database insertion conflict, and the 1062 Exception code is returned.

For ($ I = 0; $ I <10; $ I ++) try {// put the code here} catch (DBException $ e) {if ($ e-> code () = 1062) {continue;} throw $ e ;}

Method 3 of failure
Use redis for control. The root cause of the conflict is that concurrent requests read the same seq. Therefore, we consider using the redis 'lock' mechanism. For the first request to obtain seq, a key is set, and subsequent requests are obtained based on this key.

$ Device = ""; $ result = $ this-> _ db-> getRow ("SELECT * FROM table_2 WHERE device =? Order by seq desc limit 1 ", $ device) $ Seq = $ result ['seq '] + 1; // because concurrency is actually because the same seq is found in the preceding SQL statement, // only obtain the first executed seq here, and all subsequent operations are performed by adding 1 TO THE redis. Setnx and incr are used to ensure atomic operations. // This processing method still has problems. For example, if the key value expires at the time of concurrency, $ Seq may start from 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 above Retry Mechanism relies on MySQL database insertion conflicts. The current Retry is achieved 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. Use the MySQL mechanism to implement transactions. First, add the intention exclusive lock IX in the Select statement, and the modified SQL is“SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1 FOR UPDATE”And the subsequent statements remain unchanged.

The official document introduces this as follows: the two intention exclusive statements do not conflict, so concurrent requests A and concurrent requests B will get IX at the same time, and the insert operation requires an X lock.

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

My guesses: When A requests get the X lock, B requests also get the X lock. As A result, the request is locked on the row it traverses, and the B request is also locked, resulting in A loop waiting for the lock to be released, resulting in A deadlock.

// Use select for update in the previous sentence // and wrap it with insert. A deadlock occurs when multiple processes are executed. // It can be achieved by reducing the isolation level of mysql, but the problem of non-repeated reading may occur. the general introduction is that the data read by request A twice in the transaction may be inconsistent.

Method 6 of failure
Place the two SQL statements in one SQL statement.INSERT INTO table_2(device, seq) VALUES(?, (select seq + 1 from table_2) where device = ? )", array($device)

// Because the previous SQL is my simplified version, it is actually a sub-Table query operation. // The Sub-table here is a router operation, so it is not suitable for performing an SQL statement // It will query the several tables we have divided in sequence. The most recent table cannot be found, and the second one can be found.

Method 7
Modify the data table structure and save seq to an independent table. Each device corresponds to the largest seq. In this way, the select for update will not generate a gap lock, but will be locked on a data row.

1. Labeling

The image is shown above. Most of the Code is manually typed, so you don't need to go into it in some places.
I hope you will have some constructive suggestions.

Let me give you some advice.

I really don't have any good ideas. The idea of converting concurrent requests into serial is not well implemented. Where is the great god?

Reply content:

1. Describe your problem

The following table exists at work:
Table table_2, which existsunique key(device, seq)IsUnique Index. The seq field is an auto-incrementing sequence maintained in our program.
Business NeedsThat is, each time a message is pushed, the maximum seq in the table is obtained based on the device.
select seq from table_2 where device = ? order by seq desc
Insert the obtained seq + 1 to table_2 as the record of the current message.
insert into table_2 (device, seq) values(?, ?)

2. paste the relevant code. The code is simplified for your convenience.

$ Device = "x-x"; $ this-> _ db-> startTrans (); // read the largest seq of the device from the table_2 table, // after this seq + 1, insert the device and seq into the data table again. // The problem now is that when concurrency occurs, the concurrent select statement gets the same seq, // so the insert conflict occurs. $ 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. paste the error message

Method 1 of failure:
The reason is that the exception handling mechanism is directly added. When a conflict occurs, concurrent requests are directly captured because of an exception thrown. The program continues to run without any processing. The result is: the client request fails and the client needs to initiate a new request.

Try {// code here} catch (Exception $ e) {if ($ e-> getCode () = 1062 ){//}}

Method 2 of failure
Retry Mechanism. When a request fails for the first time, it will be tried multiple times. When the unique index of the database conflicts, catch the exception and try again. The condition for retry must be: The caught exception is a database insertion conflict, and the 1062 Exception code is returned.

For ($ I = 0; $ I <10; $ I ++) try {// put the code here} catch (DBException $ e) {if ($ e-> code () = 1062) {continue;} throw $ e ;}

Method 3 of failure
Use redis for control. The root cause of the conflict is that concurrent requests read the same seq. Therefore, we consider using the redis 'lock' mechanism. For the first request to obtain seq, a key is set, and subsequent requests are obtained based on this key.

$ Device = ""; $ result = $ this-> _ db-> getRow ("SELECT * FROM table_2 WHERE device =? Order by seq desc limit 1 ", $ device) $ Seq = $ result ['seq '] + 1; // because concurrency is actually because the same seq is found in the preceding SQL statement, // only obtain the first executed seq here, and all subsequent operations are performed by adding 1 TO THE redis. Setnx and incr are used to ensure atomic operations. // This processing method still has problems. For example, if the key value expires at the time of concurrency, $ Seq may start from 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 above Retry Mechanism relies on MySQL database insertion conflicts. The current Retry is achieved 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. Use the MySQL mechanism to implement transactions. First, add the intention exclusive lock IX in the Select statement, and the modified SQL is“SELECT * FROM table_2 WHERE device = ? ORDER BY seq DESC LIMIT 1 FOR UPDATE”And the subsequent statements remain unchanged.

The official document introduces this as follows: the two intention exclusive statements do not conflict, so concurrent requests A and concurrent requests B will get IX at the same time, and the insert operation requires an X lock.

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

My guesses: When A requests get the X lock, B requests also get the X lock. As A result, the request is locked on the row it traverses, and the B request is also locked, resulting in A loop waiting for the lock to be released, resulting in A deadlock.

// Use select for update in the previous sentence // and wrap it with insert. A deadlock occurs when multiple processes are executed. // It can be achieved by reducing the isolation level of mysql, but the problem of non-repeated reading may occur. the general introduction is that the data read by request A twice in the transaction may be inconsistent.

Method 6 of failure
Place the two SQL statements in one SQL statement.INSERT INTO table_2(device, seq) VALUES(?, (select seq + 1 from table_2) where device = ? )", array($device)

// Because the previous SQL is my simplified version, it is actually a sub-Table query operation. // The Sub-table here is a router operation, so it is not suitable for performing an SQL statement // It will query the several tables we have divided in sequence. The most recent table cannot be found, and the second one can be found.

Method 7
Modify the data table structure and save seq to an independent table. Each device corresponds to the largest seq. In this way, the select for update will not generate a gap lock, but will be locked on a data row.

1. Labeling

The image is shown above. Most of the Code is manually typed, so you don't need to go into it in some places.
I hope you will have some constructive suggestions.

Let me give you some advice.

I really don't have any good ideas. The idea of converting concurrent requests into serial is not well implemented. Where is the great god?

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

Directly use redis's incr to generate an auto-incremental id. incr is an atomic operation and there is no concurrency problem.

Your problem is summarized as follows: how to achieve seq auto-growth under the same device.
Solution: use the redis hash to save the relationship between the device and seq. Use the HINCRBY command to generate a unique seq for the specified device.