Summary of Problems Caused by php Multi process Simulation of Concurrent Transactions

Preface

This article introduces some problems about php multi-process simulation concurrent transactions through example code, and shares them for your reference and study. The following words are not much to say, let's take a look at the detailed introduction

Table

drop table if exists `test`;
create table if not exists `test` (
 id int not null auto_increment , 
 count int default 0 , 
 primary key `id` (`id`)
) engine=innodb character set utf8mb4 collate = utf8mb4_bin comment ' Test table ';

insert into test (`count`) values (100);

php code

//  Number of processes 
$pro_count = 100;
$pids = [];
for ($i = 0; $i < $pro_count; ++$i)
{
 $pid = pcntl_fork();
 if ($pid < 0) {
  //  Main process 
  throw new Exception(' Failed to create child process : ' . $i);
 } else if ($pid > 0) {
  //  Main process 
  $pids[] = $pid;
 } else {
  //  Child process 
  try {
   $pdo = new PDO(...);
   $pdo->beginTransaction();
   $stmt = $pdo->query('select `count` from test');
   $count = $stmt->fetch(PDO::FETCH_ASSOC)['count'];
   $count = intval($count);
   if ($count > 0) {
    $count--;
    $pdo->query('update test set `count` = ' . $count . ' where id = 2');
   }
   $pdo->commit();
  } catch(Exception $e) {
   $pdo->rollBack(); 
   throw $e;
  }
  //  Exit child process 
  exit;
 }
}

Expected result

Expect the count field to decrease by more than 100 and become negative! That is, reduce more!

Actual results

In the case of concurrent 200, the results after multiple runs are as follows:

1. count = 65
2. count = 75
3. count = 55
4. count = 84
...

It is far from the expected result! Why is there such a phenomenon?

Explanation

First of all, clear the current program running environment and concurrency scenario. What is concurrency, almost simultaneous execution, is called concurrency. The specific explanation is as follows:

Processes get updates
1-40 Create and run 100 99 simultaneously
41-80 simultaneously create and run 99 98
81-100 simultaneously create and run 98 97

As explained in line 1 above, child processes 1-40 are created and run almost at the same time:

Process 1 gets count = 100, updates 99
Process 2 gets count = 100, updates 99
...
Process 40 gets count = 100, updates 99

Therefore, in fact, these processes all do the operation of 1, which is not as expected: Process 1 gets count=100 and updates 99; Process 2 obtains the result count=99 after the update of Process 1, and updates 98; ...; Process 99 gets the updated result of Process 98 count=1, updates 0
, the phenomenon is less reduced! !

Conclusion

With the procedure implemented by the above approach, inventory is always > = 0.

Question

How to design the program to simulate the over-inventory scenario?

Still using the above code, put the following code:

if ($count > 0) {
 $count--;
 $pdo->query('update test set `count` = ' . $count . ' where id = 2');
}

Modify it to read as follows:

if ($count > 0) {
 $pdo->query('update test set `count` = `count` - 1 where id = 2');
}

As a result, there will be overstock! !

Inventory is 100, concurrent 200, and the final inventory is reduced to-63. Why is this happening? The following describes the specific process of running the program

Process 1 gets inventory 100, updates 99
Process 2 Get Inventory 100, Update 98 (99-1)
Process 3 Get Inventory 100, Update 97 (98-1)
....
Process 168 acquires Inventory 1, updates 0 (1-1)
Process 169 acquires Inventory 1, Update-1 (0-1)
Process 170 acquires Inventory 1, updates-2 (-1-1)
....
Process 200 acquires inventory 1, updates-63 (-62-1)

Now it seems to be very embarrassing, which is actually caused by the following statement:

$pdo->query('update test set `count` = `count` - 1 where id = 2');

Process 1, referred to as a;, is described in detail here; Process 2, referred to as b for short, their specific execution sequence:

1. a query inventory 100
2. b query inventory 100
3. a updated the inventory to 99 (100-1), which should be understood in seconds
4. b updated inventory to 98 (99-1)
-When b performs the update operation, it gets the updated inventory of a!
-Why is this happening? Because the update statement is ` update test set count = count-1 where id = 2 `

Summarize