MySQL lock table Introduction

I believe you are familiar with MySQL lock tables. I will introduce you to MySQL lock tables for your reference. If you are interested in MySQL lock tables, take a look.

MySQL lock table

In order to have quick locks, apart from the InnoDB and BDB storage engines, MySQL uses table-level locks instead of page, row, and column-level locks ).

For InnoDB and bdb tables, MySQL uses table-level locks only when the lock tables Table is specified. We recommend that you do not use lock tables in these two TABLES, because InnoDB automatically uses row-level locks and BDB uses page-level locks to ensure transaction isolation.
If the data table is large, table-level locks are much better than Row-level locks in most applications, but there are some traps.
Table-level locks allow many threads to read data from the data table at the same time. However, if another thread wants to write data, it must first obtain exclusive access. When updating data, you must wait until the update is complete before other threads can access the table.
Update operations are generally considered to be more important than reading, so they have a higher priority. However, it is recommended that you first check whether the data table has a high Select operation, and the update operation is not 'urgender '.
The table is locked in a thread while waiting, because the disk space is full, but the remaining disk space is required. This thread can continue to process the problem. In this case, all the threads that want to access the problematic table will be set to the waiting state until there is any available disk space.

Table locks are unfavorable in the following scenarios:
A client submits a long-running Select operation.
Other clients submit the Update operation for the same table. This client must wait until the Select statement is complete before execution can begin.
Other clients also submit Select requests for the same table. Since the priority of Update is higher than that of Select, the Select statement is executed only after the Update is complete. It is also waiting for the first Select operation.

The following describes how to reduce resource contention caused by table locks:
To make the Select statement as fast as possible, you may need to create some summary tables.
Use the -- low-priority-updates parameter when starting mysqld. Therefore, the priority of the update operation is lower than that of the Select operation. In this case, in the preceding assumptions, the second Select statement will be executed before the Insert statement, and you do not need to wait for the first Select statement.
You can run the SET LOW_PRIORITY_UpdateS = 1 command to specify that all update operations are put in a specified link. For details, see "14.5.3.1 SET Syntax ".
Use the LOW_PRIORITY attribute to reduce the priority of Insert, Update, and Delete operations.
Use HIGH_PRIORITY to increase the Select statement priority. For details, see "14.1.7 Select Syntax ".
Starting from MySQL 3.23.7, you can specify the system variable max_write_lock_count as a relatively low value when starting mysqld, it can temporarily increase the insert count of a table to the priority of all Select operations after a specific value. It allows a READ lock after the WRITE lock reaches a certain number.
When the use of Insert and Select statements fails, you can use the MyISAM table instead. This table supports concurrent Select and Insert operations.
Insert DELAYED may be useful when both Insert and delete operations are performed on the same table. For details, see "14.1.4.2 Insert DELAYED Syntax ".
When the use of Select and Delete fails, the Delete LIMIT parameter may be useful. For details, see "14.1.1 Delete Syntax"
Using SQL _BUFFER_RESULT when executing Select helps reduce the duration of the short lock table. For details, see "14.1.7 Select Syntax ".
You can modify the source code 'mysys/thr_lock.c 'and use only one queue. In this case, the write lock and read lock have the same priority, which may be helpful to some applications.

The following are some suggestions for MySQL locks:
As long as a large number of update and query operations are not mixed in the same table, the current user is not a problem.
Execute lock tables to increase the speed. Many update operations are placed in one LOCK, which is much faster than many updates without a LOCK ). Splitting data into multiple tables may also be helpful.
When MySQL encounters a speed problem caused by the lock table, converting the table type to InnoDB or BDB may help improve performance.

Detailed description of MySQL Data Table types

Enumeration in the MySQL Field

Detailed description of MySQL foreign key settings

Add and delete fields in MySQL

In-depth discussion on MySQL Lock Mechanism