MySQL about table_lock_wait and table_lock_immediate

MySQL Reading Notes about table_lock_wait and table_lock_immediate received a document from a fellow user a few days ago, which is MySQL High Performance 2 Reading Notes, 97 pages, 6 w words. After describing the knowledge point, I hope to discuss the problem with you if you have any questions. After reading it, I admire myself and feel that I cannot be so careful. In respect of the table, I promise to answer all the questions in it. If you cannot simply reply to a question, you can write it through a blog to facilitate discussion. Therefore, this series replies to questions in this document. Www.2cto.com server variables table_locks_immediate and table_locks_waited #? What is the proper proportion of them? Background table_locks_immediate indicates the number of queries that can obtain the lock immediately, table_locks_waited indicates the number of times that the lock cannot be obtained immediately, Session 1 Session 2 lock tables t1 read; (table_locks_immediate + 1) update t1 set y = 1 where id = 1; (locked and table_locks_waited + 1) in the preceding example, session 1 adds a table, but session 2 needs to be updated, an error occurred while obtaining the table lock. Vision www.2cto.com Session 1 Session 2 lock tables t1 write; (table_locks_immediate + 1) update t1 set y = 1 where id = 1; (locked but table_locks_waited remains unchanged) This example looks like a bug, session 1 locks the table. session 2 tries to update the table and is locked. However, table_locks_waited does not add 1, and table_locks_immediate does not change. The reason is that the metadata lock (MDL) introduced by 5.5 requires MDL for table access. In this example, session 1 has an exclusive MDL, so Session2 is locked at the stage of obtaining the MDL. Because MDL is used to obtain the table lock, the above two variables remain unchanged when session 2 is locked. Under what circumstances does www.2cto.com trigger table_locks_waited? From the above example, table_locks_waited is not easy to trigger after MDL is introduced. Unless the application actively performs lock tables t read. We use the concurrency pressure to execute update t1 set y = y + 1 where id = 1 and 5000 times each. If t1 is a MyISAM table and MyISAM is a table lock, table_locks_waited increases sharply under the concurrent pressure. In the InnoDB table, because it is a row lock, the logic for getting the table lock can be passed smoothly, so table_locks_waited remains unchanged. Table_locks_waited should return to the initial issue. If the database contains InnoDB tables, the value of table_locks_waited should be small after 5.5. When mysqldump exports a table, it will execute lock table, which may increase the value. In other cases, if the value is changed, the application actively locks the table, which is not required in the InnoDB table and needs to be modified by the application.