Summary of use of lock tables and unlock tables (lock table/unlock) in MySQL

How to use PHP MySQL lock tables

MySQL table lock lock tables feel like a closed space

When MySQL discovers the lock tables command, it takes the table with the lock tag into the enclosing space until the unlock tables command or thread ends, and the closed space is closed.

When you enter a closed space, only the lock-marked table can be used inside, and other tables cannot be used.

The lock marks are divided into read and write under the difference between the two locks

--------------------------------------------------------------------

If the Table1 is set to read lock, Table2 is set to write lock, Table3 is set to read lock

Lock tables [table1] read,[table2] write,[table3] read;
----------------------------------------------------------------------

When performing here, enter the enclosed space.

1. table1 only allows [everyone] to read, [space outside] if you need to write, update to wait [space to exit],[space] if required to write, update will cause MySQL error.
2. Table2 only allow [in space] read and write updates, [space outside] if you need to write, update to wait [space exit].
3. Table3 only allows [everyone] to read, [space outside] if you need to write, update to wait [space to exit],[space] if required to write, update will cause MySQL error.

----------------------------------------------------------------------
When performing here, exit the closed space and release all table locks

Unlock tables
----------------------------------------------------------------------

When the current thread shuts down, automatically exits the enclosing space, releasing all the table locks, whether or not the unlock tables are executed

A lot of things on the top feel very messy, let's take a look at the example below.

There is an example of a place that looks like this: The store now has only 1 items in stock, then A and B are online, A and B are almost simultaneous (perhaps a few milliseconds, a little bit faster than B).

It is clear that only a can succeed in a single order, and B will receive a hint of insufficient inventory, but how do we deal with the problem as the page (or script) that is placed on the server side? Or I'll give you a piece of code first.

The

code is as follows	copy code
$sql = ' Select number from goods where id=1 ';     $numbe R = intval ($db->result ($db->query ($sql), 0));     if ($number > 0) {     sleep (2);     $sql = "Update g Oods set number=number-1 WHERE id = 1 ";     if ($db->query ($sql)) {     echo ' ok! Here you are! ';    } else {     echo ' sorry! Something Go wrong! Try it again. ';    }    } else {     echo ' No more!you are so late! ';    }

This part of the code, in addition to the lack of a certain comment is written correctly, of course, $DB is a database operation of the class, I just encapsulate most of the methods, the logic is also very obvious.

First get ID 1 inventory of this dongdong, see if it is 0, if the order of 0 is not successful, if greater than 0 will inventory minus 1 then prompted OK. There's really no mistake, and the logic is right. If the request is generated one after another, then there is no problem, but when some concurrent situation (Paperen also do not want to use this professional noun, in fact, the case of the above example, in the absence of a significant difference in time to produce multiple requests) may appear some nonsense problems. You want to, is it possible that there is a situation, a just issued a request, the script processed to update before B issued a request, then the inventory is still 1, because A's update has not been implemented, so $number not less than 0, this is over, B also under a single, So the inventory turned to-1 (assuming there were only 1), and it was a ridiculous and rather funny result.

The obvious reason for the problem is that it ignores the concurrency considerations, the processing should be a kind of queue, that is, first come first, that is, in the execution of the next action is to be queued, the front of the first order then the latter can be set, of course, when the latter under the order before judging the quantity of inventory. So how do we solve this problem, at the procedural level there seems to be really no way to solve this problem (Paperen can not think of the code on the solution, there are ideas can leave a message), so this refers to the concept of the lock table, you want to Ah, the above appears in the root of the problem is not control a select The order of number (or so to speak), because before a executes update you allow B to query the inventory, of course, the result is 1, at least wait for a to update the inventory before allowing anyone else's operation, that is, the goods table for a queued operation, the goods table to lock.

Speaking of which, please do not think that the lock table is so profound, in fact it is a SQL

LOCK table ' table ' [read| WRITE]

Unlock

UNLOCK TABLES;

References to professional descriptions are

Lock tables locks the table for the current thread. UNLOCK Tables releases any locks held by the current thread. When a thread issues another lock table, or when the server's connection is closed, all tables that are locked by the current thread are automatically unlocked.

If a thread obtains a read lock on a table, the thread and all other threads can only read from the table. If a thread obtains a write lock on a table, then only the thread holding the lock is read or write, and other threads are blocked.

There is already a taste of the queue, yes No, so the solution is simple, lock it before the Select, and then unlock it after executing the logical code behind it. Perhaps there is no one to have a doubt, is if the thread is broken when the table is locked. So it's possible to keep the table locked. But since you think about it, the database designer must have taken into account that you can tell you something about unlock: When the thread issues another LOCK tables, or when a connection to the server is turned off, any table locked by the current thread is automatically unlocked. That's a relief.

OK, look at the improved code.

The code is as follows	Copy Code
$db->lock (' goods ', 2); $sql = "Select number from goods where id=1"; $number = Intval ($db->result ($db->query ($sql), 0)); if ($number > 0) { Sleep (2); $sql = "Update goods set number=number-1 where id = 1"; if ($db->query ($sql)) { Echo ' ok! Here you are! '; } else { Echo ' sorry! Something Go wrong! Try it again. '; } } else { Echo ' No more!you are so late! '; } $db->unlock ();

Only two lines of code are added, but I can't say that, because paperen I modified my database class, added two methods lock and unlock, in fact, these two methods are also very simple.

The

code is as follows	copy code
/**     * Lock Table     * @param string $table table name     * @param int $type read lock 1 or write lock 2     */     Public Function Lock ($ta ble, $type = 1) {     $type = ($type = = 1)? ' READ ': ' WRITE ';     $this->query ("LOCK TABLE ' $table ' $type");    }         /**     * Unlock     */ & nbsp;   public Function Unlock () {     $this->query ("Unlock TABLES");     }

About lock yourself can reconsider, because the second parameter does not look very comfortable. Well, how about the test? Paperen using JMeter for test results

About JMeter can be downloaded here in http://jakarta.apache.org/site/downloads/downloads_jmeter.cgi, in the hands of evil people can be a tool of terror in the hands of good people is a friendly tool.

You need to create two threads, in fact, send two requests to the server.

Specific configuration Paperen This does not say, I exported a plan file, we can try to open can see how paperen is tested. Http://iamlze.cn/demo/locktable/locktable.jmx

Save and then import must adjust your local test path, the last Ctrl+r (run), the thread under the view of the result tree has requested response information.

First Test the situation without locking the table (the code that starts without lock and unlock operations) to see the results of two threads.

ok~~, look at the database again.

Then change the number back to 1, then lock and unlock, lock table operation Plus, and then run.

Well, the datasheet doesn't have to look, the results are already obvious, before the previous request to the table operation completed, then those requests are waiting until the previous request completed before the operation, that is, the taste of the queue.

To be honest, MySQL's business also needs a little Kung fu study, paperen about the lock table in the process of viewing the transaction generated, in the advanced application process of this technology is more important, more rigorous logical code and rigorous database management to further ensure the true and accurate data. It's really after knowing.