MySQL lock mechanism and PHP file lock processing high concurrency simple idea

Examples of items to buy:

① Gets the quantity of inventory from the database.

② Check if the quantity of stock is sufficient.

The quantity of ③ inventory minus the amount purchased by the buyer (in the case of each user buying one).

④ final purchase completed.

Just a few lines of logical code in the case of concurrency will be problematic, you can imagine.

It is not tested here for the time being, and the following will give the test results for concurrent processing.

To create a table:

CREATE TABLE ' Warehouse ' (  ' id ' int (one) not NULL auto_increment COMMENT ' id ',  ' stock ' int (one-by-one) not null DEFAULT ' 0 ' COMMENT ' inventory ',  PRIMARY KEY (' id ')) engine=myisam auto_increment=3 DEFAULT Charset=utf8

The first scenario, using the MySQL lock (not related to the table engine).

Shared Lock: Everyone can read a resource, but only the person who acquires the lock can operate;

Exclusive lock: Only get the object can manipulate the resources, others can not operate, read can not.

Grammar:

Lock Table A read,b write,c read,d WRITE; (multiple tables can be locked, only locked tables are manipulated during the lock table, and other tables cannot be manipulated). UNLOCK TABLES; (release table). @ $mysql = mysql_connect (' localhost ', ' root ', '), mysql_query (' Set names UTF8 '); mysql_select_db (' Test '); mysql_query (' LOCK TABLE ' warehouse ' WRITE ');  After the lock table only one person can operate at the same time, that is, only one person can get to the lock $sql = ' SELECT ' stock ' from warehouse '; $res = mysql_query ($sql); $row = Mysql_fetch_ Array ($res), $stock = $row [0];if ($stock < 1) {die    (' insufficient inventory ');} else{    $new _stock = intval ($stock-1);    mysql_query (' UPDATE warehouse SET ' stock ' = '. $new _stock);    mysql_query (' UPDATE TABLES ');}

The disadvantage of the lock table is that there will be blocking, and if you lock multiple tables at the same time, it will also affect the loading of the entire site related tables.

The second option is to use the PHP file lock.

Features: When calling flock lock a file, if the lock is not acquired, it returns false directly, and no blocking occurs.

Exclusive lock: Flock ($FP, LOCK_EX);

Shared Lock: Flock ($FP, lock_sh);

Release Lock: Flock ($FP, lock_un);

@ $mysql = mysql_connect (' localhost ', ' root ', ');

mysql_query (' Set names UTF8 '); mysql_select_db (' Test '); $fp = fopen ('./lock.txt ', ' R '); $try = ten;  Declares a variable that represents the number of times to get, preventing a dead loop do{    $lock = Flock ($fp, lock_ex);    if (! $lock)        usleep (5000);//If you do not get to the lock, release the CPU, rest 5000 milliseconds}while (! $lock &&--$try >=0);    if ($lock) {    $sql = ' SELECT ' stock ' from warehouse ';    $res = mysql_query ($sql);    $row = Mysql_fetch_array ($res);    $stock = $row [0];    if ($stock < 1) {die        (' insufficient inventory ');    } else{        $new _stock = intval ($stock-1);        mysql_query (' UPDATE warehouse SET ' stock ' = '. $new _stock);    }    Flock ($FP, lock_un);    Fclose ($FP);} Else{die (' System busy! ‘); }

In the third scenario, a simple SQL statement can avoid a negative warehouse.

@ $mysql = mysql_connect (' localhost ', ' root ', '), mysql_query (' Set names UTF8 '); mysql_select_db (' Test '); mysql_query (' UPDATE Warehouse SET ' stock ' = ' stock '-1 WHERE ' stock ' > 0 ');  Can avoid negative stock

　　

MySQL lock mechanism and PHP file lock processing High concurrency simple idea