PHP Using Mysql Lock to Solve High Concurrency
- 2021-11-01 02:30:07
- OfStack
Write before the use of file locks to deal with the problem of high concurrency, now we say another way to deal with the use of Mysql locks to solve the problem of high concurrency
Look at the consequences of concurrency when transactions are not utilized
Create Inventory Management Table
CREATE TABLE `storage` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`number` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
Create an order management table
CREATE TABLE `order` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`number` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1
Test code
$pdo = new PDO('mysql:host=127.0.0.1;port=3306; dbname=test','root','123456');
$sql="select `number` from storage where id=1 limit 1";
$res = $pdo->query($sql)->fetch();
$number = $res['number'];
if($number>0)
{
$sql ="insert into `order` VALUES (null,$number)";
$order_id = $pdo->query($sql);
if($order_id)
{
$sql="update storage set `number`=`number`-1 WHERE id=1";
$pdo->query($sql);
}
}
We preset the inventory to 10, and then perform the ab test to see the results
mysql> select * from storage
-> ;
+----+--------+
| id | number |
+----+--------+
| 1 | -2 |
+----+--------+
1 row in set (0.00 sec)
mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 22 | 10 |
| 23 | 10 |
| 24 | 8 |
| 25 | 8 |
| 26 | 7 |
| 27 | 6 |
| 28 | 4 |
| 29 | 3 |
| 30 | 2 |
| 31 | 2 |
| 32 | 2 |
| 33 | 1 |
+----+--------+
12 rows in set (0.00 sec)
A total of 12 orders were obtained, and the inventory in the inventory table was reduced to-2, which obviously does not conform to the actual logic;
Let's look at using database row locks to solve this problem
Modify the code as follows
$pdo = new PDO('mysql:host=127.0.0.1;port=3306; dbname=test','root','123456');
$pdo->beginTransaction();// Open a transaction
$sql="select `number` from storage where id=1 for UPDATE ";// Utilization for update Open the row lock
$res = $pdo->query($sql)->fetch();
$number = $res['number'];
if($number>0)
{
$sql ="insert into `order` VALUES (null,$number)";
$order_id = $pdo->query($sql);
if($order_id)
{
$sql="update storage set `number`=`number`-1 WHERE id=1";
if($pdo->query($sql))
{
$pdo->commit();// Commit transaction
}
else
{
$pdo->rollBack();// Rollback
}
}
else
{
$pdo->rollBack();// Rollback
}
}
View the results
mysql> select * from storage;
+----+--------+
| id | number |
+----+--------+
| 1 | 0 |
+----+------
--+
1 row in set (0.00 sec)
mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 1 | 10 |
| 2 | 9 |
| 3 | 8 |
| 4 | 7 |
| 5 | 6 |
| 6 | 5 |
| 7 | 4 |
| 8 | 3 |
| 9 | 2 |
| 10 | 1 |
+----+--------+
10 rows in set (0.00 sec)
Obviously, after using mysql lock, the inventory is effectively controlled, which solves the logical problems caused by concurrency in the first paragraph of the code