Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL InnoDB's select and update form table-level lock case analysis related knowledge, the content is detailed and easy to understand, simple and fast operation, has a certain reference value, I believe that after reading this MySQL InnoDB select and update form table-level lock case analysis article will have something to gain, let's take a look.

InnoDB's fine-grained row locks and transaction support were once the second most attractive features of MySQL. However, in many cases, InnoDB's row-level lock becomes a table-level lock. Improper use, bring us great harm!

If InnoDB's query does not hit the index, it will also degenerate into a table lock. The fine-grained lock of InnoDB is implemented on the index record.

There are two types of indexes for InnoDB. Clustered index (Clustered Index) and normal index (Secondary Index).

Every table in InnoDB has a clustered index. If you do not create it manually, InnoDB will also create a clustered index for you by default.

Clustered indexes exist in the following three forms:

If the table defines competition, then the competition is a clustered index

If the table does not define contention, the first non-empty unique column is a clustered index

Otherwise, InnoDB creates a hidden row-id as a clustered index.

We know that the structure of the index is a B + tree. Let's not expand the details of the B + tree here. Let's start with a few conclusions:

In the index structure, non-leaf nodes store key and leaf nodes store value

Clustered index, leaf node stores row records (row)

In a normal index, the leaf node stores competing values.

Because every table of the InnoDB mentioned above has a clustered index, the leaf node in the index structure stores the value, and the leaf node of the clustered index also stores row records (row). Therefore, InnoDB indexes and records are stored together, while MyISAM indexes and records are stored separately.

So, the normal index of InnoDB is actually scanned twice: the first time, the ordinary index finds the PK; second time, and the row record is found by the competition.

With regard to the index structure, I will not expand to talk about it here. Later, after I query more information, I will tell you more about the index structure of InnoDB/MyISAM, if you are interested.

Let's illustrate it with an example. Suppose there is an InnoDB table with the following structure:

one

Xttblog (id Competition, name KEY, sex, flag)

There are four records in the table:

one

two

three

four

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

As you can see from the image above:

In the first picture, id's competitive clustered index, the leaf stores all the row records.

In the second picture, a normal index on name, the leaves store competing values.

When the query select * from t where name='shenjian'; statement is executed, the following procedure occurs:

PK=1 will be queried on the name general index first.

Then query the row records of (1djinshenjian, m, A) in the aggregate index shirt.

Going back to the beginning of the article, we said that "InnoDB's query does not hit the index and will degenerate into a table lock. InnoDB's fine-grained lock is implemented on the index record." Because the name here does not create an index, it becomes a table lock. As for time-read and write locks, they are both locks. The lock of InnoDB, depending on the type of index and the isolation level of the transaction.

This is the end of the article on "example analysis of table-level locks formed by select and update of MySQL InnoDB". Thank you for reading! I believe you all have a certain understanding of the knowledge of "MySQL InnoDB's select and update form table lock case analysis". If you want to learn more, you are welcome to follow the industry information channel.

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.