MySQL lock mechanism and PHP lock mechanism

Source: Internet
Author: User
Tags flock fread

The lock in MySQL:
Grammar:
LOCK Table table Name 1 read| WRITE, table name 2 read| WRITE ... "Lock-up table" ... "..".
UNLOCK TABLES "Release table"

READ: Reading Lock | Shared Lock: All clients can only read this table and cannot write this table
Write: Lock | Lock: All currently locked clients can manipulate the table, and other clients will only block
Note: Only locked tables can be manipulated during the lock table, and if you want to manipulate other tables, you must lock all the tables you want to manipulate!

Application Scenarios:
1. When the high concurrent orders, reduce inventory to be locked
2. High concurrent grab orders, to use when robbing tickets

error_reporting(0);mysql_connect(' localhost ', ' root ', ' admin123 ');mysql_select_db(' Test '); #MySQL Lockmysql_query(' LOCK TABLE a WRITE ');//only one client can lock the table, and the other clients block the$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 unlockmysql_query(' UNLOCK TABLES ');

PHP file Lock:

BOOL Flock (int handle, int operation [, int &wouldblock]);
The handle of the flock () operation must be a file pointer that has already been opened. Operation can be one of the following values:

      1. To get a shared lock (read program), set operation to Lock_sh (PHP 4.0.1 Previous version set to 1)
      2. To obtain an exclusive lock (write program), set operation to LOCK_EX (2 in previous versions of PHP 4.0.1)
      3. To release a lock (whether shared or exclusive), set operation to Lock_un (3 in previous versions of PHP 4.0.1)
      4. If you don't want flock () to block when locked, Operation plus LOCK_NB (set to 4 in previous versions of PHP 4.0.1)

Build two files
(1) a.php

$file= "Temp.txt"; $fp=fopen($file, ' W '); if(Flock($fp,lock_ex)) {         fwrite($fp, "abc\n"); Sleep(10); fwrite($fp, "123\n"); Flock($fp,Lock_un); }    fclose($fp);

(2) b.php

$file = "Temp.txt";     $fp fopen ($file , ' R ');     Echo fread ($fp , +);     fclose ($fp);   

After running a.php, run b.php immediately and you can see the output:
Abc
After a.php run b.php, you can see the output:
Abc
123
Obviously, when the a.php writes the file The data is too big, causes the time to be long, then b.php reads the data to be incomplete

Modify the b.php to:

$file= "Temp.txt"; $fp=fopen($file, ' R '); if(Flock($fp,lock_ex)) {        Echo fread($fp, 100); Flock($fp,Lock_un); } Else{        Echo"Lock file failed...\n"; }    fclose($fp);

After running a.php, run b.php immediately, and you can see that b.php will wait until the a.php is complete (i.e., 10 seconds later) to display:
Abc
123
The read data is complete, but the time is too long for him to wait for the write lock to release.

Modify the b.php to:

$file= "Temp.txt"; $fp=fopen($file, ' R '); if(Flock($fp, Lock_sh |lock_nb)) {        Echo fread($fp, 100); Flock($fp,Lock_un); } Else{        Echo"Lock file failed...\n"; }    fclose($fp);

After running a.php, run b.php immediately and you can see the output:
Lock file failed ...
Prove that you can return the lock file failure status, not as long as you want to.

Conclusion:
When file caching is recommended, select the relevant lock, otherwise it may result in incomplete reading data or write data repeatedly.
File_get_contents seems to choose not to lock, do not know what his default lock, anyway, and the output is not locked, is incomplete data.
I want to do the file cache, so just need to know if there is a write lock exists, and some of the database can be checked.

There are two types of file locks: shared and exclusive, that is, read lock (LOCK_SH) and write lock (LOCK_EX)
File locks are typically used in this way:

$fp fopen ("filename", "a");    Flock ($fpdie ("Lock Error")   $strfread($fp, 1024x768);    flock($fp, lock_un);    fclose ($fp

Note that after fwrite, the file is updated immediately, instead of waiting for fwrite and then fclose after the file is updated, this can be checked by reading this file before fclose after Fwrite

But when to use LOCK_EX when to use Lock_sh?

when reading:
If you do not want dirty data to appear, it is best to use lock_sh shared locks. Here are three things to consider:
1. If the reader does not have a shared lock, then other programs to write (whether the write is locking or unlocked) will immediately write success. If you just read half of it and then write it to another program, then half of the reading is probably half right (the first half is a modified one and the last half is a modified one).
2. If you add a shared lock when reading (because it is just read, there is no need to use an exclusive lock), this time, the other program began to write, the program does not use the lock, then write the program will directly modify the file, will also lead to the same problem
3. Ideally, when reading locking (LOCK_SH), when writing also to lock (LOCK_EX), so that the program will wait until the completion of the program before the operation, without any hasty operation of the situation

When writing:
If multiple write programs do not lock the file at the same time, then the final data may be part of a program written, part of the B program is written
If it was locked when it was written, and there were other programs to read, what would he read?
1. If the reader does not request a shared lock, he will read the dirty data. For example, write a program to write a,b,c three parts, write a, this time read a, continue to write B, at this time read the AB, and then write C, this time read ABC.
2. If the reader has previously applied for a shared lock, the read program will wait until the program writes out the ABC and releases the lock before reading.

MySQL lock mechanism and PHP lock mechanism

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.