Advantages and disadvantages of Mysql row-level locks and page-level websites

Mysql row-level locks can reduce conflict locks when multiple threads request different records. The following describes the advantages and disadvantages of Mysql row-level locks and page-level locks.

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:

 
 

  
  
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 Mysql row-level locks and BDB uses page-level locks. For InnoDB and BDB storage engines, deadlocks may occur. This is because InnoDB will automatically capture row locks and BDB will capture page locks when executing SQL statements, rather than doing so at the beginning of the transaction.

Mysql row-Level Lock has the following advantages:

Reduce the conflict lock when multiple threads request different records.

Reduce data changes during transaction rollback.

It is possible to lock a single row of records for a long time.

Mysql row-Level Lock has the following Disadvantages:

It consumes more memory than page-level locks and table-level locks.

When used in a large number of tables, it is slower than page-level locks and table-level locks because they need to request more resources.

When you need to frequently perform group by operations on most data or frequently scan the entire table, it is obviously worse than other locks.

Using Higher-Level locks makes it easier to support different types of applications, because the cost of such locks is much lower than that of Row-level locks.

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.

Table-level locks differ from row-level locks or page-level locks in the following ways:

Make a version where there is one write and multiple reads at the same time, 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.
 

Method for randomly obtaining Mysql Data

Back up data based on Mysql tables

How to solve the problem of table structure replication in MySQL

How to clear a table in Mysql

Several common MySQL performance testing tools