DONG Yuxuan @ Jan 12, 2021 CST
The easiest way to see is putting yourself in the position of a MySQL developer.
Three events happened in order:
T1 and T2 should both acquire their locks immediately but T3 should block.
What algorithm could make it practical?
If MySQL locks the table before it locks a row, T2 will block.
If MySQL doesn’t lock the table before lock a row, T3 will acquire the lock while it shouldn’t.
Thus there must be a third type of lock and it’s called intension lock. Every time the lock on a row needs to be acquired the intension lock on the table must be acquired first. Make intension lock and table lock conflict but keep intension locks compatible within themselves. By this algorithm, locks on different rows can be acquired parallelly and locks on rows can conflict with the lock on the table.