Php+mysql High concurrency Lock transaction processing problem Solving method

This article mainly introduces the Php+mysql high concurrency lock transaction processing problem Solving method, has a certain reference value, now share to everyone, the need for friends can refer to

In this paper, the solution of Php+mysql high concurrency lock transaction processing problem is described. Share to everyone for your reference, as follows:

1. Background:

Now there is the need to insert data when the test table has no username for the ' Mraz ' data, none inserted, there is a hint "inserted", the purpose is to insert only one username for the ' Mraz ' record.

2, the general procedure 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 a small number of requests, the program logic will not have a problem. However, once the high concurrent request executes, the program does not perform as expected and inserts multiple records with a username of ' Mraz '.

4. Solution : take advantage of MySQL's for UPDATE statement and the isolation of the transaction . Note that the for update applies only to InnoDB and must be in the 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, and then use the PHP Curl Simulation high Concurrent request the PHP script, the view database will have only one username for the ' Mraz ' record. Achieve expected results of program execution ~