PHP+MySQL high concurrency locking transaction processing problem solution

This article mainly introduces the solution to the problem of high concurrency locking transaction processing in PHP MySQL. It has a certain reference value. Now I share it with you. Friends in need can refer to it.

The examples in this article are described PHP MySQL high concurrency locking transaction processing problem solution. Share it with everyone for your reference, the details are as follows:

1. Background:

Now there is such a demand. When inserting data, determine whether the test table has a username of ' If there is no data of mraz', it will be inserted. If there is, it will prompt "inserted". The purpose is to insert only one record with username as 'mraz'.

2. The general 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 &#39;insert_id：&#39;.$insert_id.&#39;<br>&#39;;
mysqli_free_result($rs);
mysqli_close($conn);

3. Generally, when there are a small number of requests, the program There will be no problem with logic. But once a high-concurrency request is executed, the program does not execute as expected, and multiple records with username 'mraz' will be inserted.

4. Solution : Use mysql’s FOR UPDATE statement and transaction isolation. Note that FOR UPDATE is only applicable to InnoDB and must be in a transaction (BEGIN/COMMIT) to take effect.

After adjusting the code, it is 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 &#39;insert_id：&#39;.$insert_id.&#39;<br>&#39;;
mysqli_free_result($rs);
mysqli_close($conn);

##5. Then use php's curl to simulate high concurrent requests for the php script , check the database and there will be only one record with username 'mraz'. Achieve the expected results of program execution~

Related recommendations:

PHP Mysql method to prevent SQL injection

How to PHP MySQL Realize paging display

PHP MySQL timing data statistics optimization

The above is the detailed content of PHP+MySQL high concurrency locking transaction processing problem solution. For more information, please follow other related articles on the PHP Chinese website!