MySQL Lock overview

Compared with other databases, the lock mechanism of MySQL is relatively simple, the most notable feature of the base is that different storage engines support different locking mechanisms. For example, the MyISAM and memory storage engines use a table-level lock (table-level locking); The BDB Storage engine uses page locks (page-level locking), but also supports table-level locks; The InnoDB storage engine supports both row-level and technical table-level locks, but row-level locks are used by default.

MySQL features of these 3 types of locks can be broadly summarized as follows:

Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock conflict is the highest probability, concurrency is the lowest.

Row-level locks: overhead, locking slow, deadlock, minimum lock granularity, the lowest probability of lock collisions, and highest concurrency.

Page locks: overhead and reselling time bounds between table and row locks, deadlock occurs, lock granularity bounds between table and row locks, and concurrency is common.

You can analyze table lock contention on the system by examining the table_locks_waited and table_locks_immediate state variables:

If the value of table_locks_waited is high, there is a serious emergency case for table-level locks.

MySQL Lock overview