undefinedfix
Sign in

What's the difference between MySQL deadlock and lock conflict?

Rosy edited in Mon, 02 Jan 2023

MySQL has row level lock and table level lock. Here are their differences

Table level lock: low cost and fast locking; There will be no deadlock; The lock granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.

Row level lock: high cost and slow locking; There will be deadlock; Lock granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

Now I have a few questions:

1. Why do row level locks have deadlocks while table level locks don't?

2. Why is row level lock conflict low and table level lock high?

3. How to understand the concept of deadlock and lock conflict? What's the difference?

1 Replies
jet_black82
commented on Tue, 03 Jan 2023
  1. Why do row level locks have deadlocks while table level locks don't? Table level locks generally refer to MyISAM storage engine, because it can obtain all the needed locks at one time, so either all of them are satisfied, or none of them, so no deadlocks will be generated. The row lock is different. If operation a locks record 1 first and then record 2, and operation B locks record 2 first and then record 1, there will be a situation: a locks record 1, B locks record 2, and then a waits for B to release the lock of record 2, and B waits for a to release the lock of record 1, waiting for each other to produce a life and death lock.
  2. Why the row level lock has low lock conflict and the table level lock is high? How to understand that the row lock only locks the records of the operation, and the probability of operating the same record at the same time is very low, so the probability of cable conflict is also very low. When the table lock is different, the probability of accessing a table at the same time is generally very high, locking the whole table at one time, All operations on the same table can only wait for other operations to release the lock, so the conflict is relatively high
  3. How to understand the concept of deadlock and lock conflict? What's the difference? A deadlock is when two locks wait for each other and do not yield to each other, and then everyone is deadlocked there. Lock conflict is a normal phenomenon, I need to lock, but now others hold the lock, I can not complete the lock operation, this is the conflict, but I can wait for others to release the lock again.