MySQL table-level locks, row-level locks, and page-level locks

The following articles mainly describe the differences between MySQL table-level locks and MySQL row-level locks or MySQL page-level locks, as well as the superiority of table-level locks and other two locks, the following is a description of the specific content of the article. I hope you will gain some benefits.

The differences between MySQL table-level locks and row-level locks or page-level locks are as follows:

Make the version of one write and multiple reads (for example, concurrent inserts in MySQL ). That is to say, databases/Tables support different attempts based on the time points at which data is accessed. Other names include time travel, write replication, or on-demand replication.

On-demand replication is much better than page-level or row-level locks in many cases. However, in the worst case, more memory is used than other normal locks.

Application-level locks can be used to replace row-level locks, such as GET_LOCK () and RELEASE_LOCK () in MySQL (). However, they are suggested locks (Original: These are advisory locks), so they can only be used in secure and trusted applications.

MySQL table-level locks are superior to page-level locks and row-level locks in the following situations:

Many operations are read tables.

Read and update indexes with strict conditions. When updating or deleting indexes, you can use a separate index to read them:

 
 

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

The SELECT and INSERT statements are executed concurrently, but there are only a few UPDATE and DELETE statements.

Many scan tables and group by operations on the entire table, but there is no write table.
 

The above content is an introduction to the relationship between MySQL table-level locks and row-level locks or page-level locks. I hope you will gain some benefits.