Hello everyone, I am smaller than Koha. A few days ago, I was able to share the interview question of the first phase, which locks are available in MySQL and the characteristics of these locks. This interview question is a question that is often asked, and everyone’s feedback is quite good. So here’s a summary for today.

Launched on the official account [Terminal R&D Department]

The core

MySQL lock mechanism, divided into what locks, what are the characteristics?

The target

  • The classification of the lock
  • The characteristics of the lock
  • Lock application scenarios

### Transaction concurrency issues and solutions

supplement

1. Dirty reads: A transaction can read uncommitted data from another transaction. The diagram below:

Resolve dirty read: add write lock when modifying data

Non-repeatable reads: Queries with different results at different times in a transaction may be updated or deleted. The following figure

The first time transaction A reads the data with A read lock, transaction B cannot be modified, will block.

3. Phantom read: query at different time periods in a transaction, with different records. The difference with non-repeatable reads is that in a phantom read, the data that has been read does not change, but there will be more data satisfying the query criteria than before. The diagram below:

Transaction B cannot be inserted into the locked interval to prevent phantom reading.

The answer

Classification by lock granularity:

  • 1. Row level locking
  • 2. Tablet-level locking
  • 3. Page-level locking

Explanation:

1, table level lock: low overhead, lock fast; There are no deadlocks; With large lock granularity, the probability of lock conflict is the highest and the concurrency is the lowest.

2. Row level locking: high overhead, slow locking; There will be a deadlock; The lock granularity is the smallest, the probability of lock conflict is the lowest, and the degree of concurrency is the highest.

3, page lock: cost and lock time between table lock and row lock; There will be a deadlock; The locking granularity is between table lock and row lock, and the concurrency is moderate.

Classified by lock level:

  • 1. Shared locks
  • 2. Exclusive locks
  • 3, intention lock

Explanation:

1. Shared lock: Once locked, no transaction (including the current transaction) can modify it, other transactions can read the data concurrently, or add a shared lock to the data

2, Exclusive lock (exclusive lock) : If A transaction applies an exclusive lock to data A, other transactions cannot read data concurrently, nor can they apply any type of lock to A. Transactions that are allowed exclusive locks can both read and modify data.

3. Intent 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 Bai Qiao learning materials:

1. Books:

codeGoogler/ProgramBooks

2. Video Tutorials:

SpringBoot, Spring, MyBatis, Redis, RabbitMQ, SpringCloud, High Concurrency (Continuous Update)_ This era, as a programmer may want to learn a small program -CSDN blog

The last

At the end of the day, we will continue to work under the same title: “Terminal R&D Department”. Now, every day, we will recommend a quality article about technology, mainly sharing JAVA-related technology and interview skills. Our goal is to know what it is and why, lay a good foundation and do a good job! This main technology public number is worth everyone’s attention.

If you have any technical questions, you can consult me. The road of technology is long and elegant. Brother Yu can always accompany you. If you can help, welcome thumb up!