Mysql lock tables and unlock tables (lock table/unlock) usage Summary

In mysql, we used lock and unlock to lock tables and unlock tables. Today, I am going to give my friends some experience in using lock tables and unlock tables.

Usage of php mysql lock tables

Mysql table lock tables feels like a closed space.

When mysql finds the lock tables command, it will bring the table with the lock Mark into the closed space until the unlock tables command or thread ends.

When entering the closed space, only the table marked by the lock can be used in it, and other tables cannot be used.

The lock tag is divided into read and write. The difference between the two locks is as follows:

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

// Set table1 as the read lock, table2 as the write lock, and table3 as the read lock.

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

// Enter the closed space when running the command here.

1. Table 1 only allows [Everyone] to read data. If [outside space] needs to be written or updated, it will wait for [exit of Space]. mysql will report an error if [in space] needs to be written or updated.
2. Table 2 only supports read/write updates in [space]. If you want to write or update data outside [space], you must wait for [space exit].
3. Table 3 only allows [Everyone] to read data. If [outside space] needs to be written or updated, you must wait for [exit of Space]. mysql reports an error if [in space] needs to be written or updated.

----------------------------------------------------------------------
// Exit the closed space and release all table locks when the execution ends.

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

When the current thread is disabled, it automatically exits from the closed space and releases all table locks, whether or not it executes the unlock tables

The above pile of things is messy. Let's look at an instance.

An example is shown in A specific place. The specific description is similar to the following: the store now has only one item in stock, and then A and B order it online, A and B are almost at the same time (which may be A few milliseconds, A is A little faster than B.

It is obvious that only A can successfully place an order, B will receive A prompt of insufficient inventory, but as the page (or called A script program) placed on the server) how do we deal with this problem? Or let me release a piece of code first.

The Code is as follows:	Copy code
$ 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! '; }

This part of the code is correctly written except for the lack of comments. Of course, $ db is a class for database operations. I only encapsulate most of the methods, and the logic here is also very obvious.

First, obtain the inventory number of the stuff with id 1 to see if it is 0. If it is 0, the Order will fail. If it is greater than 0, the inventory will be reduced by 1 and then the prompt is OK. There are indeed no errors, and the logic is correct. If the request is generated one by one, there is no problem, but when some concurrency situations (paperen does not want to use such a professional term, it is actually the case of the above example, when multiple requests are generated within a period of time when the difference is not obvious, some non-expires headers may occur. You think, is there A possibility that A has just issued A request, and B has issued another request before the script is processed to update, so there is still 1 Inventory, because update of A has not been executed yet, $ number should not be less than 0. After this time, B has placed an order, so the inventory has changed to-1 (assuming there was only one update ), it is indeed a ridiculous and funny result.

The cause of the problem is obvious, that is, the consideration of the concurrency is ignored. The order should be a queue method, that is, first come first served, that is to say, to execute this order operation, we need to queue up. The previous one can subscribe first and then the latter can subscribe. Of course, when the latter is subscribe, We need to judge the inventory quantity. So how can we solve this problem? At the program level, it seems that there is really no way to solve this problem (paperen did not expect the solution on the code, but could leave a comment if there is a train of thought ), so I mentioned the concept of table locking here. You think, the above problem is caused by the fact that the sequence of a select number is not controlled (or you can say so ), because you allow B to query the inventory before A executes the update, of course, the result is still 1. At least you have to wait for A to update the inventory before allowing other people to perform any operations, that is, a queuing operation is performed on the goods table to lock the goods table.

Speaking of this, please do not think about the depth of the lock table, in fact, it is an SQL

Lock table 'table' [READ | WRITE]

Unlock

Unlock tables;

Reference professional description is

Lock tables is the table locked by the current thread. Unlock tables releases any lock held by the current thread. When the thread issues another lock tables, or when the server connection is closed, all TABLES locked by the current thread will be automatically unlocked.

If a thread acquires a READ lock on a table, the thread and all other threads can only READ from the table. If a thread acquires a WRITE lock on a table, only the lock-holding thread reads or writes the table, and other threads are blocked.

There is already a queue taste, right? No, so the solution is very simple. Lock before select and unlock after the logic code is executed. Maybe someone has a question, that is, if the thread breaks down after the table is locked, will it keep locking the table, this is indeed possible, but since you have thought of it, the Database Designer must have taken it into consideration and can tell you some information about unlock: When the thread sends another lock tables, or when the connection to the server is closed, all tables locked by the current thread will be automatically unlocked. You can rest assured.

Okay. Let's take a 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 ();

I added only two lines of code, but I cannot say that because paperen modified my database class and 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 Name * @ Param int $ type Read lock 1 or write lock 2 */ Public function lock ($ table, $ type = 1 ){ $ Type = ($ type = 1 )? 'Read': 'write '; $ This-> query ("lock table '$ table' $ type "); } /** * Unlock */ Public function unlock (){ $ This-> query ("unlock tables "); }

You can consider the lock, because the second parameter does not seem quite comfortable. Hmm ~ How can we test it? Paperen uses 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 horrible tool in the hands of kind people is a friendly tool.

You need to create two threads to send two requests to the server.

The specific configuration of paperen is not mentioned here. I have exported a plan file. You can try to open it to see how paperen is tested. Http://iamlze.cn/demo/locktable/locktable.jmx

Save it and import it. You must adjust the path of your local test. Finally, press ctrl + R (run) to view the response information of the request in the result tree under the thread.

First, test the no-lock table (that is, the Code for the first no-lock and unlock operations) to see the results of the two threads.

All are OK ~~ View the database again

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

Okay, you don't need to read the data table. The result is obvious. Before the previous request is completed, all the subsequent requests will be waiting until the previous request is complete, that is, the taste of the queue.

To be honest, mysql transactions also need to be studied. paperen's understanding about the lock table is generated during the process of viewing the transaction, in the advanced application process, this technology is more important. More rigorous logic code and rigorous database management can further ensure the authenticity and accuracy of data. This is a real sense of future.