“This article has participated in the call for good writing activities, click to view: the back end, the big front end double track submission, 20,000 yuan prize pool waiting for you to challenge!”
IS, IX, MDL, record locks, gap locks, next-key locks, intent insert locks, auto-inc locks.
Let’s go through the interview practice to understand.
Me, little Y.
Again to the interview, or before the company, and before the old interviewer for the second battle, the mood is still xue a little uneasy.
For those of you who haven’t seen the first battle, here’s an MVCC fighting the interviewer for 30 rounds
There was another flash of light and the interviewer opened the door. I looked up and, yes, it smelled the same.
When I saw the hair on the interviewer’s head, I almost wanted to stand up and give him a salute. Just stay calm and keep a low profile.
The interviewer glanced at me: come on, let’s continue the interview, last time I couldn’t, my girlfriend is clingy, this time ask MySQL InnoDB lock oh.
I:… Ok, I know you have a girlfriend. Ok, interviewer, go ahead.
Interviewer: What is the difference between MySQL InnoDB locks and MyISAM locks?
Me: MyISAM only supports table locking, which locks the entire table, while InnoDB not only supports table locking, but also supports lower granularity row locking, which only locks related records, so InnoDB has better performance for write operations.
Interviewer: There are actually two types of table locks and row locks. Do you know what they are?
Me: Do you mean shared (S) locks and exclusive (X) locks?
-
An S lock, called a shared lock, is acquired when a transaction reads a record. It allows multiple transactions to acquire the S lock at the same time without conflict with each other.
-
An X lock is called an exclusive lock. Only one transaction is allowed to acquire the X lock while modifying records. Other transactions need to block and wait.
So the S lock does not conflict with each other, and the X lock is an exclusive lock, so X will conflict with each other, and X and S will conflict.
| conflict | S | X |
|---|---|---|
| S | Don’t conflict | conflict |
| X | conflict | conflict |
The characteristics of S and X are the same whether it is table level locking or row level locking.
Interviewer: You say the transaction needs to acquire the S lock when reading records? Isn’t that right?
Me: Not exactly. Thanks to MVCC, normal select does not need to lock, and select… LOCK IN SHARE MODE; This reading requires an S lock on the record. SELECT … FOR UPDATE; An X lock is required on the record.
Interviewer: By the way, you just mentioned table locking, do you usually use InnoDB table locking?
Me: No, InnoDB table locks are lame, I know:
LOCK TABLES yes READLock S on yes.LOCK TABLES yes WRITELock X on the table yes.
But basically it doesn’t work.
Interviewer: Oh? What’s wrong with the ribs?
I: daily update, SELECT to use is also used row lock, can not use coarse granularity table lock. The only thing you can think of using a TABLE lock is a DDL statement. For example, when you ALTER a TABLE, you should lock the entire TABLE to prevent queries and changes. However, the server already provides a Metadata lock called MDL Locks. The table lock won’t be useful.
If you really want to use a table lock, it is estimated that the data recovery time, manual table lock restore data.
The interviewer touched the reflection on his head. “Yes, but if it comes to table locking, won’t table locking and row locking conflict?” What if a row lock has been added to the table? Do I have to go record by record to find the line lock?
Me: This is one way to implement it, but the performance is so poor that if there are tens of millions of data in the database, it would be fatal to add a table lock.
So we have something called Intention Locks.
- IS (Intention Shared Lock) indicates Intention Shared Lock
- IX (Intention Exclusive Lock) indicates Intention Exclusive Lock.
These two locks are table-level locks. To lock S on a record in a table, an IS lock IS added to the table to indicate that S locks exist in the table. When an X lock is required on a table, an IX lock is placed on the table to indicate that there is an X lock in the table.
After doing this, if you want to add a table lock, you don’t need to go through all the records to find it, just look for IS and IX locks on the table.
For example, table level S lock is required. If there is no IX on the table, it indicates that there is no exclusive lock in the table.
Table X lock (IX, IS, IX, IS, IS, IX, IS, IX);
Therefore, the purpose of IS and IX IS to quickly determine whether a table can be locked without iterating through all the records in the table.
Therefore, IS and IX do not conflict with each other, because they only serve to mark up and enrich the table above:
| conflict | S | X | IS | IX |
|---|---|---|---|---|
| S | Don’t conflict | conflict | Don’t conflict | conflict |
| X | conflict | conflict | conflict | conflict |
| IS | Don’t conflict | conflict | Don’t conflict | Don’t conflict |
| IX | conflict | conflict | Don’t conflict | Don’t conflict |
Interviewer: Ok, let’s talk about row locks. How many types of row locks does InnoDB have?
Me: Record Locks, Gap Locks, next-key Locks.
Interviewer: Tell me more about it.
Me: A record lock, as the name implies, locks the current record, which is applied to the index. We all know that InnoDB must have an index, even if there is no primary key, it will create a hidden cluster index, so record locks always lock index records.
SELECT * FROM yes WHERE name = ‘xx’ FOR UPDATE; If name = xx is locked, no other transaction can insert, delete, or modify the name = xx record.
Insert into YES (name) values (‘xx’); insert into YES (name) values (‘xx’)
However, if another transaction C performs insert into YES (name) values (‘ AA ‘), will this statement be blocked?
It depends.
If name has no index. Mysql > alter table select * from primary key; alter table select * from primary key; alter table select * from primary key; All locked!
Therefore, if name has no index, transaction C will block, and if there is an index, transaction C will not block!
Don’t lock columns without an index. Don’t think you can do anything you want with a row lock. It’s not the case.
Interviewer: Yo, there’s something. Keep going.
Me: And then the gap lock, this thing it has something.
As mentioned earlier, a record lock needs to be added to a record, but what if you want to add a lock to a record that doesn’t already exist? That is, to prevent the appearance of magic reading!
This is when the gap lock comes in handy, it is to add a gap lock.
For example, there are four records: 1, 3, 5, and 10. As analyzed in the previous article, there are also two virtual records in the data page, namely Infimum and Supremum.
As you can see, there is a gap before recording, but what about the gap lock, the lock is this gap!
For example, if I lock the gap between 3 and 5, then I will insert the record with id = 4, which will be blocked by the gap lock, so as to avoid phantom reading generation! This meets the need to lock uninserted records!
There is also a next-key lock that Locks only the interval (3,5), and a next-key lock that Locks only the interval (3,5). The next-key Locks prevent the phantom read on the index (id=5).
Interviewer: Are there any conflicts between gap locks?
Me: No, the only purpose of a gap lock is to prevent other transactions from inserting data into the gap, so it doesn’t matter if two gap locks want to lock the same gap, because their purpose is the same, so there is no conflict.
Interviewer: Can gap locks be explicitly disabled?
Me: Yes. Gap locks take effect when the transaction isolation level is set to repeatable reads. If you change the transaction isolation level to READ COMMITTED, they are disabled. At this point, gap locks are disabled for search and index scanning, and only for foreign key constraint checking and duplicate key checking.
Interviewer: Speaking of interstitial locks, do you know what interstitial locks are?
Me: Insert Intention Locks, also known as Insert Intention Locks, are a type of gap Locks, but instead of locking gaps, they wait for gaps. For example, the above example transaction C with id = 4 is blocked by a gap lock, so transaction C will generate an insert intent lock, indicating that it is waiting for the release of the gap lock.
And there is no blocking between intent locks, because their purpose is to wait for the gap to be released, so there is no conflict between intent locks.
Interviewer: So the intent lock is useless?
Me: Really, it’s not so much about locking up resources to prevent access, I personally think it’s more about following MySQL’s lock code implementation.
Locking is actually a structure in memory, each transaction for a record or gap lock is to create a lock object to compete for resources.
If a transaction does not grab a resource, a lock object will be generated, but the state is waiting, and when the transaction with the resource releases the lock, it will look for the lock structure that is waiting for the current resource, and then choose one to let it acquire the resource and wake up the corresponding transaction to execute.
Therefore, according to this logic, those insert transactions in wait gap lock also need to establish a corresponding lock structure, and then the lock type is insert intent lock.
In this way, when the transaction of gap lock is released, it can find the transaction waiting to be inserted and wake up. It can also be known from the type of lock that it is intended to be inserted. There is no need to block between them, so they can be inserted together.
Interviewer: Speaking of inserting new records let me ask you a question. If the inserted transaction has not committed yet, there is now another transaction via SELECT… LOCK IN SHARE MODE or SELECT… What if FOR UPDATE wants to read this record? What lock is in effect at this time?
Me :(I lose, the interviewer is trying to dig a hole for me? Hum, but this can’t stop me bully zhong Ba!
SELECT … LOCK IN SHARE MODE or SELECT… FOR UPDATE ‘is used to obtain the record S lock and X lock, but the transaction has not committed yet, so both types of SELECT block.
How exactly is it blocked? Because there is a transaction ID! With MVCC you can use the transaction ID to determine whether the current record is visible or not, which is essentially an implicit lock! Knowing that the current record is not visible, the query transaction generates a lock structure for the previously uncommitted inserted transaction, then generates its own lock structure, and waits for the inserted transaction to be released, thus completing the block!
Interviewer :(this guy, I can’t hold him down!) Ok, do you know what auto-inc Locks are?
Me: Yes, auto-Inc Lock is a special table-level Lock used for auto-increment column inserts. When inserting a data item, an auto-Inc Lock is added to the table, the incrementing value is allocated to the auto-inc column, and the auto-Inc Lock is released after the statement is inserted.
After MySQL 5.1.22, we added a mutex to add and subtract. The mutex performs better than auto-Inc Lock because auto-Inc Lock releases the Lock after the statement is inserted, whereas mutex releases the Lock after the statement is inserted and increments are obtained.
However, we also need to consider the primary and secondary data. Due to concurrent insertion, the sequence of values added cannot be controlled during statement-based binlog replication, which may cause data inconsistency between the primary and secondary data.
So MySQL has an innodb_autoinc_lock_mode configuration with three values:
- 0, use auto-inc Lock only.
- 1. The default value is mutex for an insert whose number of rows is known before the insert. Auto-inc Lock is used for an insert whose number is not known before the insert.
- 2, use only mutex.
Interviewer: Does MyISAM have auto-inc Locks?
Me: No, MyISAM insert already uses table lock.
Interviewer: do you know what locks MySQL has?
Me :(that’s not enough??) Table locks, IS, IX, MDL, record locks, gap locks, next-key locks, insert intent locks, auto-inc locks, what else?
The interviewer glances at me :(good boy, I finally cured you) don’t know?
I :(should be, still have to be) knowledge blind area, please the interviewer teach me.
Interviewer: There’s also Predicate Locks, Predicate Locks.
Me: What the hell?
Interviewer: InnoDB supports spatial data, so it has spatial indexes. In order to handle the locking of operations involving spatial indexes, next-key locking is not effective. Because there is no concept of absolute ordering in multidimensional data, it is not clear where the “next” key is.
So to support isolation levels for tables with spatial indexes, InnoDB uses predicate locks.
Spatial indexes contain minimum bounding rectangle (MBR) values, so By setting predicate locking on the MBR values used for queries, InnodB makes it impossible for other transactions to insert or modify rows that match query conditions by performing consistent reads on the indexes.
I: (… You are 66666, old interviewer.
Interviewer: Ok, your answer today was sloppy. Next time I’ll ask you about buffer pool, change buffer, doublewrite buffer.
Me :(？？？？) Is this still going on? Does that count as three sides?
Interviewer: You take care of me, I want to face you more, fully understand, our company is very strict, people are not just recruit! Get back there and wait for notice!
Me: Ok, ok, you always wait, just a pile of buffer, I will go back to prepare
Today here, next I draw a graph summary summary of a wave of today’s lock ~
For more interview articles, see my personal GitHub repository github.com/yessimida/y…
I have prepared an algorithm note to get the SSP offer 20W words.
Wait for my next article, to a concern not lost ~ if you think the article is good, to a thumb-up yo!