Hello, I’m goha. I shared the first interview question a few days ago. What kinds of locks are there in MySQL and what are the characteristics of these locks? This interview question is often asked. Everyone’s feedback is very good. Today I would like to sum up.
First official account [terminal R & D]
What are the locks and characteristics of MySQL lock mechanism?
- Classification of locks
- Characteristics of lock
- Application scenario of lock
###Problems and solutions caused by transaction concurrency
1. Dirty read: one transaction can read uncommitted data from another transaction. As shown below:
Solve dirty read: add write lock when modifying data
Non repeatable reading: in a transaction, queries in different time periods have different results, which may be updated or deleted. As shown below
Solve the problem of non repeatable reading: transaction a adds a read lock when reading data for the first time, and transaction B cannot be modified and will be blocked.
3. Phantom reading: query in different time periods in a transaction, and the number of records is different. The difference from non repeatable reading is that in phantom reading, the read data will not change, but more data will meet the query conditions than before. As shown below:
Solve unreal reading: when querying the range of transaction a, add a temporary key lock to lock the adjacent interval. Transaction B cannot be inserted into the locked interval to prevent unreal reading.
The answer is revealed
Classification by lock granularity:
- 1. Row level lock
- 2. Table lock
- 3. Page level lock
1. Table level lock: low overhead and fast locking; No deadlock; The locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.
2. Row level lock: high overhead and slow locking; Deadlock will occur; The locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.
3. Page lock: the overhead and locking time are bounded between table lock and row lock; Deadlock will occur; The locking granularity is between table lock and row lock, and the concurrency is general.
Classification by lock level:
- 1. Shared lock
- 2. Exclusive lock
- 3. Intent lock
1. Shared lock: once locked, any transaction (including the current transaction) cannot modify it. Other transactions can read data concurrently, or add a shared lock to this data
2. Exclusive lock (exclusive lock): if a transaction adds an exclusive lock to data a, other transactions cannot read data concurrently, nor can they add any type of lock to a. Transactions that are granted exclusive locks can both read and modify data.
3. Intention lock: it is divided into intention sharing lock and intention exclusive lock. Features: intention lock is automatically added by InnoDB without user intervention.
PS: how to learn Java? I recommend two materials for white whoring:
2: Video tutorial:
Download springboot, spring, mybatis, redis, rabbitmq, springcloud and high concurrency (continuous update) from free Java resources throughout the network_ In this era, as a programmer, you may have to learn small programs – CSDN blog
Finally, as usual, Amway Yibo’s job number: “terminal R & D department”, currently recommends a high-quality technology related article every day, mainly sharing Java related technologies and interview skills. Our goal is to know what it is and why, lay a good foundation and do everything well! This public official account is worth our attention.
If you have any technical problems, you can consult me. The technical road is long and elegant, and brother Yu can always accompany you. If you are helpful, welcome to praise!