In-depth discussion on MySQL Lock Mechanism

What is the MySQL lock mechanism? This is a problem that many people have mentioned. The following describes the MySQL lock mechanism in detail. I hope you can learn more about the MySQL 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 in MySQL for WRITE) 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 in MySQL for READ reading is as follows:
If no write lock is applied to the table, a read lock is applied.
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 |
| Maid | 15324 |
+ ----------------------- + --------- +

After MySQL 3.23.7 is 3.23.25 on Windows), as long as there are no conflicting Insert operations in the MyISAM table, the Insert and Select statements can be executed in parallel without using the 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. When the table is deleted or updated, it may lead to holes ). 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:
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-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.
Row-level locks have 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.
Row-level locks have 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.
Original article: Versioning (such as we use in MySQL for concurrent inserts) where you can have one writer at the same time as your readers. this means that the database/table supports different views for the data depending on when you started to access it. other names for this are time travel, copy on write, or copy on demand.
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 used to recommend the original lock: These are advisory locks), so they can only be used in secure and trusted applications.

Implementation of MySQL fuzzy query with multiple fields in a single table

Provides you with an in-depth understanding of MySQL index types

MySQL foreign key and reference integrity

How MySQL defines foreign keys

Detailed description of MySQL Data Table types