Hi, I’m less than Goha. A few days ago, I can share the first interview question, which kinds of locks in MySQL and what characteristics of these locks. This interview question is often asked about an interview question, everyone’s feedback is quite good. Today we are here to sum up.
First published on the public account [Terminal R&D Department]
The core
Mysql lock mechanism, which is divided into what locks, what features?
The target
- The classification of the lock
- The characteristics of the lock
- Application scenarios of locks
Problems and solutions arising from transaction concurrency
supplement
1. Dirty read: A transaction can read uncommitted data from another transaction. The diagram below:
Solve dirty read: Add write lock when modifying data
Non-repeatable read: Different query results occur at different times in a transaction and may be updated or deleted. The following figure
Solve unrepeatable read: when transaction A reads data for the first time, A read lock is added to transaction B, which cannot be modified and will block.
3. Phantom read: the number of records is different in different time periods of a transaction. The difference with unrepeatable reads is that in a magic read, the read data does not change, but more data meets the query conditions than before. The diagram below:
Solve phantom read: transaction A range query with A temporary key lock, locked adjacent interval, transaction B can not be inserted into the locked interval, to prevent phantom read.
The answer
Classification by lock granularity:
- 1. Row-level locking
- 2. Table level locking
- Page-level locking
Explanation:
1, table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low.
2, row-level lock: high overhead, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.
3. Page lock: the overhead and locking time are between table lock and row lock; Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.
By lock level:
- 1. Shared lock
- 2, exclusive lock
- 3. Intent lock
Explanation:
1. Shared lock: Once locked, no transaction (including the current transaction) can modify it. Other transactions can concurrently read data or add a shared lock to the data
2. Exclusive lock (exclusive lock) : If A transaction has an exclusive lock on data A, other transactions cannot concurrently read data, nor can they add any type of lock on data A. Transactions that are granted exclusive locks can both read and modify data.
3. Intention lock: it can be divided into intention shared lock and intention exclusive lock. Features: InnoDB automatically adds intention lock without user intervention.
Ps: How to learn Java, recommend two white piao learning materials:
1. Books:
codeGoogler/ProgramBooks
2: Video tutorial:
SpringBoot, Spring, Mybatis, Redis, RabbitMQ, SpringCloud, high concurrency (continuous update)
The last
Finally, our job title is “Terminal RESEARCH and Development Department”. At present, we recommend a high-quality technology-related article every day, mainly sharing Java related technology and interview skills. Our goal is to know what it is, why, lay a solid foundation, and do everything well! The main technology of the public super worth our attention.
If you have any technical problems, you can consult me, the technical road is long and elegant, yu elder brother can always accompany. If it helps, please like it!
