How to optimize MySQL locks

The following articles mainly introduce the actual optimization process of MySQL locks. The following describes the specific solution of MySQL locks optimization. I hope this will help you in your future studies. We all know that MySQL currently supports table-level locks for ISAM, MyISAM, and MEMORY (HEAP) tables. BDB tables support page-level locks, while InnoDB tables support many row-level locks

The following articles mainly introduce the actual optimization process of MySQL locks. The following describes the specific solution of MySQL locks optimization. I hope this will help you in your future studies. We all know that MySQL currently supports table-level locks for ISAM, MyISAM, and MEMORY (HEAP) tables. BDB tables support page-level locks, while InnoDB tables support many row-level locks

The following articles mainly introduce the actual optimization process of MySQL locks. The following describes the specific solution of MySQL locks optimization. I hope this will help you in your future studies. We all know that MySQL currently supports table-level locks for ISAM, MyISAM, and MEMORY (HEAP) tables.

The BDB table supports page-level locks and the InnoDB table supports row-level locks.

Most of the time, we can use experience to guess what kind of lock is more suitable for the application, but it is often difficult to say that a MySQL lock is better than others, it is all determined by the application, different locks may be required in different places.

Lock Mechanism

Currently, MySQL supports table-level locks for ISAM, MyISAM, and MEMORY (HEAP) tables. BDB tables support page-level locks and InnoDB tables support row-level locks. Most of the time, we can use experience to guess what kind of lock is more suitable for the application, but it is often difficult to say that a lock is better than others, it is determined by the application, different locks may be required in different places.

To determine whether to use a row-Level Lock storage engine, you need to check what the application is doing and how the query and update statements are used. For example, many web applications perform a large number of queries and seldom delete them. They only insert records into specific tables based on index updates. It is appropriate to use the basic MySQL MyISAM table.

The storage engine for table-level locks in MySQL releases deadlocks. To avoid deadlock, you can request a lock before any query and lock the table in the order of the request.

The implementation mechanism of the table lock used for WRITE in MySQL is as follows:

If the table is not locked, add a write lock.

Otherwise, put the request in the write lock queue.

The implementation mechanism of the table lock used for READ (READ) in MySQL is as follows:

If the table does not have a write lock, add a read MySQL lock.

Otherwise, put the request in the read lock queue.

After the lock is released, the thread in the write lock queue can use this lock resource before it is the turn to read the thread in the lock queue.

That is to say, if there are many update operations in the table, SELECT must wait until all updates are completed.

Starting from MySQL 3.23.33, you can use the status variables Table_locks_waited and Table_locks_immediate to analyze the lock table contention in the system:

 
 

  
  
mysql> SHOW STATUS LIKE 'Table%'; 
  
  
+-----------------------+---------+ 
  
  
| Variable_name | Value | 
  
  
+-----------------------+---------+ 
  
  
| Table_locks_immediate | 1151552 | 
  
  
| Table_locks_waited | 15324 | 
  
  
+-----------------------+---------+ 
 
 

After MySQL 3.23.7 (3.23.25 on Windows), as long as there are no conflicting INSERT operations in the MyISAM table, you can freely execute the INSERT and SELECT statements in parallel without using the MySQL lock table.

That is to say, you can insert a new record when other clients are reading the MyISAM Table Record. If there are no spare disk blocks in the middle of the data file, there will be no conflict, in this case, all new records are written at the end of the data file (empty may occur when the table is deleted or updated ). When the empty space is filled with new data, the parallel insertion feature is automatically re-enabled.

If you want to perform a large number of INSERT and SELECT operations on a table, but parallel INSERT is not possible, you can INSERT records into the temporary table, then, the data in the temporary table is updated to the actual table on a regular basis. You can use the following command: