Solution of PHP+MySQL High Concurrent Lock Transaction Processing Problem

In this paper, the solution of PHP+MySQL high concurrent locking transaction processing problem is described by examples. Share it for your reference, as follows:

1. Background:

Now, when inserting data, it is necessary to judge whether there is data with username as' mraz 'in test table, insert if there is no data, and prompt "inserted" if there is data. The purpose is to insert only one record with username as' mraz '.

2. 1 The program logic is as follows:

$conn = mysqli_connect('127.0.0.1', 'root', '111111') or die(mysqli_error());
mysqli_select_db($conn, 'mraz');
$rs = mysqli_query($conn, 'SELECT count(*) as total FROM test WHERE username = "mraz" ');
$row = mysqli_fetch_array($rs);
if($row['total']>0){
  exit('exist');
}
mysqli_query($conn, "insert into test(username) values ('mraz')");
var_dump('error:'.mysqli_errno($conn));
$insert_id = mysqli_insert_id($conn);
echo 'insert_id : '.$insert_id.'<br>';
mysqli_free_result($rs);
mysqli_close($conn);

3, 1 when a small number of requests, the program logic will not be a problem. However, if 1 denier concurrent request is executed, the program does not execute as expected, and multiple records with username as' mraz 'will be inserted.

4. Solution: Take advantage of the FOR UPDATE statement of mysql and the isolation of transactions. Note that FOR UPDATE only applies to InnoDB and must be in a transaction (BEGIN/COMMIT) to take effect.

Adjust the code as follows:

$conn = mysqli_connect('127.0.0.1', 'root', '111111') or die(mysqli_error());
mysqli_select_db($conn, 'mraz');
mysqli_query($conn, 'BEGIN');
$rs = mysqli_query($conn, 'SELECT count(*) as total FROM test WHERE username = "mraz" FOR UPDATE');
$row = mysqli_fetch_array($rs);
if($row['total']>0){
  exit('exist');
}
mysqli_query($conn, "insert into test(username) values ('mraz')");
var_dump('error:'.mysqli_errno($conn));
$insert_id = mysqli_insert_id($conn);
mysqli_query($conn, 'COMMIT');
echo 'insert_id : '.$insert_id.'<br>';
mysqli_free_result($rs);
mysqli_close($conn);

5. Reuse curl of php to simulate high concurrent request for this php script, and only one record of username as' mraz 'will be found in the database. Achieve the expected result of program execution ~

For more readers interested in PHP related content, please check the topics on this site: "Introduction to php+mysql Database Operation", "Summary of php+mysqli Database Programming Skills", "Introduction to php Object-Oriented Programming", "Encyclopedia of PHP Array (Array) Operation Skills", "Summary of php String (string) Usage" and "Summary of php Common Database Operation Skills"

I hope this article is helpful to everyone's PHP programming.