[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

Time:2021-10-20

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]

core

What are the locks and characteristics of MySQL lock mechanism?

target

  • Classification of locks
  • Characteristics of lock
  • Application scenario of lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

###Problems and solutions caused by transaction concurrency

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

supplement

1. Dirty read: one transaction can read uncommitted data from another transaction. As shown below:

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

Solve dirty read: add write lock when modifying data

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

Non repeatable reading: in a transaction, queries in different time periods have different results, which may be updated or deleted. As shown below

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

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.

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

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:

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

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 first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock

The answer is revealed

Classification by lock granularity:

  • 1. Row level lock
  • 2. Table lock
  • 3. Page level lock

Explanation:

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:

Explanation:

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:

1. Books:

codeGoogler/ProgramBooks

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

last

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!

[the first bullet of Q & a sharing] MySQL lock summary: characteristics of MySQL row lock, table lock, exclusive lock and shared lock