What are the disadvantages of MySQL lock tables?

The following articles mainly introduce the concept of MySQL lock tables and describe the disadvantages of the concept of MySQL table lock, if you are interested in the MySQL table lock related content, the following articles will introduce the main content. I hope you will gain some benefits after browsing.

MySQL lock table

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

For InnoDB and bdb tables, MySQL lock tables use 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 lock is waiting on a thread because the disk space is full, but the remaining disk space is required for the thread to continue processing. 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.

MySQL 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.

Use the LOW_PRIORITY attribute to reduce the priority of INSERT, UPDATE, and DELETE operations.

Use HIGH_PRIORITY to increase the SELECT statement priority.

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.

When the use of SELECT and DELETE fails, the delete limit parameter may be useful.

Using SQL _BUFFER_RESULT when executing SELECT helps reduce the duration of MySQL lock tables.

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.