Shulou(Shulou.com)06/03 Report--

This article mainly explains "there are several kinds of table-level locks in mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "there are several table-level locks in mysql".

1. There are two kinds of table-level locks in MySQL: one is table lock, the other is metadata lock (meta data lock,MDL).

The syntax of a table lock is lock tables. Read/write . The lock can be released actively with unlock tables, or automatically when the client is disconnected. Lock tables syntax not only restricts the reading and writing of other threads, but also defines the next operation object of this thread.

If the statement lock tables T1 read,t2 wirte; is executed in one thread A, the statements of other threads writing T1 and reading and writing T2 will be blocked. At the same time, thread A can only read T1 and write T2 before executing unlock tables. It is not even allowed to write T1.

Another table-level lock is MDL.

2. MDL does not need to be displayed and added automatically when visiting a watch.

The function of MDL is to ensure the correctness of reading and writing. If a query is passing through the data in a table and another thread changes the structure of the table and deletes a column during execution, the results obtained by the query thread must not match the structure of the table.

MDL introduces MySQL5.5 version, which adds MDL read lock when the table is added, deleted and changed, and MDL write lock when the table structure is changed.

Read locks are not mutually exclusive, so multiple threads can add, delete and change a table at the same time.

Read-write locks and write locks are mutually exclusive to ensure the security of changing table structure operations. Therefore, if there are two threads, one should add a field to a table, and one of them should wait until the other completes execution.

To add fields to a table, or to modify fields, or to add indexes, you need to scan the data of the entire table. Special care should be taken when operating large watches so as not to affect online services.

At this point, I believe you have a deeper understanding of "there are several table-level locks in mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.