Shulou(Shulou.com)05/31 Report--

This article shows you how to understand the lock mechanism of mysql, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Lock 0.1 lock mechanism

Currently, MySQL supports table-level locks for ISAM, MyISAM, MEMORY (HEAP) tables, BDB tables support page-level locks, and InnoDB tables support row-level locks.

In many cases, experience can be used to guess what kind of lock is more appropriate for the application, but it is usually difficult to say that one lock is better than the other, it all depends on the application, and different locks may be required in different places.

To decide whether or not to use a storage engine that supports row-level locks, look at what the application does and how the query and update statements are used. For example, many web applications do a lot of queries and rarely delete, mainly index-based updates, inserting records into specific tables only. It is appropriate to use a basic MySQL MyISAM table.

Deadlocks are released in MySQL for the storage engine of table-level locks. Deadlock avoidance can be done by requesting a lock before any query and locking the table in the order in which it is requested.

The implementation mechanism of table lock for WRITE (write) in MySQL 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 table lock for READ (read) in MySQL is as follows:

If the table does not have a write lock, then add a read lock.

Otherwise, put the request in the read lock queue.

When the lock is released, the thread in the write lock queue can use the lock resource, and then it is the turn of the thread in the read lock queue.

That is, if there are many updates in the table, Select must wait until all the updates are complete.

Starting with MySQL 3.23.33, lock table contention in the system can be analyzed through the state variables Table_locks_waited and Table_locks_immediate:

Mysql > SHOW STATUS LIKE 'Table%' +-- +-+ | Variable_name | Value | +-- +-+ | Table_locks_immediate | 1151552 | | Table_locks_waited | 15324 | +-+-+

After MySQL 3.23.7 (3.23.25 on Windows), as long as there are no conflicting Insert operations in the MyISAM table, you can freely execute Insert and Select statements in parallel without using a lock table. That is, you can insert a new record while another client is reading the MyISAM table record. If there are no free blocks in the middle of the data file, there will be no conflict, because in this case all new records will be written at the end of the data file (when deleting or updating in the middle of the table, it can lead to holes). When the hole is filled with new data, the parallel insert feature is automatically re-enabled.

If you want to do a lot of Insert and Select operations on a table, but parallel inserts are not possible, you can insert records into the temporary table, and then periodically update the data in the temporary table to the actual table. You can do this with 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 can occur. This is because InnoDB automatically catches row locks, and BDB captures page locks when the SQL statement is executed, not at the beginning of the transaction.

The advantages of row-level locks are:

Reduce conflicting locks when many threads request different records.

Reduce the change of data when the transaction is rolled back.

Makes it possible to lock individual rows of records for a long time.

The disadvantages of row-level locks are:

Consumes more memory than page-level and table-level locks.

When used in large tables, it is slower than page-level locks and table-level locks because it needs to request more resources.

When most of the data needs to be GROUP BY frequently or the entire table needs to be scanned frequently, it is obviously worse than other locks.

Using higher-level locks makes it easier to support different types of applications because such locks are much less expensive than row-level locks.

Table-level locks are superior to page-level and row-level locks in the following situations:

A lot of operations are meter reading.

Read and update on a strictly conditional index, when updates or deletions can be read with a separate index:

Updatetbl_nameSETcolumn=valueWhereunique_key_col=key_value; Delete FROMtbl_nameWhereunique_key_col=key_value

Select and Insert statements are executed concurrently, but there are very few Update and Delete statements.

A lot of scanning tables and GROUP BY operations on the whole table, but there is no writing to the table.

The difference between table-level locks and row-level or page-level locks is also:

Versions will be made with one write and multiple reads at the same time (such as concurrent inserts in MySQL). That is, the database / table supports a variety of attempts depending on the point in time when you start accessing the data. Other names are: time schedule, write copy, or copy on demand.

Versioning (such as we use in MySQL for concurrent inserts) where you can have one writer at the same time as many 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 in many cases much better than page-level or row-level locks. However, in the worst case, more memory is used than other normal locks.

You can replace row-level locks with application-level locks, such as GET_LOCK () and RELEASE_LOCK () in MySQL. But they are advice locks (original: These are advisory locks), so they can only be used in secure and trusted applications.

7.3.2 Lock the table

In order to have fast locks, MySQL uses table-level locks (rather than page, row, column-level locks) except for InnoDB and BDB storage engines.

For InnoDB and BDB tables, MySQL uses table-level locks only when the table is specified to be locked with LOCK TABLES. In these two tables, it is recommended that you do not use LOCK 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 pitfalls.

Table-level locks allow many threads to read data from the data table at the same time, but if another thread wants to write data, it must first obtain exclusive access. When you are updating the data, you must wait until the update is complete before other threads can access the table.

The update operation is generally considered more important than reading, so it has a higher priority. However, it is best to first confirm that the data table has a high Select operation, and the update operation is not very "urgent".

The table lock has a problem when a thread is waiting because the disk space is full, but it needs free disk space before the thread can continue to process. In this case, all threads that want to access the problematic table are put in a waiting state until there is disk space left.

Table locks are disadvantageous in the following scenarios:

A client submitted a long-running Select operation.

Other clients submit Update operations on the same table, and this client has to wait until the Select is complete before it can start execution.

Other clients have also submitted Select requests for the same table. Because Update takes precedence over Select, Select waits for the Update to complete before it executes, and it is also waiting for the first Select operation.

The following can reduce the competition for resources caused by table locks:

Make Select as fast as possible, which may require the creation of some summary tables.

Start mysqld with the parameter-- low-priority-updates. This gives the update operation a lower priority than Select. In this case, in the above assumption, the second Select will be executed before the Insert, and there is no need to wait for the first Select.

You can execute the SET LOW_PRIORITY_UpdateS=1 command to specify that all updates are done in a specified link. For details, please see "14.5.3.1 SET Syntax".

Use the LOW_PRIORITY attribute to lower the priority of Insert,Update,Delete.

Use HIGH_PRIORITY to raise the priority of Select statements. For details, please see "14.1.7 Select Syntax".

Starting with MySQL 3.23.7, the system variable max_write_lock_count can be specified to a lower value when mysqld is started, which forces all Select operations after table inserts reach a specific value to be temporarily increased in priority. It allows WRITE locks to exist after a certain number of READ locks are reached.

When there is a problem with Insert and Select, you can switch to the MyISAM table, which supports concurrent Select and Insert operations.

Insert DELAYED can be useful when there are both insert and delete operations on the same table. For details, please see "14.1.4.2 Insert DELAYED Syntax".

The LIMIT parameter of Delete can be useful when there is a problem with Select and Delete. For details, please see "14.1.1 Delete Syntax"

Using SQL_BUFFER_RESULT when performing Select helps reduce the duration of locking tables. For details, please see "14.1.7 Select Syntax".

You can modify the source code `mysys/thr_lock.c', to use only one queue. In this case, write locks and read locks have the same priority, which may be helpful for some applications.

Here are some suggestions for MySQL locks:

As long as there are not a large number of updates and queries mixed up on the same table, current users are not a problem.

Perform LOCK TABLES to improve speed (many updates are placed in one lock much faster than many updates without locks). Splitting data into multiple tables may also be helpful.

When MySQL encounters speed problems caused by locking tables, converting the table type to InnoDB or BDB may help improve performance. For details, please see "16 The InnoDB Storage Engine" and "15.4 The BDB (BerkeleyDB) Storage Engine".

The above content is how to understand the locking mechanism of mysql. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.