Why Intention Locks Are Required in MySQL

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?

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.