MySQL 8.0 参考手册  / 第8章优化  / 8.11 优化锁定操作  /  8.11.1 内部锁定方法

8.11.1 内部锁定方法

本节讨论内部锁定;也就是说,在 MySQL 服务器本身内执行锁定以管理多个会话对表内容的争用。这种类型的锁定是内部的,因为它完全由服务器执行并且不涉及其他程序。对于其他程序对 MySQL 文件执行的锁定,请参阅第 8.11.5 节,“外部锁定”

行级锁定

MySQL对表使用行级锁定InnoDB,支持多个会话同时写访问,适用于多用户、高并发、OLTP应用。

为避免在单个表上执行多个并发写入操作时出现 死锁InnoDB,请在事务开始时通过SELECT ... FOR UPDATE为每组预期要修改的行发出一条语句来获取必要的锁,即使数据更改语句出现在事务的后面也是如此。如果事务修改或锁定多个表,请在每个事务中以相同的顺序发出适用的语句。死锁会影响性能而不是表示严重错误,因为它 会InnoDB自动 检测 死锁情况并回滚受影响的事务之一。

行级锁的优点:

  • 当不同的会话访问不同的行时,锁冲突更少。

  • 回滚的更改更少。

  • 可能长时间锁定单行。

表级锁定

MySQL对、 和表使用表级锁定, 一次只允许一个会话更新这些表。这种锁定级别使这些存储引擎更适合只读、多读或单用户应用程序。 MyISAMMEMORYMERGE

这些存储引擎 通过始终在查询开始时立即请求所有需要的锁并始终以相同的顺序锁定表来避免死锁。权衡是这种策略降低了并发性;其他想要修改表的会话必须等到当前数据更改语句完成。

表级锁的优点:

  • 所需内存相对较少(行锁定需要每行或每组行锁定的内存)

  • 在表的大部分上使用时速度很快,因为只涉及一个锁。

  • 如果您经常GROUP BY 对大部分数据进行操作或者必须经常扫描整个表,则速度很快。

MySQL 授予表写锁如下:

  1. 如果表上没有锁,则在其上放置一个写锁。

  2. 否则,将锁请求放入写锁队列。

MySQL 授予表读锁如下:

  1. 如果表上没有写锁,则在其上放置读锁。

  2. 否则,将锁请求放入读锁队列。

表更新的优先级高于表检索。因此,当锁被释放时,该锁可用于写锁队列中的请求,然后可用于读锁队列中的请求。这确保了对表的更新不会饿死,即使表有大量SELECT活动也是如此。但是,如果表有很多更新, SELECT语句会等待直到没有更多更新。

有关更改读写优先级的信息,请参阅第 8.11.2 节,“表锁定问题”

您可以通过检查 Table_locks_immediateTable_locks_waitedstatus 变量来分析系统上的表锁争用,这些变量分别表示可以立即授予表锁请求的次数和必须等待的次数:

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

MyISAM存储引擎支持并发插入以减少读取器和写入器之间对给定表的争用:如果 表MyISAM 在数据文件中间没有空闲块,则行总是插入到数据文件的末尾。在这种情况下,您可以在没有锁的情况下自由混合并发 INSERTSELECT表的语句 。MyISAM也就是说,您可以将行插入到MyISAM表同时其他客户正在从中读取。空洞可能是由于表中的行被删除或更新而产生的。如果有空洞,并发插入将被禁用,但当所有空洞都被新数据填充后,并发插入将自动再次启用。要控制此行为,请使用 concurrent_insert系统变量。请参阅第 8.11.3 节,“并发插入”

如果您使用 显式获取表锁 LOCK TABLES,您可以请求一个 READ LOCAL锁而不是一个 READ锁,以允许其他会话在您锁定表时执行并发插入。

要在不能并发插入的情况下对表执行许多INSERTand SELECT操作 t1,可以将行插入临时表 temp_t1并使用临时表中的行更新实际表:

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

选择锁定类型

通常,表锁在以下情况下优于行级锁:

  • 该表的大多数语句都是读取的。

  • 该表的语句是读取和写入的混合,其中写入是对单个行的更新或删除,可以通过读取一个键来获取:

    UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
    DELETE FROM tbl_name WHERE unique_key_col=key_value;
  • SELECT结合并发INSERT 语句,很少有 UPDATEor DELETE语句。

  • GROUP BY在没有任何编写器的情况下对整个表进行 多次扫描或操作。

使用更高级别的锁,您可以通过支持不同类型的锁来更轻松地调整应用程序,因为锁开销小于行级锁。

行级锁定以外的选项:

  • 版本控制(例如 MySQL 中用于并发插入的版本控制),其中可以同时拥有一个写入器和多个读取器。这意味着数据库或表根据访问开始的时间支持不同的数据视图。其他常用术语是 时间旅行”、 时复制按需复制”。

  • 在许多情况下,按需复制优于行级锁定。然而,在最坏的情况下,它可以使用比使用普通锁更多的内存。

  • 您可以使用应用程序级锁,例如 MySQL 提供的 GET_LOCK()以及 RELEASE_LOCK()在 MySQL 中提供的锁,而不是使用行级锁。这些是咨询锁,因此它们仅适用于相互协作的应用程序。请参阅 第 12.15 节,“锁定功能”