MySQL 8.0 Reference Manual（讀書筆記59節-- Buffering and Caching 和 Locking）

第一部分 Buffering and Caching

MySQL uses several strategies that cache information in memory buffers to increase performance.

1 InnoDB Buffer Pool Optimization

InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. Knowing how the InnoDB buffer pool works, and taking advantage of it to keep frequently accessed data in memory, is an important aspect of MySQL tuning.

【其它章節有專門、深入的介紹】

2 The MyISAM Key Cache

To minimize disk I/O, the MyISAM storage engine exploits【ɪkˈsplɔɪts 利用(…為自己謀利);剝削;發揮;運用;壓榨;】 a strategy that is used by many database management systems. It employs a cache mechanism to keep the most frequently accessed table blocks in memory:

• For index blocks, a special structure called the key cache (or key buffer) is maintained. The structure contains a number of block buffers where the most-used index blocks are placed.

• For data blocks, MySQL uses no special cache. Instead it relies on the native operating system file system cache.

This section first describes the basic operation of the MyISAM key cache. Then it discusses features that improve key cache performance and that enable you to better control cache operation:

• Multiple sessions can access the cache concurrently.

• You can set up multiple key caches and assign table indexes to specific caches.

To control the size of the key cache, use the key_buffer_size system variable. If this variable is set equal to zero, no key cache is used. The key cache also is not used if the key_buffer_size value is too small to allocate the minimal number of block buffers (8).

When the key cache is not operational, index files are accessed using only the native【ˈneɪtɪv 本地的;土著的;當地的;土著人的;出生地的;天賦的;原產於某地的;兒時居住地的;】 file system buffering provided by the operating system. (In other words, table index blocks are accessed using the same strategy as that employed for table data blocks.)

An index block is a contiguous unit of access to the MyISAM index files. Usually the size of an index block is equal to the size of nodes of the index B-tree. (Indexes are represented on disk using a B-tree data structure. Nodes at the bottom of the tree are leaf nodes. Nodes above the leaf nodes are nonleaf nodes.)

All block buffers in a key cache structure are the same size. This size can be equal to, greater than, or less than the size of a table index block. Usually one these two values is a multiple of the other.

When data from any table index block must be accessed, the server first checks whether it is available in some block buffer of the key cache. If it is, the server accesses data in the key cache rather than on disk. That is, it reads from the cache or writes into it rather than reading from or writing to disk. Otherwise, the server chooses a cache block buffer containing a different table index block (or blocks) and replaces the data there by a copy of required table index block. As soon as the new index block is in the cache, the index data can be accessed.

If it happens that a block selected for replacement has been modified, the block is considered “dirty.” In this case, prior to being replaced, its contents are flushed to the table index from which it came.

Usually the server follows an LRU (Least Recently Used) strategy: When choosing a block for replacement, it selects the least recently used index block. To make this choice easier, the key cache module maintains all used blocks in a special list (LRU chain) ordered by time of use. When a block is accessed, it is the most recently used and is placed at the end of the list. When blocks need to be replaced, blocks at the beginning of the list are the least recently used and become the first candidates for eviction.

The InnoDB storage engine also uses an LRU algorithm, to manage its buffer pool.

2.1 Shared Key Cache Access

Threads can access key cache buffers simultaneously, subject to the following conditions:

• A buffer that is not being updated can be accessed by multiple sessions.

• A buffer that is being updated causes sessions that need to use it to wait until the update is complete.

• Multiple sessions can initiate requests that result in cache block replacements, as long as they do not interfere with each other (that is, as long as they need different index blocks, and thus cause different cache blocks to be replaced).

Shared access to the key cache enables the server to improve throughput significantly【sɪɡˈnɪfɪkəntli 顯著地;明顯地;意味深長地;別有含義地;有重大意義地;有某種意義;】.

2.2 Multiple Key Caches

【As of MySQL 8.0, the compound-part structured-variable syntax discussed here for referring to multiple MyISAM key caches is deprecated.】

Shared access to the key cache improves performance but does not eliminate contention among sessions entirely. They still compete for control structures that manage access to the key cache buffers. To reduce key cache access contention further, MySQL also provides multiple key caches. This feature enables you to assign different table indexes to different key caches.

 Where there are multiple key caches, the server must know which cache to use when processing queries for a given MyISAM table. By default, all MyISAM table indexes are cached in the default key cache. To assign table indexes to a specific key cache, use the CACHE INDEX statement . For example, the following statement assigns indexes from the tables t1, t2, and t3 to the key cache named hot_cache:

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

The key cache referred to in a CACHE INDEX statement can be created by setting its size with a SET GLOBAL parameter setting statement or by using server startup options. For example:

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

To destroy a key cache, set its size to zero:

mysql> SET GLOBAL keycache1.key_buffer_size=0;

You cannot destroy the default key cache. Any attempt to do this is ignored:

mysql> SET GLOBAL key_buffer_size = 0;
mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8384512 |
+-----------------+---------+

Key cache variables are structured system variables that have a name and components. For keycache1.key_buffer_size, keycache1 is the cache variable name and key_buffer_size is the cache component. 

By default, table indexes are assigned to the main (default) key cache created at the server startup. When a key cache is destroyed, all indexes assigned to it are reassigned to the default key cache.

For a busy server, you can use a strategy that involves three key caches:

• A “hot” key cache that takes up 20% of the space allocated for all key caches. Use this for tables that are heavily used for searches but that are not updated.

• A “cold” key cache that takes up 20% of the space allocated for all key caches. Use this cache for medium-sized, intensively modified tables, such as temporary tables.

• A “warm” key cache that takes up 60% of the key cache space. Employ this as the default key cache, to be used by default for all other tables.

One reason the use of three key caches is beneficial【ˌbenɪˈfɪʃl 有益的;有利的;有用的;有裨益的;】 is that access to one key cache structure does not block access to the others. Statements that access tables assigned to one cache do not compete with statements that access tables assigned to another cache. Performance gains occur for other reasons as well:

• The hot cache is used only for retrieval queries, so its contents are never modified. Consequently, whenever an index block needs to be pulled in from disk, the contents of the cache block chosen for replacement need not be flushed first.

• For an index assigned to the hot cache, if there are no queries requiring an index scan, there is a high probability that the index blocks corresponding to nonleaf nodes of the index B-tree remain in the cache.

• An update operation most frequently executed for temporary tables is performed much faster when the updated node is in the cache and need not be read from disk first. If the size of the indexes of the temporary tables are comparable with the size of cold key cache, the probability is very high that the updated node is in the cache.

The CACHE INDEX statement sets up an association between a table and a key cache, but the association is lost each time the server restarts. If you want the association to take effect each time the server starts, one way to accomplish this is to use an option file: Include variable settings that configure your key caches, and an init_file system variable that names a file containing CACHE INDEX statements to be executed. For example:

key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql

The statements in mysqld_init.sql are executed each time the server starts. The file should contain one SQL statement per line. The following example assigns several tables each to hot_cache and cold_cache:

CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache
CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache

2.3 Midpoint Insertion Strategy

By default, the key cache management system uses a simple LRU strategy for choosing key cache blocks to be evicted【ɪˈvɪktɪd (尤指依法從房屋或土地上)驅逐，趕出，逐出;】, but it also supports a more sophisticated method called the midpoint insertion strategy.

When using the midpoint insertion strategy, the LRU chain is divided into two parts: a hot sublist and a warm sublist. The division point between two parts is not fixed, but the key cache management system takes care that the warm part is not “too short,” always containing at least key_cache_division_limit percent of the key cache blocks. key_cache_division_limit is a component of structured key cache variables, so its value is a parameter that can be set per cache.

When an index block is read from a table into the key cache, it is placed at the end of the warm sublist. After a certain number of hits (accesses of the block), it is promoted to the hot sublist. At present, the number of hits required to promote a block (3) is the same for all index blocks.

A block promoted into the hot sublist is placed at the end of the list. The block then circulates within this sublist. If the block stays at the beginning of the sublist for a long enough time, it is demoted to the warm sublist. This time is determined by the value of the key_cache_age_threshold component of the key cache.

The threshold value prescribes that, for a key cache containing N blocks, the block at the beginning of the hot sublist not accessed within the last N * key_cache_age_threshold / 100 hits is to be moved to the beginning of the warm sublist. It then becomes the first candidate for eviction, because blocks for replacement always are taken from the beginning of the warm sublist.

The midpoint insertion strategy enables you to keep more-valued blocks always in the cache. If you prefer to use the plain LRU strategy, leave the key_cache_division_limit value set to its default of 100.

The midpoint insertion strategy helps to improve performance when execution of a query that requires an index scan effectively pushes out of the cache all the index blocks corresponding to valuable high-level B-tree nodes. To avoid this, you must use a midpoint insertion strategy with the key_cache_division_limit set to much less than 100. Then valuable frequently hit nodes are preserved in the hot sublist during an index scan operation as well.

2.4 Index Preloading

If there are enough blocks in a key cache to hold blocks of an entire index, or at least the blocks corresponding to its nonleaf nodes, it makes sense to preload the key cache with index blocks before starting to use it. Preloading enables you to put the table index blocks into a key cache buffer in the most efficient way: by reading the index blocks from disk sequentially【səˈkwɛntʃəli 按順序;繼續地，從而;】.

Without preloading, the blocks are still placed into the key cache as needed by queries. Although the blocks stay in the cache, because there are enough buffers for all of them, they are fetched from disk in random order, and not sequentially.

To preload an index into a cache, use the LOAD INDEX INTO CACHE statement. For example, the following statement preloads nodes (index blocks) of indexes of the tables t1 and t2:

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
| test.t2 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

The IGNORE LEAVES modifier causes only blocks for the nonleaf nodes of the index to be preloaded. Thus, the statement shown preloads all index blocks from t1, but only blocks for the nonleaf nodes from t2.

If an index has been assigned to a key cache using a CACHE INDEX statement, preloading places index blocks into that cache. Otherwise, the index is loaded into the default key cache.

2.5 Key Cache Block Size

It is possible to specify the size of the block buffers for an individual key cache using the key_cache_block_size variable. This permits tuning of the performance of I/O operations for index files.

The best performance for I/O operations is achieved when the size of read buffers is equal to the size of the native operating system I/O buffers. But setting the size of key nodes equal to the size of the I/O buffer does not always ensure the best overall performance. When reading the big leaf nodes, the server pulls in a lot of unnecessary data, effectively preventing reading other leaf nodes.

To control the size of blocks in the .MYI index file of MyISAM tables, use the --myisam-block-size option at server startup.

2.6 Restructuring a Key Cache

A key cache can be restructured at any time by updating its parameter values. For example:

mysql> SET GLOBAL cold_cache.key_buffer_size=4*1024*1024;

If you assign to either the key_buffer_size or key_cache_block_size key cache component a value that differs from the component's current value, the server destroys the cache's old structure and creates a new one based on the new values. If the cache contains any dirty blocks, the server saves them to disk before destroying and re-creating the cache. Restructuring does not occur if you change other key cache parameters.

When restructuring a key cache, the server first flushes the contents of any dirty buffers to disk. After that, the cache contents become unavailable. However, restructuring does not block queries that need to use indexes assigned to the cache. Instead, the server directly accesses the table indexes using native file system caching. File system caching is not as efficient as using a key cache, so although queries execute, a slowdown can be anticipated. After the cache has been restructured, it becomes available again for caching indexes assigned to it, and the use of file system caching for the indexes ceases.

3 Caching of Prepared Statements and Stored Programs

For certain statements that a client might execute multiple times during a session, the server converts the statement to an internal structure and caches that structure to be used during execution. Caching enables the server to perform more efficiently because it avoids the overhead of reconverting the statement should it be needed again during the session. Conversion and caching occurs for these statements:

• Prepared statements, both those processed at the SQL level (using the PREPARE statement) and those processed using the binary client/server protocol (using the mysql_stmt_prepare() C API function). The max_prepared_stmt_count system variable controls the total number of statements the server caches. (The sum of the number of prepared statements across all sessions.)

• Stored programs (stored procedures and functions, triggers, and events). In this case, the server converts and caches the entire program body. The stored_program_cache system variable indicates the approximate number of stored programs the server caches per session.

The server maintains caches for prepared statements and stored programs on a per-session basis. Statements cached for one session are not accessible to other sessions. When a session ends, the server discards any statements cached for it.

When the server uses a cached internal statement structure, it must take care that the structure does not go out of date. Metadata changes can occur for an object used by the statement, causing a mismatch between the current object definition and the definition as represented in the internal statement structure. Metadata changes occur for DDL statements such as those that create, drop, alter, rename, or truncate tables, or that analyze, optimize, or repair tables. Table content changes (for example, with INSERT or UPDATE) do not change metadata, nor do SELECT statements.

Here is an illustration【ˌɪləˈstreɪʃn 插圖;(說明事實的)示例;圖解;圖示;(書、雜誌等中的)圖表;例釋;】 of the problem. Suppose that a client prepares this statement:

PREPARE s1 FROM 'SELECT * FROM t1';

The SELECT * expands in the internal structure to the list of columns in the table. If the set of columns in the table is modified with ALTER TABLE, the prepared statement goes out of date. If the server does not detect this change the next time the client executes s1, the prepared statement returns incorrect results.

To avoid problems caused by metadata changes to tables or views referred to by the prepared statement, the server detects these changes and automatically reprepares the statement when it is next executed. That is, the server reparses the statement and rebuilds the internal structure. Reparsing also occurs after referenced tables or views are flushed from the table definition cache, either implicitly to make room for new entries in the cache, or explicitly due to FLUSH TABLES.

Similarly, if changes occur to objects used by a stored program, the server reparses affected statements within the program.

The server also detects metadata changes for objects in expressions. These might be used in statements specific to stored programs, such as DECLARE CURSOR or flow-control statements such as IF, CASE, and RETURN.

To avoid reparsing entire stored programs, the server reparses affected statements or expressions within a program only as needed. Examples:

• Suppose that metadata for a table or view is changed. Reparsing occurs for a SELECT * within the program that accesses the table or view, but not for a SELECT * that does not access the table or view.

• When a statement is affected, the server reparses it only partially if possible. Consider this CASE statement:

CASE case_expr
 WHEN when_expr1 ...
 WHEN when_expr2 ...
 WHEN when_expr3 ...
 ...
END CASE

If a metadata change affects only WHEN when_expr3, that expression is reparsed. case_expr and the other WHEN expressions are not reparsed.

Reparsing uses the default database and SQL mode that were in effect for the original conversion to internal form.

The server attempts reparsing【重新分析】 up to three times. An error occurs if all attempts fail.

Reparsing is automatic, but to the extent that it occurs, diminishes【dɪˈmɪnɪʃɪz 減少;降低;(使)減弱，縮減;貶低;貶損;輕視;】 prepared statement and stored program performance.

For prepared statements, the Com_stmt_reprepare status variable tracks the number of repreparations.

第二部分 Optimizing Locking Operations

MySQL manages contention【kənˈtenʃn (尤指爭論時的)看法，觀點;爭論;爭執;爭吵;】 for table contents using locking:

• Internal locking is performed within the MySQL server itself to manage contention for table contents by multiple threads. This type of locking is internal because it is performed entirely【ɪnˈtaɪərli 完全;完整地;全部地;】 by the server and involves no other programs.

• External【ɪkˈstɜːrnl 外部的;在外的;外界的;外來的;外面的;】 locking occurs when the server and other programs lock MyISAM table files to coordinate among themselves which program can access the tables at which time.

1 Internal Locking Methods

1.1 Row-Level Locking

MySQL uses row-level locking for InnoDB tables to support simultaneous【ˌsaɪmlˈteɪniəs 同時的;同步的;同時發生(或進行)的;】 write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications.

To avoid deadlocks when performing multiple concurrent【kənˈkɜːrənt 同時發生的;同意的，一致的;<律>有相等權力的，同時(實施)的;合作的;協調的;並存的;<數>共點的，會合的;共同(或同時)起作用的;】 write operations on a single InnoDB table, acquire necessary locks at the start of the transaction by issuing a SELECT ... FOR UPDATE statement for each group of rows expected to be modified, even if the data change statements come later in the transaction. If transactions modify or lock more than one table, issue the applicable statements in the same order within each transaction. Deadlocks affect performance rather than representing a serious error, because InnoDB automatically detects deadlock conditions by default and rolls back one of the affected transactions.

On high concurrency systems, deadlock detection can cause a slowdown when numerous【ˈnuːmərəs 很多的;眾多的;許多的;】 threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.

Advantages of row-level locking:

• Fewer lock conflicts when different sessions access different rows.

• Fewer changes for rollbacks.

• Possible to lock a single row for a long time.

 1.2 Table-Level Locking

MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables, permitting only one session to update those tables at a time. This locking level makes these storage engines more suitable for read-only, read-mostly, or single-user applications.

These storage engines avoid deadlocks by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. The tradeoff is that this strategy reduces concurrency; other sessions that want to modify the table must wait until the current data change statement finishes.

Advantages of table-level locking:

• Relatively【ˈrelətɪvli 相對地;相當地;相當程度上;】 little memory required (row locking requires memory per row or group of rows locked)

• Fast when used on a large part of the table because only a single lock is involved.

• Fast if you often do GROUP BY operations on a large part of the data or must scan the entire table frequently.

MySQL grants table write locks as follows:

1. If there are no locks on the table, put a write lock on it.

2. Otherwise, put the lock request in the write lock queue.

MySQL grants table read locks as follows:

1. If there are no write locks on the table, put a read lock on it.

2. Otherwise, put the lock request in the read lock queue.

Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is made available to the requests in the write lock queue and then to the requests in the read lock queue. This ensures that updates to a table are not “starved” even when there is heavy SELECT activity for the table. However, if there are many updates for a table, SELECT statements wait until there are no more updates.

You can analyze【ˈænəlaɪz 分析（研究）;解析;分解;】 the table lock contention on your system by checking the Table_locks_immediate and Table_locks_waited status variables, which indicate the number of times that requests for table locks could be granted immediately and the number that had to wait, respectively:

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

The Performance Schema lock tables also provide locking information.

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix concurrent INSERT and SELECT statements for a MyISAM table without locks. That is, you can insert rows into a MyISAM table at the same time other clients are reading from it. Holes can result from rows having been deleted from or updated in the middle of the table. If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. To control this behavior, use the concurrent_insert system variable.

If you acquire a table lock explicitly with LOCK TABLES, you can request a READ LOCAL lock rather than a READ lock to enable other sessions to perform concurrent inserts while you have the table locked.

To perform many INSERT and SELECT operations on a table t1 when concurrent inserts are not possible, you can insert rows into a temporary table temp_t1 and update the real table with the rows from the temporary table:

mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;
mysql> INSERT INTO t1 SELECT * FROM temp_t1;
mysql> DELETE FROM temp_t1;
mysql> UNLOCK TABLES;

1.3 Choosing the Type of Locking

Generally, table locks are superior【suːˈpɪriər 優越的;(在品質上)更好的;(在級別、重要性或職位上)更高的;占優勢;更勝一籌;高傲的;有優越感的;質量卓越的;】 to row-level locks in the following cases:

• Most statements for the table are reads.

• Statements for the table are a mix of reads and writes, where writes are updates or deletes for a single row that can be fetched with one key read:

UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;

• SELECT combined with concurrent INSERT statements, and very few UPDATE or DELETE statements.

• Many scans or GROUP BY operations on the entire table without any writers.

With higher-level locks, you can more easily tune applications by supporting locks of different types, because the lock overhead is less than for row-level locks.

Options other than row-level locking:

• Versioning (such as that used in MySQL for concurrent inserts) where it is possible to have one writer at the same time as many readers. This means that the database or table supports different views for the data depending on when access begins. Other common terms for this are “time travel,” “copy on write,” or “copy on demand.”

• Copy on demand is in many cases superior to row-level locking. However, in the worst case, it can use much more memory than using normal locks.

• Instead of using row-level locks, you can employ application-level locks, such as those provided by GET_LOCK() and RELEASE_LOCK() in MySQL. These are advisory locks, so they work only with applications that cooperate with each other.

2 Table Locking Issues

InnoDB tables use row-level locking so that multiple sessions and applications can read from and write to the same table simultaneously, without making each other wait or producing inconsistent results. For this storage engine, avoid using the LOCK TABLES statement, because it does not offer any extra protection, but instead reduces concurrency. The automatic row-level locking makes these tables suitable for your busiest databases with your most important data, while also simplifying application logic since you do not need to lock and unlock tables. Consequently, the InnoDB storage engine is the default in MySQL.

MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB. The locking operations themselves do not have much overhead. But because only one session can write to a table at any one time, for best performance with these other storage engines, use them primarily for tables that are queried often and rarely inserted into or updated.

2.1 Performance Considerations Favoring InnoDB

When choosing whether to create a table using InnoDB or a different storage engine, keep in mind the following disadvantages of table locking:

• Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must first get exclusive access, meaning it might have to wait for other sessions to finish with the table first. During the update, all other sessions that want to access this particular table must wait until the update is done.

• Table locking causes problems when a session is waiting because the disk is full and free space needs to become available before the session can proceed. In this case, all sessions that want to access the problem table are also put in a waiting state until more disk space is made available.

• A SELECT statement that takes a long time to run prevents other sessions from updating the table in the meantime, making the other sessions appear slow or unresponsive【ˌʌnrɪˈspɑːnsɪv 無反應的;反應遲鈍的;未答覆的;】. While a session is waiting to get exclusive access to the table for updates, other sessions that issue SELECT statements queue up behind it, reducing concurrency even for read-only sessions.

2.2 Workarounds for Locking Performance Issues

The following items describe some ways to avoid or reduce contention caused by table locking:

• Consider switching the table to the InnoDB storage engine, either using CREATE TABLE ... ENGINE=INNODB during setup, or using ALTER TABLE ... ENGINE=INNODB for an existing table.

• Optimize SELECT statements to run faster so that they lock tables for a shorter time. You might have to create some summary tables to do this.

• Start mysqld with --low-priority-updates. For storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE), this gives all statements that update (modify) a table lower priority than SELECT statements. In this case, the second SELECT statement in the preceding scenario would execute before the UPDATE statement, and would not wait for the first SELECT to finish.

• To specify that all updates issued in a specific connection should be done with low priority, set the low_priority_updates server system variable equal to 1.

• To give a specific INSERT, UPDATE, or DELETE statement lower priority, use the LOW_PRIORITY attribute.

• To give a specific SELECT statement higher priority, use the HIGH_PRIORITY attribute.

• Start mysqld with a low value for the max_write_lock_count system variable to force MySQL to temporarily elevate the priority of all SELECT statements that are waiting for a table after a specific number of write locks to the table occur (for example, for insert operations). This permits read locks after a certain number of write locks.

• If you have problems with mixed SELECT and DELETE statements, the LIMIT option to DELETE may help.

• Using SQL_BUFFER_RESULT with SELECT statements can help to make the duration of table locks shorter.

• Splitting table contents into separate tables may help, by allowing queries to run against columns in one table, while updates are confined to columns in a different table.

• You could change the locking code in mysys/thr_lock.c to use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications.

3 Concurrent Inserts

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no holes in the data file (deleted rows in the middle), an INSERT statement can be executed to add rows to the end of the table at the same time that SELECT statements are reading rows from the table. If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements. The results of a concurrent INSERT may not be visible immediately.

The concurrent_insert system variable can be set to modify the concurrent-insert processing. By default, the variable is set to AUTO (or 1) and concurrent inserts are handled as just described. If concurrent_insert is set to NEVER (or 0), concurrent inserts are disabled. If the variable is set to ALWAYS (or 2), concurrent inserts at the end of the table are permitted even for tables that have deleted rows. See also the description of the concurrent_insert system variable.

If you are using the binary log, concurrent inserts are converted to normal inserts for CREATE ... SELECT or INSERT ... SELECT statements. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation.In addition, for those statements a read lock is placed on the selected-from table such that inserts into that table are blocked. The effect is that concurrent inserts for that table must wait as well.

With LOAD DATA, if you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other sessions can retrieve data from the table while LOAD DATA is executing. Use of the CONCURRENT option affects the performance of LOAD DATA a bit, even if no other session is using the table at the same time.

If you specify HIGH_PRIORITY, it overrides the effect of the --low-priority-updates option if the server was started with that option. It also causes concurrent inserts not to be used.

For LOCK TABLE, the difference between READ LOCAL and READ is that READ LOCAL permits nonconflicting INSERT statements (concurrent inserts) to execute while the lock is held. However, this cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock.

4 Metadata Locking

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas, stored programs (procedures, functions, triggers, scheduled events), tablespaces, user locks acquired with the GET_LOCK() function.

The Performance Schema metadata_locks table exposes metadata lock information, which can be useful for seeing which sessions hold locks, are blocked waiting for locks, and so forth.

Metadata locking does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.

Metadata locking is not a replacement for the table definition cache, and its mutexes and locks differ from the LOCK_open mutex. The following discussion provides some information about how metadata locking works.

4.1 Metadata Lock Acquisition

If there are multiple waiters for a given lock, the highest-priority lock request is satisfied first, with an exception related to the max_write_lock_count system variable. Write lock requests have higher priority than read lock requests. However, if max_write_lock_count is set to some low value (say, 10), read lock requests may be preferred over pending write lock requests if the read lock requests have already been passed over in favor of 10 write lock requests. Normally this behavior does not occur because max_write_lock_count by default has a very large value.

Statements acquire metadata locks one by one, not simultaneously, and perform deadlock detection in the process.

DML statements normally acquire locks in the order in which tables are mentioned in the statement.

DDL statements, LOCK TABLES, and other similar statements try to reduce the number of possible deadlocks between concurrent DDL statements by acquiring locks on explicitly named tables in name order. Locks might be acquired in a different order for implicitly used tables (such as tables in foreign key relationships that also must be locked).

For example, RENAME TABLE is a DDL statement that acquires locks in name order:

• This RENAME TABLE statement renames tbla to something else, and renames tblc to tbla:

RENAME TABLE tbla TO tbld, tblc TO tbla;

The statement acquires metadata locks, in order, on tbla, tblc, and tbld (because tbld follows tblc in name order):

• This slightly different statement also renames tbla to something else, and renames tblc to tbla:

RENAME TABLE tbla TO tblb, tblc TO tbla;

In this case, the statement acquires metadata locks, in order, on tbla, tblb, and tblc (because tblb precedes tblc in name order):

Both statements acquire locks on tbla and tblc, in that order, but differ in whether the lock on the remaining table name is acquired before or after tblc.

Metadata lock acquisition order can make a difference in operation outcome when multiple transactions execute concurrently, as the following example illustrates.

Begin with two tables x and x_new that have identical【aɪˈdentɪkl 完全相同的;相同的;同一的;完全同樣的;】 structure. Three clients issue statements that involve these tables:

Client 1:

LOCK TABLE x WRITE, x_new WRITE;

The statement requests and acquires write locks in name order on x and x_new.

Client 2:

INSERT INTO x VALUES(1);

The statement requests and blocks waiting for a write lock on x.

Client 3:

RENAME TABLE x TO x_old, x_new TO x;

The statement requests exclusive locks in name order on x, x_new, and x_old, but blocks waiting for the lock on x.

Client 1:

UNLOCK TABLES;

The statement releases the write locks on x and x_new. The exclusive lock request for x by Client 3 has higher priority than the write lock request by Client 2, so Client 3 acquires its lock on x, then also on x_new and x_old, performs the renaming, and releases its locks. Client 2 then acquires its lock on x, performs the insert, and releases its lock.

Lock acquisition order results in the RENAME TABLE executing before the INSERT. The x into which the insert occurs is the table that was named x_new when Client 2 issued the insert and was renamed to x by Client 3:

mysql> SELECT * FROM x;
+------+
| i    |
+------+
| 1    |
+------+
mysql> SELECT * FROM x_old;
Empty set (0.01 sec)

Now begin instead with tables named x and new_x that have identical structure. Again, three clients issue statements that involve these tables:

Client 1:

LOCK TABLE x WRITE, new_x WRITE;

The statement requests and acquires write locks in name order on new_x and x.

Client 2:

INSERT INTO x VALUES(1);

The statement requests and blocks waiting for a write lock on x.

Client 3:

RENAME TABLE x TO old_x, new_x TO x;

The statement requests exclusive locks in name order on new_x, old_x, and x, but blocks waiting for the lock on new_x.

Client 1:

UNLOCK TABLES;

The statement releases the write locks on x and new_x. For x, the only pending request is by Client 2, so Client 2 acquires its lock, performs the insert, and releases the lock. For new_x, the only pending request is by Client 3, which is permitted to acquire that lock (and also the lock on old_x). The rename operation still blocks for the lock on x until the Client 2 insert finishes and releases its lock. Then Client 3 acquires the lock on x, performs the rename, and releases its lock.

In this case, lock acquisition order results in the INSERT executing before the RENAME TABLE. The x into which the insert occurs is the original x, now renamed to old_x by the rename operation:

mysql> SELECT * FROM x;
Empty set (0.01 sec)
mysql> SELECT * FROM old_x;
+------+
| i    |
+------+
| 1    |
+------+

If order of lock acquisition in concurrent statements makes a difference to an application in operation outcome, as in the preceding example, you may be able to adjust the table names to affect the order of lock acquisition.

Metadata locks are extended, as necessary, to tables related by a foreign key constraint to prevent conflicting DML and DDL operations from executing concurrently on the related tables. When updating a parent table, a metadata lock is taken on the child table while updating foreign key metadata. Foreign key metadata is owned by the child table.

4.2 Metadata Lock Release

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

This principle applies not only to transactional tables, but also to nontransactional tables. Suppose that a session begins a transaction that uses transactional table t and nontransactional table nt as follows:

START TRANSACTION;
SELECT * FROM t;
SELECT * FROM nt;

The server holds metadata locks on both t and nt until the transaction ends. If another session attempts a DDL or write lock operation on either table, it blocks until metadata lock release at transaction end. For example, a second session blocks if it attempts any of these operations:

DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE;

The same behavior applies for The LOCK TABLES ... READ. That is, explicitly or implicitly started transactions that update any table (transactional or nontransactional) block and are blocked by LOCK TABLES ... READ for that table.

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

In autocommit mode, each statement is in effect a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement.

Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction.

As of MySQL 8.0.13, for XA transactions in PREPARED state, metadata locks are maintained across client disconnects and server restarts, until an XA COMMIT or XA ROLLBACK is executed.

5 External Locking

External locking is the use of file system locking to manage contention【kənˈtenʃn (尤指爭論時的)看法，觀點;爭論;爭執;爭吵;】 for MyISAM database tables by multiple processes. External locking is used in situations where a single process such as the MySQL server cannot be assumed to be the only process that requires access to tables. Here are some examples:

• If you run multiple servers that use the same database directory (not recommended), each server must have external【ɪkˈstɜːrnl 外部的;在外的;外界的;外來的;外面的;】 locking enabled.

• If you use myisamchk to perform table maintenance【ˈmeɪntənəns 維護;維修;保養;維持;保持;撫養費;(依法應負擔的)生活費;】 operations on MyISAM tables, you must either ensure that the server is not running, or that the server has external locking enabled so that it locks table files as necessary to coordinate【koʊˈɔːrdɪneɪt , koʊˈɔːrdɪnət 使協調;(衣服、傢具等)搭配，協調;使相配合;使(身體各部分)動作協調;協同動作;】 with myisamchk for access to the tables. The same is true for use of myisampack to pack MyISAM tables.

If the server is run with external locking enabled, you can use myisamchk at any time for read operations such a checking tables. In this case, if the server tries to update a table that myisamchk is using, the server waits for myisamchk to finish before it continues. If you use myisamchk for write operations such as repairing or optimizing tables, or if you use myisampack to pack tables, you must always ensure that the mysqld server is not using the table. If you do not stop mysqld, at least do a mysqladmin flush-tables before you run myi