Using lock mechanism to solve the concurrency problem of commodity table and Inventory table

Lock mechanism

Problem: When a script is accessed by a client is normal, but when multiple clients concurrently access, the results of this script will appear incorrect, this problem needs to use the lock mechanism to solve. In our site, the need to use the lock is the high concurrent orders when reducing the inventory amount of goods.

Like example 1:

There is an ID number inside a table :

Now write a script to manipulate this a -table, each visit to reduce the ID :

This foot uses AB to simulate The number of concurrent accesses of users and will find that the reduction is not ten:

。

Example 2: If you want to reduce inventory when placing orders at high concurrency, inventory will be problematic:

Before locking:

After locking:

There are two types of locking mechanisms : table Locks in MYSQL and file locks in PHP.

the lock in MYSQL

Syntax:LOCK table table name 1 read| WRITE, table name 2 read| Wrtie .....

UNLOCK TABLES;

Read: Reading lock (Shared lock): If the table is locked in this way, then all clients in the locked process will only read this table

Write: Lock (Exclusive lock): If the table is locked in this way, only the client that locks the table can manipulate the table, and the other clients will only be able to manipulate tables until the lock is released.

Note: Only locked tables can be manipulated during the lock table, and if you want to manipulate more than one table, you need to lock all the tables you want to manipulate.

file locks in PHP

PHP is locked by a file, so there is a file first.

Create a file first, the file name and extension does not matter.

File locks are also divided into exclusive lock (lock_ex) and shared (LOCK_SH) lock two kinds.

Think: What kind of lock should we use better?

For example, when placing an order, we want to lock the product table, if the number of people to place a lot, it will lead to the product table has been locked state, then the entire site to read the function of the product list will block the inability to read the product, the entire site slowed down.

And the file lock just locked a file that has nothing to do with the site, no impact on the site's other functions.

So the project should use a file lock. Unless a table in the project can only be used for this function, other features can be locked when the table is not available.

Extension: Stress test (ab.exe) software to simulate concurrent requests for a script.

Using lock mechanism to solve the concurrency problem of commodity table and Inventory table