Detailed explanation of the steps for handling high-concurrency locking transactions with PHP+MySQL

This time I will bring you a detailed explanation of the steps for PHP MySQL to process high-concurrency locking transactions, and Notes for PHP MySQL to process high-concurrency locking transactions. ##What are they? The following is a practical case. Let’s take a look.

1. Background:

Now there is such a demand.

When inserting data, determine whether the test table has data with username as 'mraz' , if not, 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 'insert_id：'.$insert_id.'<br>';
mysqli_free_result($rs);
mysqli_close($conn);

3. Generally, when there are a small number of requests, there will be no problem with the program 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 'insert_id：'.$insert_id.'<br>';
mysqli_free_result($rs);
mysqli_close($conn);

I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other related articles on the PHP Chinese website!

Recommended reading:

PHP generated QR code poster case analysis

Detailed explanation of the steps for PHP to dynamically compress js and css files

Detailed explanation of the steps to modify PHPExcel

The above is the detailed content of Detailed explanation of the steps for handling high-concurrency locking transactions with PHP+MySQL. For more information, please follow other related articles on the PHP Chinese website!