How much is MySQL’s lock? And Tencent big brother’s technical interview, I still underestimate the lock!

Time:2021-4-7

How much is life! Every day and every night, only oneself.
How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

Looking for a job, I don’t know that today is a pain in my heart. I don’t deserve a job. Since the interview, his so-called waiting has passed for some time, but it’s a pity that he’s going to be low in the small Treasury on the lower Jingdong. Every time I think of it, I can’t help but feel tight in my heart. Just in the middle of the dilemma, a short message came from the mobile phone to make an appointment for the follow-up interview. I immediately kicked the door with my bag. He ran away.
How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

At the moment, the first thing that comes into view outside the door of the interview is a white ball like thing, which is round but not round. Curious wax gourd landing in general. There is also a wet part in the upper part, which is particularly eye-catching. What’s the situation?

Then came a middle-aged man. He was dressed in a pure white T-shirt, a gray loose casual trousers, waist circumference of at least more than 30. Plus a pair of summer essential leather sandals. He was looking down at a piece of A4 paper in his hand. Through a short black hair. His face was full of flesh. Plus that big belly is about to burst the T-shirt.

I was so scared that I couldn’t help swallowing my saliva for fear of saying something wrong. It’s like a meat dumpling. If you don’t work hard in the workplace for eight or nine years, you won’t have the current situation.
How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

What is a lock

interviewer:Are you here for an interview?
Zha Zha Hui:No, no, no, I’m here for the second interview.

interviewer:: seeing the comments from others last time, MySQL optimization still has a long way to go. What do you think of locks first?

Zha Zha Hui:Hey, hey, it’s OK!

lockIt’s the computerMulti process, multi threadA synchronization mechanism that forcibly restricts access to resources during schedulingConcurrent accessTo ensure the consistency and validity of the data;

Lock is a synchronization mechanism used to restrict resource access when multithreading, that is to say, it is used in concurrency control to ensure mutual exclusion.

A general lock is an advisory lock. Before accessing the corresponding resource, each thread needs to obtain the information of the lock, and then decide whether it can be accessed according to the information. If you access the corresponding information, the lock state will change to lock, so other threads will not access the resource at this time. When the resource ends, the lock state will be restored to allow other threads to access.

Some systems have mandatory locks. If an unauthorized thread wants to access the locked data, an exception will be generated.

---Wikipedia

Types and application principles of locks

interviewer:What are the locks in general database? How to use it?

At the moment, I look at the interviewer with my dull eyes, and my heart really belongs to youEmbarrassment + fearDatabase is sharing and mutex?
In this way, I am too tender. There must be a hole here. I don’t know that at this moment, I’ve already pinched you in my heart, and I will not spare you.

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

Zha Zha Hui:There are many views on database locks according to different division methods. There are the following two kinds of business access:

  • Exclusive lock

Lock the shared resource before accessing it, and unlock it after accessing. After the lock is successfully added, any other thread requests to acquire the lock will be blocked until the current thread releases the lock itself.

Thread 3 State: ready, block, execute

If more than one thread is blocked when unlocking (the resource has been released), then all threads trying to acquire the lock are considered by the CPUReady stateIf the first thread in ready state performs locking operation, other threads will enter ready state again. In this way, only one thread can access the resources protected by mutex

Therefore, after a mutex lock is added to the SQL statement of MySQL, only the thread that receives the request and obtains the lock can access and modify the data. Because mutex is for thread access control, not the request itself.

  • Shared lock

Locked resources can be shared, but only for read requests. Its write request can only be exclusive to the lock request. That is to say, the data with shared lock can only be modified by the current thread, and other threads can only read the data and cannot modify it.

Zha Zha Hui:SQL requests can be divided into read and write locks. But the essence is to deal with shared lock and exclusive lock.

interviewer:How to access SQL request without lock? Why do they belong to shared lock and exclusive lock? What is the relationship between them?

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

Zha Zha Hui:In addition to lock reading, there is another kind of reading without lock. This approach is calledSnapshot read, read request lock is called shared read.

The reason for adding shared and exclusive locks to requests is that read requests are inherentlyIdempotencyNo matter how many times you read, the data will not change, so locking the read request should be a shared lock. Otherwise, how to guarantee its characteristics?
The write request itself needs to modify the data, so it needs exclusive lock to ensure the consistency of data modification.

Zha Zha Hui:According to the particle size of the lock, there isTable lock and row lock

  • Table lock:

It is the most basic lock policy in mysql, and it has the least cost. Less concurrent processing. Table locks are managed by MySQL service or storage engine. In most cases, it is managed by the service layer. See SQL operation for details.

For example, the server willStatements like alter table use table locks
, ignoring the lock of the storage engine.

Locking mechanism:

It locks the entire table. Before a user writes to a table (insert, delete, update, etc.), he needs to obtain a write lock, which will block all read and write operations of other users. Only when there is no write lock, other users can obtain the read lock.

  • Row lock:

Lock the data of the current access row, and the concurrent processing ability is very strong. But locking costs the most. It depends on the amount of line data. Supported by InnoDB storage engine.

  • Page level lock:

Page level lock is a kind of lock with locking granularity between row level lock and table level lock in MySQL. Table level lock is fast, but there are more conflicts, row level conflict is less, but the speed is slow. Therefore, a compromise page level lock is adopted to lock a group of adjacent records at a time. The BDB storage engine manages page level locks.

interviewer:Why is table lock less expensive than row lock? After all, table lock is the whole table

Zha Zha Hui:It’s true that a table lock locks the table, but it doesn’t lock all the data rows in the table, which is equivalent to blocking the entry of the table. In this way, it just needs to determine whether each request can obtain the lock of the table. If it doesn’t, it won’t lock.
The row lock is for each row of data in the table. As the amount of data is more, the lock content is more, so the overhead is large. However, because of its small granularity, locking rows will not affect other rows. So concurrency is high. If the table is locked in an entry, the overall request processing will be reduced.

interviewer:I remember there are several different ways to implement row lock, you know?

It’s very kind of you to think so much for me. Are you so considerate? If I don’t know, you are always ready to wear shoes. Resist the impulse to gnaw at others

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

PS: reading the picture shows that you have a story

Zha Zha Hui:Although InnoDB supports row lock, the algorithm of lock implementation is related to the query form of SQL

  • Record lock:Lock on a single row record. That is what we think of as row lock. from

`
where =
`
Trigger in the form of

  • Gap lock): gap lock, which locks a range, but does not include the record itself (it locks multiple rows in a range, including data that does not exist at all).

The purpose of gap lock is to prevent unreal reading caused by transaction insertion. This lock will only exist at isolation level RR or above. The purpose of gap lock is to prevent other transactions from adding new data in the gap.SQL is triggered by where >, > = and other range conditions,However, it will be changed according to whether the real records in the table are included in the locked range. If there are real records, they will evolve intoTemporary lock. On the contrary, it is caused by the gap.

  • Next key lock: it is a combination of record lock and clearance lock, locking a range and locking the record itself. The main way to solve the problem is to read the unreal line. Next key lock is the default of InnoDB. It’s a left open right close rule
  • Is lock: intention sharing lock、Intention Shared Lock。 When a transaction is ready to add an S (read) lock to a record, it needs to add an is lock at the table level first.
  • IX lock: exclusive lock、Intention Exclusive Lock。 When a transaction is ready to add an X (write) lock to a record, it needs to add an IX lock at the table level first.

interviewer:How is this thing realized?

t(id PK, name KEY, sex, flag);

There are four records in the table:

1, zhazhahui, m, A

3, nezha, m, A

5, lisi, m, A

9, wangwu, f, B
  • Record lock

select * from t where id=1 for update;
Lock record with id = 1

  • Clearance lock

select * from t where id > 3 and id < 9 ;

Lock the values of (3,5], (5,9) ranges, because when accessing the range records from 3 to 9, you need to lock the existing data in the table to solve the problem of unreadable reading and non repeatable reading

  • Temporary lock

select * from t where id >=9 ;

Will lock [9, + ∞). The query will first select record No. 9, so the locking range starts from 9 to positive infinite data.

interviewer:What about exclusive and shared locks? What is the content

Zha Zha Hui:Intentionally exclusive lock and intentionally shared lock are used to apply for access in advance when the current SQL request accesses the data row. If the final row lock fails to hit, it will degenerate to this onetypeThe table lock of.

interviewer:What’s the advantage of exclusive lock?

Zha Zha Hui:Pre judgment can be made in advance. Before each attempt to obtain a row lock, it will check whether there is a table lock. If there is a row lock, it will not continue to apply for a row lock, so as to reduce the cost of the lock. Thus, the whole table degenerates into a table lock.

interviewer:Then you can show me every scene

Well… (pupil dilates 2 times) do I understand what I’m not saying?
Why is it against you on purpose. Bullying people, isn’t it
I saw the interviewer suddenly cocked up his legs, and the beat of shaking his legs, looking at me. Tiktok is a lot of noise.
Ah, there’s no way to be an official or to crush people. He broke his teeth and swallowed by himself. You’d better not blink your eyes

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

Zha Zha Hui:Because the lock is to solve the problem of transaction concurrency, the record lock will not be demonstrated, and will directly wander in the gap and temporary lock.

Create statement:

CREATE TABLE `t1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Table data:

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

Clearance lock:

  • Turn off the default transaction auto commit mechanism in MySQL.

    • Before closing:

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

  • After closing:

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

Locking:
How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!
If you directly insert data > 8, it will block and lock. In order to solve the problem of inserting new data and resulting in unreal reading.
How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

Ah! I don’t know. The next article will be arranged for you
How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

interviewer:Isn’t your condition > = 8? How about eight? Being eaten spicy?

Zha Zha Hui:Don’t worry. It’s not finished yet. Why not specify 8?

because>=8The terms of the contract will be changed fromUpgrade clearance lock to temporary lockBecause your condition contains the real data of 8. So I’ll lock it up. As follows:

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

Therefore, the final row lock will be related to the conditional trigger of SQL statement. Once the range query contains the real data in the database, it will be upgraded to temporary lock. Don’t ask me why? Look at the definition

Interview monologueThis guy seems to have some goods, not bad. At the moment, the interviewer is smiling. But he had a new idea in his heart. Just wait for me to enter the urn

interviewer:What scenario will the downlink lock not take effect? What is the lock?

At the moment, I stay, this is what with what ah. I don’t think so. God damn it. It’s bad

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

Trigger mechanism of lock

Zha Zha Hui:
InnoDB’s row lock is triggered according to the index. If there is no relevant index, the row lock will degenerate into a table lock (that is, locking the rows in the whole table).
andlockIt’s locked inIndexesThat is, the value of the database field in the index tree.

  • ID is the primary key index field.

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

  • Lock the age field

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

  • The age field has no index and degenerates into a table lock. Direct queries will fail.

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

If there is an index, you can use the index field to query the available data, and the query of other fields will fail. Because the row lock could not be obtained, we had to wait. The lock is the index, so other queries with other index values can get the query data

  • Index field locked

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

  • The current field of the index is locked, and other index fields can be used to query

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

  • Not all index fields are bad.

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

interviewer:The lock you mentioned above can solve transaction concurrency, but mvcc is also used to solve concurrency, so why use lock? Tell me about it

Zha Zha Hui:Mvcc can solve the read consistency problems of dirty read, non repeatable read and unreal read, but in fact, it only solves the data read problem of ordinary select statement.
The read operation of transactions using mvcc is called snapshot read. All ordinary select statements are snapshot read under read committed and repeatable read isolation levels.

In addition to snapshot reading, there is another wayLock read, that is, lock the record when readingLock readIn this case, we still need to solve the problems of dirty reading, non repeatable reading and unreal reading.

For example, if the data of 1 4 7 9. If the condition is where > 4, then if it is not locked to (4, 7] (7, 9], (9, + ∞). That is bound to lead to the problem of unreadable reading and non repetition.

PS: don’t repeat? How does dirty reading come into being?

deadlock

interviewer:What’s the situation of database deadlock?

Zha Zha Hui:Deadlock means that two or more transactions occupy the same resource and request to lock the resource occupied by the other party, which leads to a vicious circle.

Deadlock can occur when transactions try to lock resources in different order. Deadlock may also occur when multiple transactions lock the same resource at the same time.

Generally, deadlock detection and deadlock timeout mechanism can be used to solve this problem.
Deadlock check:
Like InnoDB storage engine, it can detect the circular dependency of deadlock and return an error immediately. Otherwise, the deadlock will cause very slow queries. Through the parameter InnoDB_ deadlock_ Set detect to on to turn it on.

Timeout mechanism:
It is to give up the lock request when the query time reaches the lock waiting timeout setting. At present, InnoDB’s method to deal with deadlock is to roll back the transaction with the least row level exclusive lock (this is a relatively simple deadlock rollback algorithm).

The parameter InnoDB can be configured_ lock_ wait_ Timeout is used to set the timeout. If some users use a large transaction, set itThe lock timeout is greater than the transaction execution time
But in this case, the detection time of deadlock timeout check is unacceptable.

interviewer:How did InnoDB and MyISAM find deadlock?

Zha Zha Hui:

  • innodb

The database will record the lock maintained by the transaction unit lock and the lock it is waiting for. InnoDB provides the wait for graph algorithm to actively detect deadlocks. Whenever the lock request cannot be satisfied immediately and needs to wait, the wait for graph algorithm will be triggered. When the database detects that two transactions lock the same resource in different directions, it considers that a deadlock has occurred and triggers the wait for graph algorithm.

For example: transaction 1 locks a, transaction 2 locks B, and transaction 1 locks (waits) B, and transaction 2 locks a, resulting in a deadlock. Then the deadlock solution is to terminate the execution of one side transaction. Generally speaking, this kind of efficiency is the highest, which is also the method adopted by mainstream databases.

InnoDB’s current approach to deadlock isRoll back the transaction with the least row level exclusive lock. This is a relatively simple deadlock rollback method. After a deadlock occurs, only partially or completely rolling back one of the transactions can the deadlock be broken.

For transactional system, this is unavoidable, so the application must consider how to deal with deadlock in the design. In most cases, only the transaction rolled back due to deadlock needs to be re executed.

  • MyisAM

MyISAM only supports table level locks, so it can be acquired at one time after locking. Therefore, there is no need for multiple transactions to be processed after each other releases the lock. So there is no deadlock

interviewer:How to understand the wait for graph algorithm?

Zha Zha Hui:As shown below, four cars are deadlocks
How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

They wait for each other’s resources, and form a loop! Each vehicle can be seen as a node. When node 1 needs to wait for the resources of node 2, a directed edge is generated to point to node 2, and finally a directed graph is formed. We just need to check whether the directed graph has loops, and loops are deadlocks! This is the wait for graph algorithm.

How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

InnoDB regards each transaction as a node, and the resource is the lock occupied by each transaction. When transaction 1 needs to wait for the lock of transaction 2, it generates a directed edge from 1 to 2, and finally forms a directed graph.

interviewer:Since deadlocks cannot be avoided, how can they be reduced?

Zha Zha Hui:

  • Adjust / modify the application. In some cases, you can decompose a large transaction into several small transactions, so that the lock can be released more quickly, thus greatly reducing the frequency of deadlock. In other cases, deadlock occurs because two transactions operate on the same dataset of one or more tables in different order. It is necessary to read and write these datasets in the same order. In other words, the access to these datasets is serialized. In this way, when concurrent transactions occur, deadlocks become lock waiting.
  • Modify the table schema, such as deleting foreign key constraints to separate two tables, or adding indexes to reduce the number of rows scanned and locked.
  • If a gap lock occurs, you can change the transaction isolation level of the session or transaction to RC (read committed) level to avoid it. You can avoid many deadlocks caused by gap locks, but you need to change the binlog_ Format is set to row or mixed format.
  • Adding a reasonable index to a table will add a lock (equivalent to a table lock) to each row of records in the table if the index is not moved, which greatly increases the probability of deadlock.
  • In order to avoid deadlock when multiple concurrent write operations are performed on a single InnoDB table, the necessary locks can be obtained at the beginning of a transaction by using the select… For UPDATE statement for each meta ancestor (row) that is expected to be modified, even if the change statement of these rows is executed later.
  • After obtaining the read lock of a row through select… Lock in share mode, if the current transaction needs to update the record again, it is likely to cause deadlock. Due to lock read, the

At this time, I saw the interviewer sitting opposite, stroking his hairless chin, pretending to be thoughtful, as if looking at what this is. Did you let me pass?
At the moment, my heart is like a deer bumping into each other, shouting that I want to do it twice. It’s really not easy. At this moment, he got up and stood up, the white T-shirt wrapped his big belly, like waves rolling up and down. At first glance, there is no lack of meat on the wine table.

I saw that the young man was good.
How much is MySQL's lock? And Tencent big brother's technical interview, I still underestimate the lock!

Is that for sure? It’s not easy. If I don’t open a few lols today, I can’t get rid of my hatred

interviewer:In fact, the database has a lot of content. Go back and prepare for the next interview

。。。。 What is it, next time? That’s not the case this time. It’s not enough. Your chin is not hairy. You can’t smooth it out. It’s so amazing. At this time, my heart is like a dragon crossing the river. White crane bright wing impulse hit him, but I this small body board can’t

Zha Zha Hui:OK, how long will it be next time? I haven’t sorted it out for many days. Give me a letter.

I looked at him with those watery, pathetic little eyes and said. He is very gentle smile, said

interviewer:Come on, young man. Don’t worry. I’ll take care of you. Come on

I’ll squeeze peanut oil with your silk. Face a try, also think my face out of the oil is not much, are you squeeze out. Only to resist the inner impulse. Ah, the senior officials are killing people
Zha Zha Hui:OK, I’ll go
At this moment, my gray figure is like Kong Yiji in Lu Xun’s works

reference resources:
High performance mysql
https://zhuanlan.zhihu.com/p/29150809
https://www.cnblogs.com/yulibostu/articles/9978618.html

If you have any help, you are welcome to like and pay attention to the sharing amount. Search [lotus boy Nezha] on wechat to get systematic content. Add me to the group chat to exchange and improve learning progress.

Recommended Today

Third party calls wechat payment interface

Step one: preparation 1. Wechat payment interface can only be called if the developer qualification has been authenticated on wechat open platform, so the first thing is to authenticate. It’s very simple, but wechat will charge 300 yuan for audit 2. Set payment directory Login wechat payment merchant platform( pay.weixin.qq . com) — > Product […]