The distinction between lock and latch in MySQL

This is to distinguish between the easily confusing concept of lock and latch. In the database, lock and latch can be locks, but they have different meanings.

Latch is generally called a latch (lightweight lock) because it requires a very short time to lock, if the Shong time is long, then the application performance is very poor, in the InnoDB storage engine, latch can be divided into mutexes (mutex) and Rwlock (read-write lock) Its purpose is to ensure the correctness of critical resources of concurrent thread operation, and there is no mechanism of deadlock detection.

The lock object is a transaction, which is used to lock the UI in the database like tables, pages, and rows. And the general lock object is released only after a transaction commit or rollback (different transaction isolation levels may be released at different times), and lock, as in most databases, has a deadlock mechanism. The table shows the difference between lock and latch

Latch in the INNODB storage engine can be viewed by command show engine INNODB MUTEX

Mysql>SHOW ENGINE INNODB MUTEX;+--------+-------------------+-------------+|Type|Name|Status|+--------+-------------------+-------------+|InnoDB|dict0dict.cc:1057 |Os_waits=2  ||InnoDB|log0log.cc:844    |Os_waits=1  ||InnoDB|fil0fil.cc:1690   |Os_waits=1  ||InnoDB|dict0dict.cc:1066 |Os_waits=3  ||InnoDB|log0log.cc:907    |Os_waits= One |+--------+-------------------+-------------+5Rowsinch Set(4.14Sec

More information on latch can be seen through the show ENGINE INNODB MUTEX under the debug version

As you can see from the above example, the type is always InnoDB, the column name shows the latch information and the source location (number of lines). Column status is more complex, in debug mode, in addition to displaying os_waits, count, Spin_waits, Spin_rounds, Os_yields, Os_wait_times, and so on.

The lock information is intuitive compared to latch. Lock information can be viewed through the show ENGINE INNODB STATUS and the Innodb_trx, Innodb_locks, and innodb_lock_waits under the INFORMATION_SCHEMA architecture.

The distinction between lock and latch in MySQL