Optimization of table-level locking of MyISAM storage engine

Optimization of table-level locking of MyISAM storage engine

MySQL supports table-level locking for MyISAM and MEMORY tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.
Table-level locks: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of lock conflict, the lowest concurrency.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock conflict, and the highest concurrency.
Page lock: overhead and lock time are between table locks and row locks. Deadlocks may occur. The lock granularity is between table locks and row locks, with a general concurrency.
There are two table-level locks for MySQL: Table shared read locks and table exclusive write locks. Read operations on the MyISAM table do not block read requests from other users to the same table, but block write requests to the same table. Write operations on the MyISAM table, the read and write operations on the same table by other users are blocked. The read and write operations on the MyISAM table are serialized!
For large tables, table locking is better for most applications than row locking, but there are some defects. Table locking allows many threads to read data from a table at the same time. However, if a thread wants to write data to a table, it must first obtain exclusive access. During the update, all other threads that want to access the table must wait until the update is complete.
Table-level locking features of the MyISAM storage engine:
1. Reduce the execution time of SQL statements;
2. Data Table Indexing ensures reasonable and efficient data table indexing;
3. Set SELECT statements to be executed better than UPDATE operations (INSERT, UPDATE, DELETE) to reduce the priority of write operations;
By specifying the startup parameter low-priority-updates, the MyISAM engine gives the Read Request priority by default.

Run the SET LOW_PRIORITY_UPDATES = 1 command to lower the priority of the update request sent by the connection.

You can specify the LOW_PRIORITY attribute of an INSERT, UPDATE, or DELETE statement to reduce the priority of the statement.

4. Use temporary tables to improve query performance;
When there is a large amount of data to be queried, you can use the SQL _BUFFER_RESULT. Option to forcibly place the results in a temporary table, so that the Mysql table lock can be quickly released;
5. Table Structure Design. Avoid mixing a large number of updates and queries in the same table and split the table properly;
6. When a table combines a large number of INSERT and SELECT operations, SELECT supports concurrent SELECT and INSERT operations.

The MyISAM storage engine has a system variable concurrent_insert, which is used to control its Concurrent Insertion behavior. The values can be 0, 1, or 2, respectively.
Concurrent inserts are not allowed when concurrent_insert is set to 0.
When concurrent_insert is set to 1, if the MyISAM table does not have holes (that is, the rows in the middle of the table are not deleted), MyISAM allows a process to read the table at the same time, another process inserts records from the end of the table. This is also the default setting of MySQL.
When concurrent_insert is set to 2, records can be inserted concurrently at the end of the table regardless of whether there are holes in the MyISAM table.