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

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

php中世界最好的语言

Release： 2023-03-25 21:32:01

Original

2764 people have browsed it

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);

Copy after login

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);

Copy after login

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!