MySQL lock mechanism and PHP lock mechanism

Simulation preparation--how to simulate high concurrent access to a script: bin/ab.exe of the apache installation file can simulate the amount of concurrency -c - how much concurrency is simulated -n how many times in total http:// requests are requested for the script
For example: cmd: apache installation path/bin/ab.exe -c 10 -n 10 http://web.test.com/test.php

【Get to the point】
Locks in MYSQL:
Syntax:
LOCK TABLE table name 1 READ |WRITE, table name 2 READ|WRITE ............. [Lock table]
UNLOCK TABLES [Release table]

Read: read lock|shared lock: all Clients can only read this table but not write this table
Write: write lock|exclusive lock: All currently locked clients can operate this table, other clients can only block
Note: You can only operate during the process of locking the table If you want to operate a locked table, all tables to be operated must be locked!

File lock in PHP (the file is locked, not the table)
What is the relationship between the file locked and the table? : It has no relation at all. It is similar to a token. Whoever gets it will operate it. So the table is not locked at all.
When testing, as long as there is a file, it doesn’t matter what the name is.

Summary:
You should only use file locks in PHP in the project, and try to avoid table locks, because if the table is locked, then everything in the entire website related to this table All functions have been slowed down (for example: many users at the front desk have been placing orders, the product table mysql is locked, and other operations related to the product table have been blocked [cannot read the product table], because one function slows down the entire website. slow).

One of my projects is O2O takeout. 12-2pm and 6pm are the times when order concurrency is high. In this case, MySQL lock is obviously not considered, and the user experience is too poor. In fact, depending on actual needs, there is no need to design inventory for takeaways. Of course, in addition to the flash sale activity module, PHP file lock is still required.

Application scenarios:
1. When placing orders with high concurrency, locking is required when reducing inventory
2. When ordering and grabbing tickets with high concurrency, use

MySQL lock sample code:

Php code

/**

Simulated flash sale event - 100 items of goods

CREATE TABLE a

(

id int comment 'Simulate the quantity of 100 event items'

);

INSERT INTO a VALUES(100);

Imitate: Access this script with 10 concurrency! Use the ab.exe software that comes with apache

*/

error_reporting(0);

mysql_connect('localhost','root','admin123');

mysql_select_db('test'); Lock

mysql_query('LOCK TABLE a WRITE'); // Only one client can lock the table, other clients are blocked here

$rs = mysql_query('SELECT id FROM a');

$id = mysql_result ($rs, 0, 0);

if($id > 0)

{

 --$id;

mysql_query('UPDATE a SET id='.$id);

}

# mysql unlock

mysql_query('UNLOCK TABLES');

PHP file lock sample code:

Php code

/**

Simulated flash sale event - 100 items of goods

CREATE TABLE a

(

id int comment 'Simulate the quantity of 100 event items'

);

INSERT INTO a VALUES(100);

Imitate: Access this script with 10 concurrency! Use the ab.exe software that comes with apache

*/

error _reporting(0) ;

mysql_connect('localhost','root','admin123');

mysql_select_db('test');

# File lock in php

$fp = fopen('./a.lock', 'r'); // PHP's file lock has nothing to do with the table, just any file

flock($fp, LOCK_EX); // Exclusive lock

$rs = mysql_query('SELECT id FROM a ');

$id = mysql_result($rs, 0, 0);

if($id > 0)

{

--$id;

mysql_query('UPDATE a SET id ='. $id);

}

#php file lock, release lock

flock($fp, LOCK_UN);

fclose($fp);