Principles of MySQL Locking Mechanism

The following articles mainly describe the MySQL locking mechanism. We all know that MySQL locking is becoming more and more popular in practical applications, so the actual operations on it are becoming more and more important, the following is a description of the specific solution. I hope you will find some benefits. Supports table-level locking for MyISAM and MEMORY tables, page-level locking for BDB tables, and I

The following articles mainly describe the MySQL locking mechanism. We all know that MySQL locking is becoming more and more popular in practical applications, so the actual operations on it are becoming more and more important, the following is a description of the specific solution. I hope you will find some benefits. Supports table-level locking for MyISAM and MEMORY tables, page-level locking for BDB tables, and I

The following articles mainly describe the MySQL locking mechanism. We all know that MySQL locking is becoming more and more popular in practical applications, so the actual operations on it are becoming more and more important, the following is a description of the specific solution. I hope you will find some benefits.

Supports table-level locking for MyISAM and MEMORY tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.

In many cases, you can guess which MySQL lock type is best used by the application based on training, but it is generally difficult to say that a given lock type is better than another one. Everything depends on the application. Different parts of the application may require different lock types.

To determine whether you want to use a row-level locking storage engine, you should check what the application is doing and what options and update statements are used together. For example, most Web applications execute many choices, but seldom delete them. They only update the value of the keyword and insert only a small number of specific tables. The basic MySQL MyISAM settings have been well adjusted.

In MySQL, table locks are not deadlocked when table-level locking is used for storage engines. This means that all necessary locks are requested immediately at the beginning of a query and the tables are always locked in the same order.

WRITE,MySQL uses the following table locking method:

If there is no lock in the table, put a write lock on it.

Otherwise, put the lock request in the write lock queue.

The locking method for READ and MySQL is as follows:

If there is no write lock on the table, place a read lock on it.

Otherwise, put the lock request in the read lock queue.

When a lock is released, the lock can be obtained by the threads in the write lock queue, followed by the threads in the read lock queue.

This means that if you have many updates on a table, the SELECT statement will wait until there are no more updates.

You can check the table_locks_waited and table_locks_immediate status variables to analyze the competition for locking MySQL tables on the system:

 
 

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

If the INSERT statement does not conflict with each other, you can use a hybrid parallel INSERT and SELECT statement for the MyISAM table without locking. That is to say, you can insert rows when other customers are reading the MyISAM table. If the data file does not contain idle blocks, there is no conflict, because in this case, records are always inserted at the end of the data file. (Deleting or updating rows from the middle of the table may result in holes ). If there are holes, when all holes are filled with new data, parallel inserts can be automatically enabled again.

If you cannot INSERT data at the same time, to perform multiple INSERT and SELECT operations in a table, you can INSERT rows in the temporary table and immediately update the real table with the records in the temporary table.

This can be done with the following code:

 
 

  
  
MySQL> LOCK TABLES real_table WRITE, insert_table WRITE; 
  
  
MySQL> INSERT INTO real_table SELECT * FROM insert_table; 
  
  
MySQL> TRUNCATE TABLE insert_table; 
  
  
MySQL> UNLOCK TABLES; 
 
 

InnoDB uses row locking and BDB uses page MySQL locking. Both storage engines may have deadlocks. This is because, during SQL statement processing, InnoDB automatically acquires row locks and BDB obtains page locks, rather than at transaction startup.

Advantages of Row-level locking:

Only a small number of lock conflicts exist when different rows are accessed in many threads.

Only a few changes are allowed during rollback.

A single row can be locked for a long time.

Disadvantages of Row-level locking:

More memory is occupied than page-level or table-level locking.

When used in most tables, it is slower than page-level or table-level locking because you must obtain more locks.

If you often perform group by operations on most data or scan the entire table frequently, it is much slower than other locks.

With high-level locking, you can easily adjust the application by supporting different types of locking because the lock cost is less than the row-level locking.

In the following cases, table locking takes precedence over page-level or row-level locking:

Most of the table statements are used for reading.

Read and update strict keywords. You can update or delete a row that can be extracted with a single read Keyword:

 
 

  
  
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value; 
  
  
DELETE FROM tbl_name WHERE unique_key_col=key_value; 
 
 

SELECT combines parallel INSERT statements with few UPDATE or DELETE statements.

There are many scan or group by operations on the entire table without any write operations.

Different from row-level or page-level MySQL lock options:

Version (for example, the technology used to insert data in MySQL in parallel), which can be a write operation with many read operations at the same time. This indicates that the database or table supports different views of Data Dependencies, depending on the Access time. Other common terms are "time tracking", "Write replication", or "On-Demand replication ".

On-demand replication takes precedence over page-level or row-level locking in many cases. However, in the worst case, it may use more memory than using regular locks.

In addition to row-level MySQL locks, you can use application-level locks, such as using GET_LOCK () and RELEASE_LOCK () in MySQL (). These are creation locks, and they can only work in well-running applications.