In our e-commerce system , There's a lot of demand , Never finish . At the same time, some of them often face some technical problems , such as , One this time SQL Statement makes the whole database hang up .

Due to the recent performance explosion , The design of a table in the system is not reasonable , This leads to a lot of redundant data . therefore , After some discussion , We want to move some of the redundant data out . This is a little different from what we said before . In front of us is the whole library migration , Now for single table migration .

For single table read-only , We use table lock Syntax lock tables … read/writ…. The usage is as follows :


After using the above statement , When we're done , Can pass unlock tables To unlock or , It can also be released automatically when the client is disconnected .


Use lock tables xttblog read,writ xttblog2 What we need to pay attention to in grammar is , The current session can only xttblog surface read , You can't write. Other conversations cannot be written xttblog surface , Reading and writing xttblog2 surface . The current session cannot read,write, And before unlocking , The current conversation can only read xttblog, Reading and writing xttblog2.

such BT The limitation of , Caused an executing transaction to be blocked . Light blocking is good , Because transactions have a time-out mechanism .

But the thread pool configuration has a big Bug, The timeout will be retried . The current table is locked , After limiting read-only , Add, delete and modify requests are blocked . So there are threads blocked all the time , And then there are retries .

Originally, this table was designed with more redundant data , The operation is frequent . Ok now , A lot of threads are blocked . So the whole production warehouse went down .

therefore , Sometimes there is DBA It's a great happiness . Remember to operate the production library during peak business hours , Dead of night is the time for data migration .

also , Encounter such problems , Modify the business code first . Control from the source , Don't just think about data migration . There are advantages and disadvantages to this mechanism , Use it reasonably .

Last , Combined with the previous article to summarize , Global lock Flush tables with read lock(FTWRL) And lock table  table_name read/write The difference between locks .

  • Flush tables with read lock: A global read lock , Unlock :unlock tables .

  • lock table  table_name read/write : Specify table lock , Unlock :unlock table table_name /unlock tables table_names Only one lock at a time .

  • lock tables table read : No conversation can DML read-locked surface .

  • lock tables table_name write : In addition to holding lock sessions , No session can access write-locked surface .

One session You can only get and release locks for yourself , It's not for anything else session Get the lock , Nor can it be released by other session Keep the lock . Hold the lock session Can read the locked table , But it can't be written , Tables that are not locked cannot be read or written . Multiple session You can get... For the same table at the same time READ lock . At present session You can only read explicit access READ Locked table , The table cannot be updated , Also can't read and update others without getting READ Locked table . Other session Can read not in the current session Get... Clearly in READ Locked table , Of course, it can also be read READ Locked table . But the update is in other session I got READ The locked table will be blocked , Other tables can be updated normally .

The code on the Internet is good Copy, do demo The demonstration was very smooth , It's a car accident when it comes to production . The main reason is that the understanding is too one-sided , Don't understand , Easy to roll over !