What MySQL must think of before creating an index

What MySQL must think of before creating an index

MySQL introduced metadata lock in version 5.5.3
His intention is to solve several bugs in the transaction isolation feature of previous versions, but the introduced problems are not small.

Let's talk about MySQL transactions first.
Oracle transactions refer to SQL statements that need to allocate rollback segments. That is to say, select is not part of oracle transactions.
For example, if you run a query and then query v $ transaction in another session, there is no relevant information until insert, update, and delete appear in the transaction.
Innodb transactions include select queries.
No matter whether the transaction isolation level is Repeatable read or read commit, as long as there is a query, the transaction starts
It is proved that after autocommit = 0 is set in 5.6.15, a transaction can be started by running a query.
The first session runs the query.

The second session, run show engine innodb status \ G to view the Transaction status

We can see that the thread with id 1 has started a transaction.

Why Does Oracle transactions only include insert, update, and delete statements, while innodb transactions include all statements?
I think this has a lot to do with the isolation level supported by the vendor.
As we all know, Oracle only supports two transaction isolation levels: Read commit and serialization. Read commit is the choice of most databases.
Read commit means that Phantom reads and non-repeated reads can occur. From the perspective of implementation principle, Oracle can record SCN at the beginning of a Statement (Statement level) and then apply MVCC queries. each query only needs to record its own SCN. the SCN at the beginning of the statement has nothing to do with the transaction. therefore, Oracle transactions do not include queries.

Innodb supports the Repeatable read isolation level. That is to say, no matter how many queries are run in a transaction, the results must be consistent.
(Innodb not only supports Repeatable read, but also avoids phantom read by using Gap locks at the Repeatable read level. Of course, this also brings many problems ..)
Therefore, it records not the LSN of each query statement, but the LSN when the first statement of the transaction occurs, whether the first statement is query or modification.
At the Repeatable read level, innodb applies mvcc to the lsn at the start of the transaction. Unlike Oracle, innodb queries the data version of The LSN in the rollback segment of the transaction,
The oracle query rollback segment is smaller than the data version of the statement SCN.
That is to say, it is also MVCC. oracle is statement-level and innodb is transaction-level.

There is a problem here. The transaction includes queries because of the need of the Repeatable read isolation level, but the innodb read commit isolation level also regards queries as part of the transaction.
It may be because of architecture or code implementation issues.
In any case, Innodb does this.

Let's talk about metadata lock.
Before 5.5.3, metadata lock was statement-level, which actually damaged transaction consistency.
For example, when a transaction runs two queries at the Repeatable read isolation level, the results are inconsistent.

This is precisely because metadata lock is a problem at the statement level,
At the interval of two queries, the other session executes the truncate table.
So run the query again without any results.

To solve this problem, MySQL upgraded the metadata lock to a transaction-Level lock in 5.5.3.
Any DDL operation must first obtain the metadata lock, but the lock must be released at the end of the transaction.
In the same experiment, it became like this in 5.6.13.
The transaction of the first session is not completed, so the DDL of the second session is blocked.

Using show processlist, you can see that the DDL statement is waiting for the metadata lock of the first session transaction.

In this way, transaction consistency is ensured at the Repeatable read isolation level.

As with the query mentioned earlier, innodb does not customize some things for read commit,
For example, read commit does not need to be queried as part of a transaction.
And read commit do not require transaction-level metadata lock.
It may be due to architecture issues that many Repeatable read features are imposed on read commit,
So once these features have problems, it cannot be avoided even if the isolation level is reduced to read commit.

The following question comes,
The DDL is blocked by metadata lock, which further blocks other transactions and even queries (queries are part of innodb transactions .)

This is a bit crazy, because at this time, the system is already Hung.
Assume that the thread with id 1 holds metadata lock and is not committed,
The thread with id 2 performs DDL, and is blocked in the metadata lock of thread 1,
At this time, the database has eight queries in sequence, and they are all blocked on thread 2.
If the transaction of thread 1 is not terminated, other threads will be blocked.
Even if the transaction of thread 1 ends, the metadata lock is obtained from the last eight transactions in sequence. At the same time, this DDL may block 80 transactions ..

At this time, the system concurrency is 1, and this DDL may never be executed, and this situation is not within the range of Deadlock Detection.
The lock timeout time is controlled by the lock_wait_timeout parameter. The default value is 31536000 (one year, right)

Although MySQL ensures transaction consistency and avoids bugs, the introduced problems may cause me to lose my job as a beginner dba ..

Finally, let's sort out the situations that may cause metadata lock serial congestion.
1. Perform DDL operations (alter table; truncate;) when other transactions are running ;)
2. Perform DDL operations when mysqldump is running (think about it)
3. In the Master-Slave replication environment, running the query in Slave will cause DDL blocking passed by the Master, resulting in increased replication latency.
4. Create an index (...)

As a beginner dba, two actions can be taken to save the job.
1. Lower the lock_wait_timeout parameter.
2. Check whether transactions are frequently performed before running DDL. After running DDL, start another session and use show processlist to check whether transactions are blocked by metadata lock.
Once blocking occurs, Kill the ddl operation first.

MySQL index isolation Column

Hash indexes of MySQL Indexes

Prefix index and index selectivity of MySQL Indexes

MySQL index usage monitoring

Replace MySQL index optimization and in or with union all

MySQL index design principles

This article permanently updates the link address: