Hello everyone, I am Chen Haha, and I have been drifting north for five years. Friends who know me know that I was born in a non-major class, renounced halfway through, and the university is also very poor! Come to Beidiao with this background, you don’t know what you will experience 🙃🙃.
I can’t agree. I believe everyone is like me
都有一个大厂梦. As a senior Java player, I know the importance of interviews. Next, I’m going to spend 100 days, based on the high-frequency interview questions in Java post interviews,
每日3题and take you in the form of Go through the popular interview questions and the appropriate answers. Of course, I won't go too deep, because I'm afraid I can't remember! !
因此，不足的地方希望各位在评论区补充疑惑、见解以及面试中遇到的奇葩问法, I hope these 100 days will allow us to fly over qualitatively and rush into the big factory together! ! , Let us learn together (juan)! ! !
- Interview Question 1:
- Serious answer: Let me talk about what MySQL transaction is
- In-depth inquiry:
- Follow-up 1: Tell me about your understanding of the four characteristics of ACID
- Follow-up 2: Can you talk about how ACID is implemented in principle?
- Interview question 2: What data problems will exist in transactions in a concurrent scenario?
- Seriously answer:
- In-depth inquiry:
- Follow-up 1: How does Innodb solve the phantom reading problem?
- Interview Question 3: Tell me about which locks do you know in MySQL?
- Seriously answer:
- In-depth inquiry:
- Follow-up 1: Then you can talk about your understanding of table locks and row locks.
- Follow-up 2: When is the global lock used?
- Follow-up 2: Then can you talk about the usage scenarios and understanding of the types of locks divided by lock level?
- Daily summary
This column Java developers Kong high-frequency face questions from each of the following major technology
Linux操作技巧and so on.
Interview Question 1:
Serious answer: Let me talk about what MySQL transaction is
事务就是一组原子性的SQL执行单元put, . If the database engine can successfully apply all the statements of the set of queries to the database, then execute the set of SQL. If any of the statements cannot be executed due to a crash or other reasons, all the statements will not be executed.
Here is an example of bank transfer. Assume that the bank's database has two tables: a credit card (credit) table and a savings (savings) table. The user Chen Haha wants to transfer the last 100 yuan in the credit card to his savings account to use for food, then at least three steps are required:
- Check whether the credit card balance is higher than 100 yuan.
- Subtract 100 yuan from the credit card account balance.
- Add 100 yuan to the savings account balance.
The above three steps must be executed in the same transaction, any SQL failure, all SQL must be rolled back. Here, a
START TRANSACTIONstatement is used to start the transaction, and either the
COMMITcommit transaction is used to persist the modified data, or
ROLLBACKall the modifications are used to cancel. The sample of transaction SQL is as follows:
START TRANSACTION; -- 检查信用卡账户额度 SELECT balance FROM credit WHERE customer_id = 'chenhh'; -- 信用卡表扣钱 UPDATE credit SET balance = balance - 100.00 WHERE customer_id = 'chenhh'; -- 储蓄表加钱 UPDATE savings SET balance = balance + 100.00 WHERE customer_id = 'chenhh'; COMMIT;
Imagine, what happens if the server crashes when the fourth statement is executed? Nonsense, I was scammed for 100 yuan and I can only get hungry at noon! Suppose, at the same time, between the execution of the third statement and the fourth statement, another process, the girlfriend from the shopping mall checkout, also needs 100 yuan of the credit card account, then the result may be that the bank does not know this In the case of logic, gave Chen Haha's girlfriend 100 yuan for nothing?
Follow-up 1: Tell me about your understanding of the four characteristics of ACID
This question comes from a blog post in the "MySQL Jianghu Road" column: "What are the four major features of database ACID? This article will show you through."
ACID characteristics: atomicity, consistency, isolation, durability
单个事务，为一个不可分割的最小工作单元, All operations in the entire transaction either all commit successfully or all fail rollback. For a transaction, it is impossible to execute only part of the SQL operations. This is the atomicity of the transaction.
The database always transitions from a consistent state to another consistent state. In the previous example, consistency is ensured. Even if the system crashes between the execution of the third and fourth statements, the credit card account will not lose 100 yuan, because the transaction is not finally committed, so the changes made in the transaction will not Save it in the database to ensure data consistency.
Generally speaking, changes made by one firm are not visible to other transactions until they are finally submitted. In the previous example, when the third statement is executed and the fourth statement has not yet started, at this time another account query balance SQL starts to run, then the credit card account balance it sees is not subtracted by 100 yuan. Later when we discuss the isolation level (Isolation level), we will discover why we have to say it
Once the transaction is committed, the changes made will be permanently saved in the database. Even if the system crashes at this time, the modified data will not be lost.
The ACID feature of the transaction ensures that the bank will not lose your money. In application logic, it is very difficult to achieve this, and it can even be said to be an impossible task. An ACID-compatible database system needs to do a lot of complicated work that may not be noticed by users in order to ensure the realization of ACID.
Follow-up 2: Can you talk about how ACID is implemented in principle?
逻辑备份日志（binlog）、重做日志（redolog）、回滚日志（undolog）、锁技术 + MVCC就是MySQL实现事务的基础MySQL, .
- Atomicity: Achieved by undolog.
- Persistence: through binlog, redolog to achieve.
- Isolation: through (read-write lock + MVCC) to achieve.
1. The principle of atomicity
The transaction usually starts with BEGIN TRANSACTION and ends with COMMIT or ROLLBACK.
COMMIT 表示提交, That is, all operations of the transaction are committed and persisted to the database.
ROLLBACK表示回滚, That is, a certain failure occurs during the operation of the transaction, and the transaction cannot be continued. The system cancels all completed operations on the database in the transaction and rolls back to the state at the beginning of the transaction. The operation here refers to the operation of the database The update operation does not care about the executed query operation. At this time, undolog is also needed for rollback.
- Each data change (INSERT/UPDATE/DELETE/REPLACE) and other operations will generate an undolog record, which is persisted to disk before the SQL is executed.
- When the transaction needs to be rolled back, MySQL will perform reverse operations on the SQL executed in the transaction according to the rollback log. For example, the reverse operation of DELETE to drop a row of data is to INSERT this row of data back, and other operations are the same.
2. The principle of persistence
Let's first understand the data storage mechanism of MySQL. MySQL table data is stored on the disk. Therefore, you must experience disk IO when you want to access it. However, even using SSD disk IO is very performance consuming. To this end, in order to improve performance, InnoDB provides a buffer pool (Buffer Pool), which contains a mapping of disk data pages, which can be used as a cache:
- Read data: first read from the buffer pool, if there is no buffer pool, read from the disk and put it into the buffer pool;
- Write data: It will be written to the buffer pool first, and the data in the buffer pool will be periodically synchronized to the disk;
We know that MySQL table data is persisted to the disk, but if all operations are to operate the disk and wait for concurrency, no one can handle the processing speed. Therefore
缓冲池(Buffer Pool), the concept of introducing the concept of Buffer Pool contains part of the data in the disk. The page mapping can be used as a cache; in this way, when the table data is modified, we first write the operation record to the Buffer Pool and mark the transaction as completed. When MySQL is free, the update operation will be persisted to the disk (
你可能会问，到底什么时候执行持久化呢？1、MySQL线程低于高水位；2、当有其他查询、更新语句操作该数据页时) , Thus greatly alleviating the pressure of MySQL concurrency.
But it also brings new problems. When the MySQL system is down, the Buffer Pool data is lost when the power is off?
Because our data has been submitted, but at this time it is in the buffer pool and has not had time to persist on the disk, so we urgently need a mechanism to save the data of the submitted transaction in order to recover the data.
So the classic combination of redo log + binlog is here, so I won’t expand on it here. You can refer to "After listening to me explain the principles of redo log and binlog, the interviewer blushes"
3. The principle of isolation
Isolation is one of the most complex features of transaction ACID. There are four isolation levels defined in the SQL standard. Each level specifies the modifications in a transaction, which are visible between transactions and which are invisible.
The lower the isolation level, the higher the concurrency, but the greater the complexity and overhead.
To understand the MySQL transaction isolation level, please refer to "Kung Fu on the Toilet, Understanding the MySQL Transaction Isolation Level"
Mysql isolation level has the following four (level from low to high):
|Read uncommitted (RU)||When a transaction is not committed, the changes it makes can be seen by other transactions. (Other affairs refer to the addition, deletion, modification, and checking operations performed at the same time)|
|Read submission (RC)||After a transaction commits, the changes it makes will be seen by other transactions.|
|Repeatable read (RR)||The data seen during the execution of a transaction is always consistent with the data seen at the start of the transaction. |
Of course, under the repeatable read isolation level, uncommitted changes are also invisible to other transactions.
|Serialization (xíng) (S)||As written in the physics book, serial is a single line. As the name implies, only a single transaction is allowed to execute at the same time in MySQL. "Write" will add "write lock", and "read" will add "read lock". |
When a read-write lock conflict occurs, the later-accessed transaction must wait for the completion of the previous transaction before it can continue.
Knowing the isolation level and implementation principle can actually understand the isolation in ACID. As mentioned earlier, the purpose of atomicity, isolation, and persistence is to achieve consistency, but the isolation type is different from the other two. Atomicity and persistence are to achieve correct and usable data, such as Recover after downtime, rollback of transactions, etc., to ensure that the data is correct and usable!
So what does isolation do?
Isolation is to manage: the order of execution when multiple concurrent read and write requests (transactions) come over. Like a traffic policeman directing traffic at a crossing,
Therefore, from the realization principle of isolation, we can see that this is a
4. The principle of consistency
Consistency, what we want to ensure is
数据一致性the addition, deletion, and modification operations in the database
事务该回滚的回滚，该提交的提交，提交后该持久化磁盘的持久化磁盘，该写缓冲池的写缓冲池+写日志；对于数据可见性，通过四种隔离级别进行控制，使得库表中的有效数据范围可控，保证业务数据的正确性的前提下，进而提高并发程度，支撑服务高QPS的稳定运行，保证数据的一致性, These are the four major characteristics of database ACID that we can't understand clearly.
Take a break between classes and admire the
SQL大腿群brick-moving site from our classmates. Coordinate: Hangzhou .
Interview question 2: What data problems will exist in transactions in a concurrent scenario?
In a concurrent scenario, MySQL transactions may have dirty reads, phantom reads, and non-repeatable reads;
- Dirty Read : A transaction has updated a piece of data, and another transaction reads the same piece of data at this time. For some reason, the previous RollBack operation is performed, and the data read by the next transaction is Would be incorrect.
- Non-repeatable read : The data is inconsistent in the two queries of a transaction. This may be caused by inserting a transaction between the two queries to update the original data.
- Phantom Read : The number of data items in the two queries of a transaction is inconsistent. For example, one transaction queries several rows of data, while another transaction inserts new columns of data at this time. In the next query, you will find that there are several columns of data that it did not have before.
Follow-up 1: How does Innodb solve the phantom reading problem?
Let me start with the conclusion that the MySQL storage engine InnoDB solves the problem of phantom reads under the Repeatable Read (RR) isolation level.
The method is to use next-key lock when the current read transaction is opened, 1. Add a write lock (row lock) to the row involved to prevent write operations; 2. Add a gap lock (Gap Lock) to both ends of the row involved to prevent new Increase the line to write; thus solve the problem of phantom reading.
The scene of the phantom reading:
- The phantom read is now under the Repeatable Read (RR) isolation level, and ordinary SELECT queries are snapshot reads, and data inserted by other transactions will not be seen. Therefore, the phantom reading will only appear under the "current reading". (Currently reading will generate row locks, but row locks can only lock existing rows, and there are no restrictions on newly inserted operations)
- The modification result of session B above is seen by the select statement after session A with "current reading", which cannot be called phantom reading. The phantom read only refers specifically to the "newly inserted row".
- Because these three queries are all added for update, they are all currently read. The current read rule is to be able to read the latest value of all submitted records. In addition, the two statements of session B and session C will be submitted after execution, so Q2 and Q3 should see the operating effects of these two transactions, and they have also seen that this does not contradict the visibility rules of the transaction.
Examples of phantom reading scenes:
The test table data is as follows:
mysql> select * from LOL; +----+--------------+--------------+-------+ | id | hero_title | hero_name | price | +----+--------------+--------------+-------+ | 1 | 刀锋之影 | 泰隆 | 6300 | | 2 | 迅捷斥候 | 提莫 | 6300 | | 3 | 光辉女郎 | 拉克丝 | 1350 | | 4 | 发条魔灵 | 奥莉安娜 | 6300 | | 5 | 至高之拳 | 李青 | 6300 | | 6 | 无极剑圣 | 易 | 450 | | 7 | 疾风剑豪 | 亚索 | 6300 | +----+--------------+--------------+-------+ 7 rows in set (0.00 sec)
The following is an example process with a phantom reading situation:
|Time T||Session A||Session B||Session C|
select * from LOL where price=450 for update;
Result: (6,'Promise Sword Saint',450)
|T2||update LOL set price=450 where hero_title ='Shifeng Jianhao';|
|T3||– Query2 |
select * from LOL where price=450 for update;
Result: (6,'Promise Sword Saint',450),(7,'Shifeng Jianhao',450)
|T4||insert into LOL values(10,'Snowman Knight','Nunu','450');|
|T5||– Query3 |
select * from LOL where price=450 for update;
Result: (6,'Promise Swordsman',450),(7,'Swordsman of the Storm',450),(10,'Snowman Knight',450)
As you can see, three queries were executed in session A, namely Q1, Q2, and Q3. Their SQL statements are the same, both are select * from LOL where price=450 for update. You should be very clear about the meaning of this statement. Check all the rows with price=450, and use the current read and write lock. Now, let's take a look at these three SQL statements and what results they will return.
- Q1 only returns the line "Promise Swordmaster";
- At time T2, session B changed the price value of the line "Shi Feng Jian Hao" to 450, so Q2 at time T3 detected the two lines "Wuji Jian Sheng" and "Shi Feng Jian Hao";
- At T4 time, session C inserts another line (10,'Snowman Knight','Nunu','450'), so Q3 at T5 time finds out that the price = 450 is "Promise Swordsman", "Shipfeng Swordsman" and "Snowman Knight" these three lines.
Among them, the phenomenon that Q3 reads the line (10,'Snowman Knight', 450) is called "phantom reading". In other words, phantom reading refers to when a transaction queries the same range twice before and after,
Solve the analysis of phantom reading
If you see this, then I will assume that you understand dirty reads, non-repeatable reads and repeatable reads. If you are still unclear, you can first refer to "Kung Fu on a Toilet, Understanding the MySQL Transaction Isolation Level"
The scene is as above, the scene isolation level is RR, and it is currently read.
1. Principle interpretation
So can phantom reading be solved only by row locking? The answer is no, as in the above example, first of all, it
select xx for update（当前读）is to add row locks to all the rows involved in the conditions (which meet the where conditions). However, even if I add row locks to all rows when the select xx for update transaction is opened. Then the new row of Session C cannot be locked, because when I lock the data, there is no new row at all, so naturally I won't lock the new row.
Therefore, in order to solve the phantom reading, the problem of adding new lines must be solved.
Now you should understand that the reason for the phantom read is: the row lock can only lock the row, but for the action of inserting a new record, what needs to be updated is the "gap" between the records. Therefore, in order to solve the phantom read problem, InnoDB had to introduce a new lock, that is, a gap lock (Gap Lock). As the name implies, gap lock, locks the gap between two values. For example, the table LOL at the beginning of the article initially inserted 7 records, which resulted in 8 gaps.
Two, next-key lock
In this way, when you execute select * from LOL where hero_title ='Shifeng Jianhao' for update, you will not only add row locks to the 7 existing records in the database, but also add 8 gap locks at the same time. This ensures that no new records can be inserted, that is, when Session C adds (10,'Snowman Knight','Nunu','450') rows in T4, because the ID is greater than 7, it is locked by the gap (7, +∞) Locked.
In the process of scanning line by line, not only the line lock is added to the line, but also the gap lock is added to the gap on both sides of the line. MySQL collectively refers to the row lock + gap lock combination as next-key lock, and solves the problem of phantom reading through next-key lock.
next-key lock does solve the problem of phantom reading, but next-key lock often causes deadlock in concurrent situations. Deadlock detection and processing will also take time, which affects the amount of concurrency to a certain extent.
Interview Question 3: Tell me about which locks do you know in MySQL?
This question is taken from a blog post in the "MySQL Jianghu Road" column: "MySQL lock mechanism that HR can understand during interviews, understand MySQL lock in laughter and laughter"
Press lock granularity descending classification:
行锁; used under the particular scene and
If you press the lock-level classification
And Innodb engine data to solve problems in concurrent scenario affairs phantom read, etc., the introduction of
Next-key Lock（Record Lock + Gap Lock结合）and so on;
There are two types of locking thoughts we have for programming: pessimistic locking and optimistic locking.
Follow-up 1: Then you can talk about your understanding of table locks and row locks.
Table-level locking is the most granular locking mechanism among MySQL storage engines. The biggest feature of this locking mechanism is
实现逻辑非常简单，带来的系统负面影响最小. So the speed of acquiring and releasing locks is very fast. Since the table-level lock will lock the entire table at one time, it can avoid the deadlock problem that plagues us.
Of course, the biggest negative impact brought by the large lock granularity is that the probability of contention for locked resources will be the highest, which greatly reduces the concurrency.
Table-level locking is mainly used by some non-transactional storage engines such as MyISAM, MEMORY, and CSV.
Contrary to table locks, the biggest feature of row locks is that the granularity of locked objects is very small, and it is also the smallest granularity of locks implemented by major database management software at present. Because the locking granularity is small, the probability of contention for locked resources is also the smallest, which can give the application as much concurrent processing power as possible to improve the overall performance of the system.
Although it can have greater advantages in concurrent processing capabilities, row-level locking also brings a lot of drawbacks. Because the granularity of locked resources is very small, there are more things to do to acquire and release locks each time, and the consumption is naturally greater. In
The main use of row-level locking is the InnoDB storage engine.
适用场景: From the perspective of locks, table-level locks are more suitable for applications that focus on querying and only a small amount of data is updated based on index conditions, such as Web applications; while row-level locks are more suitable for a large number of concurrently updated data based on index conditions. At the same time, there are application scenarios for concurrent queries.
In addition to table locks and row locks, MySQL also has a relatively neutral
页级lock. Page locks are a unique locking level in MySQL, and they are not too common in other database management software. The feature of page-level locking is that the granularity of locking is between row-level locking and table-level locking. Therefore, the resource overhead required to obtain the lock and the concurrent processing capabilities that can be provided are also between the above two. In addition, page-level locking is the same as row-level locking, and deadlock occurs.
The main use of page-level locking is the BerkeleyDB storage engine.
Follow-up 2: When is the global lock used?
First, the global lock is to lock the entire database instance. The usage scene is generally at the
MySQL provides commands to add global read locks:
Flush tables with read lock(FTWRL)
This command can make the entire library in a read-only state. After using this command, operations that modify the database such as data update statements, data definition statements, and update transaction commit statements will be blocked.
- If it is backed up in the main database, it cannot be updated during the backup period, and the business is suspended
- If you are backing up from the slave database, the binlog of the master database synchronization cannot be executed during the backup, causing the master-slave synchronization to delay
There is also a way to lock the global:,
set global readonly=truewhich is equivalent to setting the entire library to a read-only state, but this modification of the global configuration is of a heavier magnitude, and the difference from the global lock is: if the
Flush tables with read lockcommand is executed , if the client is abnormally disconnected , Then MySQL will automatically release this global lock, and the entire library will return to a state where it can be updated normally. But after the library is set to readonly, the client is abnormally disconnected, and the database will still remain in the readonly state, which will cause the entire library to be in an unwritable state for a long time. Imagine that WeChat can only be read and cannot be typed~~
Follow-up 2: Then can you talk about the usage scenarios and understanding of the types of locks divided by lock level?
MySQL based lock level is divided
排他（写）锁for example, when we stay in a hotel, customers have the right to inspect the room before they check in. If you can't help but want to go for a prostitute, the lady at the front desk will open the door for you. Of course, different customers are also allowed to watch together (
共享 读), such as with this guy who kills Matt.
The room is equivalent to a public place when viewing the house. Miss sister told me not to scribble or drink free mineral water. . If you think it's good, you secretly rush to the front desk to book this room. After you pay the money, you will be given the key to the room and the room status will be changed to checked in. No one else is allowed to view the room (exclusively written).
By the way, when checking in, the young lady at the front desk will also inform the inspector that the room has been booked! ! After waiting for the inspector to scold and scold him and go out, he saw you sweating profusely and swallowed contemptuously, coughing tui! Then you locked the door, hummed, and started to do the shameful things~~
It can be seen that read locks can be acquired concurrently (shared), while write locks can only be processed by one transaction (exclusive). When you want to acquire a write lock, you need to wait for the previous read locks to be released before adding a write lock; and when you want to acquire a read lock, as long as the data is not locked by the write lock, you can acquire the read lock, and then go Inspections.
意向读\写锁, strictly speaking, they are not a kind of lock, but store the information of all row locks in the table. Just like when we are in a hotel, when we reserve a room, we add to the row (room)
意向写锁, but at the same time, we will do a registration of the row (room) at the front desk of the hotel (guest name, gender, length of stay, A few cows at home, etc.). You can regard the intention lock as the front desk of this hotel. It is not a lock (key) in the true sense. It maintains the lock information of each row in the table and is shared. Subsequent travelers will use the hotel reception to see which room is optional. Then, if there is no intention to lock, what will happen? Suppose I want a room, then I have to go to each room every time to see if there are people in this room. Obviously, the efficiency of this is very low. Kill the Matt guy to show his support!
The compatibility of read-write locks and intention locks are as follows;
|Read lock||Write lock||Intent read lock||Intent write lock|
|Intent read lock||compatible||compatible||compatible|
|Intent write lock||compatible||compatible|
Let’s go back to the principle of MySQL
1. Share (read) lock (Share Lock)
Shared locks, also called read locks, are locks created during a read operation (SELECT). Other users can read data concurrently, but before the read lock is released, that is, before the end of the query transaction, no transaction can modify the data (acquire the write lock on the data) until all read locks have been released.
If a read lock is added
数据B(1024 room), other transactions can only
数据Badd a read lock to it, not a write lock. The transaction that obtains the read lock can only read the data and cannot modify the data.
SQL display lock writing:
SELECT … LOCK IN SHARE MODE;
Add LOCK IN SHARE MODE after the query statement, MySQL will add a read lock to each row in the query result. When no other thread uses a write lock on any row in the query result set, it can successfully apply for a read lock, otherwise it will be block. Other threads can also read the table that uses the read lock, and these threads read the same version of the data.
2, Exclusive (write) lock (Exclusive Lock)
Exclusive locks are also called write locks and exclusive locks. If
数据Bwrite lock is added to the lock, other transactions can no longer
数据Badd any type of lock.
SQL display lock writing:
SELECT … FOR UPDATE;
Add FOR UPDATE after the query statement, and MySQL will add a write lock to each row in the query result. When no other thread uses a write lock on any row in the query result set, the write lock can be successfully applied for, otherwise it will be blocked. In addition, after successfully applying for a write lock, you must wait for the read lock before the transaction to be released before you can operate.
3. Intention Lock
Intent locks are table-level locks, and their design purpose is to reveal the type of lock that will be requested for the next row in a transaction. Two table locks in InnoDB:
Intentional shared lock (IS): Indicates that the transaction is ready to add a shared lock to the data row, that is to say, the IS lock of the table must be obtained before a data row is added to the shared lock;
Intentional exclusive lock (IX): Similar to the above, it means that the transaction is ready to add an exclusive lock to the data row, indicating that the transaction must obtain the IX lock of the table before adding an exclusive lock to a data row.
The intention lock is automatically added by InnoDB and does not require user intervention.
Again, for INSERT, UPDATE and DELETE, InnoDB will automatically add exclusive locks to the data involved; for general SELECT statements, InnoDB will not add any locks, transactions can explicitly add shared locks or exclusive locks through the following statements.
Shared lock: SELECT… LOCK IN SHARE MODE;
Exclusive lock: SELECT… FOR UPDATE;
Today we reviewed the
MySQL事务类three frequently asked questions in interviews . Have you done it well?
对了，如果你的朋友也在准备面试，请将这个系列扔给他，如果他认真对待，肯定会感谢你的！！Okay, let’s stop here today
记得在评论区留言：打卡。, to encourage students who have lost their studies .
Summary of MySQL series articles and "MySQL Jianghu Road | Column Directory"