preface

Before we understand the internal structure and execution process of MySQL innoDB through query statement, update statement, then we talk about the content of index, by introducing innoDB logical storage structure and B+Tree model deduce to introduce the concept of index. It also explains how we can optimize MySQL performance through our understanding of indexes. To the back, we came to the chapter of the transaction, we respectively about the four major characteristic of the transaction and the isolation level, as for the implementation of isolation level, we there are two kinds of implementation scheme, and LBCC MVCC, in the last chapter, we focuses on MVCC related introduction, on the lock, and we look at the chapter.

As usual, first on the plane ticket:

1. How is a query executed
2. How is an update statement executed
3. Innodb logical storage structure;
4. MySQL > index data model (B+Tree)
5. MySQL related (4) – Performance optimization key index
6. MySQL – Transaction features and isolation levels
7. MySQL > Implement transaction Isolation level (MVCC)

The brain map mentioned above is as follows. If you want a complete hd picture, you can go to my wechat official account [6 Xixuanxin] and reply to MySQL to obtain the brain map:

The body of the

MySQL InnoDB lock basic type

Dev.mysql.com/doc/refman/…

The website divides locks into eight categories. Shared and Exclusive Locks and two table-level Locks are the basic locking patterns.

The last three Record Locks, Gap Locks, and next-key Locks are what we call the locking algorithm, which ranges are locked under what conditions.

The granularity of the lock

InnoDB has row-level locks and table-level locks. Let’s analyze some of the differences in locking granularity between InnoDB and InnoDB.

Table locks, as the name suggests, lock a table; A row lock locks a row of data in a table. Lock granularity, table locks must be greater than row locks.

• So lock efficiency, should table lock be greater than row lock or less than row lock?

Greater than. Why is that? Table lock only needs to directly lock the table on the line, and row lock, but also need to retrieve the row of data in the table, so the table lock is more efficient.

• The second probability of conflict? Are table locks more or less likely to collide than row locks?

>, because when we lock a table, no other transaction can operate on the table. However, when we lock a row in the table, other transactions can also operate on other rows in the table that are not locked, so the probability of table lock conflicts is higher. Table locks are more likely to collide, so concurrency performance is lower, where concurrency is less than.

InnoDB supports both table locking and row locking. What granularity does MyISAM, another popular storage engine, support? That’s the first question. InnoDB already supports row locking, so it can also implement table locking by locking every row in a table. Why provide table locking?

To figure this out, we need to look at InnoDB’s basic lock mode, which has two row locks and two table locks.

A Shared lock

The first row-level lock is a Shared lock that is used to read a row of data. It is also called a read lock. Do not write data after adding a read lock. And multiple transactions can share a read lock. How do you lock a row of data?

We can use select… lock in share mode; The way to manually add a read lock.

There are two ways to release a lock. The lock automatically transactions as soon as the transaction ends, including committing the transaction and ending the transaction.

Let’s also verify that the shared lock is repeatable.

Transaction 1	Transaction 2
begin;
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
–	begin;
–	SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE; // OK

Exclusive lock

The second row-level lock is called Exclusive Locks, and is used to manipulate data, so it is also called write Locks. Once a transaction acquires an exclusive lock for a row, other transactions cannot acquire a shared lock or an exclusive lock for that row.

There are two ways of exclusive lock, the first is automatic exclusive lock. When we operate data, including adding, deleting and modifying, we will add an exclusive lock by default.

Another is manual locking. We use a “FOR UPDATE” to add an exclusive lock to a row of data. This is common in our code and in tools that manipulate data.

The lock is released in the same way as before.

Authentication of exclusive locks:

Transaction 1	Transaction 2
begin;
UPDATE student SET sname = ‘sname’ WHERE id=1;
–	begin;
–	SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE; // BLOCKED
–	SELECT * FROM student where id=1 FOR UPDATE; // BLOCKED
–	DELETE FROM student where id=1 ; // BLOCKED

This is two row locks, followed by two table locks.

Intent locks

What is an intent lock? We never seem to have heard of them or used them, but they are maintained by the database itself.

That is, before we assign a shared lock to a row of data, the database automatically assigns an intended shared lock to the table.

Before we assign an exclusive lock to a row of data, the database automatically assigns an intentional exclusive lock to the table.

The other way around:

If there is at least one intended shared lock on a table, some rows are locked by other transactions.

If there is at least one intentional exclusive lock on a table, other transactions have placed exclusive locks on some rows.

select * from t2 where id =4 for update;
Copy the code

TABLE LOCK table example.t2 trx id 24467 lock mode IX RECORD LOCKS space id 64 page no 3 n bits 72 index PRIMARY of table example.t2 trx id 24467 lock_mode X locks rec but not gap

So what is the point of these two table-level locks? First, we have table-level locking, which in InnoDB allows for more granular locking. The second thing it does, let’s think about it, is if we don’t have intentional locks, what do we do first when we’re going to add table locks to a table? Do I have to determine if other transactions are locking rows? If so, you must not add table locks. Then we need to scan the entire table to determine whether we can successfully add a table lock. If the data volume is very large, such as tens of millions of data, is the efficiency of adding a table lock very low?

But when we introduced intent locks, that changed. I just need to determine if there is an intent lock on this table, and if there is, I return failure. If not, the lock is successful. So the table lock in InnoDB, we can think of it as a flag. It’s like when the train goes to the toilet, the lights are used to increase the efficiency of locking.

Transaction 1	Transaction 2
begin;
SELECT * FROM student where id=1 FOR UPDATE;
–	BEGIN;
–	LOCK TABLES student WRITE; // BLOCKED UNLOCK TABLES; // How to release the table lock

These are the four basic lock modes, or lock types, in MySQL.

By the way

There is a problem? Can you leave me a message or chat privately? Just give it a thumbs up

Of course, you can also go to my official account “6 Xi Xuan”,

Reply to “Learn” and receive a copy of the Video tutorial for Advanced Architects for Java Engineers

Answer “interview”, can obtain:

MySQL brain Map MySQL brain map

Because, I trained programmers, PHP, Android and hardware are done, but in the end or choose to focus on Java, so have what questions to ask the public for discussion (emotional pouring technology can ha ha ha), see words will reply as soon as possible, hope can with everyone common learning progress, on the server architecture, Java core knowledge analysis, career, interview summary and other articles will be pushed irregularly output, welcome to pay attention to ~~~