MySQL 对 InnoDB 表使用 行级锁 来支持多个会话的同时写访问，使其适用于多用户、高并发和 OLTP 应用。MySQL uses row-level locking for InnoDB tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications.

为了在对单个 InnoDB 表执行多个并发写操作时避免 死锁，请在事务开始时获取必要的锁，通过对预计将被修改的每组行发出 SELECT ... FOR UPDATE 语句，即使数据更改语句随后才在事务中执行。如果事务修改或锁定多个表，请在每个事务中按相同的顺序发出相应的语句。死锁影响性能，而不表示严重错误，因为 InnoDB 默认会自动 检测 死锁情况并回滚其中一个受影响的事务。To avoid deadlocks when performing multiple concurrent 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.

在高并发系统中，当大量线程等待同一个锁时，死锁检测可能会导致性能下降。有时，禁用死锁检测并依赖innodb_lock_wait_timeout设置在发生死锁时回滚事务可能更高效。可以使用innodb_deadlock_detect配置选项来禁用死锁检测。On high concurrency systems, deadlock detection can cause a slowdown when numerous 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:

MySQL 对 MyISAM、MEMORY 和 MERGE 表使用 表级锁定，一次只允许一个会话更新这些表。这种锁定级别使这些存储引擎更适合只读、大部分为读或单用户的应用。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:

MySQL 授予表写锁如下：MySQL grants table write locks as follows:

MySQL 授予表读锁如下：MySQL grants table read locks as follows:

表更新的优先级高于表的读取操作。因此，当锁被释放时，锁会先提供给写锁队列中的请求，然后再提供给读锁队列中的请求。这确保了即使在表的 SELECT 活跃的情况下，表的更新也不会被“饿死”。然而，如果表有大量更新，SELECT 语句将等待，直到没有更多的更新。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.

有关更改读写优先级的信息，请参见 第 10.11.2 节，“表锁问题”。For information on altering the priority of reads and writes, see Section 10.11.2, “Table Locking Issues”.

您可以通过检查 Table_locks_immediate 和 Table_locks_waited 状态变量来分析系统上的表锁争用情况，它们分别指示请求表锁能够立即被授予的次数以及必须等待的次数：You can analyze 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   |
+-----------------------+---------+

性能模式锁表也提供锁定信息。请参阅 第 29.12.13 节，“性能模式锁表”。The Performance Schema lock tables also provide locking information. See Section 29.12.13, “Performance Schema Lock Tables”.

MyISAM 存储引擎支持并发插入，以减少给定表的读写操作之间的争用：如果 MyISAM 表在数据文件中间没有空闲块，行总是插入到数据文件的末尾。在这种情况下，你可以自由地在 MyISAM 表上混合执行并发的 INSERT 和 SELECT 语句而无需锁定。也就是说，你可以在其他客户端读取 MyISAM 表的同时向其中插入行。空洞可能是由于表中间的行被删除或更新而产生的。如果存在空洞，则并发插入会被禁用，但当所有空洞都被新数据填充后，并发插入会自动重新启用。要控制此行为，请使用 concurrent_insert 系统变量。请参见 第 10.11.3 节，“并发插入”。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. See Section 10.11.3, “Concurrent Inserts”.

如果你通过 LOCK TABLES 显式获取表锁，你可以请求 READ LOCAL 锁，而不是 READ 锁，以便在表被锁定时允许其他会话执行并发插入操作。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.

当无法进行并发插入时，要对表 t1 执行大量 INSERT 和 SELECT 操作，可以先将行插入临时表 temp_t1，然后用临时表中的行更新真实表：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;

通常，表锁在以下情况下优于行级锁：Generally, table locks are superior to row-level locks in the following cases:

使用更高级别的锁，您可以更轻松地通过支持不同类型的锁来调整应用程序，因为锁的开销比行级锁要小。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: