Concurrency problems with databases in PHP

originally included in my blog http://starlight36.com/post/php-db-concurrency

concurrency problems in parallel systems can never be overlooked. Although the PHP language natively does not provide a multithreaded mechanism, it does not mean that all operations are thread-safe. Especially in operations such as orders, payments and other business systems, the need to pay more attention to the operation of the database concurrency problems. Next I use a case to analyze the problem of concurrency problems when PHP is working with databases.

First, we have such a data sheet:

Mysql> SELECT * FROM counter;+----+-----+| ID |  num |+----+-----+| 1 |   0 |+----+-----+1 row in Set (0.00 sec)

This code simulates a business operation:

<?phpfunction dummy_business () {$conn = Mysqli_connect (' 127.0.0.1 ', ' public ', ' public ') or Die (Mysqli_error ()); mysqli_select_db ($conn, ' test '); for ($i = 0; $i < 10000; $i + +) {mysqli_query ($conn, ' UPDATE counter SET num = num + 1 W Here id = 1 ');} Mysqli_close ($conn);} for ($i = 0; $i <, $i + +) {$pid = Pcntl_fork (); if ($pid = =-1) {die (' Can not fork. ');} elseif (! $pid) {dummy_business ( Echo ' Quit '. $i. php_eol;break;}}? >

The code above simulates a 10-user concurrent execution of a business, each of which increases the value of NUM by 1, each user performs 10,000 operations, and the final value of NUM should be 100000.

To run this code, the value of NUM is the same as the value we expected:

Mysql> SELECT * from counter;+----+--------+| ID |  Num   |+----+--------+| 1 | 100000 |+----+--------+1 row in Set (0.00 sec)

There is no problem here because the single UPDATE statement operation is atomic, and regardless of how it is executed, the value of NUM will eventually be 100000. However, in many cases, the logic that we execute in our business process is usually queried and executed, not as simple as the self-increment above:

<?phpfunction dummy_business () {$conn = Mysqli_connect (' 127.0.0.1 ', ' public ', ' public ') or Die (Mysqli_error ()); mysqli_select_db ($conn, ' test '); for ($i = 0; $i < 10000; $i + +) {$rs = Mysqli_query ($conn, ' Select num from counter wher E id = 1 '); Mysqli_free_result ($rs); $row = Mysqli_fetch_array ($rs); $num = $row [0];mysqli_query ($conn, ' UPDATE counter SET num = '. $num. ' + 1 WHERE id = 1 ');} Mysqli_close ($conn);} for ($i = 0; $i <, $i + +) {$pid = Pcntl_fork (); if ($pid = =-1) {die (' Can not fork. ');} elseif (! $pid) {dummy_business ( Echo ' Quit '. $i. php_eol;break;}}? >

Changed the script, the original atomic operation update replaced the first query and then update, run again we found that because of the concurrency of the program did not follow our expectations:

Mysql> SELECT * FROM counter;+----+------+| ID |  Num |+----+------+| 1 | 21495|+----+------+1 row in Set (0.00 sec)

A particularly easy mistake for a starter programmer is to think that this is not an open transaction. Now we add a transaction to it:

<?phpfunction dummy_business () {$conn = Mysqli_connect (' 127.0.0.1 ', ' public ', ' public ') or Die (Mysqli_error ()); mysqli_select_db ($conn, ' test '); for ($i = 0; $i < 10000; $i + +) {mysqli_query ($conn, ' BEGIN '); $rs = Mysqli_query ($conn, ' SELECT num from counter WHERE id = 1 '); Mysqli_free_result ($rs); $row = Mysqli_fetch_array ($rs); $num = $row [0];mysqli_quer  Y ($conn, ' UPDATE counter SET num = '. $num. ' + 1 WHERE id = 1 '), if (Mysqli_errno ($conn)) {mysqli_query ($conn, ' ROLLBACK ');} else {mysqli_query ($conn, ' COMMIT ');}} Mysqli_close ($conn);} for ($i = 0; $i <, $i + +) {$pid = Pcntl_fork (); if ($pid = =-1) {die (' Can not fork. ');} elseif (! $pid) {dummy_business ( Echo ' Quit '. $i. php_eol;break;}}? >

Still failed to solve the problem:

Mysql> SELECT * FROM counter;+----+------+| ID |  Num |+----+------+| 1 | 16328|+----+------+1 row in Set (0.00 sec)

Note that database transactions maintain the acid nature of the transaction according to different levels of transaction isolation, which means that the transaction is not an open solution to all concurrency problems. Typically, there are four possible problems with concurrent operations:

• Update lost: One transaction update overwrites the update of another transaction, and here is the problem of missing the update.
• Dirty reads: One transaction reads uncommitted data from another transaction.
• Non-REPEATABLE READ: One transaction reads the same data two times and two reads inconsistent data.
• Phantom reads: One transaction reads a range of records two times, and the number of records read two times is inconsistent.

The database typically has four different levels of transaction isolation:

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

The default transaction isolation level for most databases is read Committed, while the transaction isolation level for MySQL is read repeatedly (Repeatable read). For missing updates, only the serialization (Serializable) level can be completely resolved. For high-performance systems, however, transaction isolation using serialization levels can lead to a dramatic drop in deadlock or performance. Therefore, it is necessary to use pessimistic lock and optimistic lock. In concurrent systems, pessimistic locks (pessimistic Locking) and optimistic locks (optimistic Locking) are two common locks:

• Pessimistic locks believe that the probability that someone else is accessing the data being changed is very high, so lock the data when the data starts to change until the change is complete. Pessimistic locks are typically implemented by a database (using SELECT ... For UPDATE statement).
• Optimistic locking believes that the probability that someone else is accessing the data being changed is very low, so the data will not be locked until the modification is ready to commit the changes to the database and released after the change is complete.

In the example above, we use pessimistic locks to achieve:

<?phpfunction dummy_business () {$conn = Mysqli_connect (' 127.0.0.1 ', ' public ', ' public ') or Die (Mysqli_error ()); mysqli_select_db ($conn, ' test '); for ($i = 0; $i < 10000; $i + +) {mysqli_query ($conn, ' BEGIN '); $rs = Mysqli_query ($conn, ' SELECT num from counter WHERE id = 1 for UPDATE '), if ($rs = = False | | Mysqli_errno ($conn)) {//ROLLBACK TRANSACTION mysqli_query ($conn, ' RO Llback ');//re-execute the operation $i--;continue;} Mysqli_free_result ($rs); $row = Mysqli_fetch_array ($rs), $num = $row [0];mysqli_query ($conn, ' UPDATE counter SET num = '). $ Num. ' + 1 WHERE id = 1 '), if (Mysqli_errno ($conn)) {mysqli_query ($conn, ' ROLLBACK '),} else {mysqli_query ($conn, ' COMMIT ');} }mysqli_close ($conn);} for ($i = 0; $i <, $i + +) {$pid = Pcntl_fork (); if ($pid = =-1) {die (' Can not fork. ');} elseif (! $pid) {dummy_business ( Echo ' Quit '. $i. php_eol;break;}}? >

As you can see, this business was executed correctly in the desired manner:

Mysql> SELECT * from counter;+----+--------+| ID |  Num   |+----+--------+| 1 | 100000 |+----+--------+1 row in Set (0.00 sec)

Because pessimistic locks start to lock at the start of a read, performance can become worse in the case of large concurrent accesses. For MySQL Inodb, finding data by specifying an explicit primary key is a single row lock, while a query-scoped or non-primary key operation locks the table. Next, let's look at how to use optimistic locking to solve this problem, first we add a column of fields to the counter table:

Mysql> SELECT * FROM counter;+----+------+---------+| ID | num  | version |+----+------+---------+|  1 | |    |+----+------+---------+1 row in Set (0.01 sec)

Here's how it's implemented:

<?phpfunction dummy_business () {$conn = Mysqli_connect (' 127.0.0.1 ', ' public ', ' public ') or Die (Mysqli_error ()); mysqli_select_db ($conn, ' test '); for ($i = 0; $i < 10000; $i + +) {mysqli_query ($conn, ' BEGIN '); $rs = Mysqli_query ($conn, ' SELECT num, version from counter WHERE id = 1 '); Mysqli_free_result ($rs); $row = Mysqli_fetch_array ($rs); $num = $row [0]; $ve  rsion = $row [1];mysqli_query ($conn, ' UPDATE counter SET num = '. $num. ' + 1, Version = version + 1 WHERE id = 1 and version = '. $version); $affectRow = Mysqli_affected_rows ($conn); if ($affectRow = = 0 | | Mysqli_errno ($conn)) {//ROLLBACK TRANSACTION Resubmit Mysqli_ Query ($conn, ' ROLLBACK '); $i--;continue;} else {mysqli_query ($conn, ' COMMIT ');}} Mysqli_close ($conn);} for ($i = 0; $i <, $i + +) {$pid = Pcntl_fork (); if ($pid = =-1) {die (' Can not fork. ');} elseif (! $pid) {dummy_business ( Echo ' Quit '. $i. php_eol;break;}}? >

This time, we also got the desired result:

Mysql> SELECT * from counter;+----+--------+---------+| ID | num    | version |+----+--------+---------+| 1  | 100000 | 100000  |+----+--------+---------+1 row in set (0.01 se C

Because optimistic locks are ultimately executed in the same way that atomic update, the performance is much better than pessimistic locking. In an environment where the doctrine ORM framework is used, doctrine native provides support for pessimistic and optimistic locks. Please refer to the manual for specific use: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/ Transactions-and-concurrency.html#locking-support

The Hibernate framework also provides support for both types of locks, which are not discussed here. In high-performance systems to deal with concurrency problems, limited by the backend database, no matter how the lock performance can not be effectively processed such as e-commerce seconds to buy the volume of business. You can use NoSQL databases, message queues, and so on to do business more efficiently.

Reference Articles

• Database Transaction ISOLATION LEVEL
• Description of the isolation level for MySQL database transactions
• Problems caused by database transaction concurrency
• Optimistic lock and pessimistic lock
• What does optimistic locking and pessimistic locking mean?

Concurrency problems with databases in PHP