发表更新MySQL系列42 分钟读完 (大约6228个字)

MySQL系列(10)： MySQL InnoDB 锁机制

　　锁是计算机协调多个进程或并发访问某一资源的机制，数据也是供许多用户共享的资源。Java有锁机制，数据库也有锁机制。数据库锁定机制简单来说，是为了保证在并发情况下数据的一致性、有效性。　　

　　MySQL的锁机制是由存储引擎提供的，InnoDB存储引擎支持行级锁和表级锁，默认级别行级锁(row-Level locking); MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking),BDB存储引擎采用的是页面锁(page-level locking),也支持表级锁，BDB已成为历史。

　　InnoDB 与 MyISAM 最大的不同有两点：一是支持事务，二是采用行级锁。

　　MySQL 5.7 官方文档：14.7.1 InnoDB Locking，14.7.3 Locks Set by Different SQL Statements in InnoDB，14.7.5 Deadlocks in InnoDB。

概述

MySQL 3种锁的特点大致归纳如下：

  1. 行级锁:开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。
  2. 表级锁:开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。
  3. 页级锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

在锁的选择上，应根据具体应用来选择合适的锁。从锁的特性来说：

  1. 表级锁：更适合以查询为主，少量根据条件更新数据的应用，如Web应用。
  2. 行级锁：适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。

查看锁信息

  1. 查看行锁信息 
    1
    SHOW STATUS LIKE 'Innodb_row_lock%';
    • Innodb_row_lock_current_waits：当前等待行锁的数量。
    • Innodb_row_lock_time：获取行锁总时间，毫秒。
    • Innodb_row_lock_time_avg：获取行锁的平均时间，毫秒。
    • Innodb_row_lock_time_max：获取行锁的最长时间，毫秒。
    • Innodb_row_lock_waits：等待行锁的操作次数。
      如果 Innodb_row_lock_time_avg 和 Innodb_row_lock_waits 的值较高说明锁争用比较严重。
  2. 通过查询 information_schema 数据库中相关的表来查看锁情况，如下： 
    1
    SELECT * FROM information_schema.`INNODB_LOCKS`;
  3. 通过设置 InnoDB Monitors 来进一步观察发生锁冲突的表，数据行等，并分析锁争用的原因，如下： 
    1
    2
    3
    4
    5
    6
    -- 创建观察表
    CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
    -- 查看存储引擎状态
    SHOW ENGINE INNODB STATUS;
    -- 查看分析完后，删除观察表
    DROP TABLE innodb_monitor;

行锁与意向锁

行锁

InnoDB 实现的行级锁有两种类型：共享锁(S) 和 独占锁(X)-又被称为排他锁。

  • 共享锁(Shared Locks)：又称 读锁，允许多个事务共享一把锁，都能访问到数据，都只能读不能改。
  • 排他锁(Exclusive Locks)：又称 独占锁、写锁，锁定数据行的事务可以读取行和修改数据，其它事务不能再获取该行的其他锁，包括共享锁和排他锁，只能等待锁被释放才能对该行进行锁定。

意向锁

InnoDB 支持多种粒度锁定，允许 行锁 和 表锁 共存。 为了实现多个粒度级别的锁定，InnoDB 内部使用了 意向锁(Intention locks)。
意向锁： 的主要目的是显示有人正在锁定一行，或者将要锁定表中的一行。意向锁并不会阻止除全表请求(如：锁表…写)之外的任何操作。意向锁 是 表级锁，有 意向共享锁 和 意向排他锁 两种类型。

  • 意向共享锁(intention shared lock (IS))：在事务可以获取表中某数据行的 共享锁 之前，它必须先获取表的 意向共享锁 或更强的锁。
  • 意向排他锁( intention exclusive lock (IX))：在事务可以获取表中某数行的 排他锁 之前，它必须首先获取表上的意向排他锁。

表级锁类型兼容性如下：

X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容
如果一个请求事务与现有锁兼容，则可以获得锁；若冲突，则无法获得锁，事务等待，直到锁被释放；如果锁请求与现有锁冲突并且导致死锁，则会发生错误。

加锁操作

意向锁是 InnoDB 自动加的，不需用户干预。
对于 update,delete,insert 语句，InnoDB会自动给涉及数据行加 排他锁。
对于 select 语句，InnoDB 不会加任何锁(也就是被加 排他锁 的数据行，默认情况下其它事务仍可执行 select 查询数据)。

可通过以下语句显式给数据行加共享锁和排他锁。

  1. 共享锁：

    SELECT * FROM tb_name WHERE … LOCK IN SHARE MODE;

    主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行update,delete操作。但如果当前事务也需要对该记录进行更新操作，则很可能造成死锁。
  2. 排他锁：

    SELECT * FROM tb_name WHERE … FOR UPDATE;

    对于锁定行数据行要执行更新操作的，应使用排他锁。

备注：加锁需要在事务语句块中执行，即要放在 BEGIN … COMMIT 中，因 MySQL 默认是自动提交的，所在加锁前必须关闭自动提交：

SELECT @@autocommit;
SET AUTOCOMMIT=0;

行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，则通过隐藏的聚簇索引来对记录加锁，InnoDB行锁分为3种情形。

Record Locks

记录锁 是对索引记录的锁定，是加在索引记录上的。 即使表没有定义索引，InnoDB 会创建一个隐藏的聚簇索引并使用此索引进行记录锁定。适用于主键索引或唯一索引。
例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 防止任何其他事务插入，更新或删除 t.c1 的值为 10 的行。

Gap Locks

间隙锁 是对锁定索引记录之间的间隙加锁，或对第一条索引记录之前的间隙或最后一条索引记录之后的间隙加锁。 间隙 可能跨越单个索引值，多个索引值，甚至空值。
使用唯一索引的列不需要间隙锁(但不包括多列构成唯一索引的情况，这种情况确实会发生间隙锁定）, 如果条件列没有索引，或非唯一索引，则会产生间隙锁。

间障锁 是性能和并发之间的平衡，只在某些事务隔离级别使用。
如果将事务隔离级别为 READ COMMITTED 或启用 innodb_locks_unsafe_for_binlog 系统变量（现已弃用），对于搜索和索引扫描就会禁用间隙锁，仅用于外键约束检查和重复键检查；并且 MySQL 评估 WHERE 条件后，将释放不匹配行的记录锁。 对于 UPDATE 语句，InnoDB 执行 半一致 读取，以便将最新提交的版本返回给 MySQL，以便 MySQL可以确定该行是否与 UPDATE 的 WHERE 条件匹配。

间隙锁 是完全禁止限制，这意味着它们的唯一目的是防止其他事务插入到间隙中。间隙锁可以共存，一个事务获取的间隙锁不会阻止另一个事务在同一间隙上获取间隙锁。共享和独占的间隙锁没有区别。它们不会相互冲突，并且执行相同的功能。
例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 阻止其他事务将值 15 插入到列 t.c1 中，无论列中是否已存在任何此类值，因为该范围内所有现有值之间的间隔都被锁定。

注意：冲突锁可以通过不同的事务保持在间隙上。 例如，事务 A 可以在间隙上保持共享间隙锁（间隙S锁），而事务 B 在同一间隙上保持独占间隙锁（间隙X锁）。 允许冲突间隙锁的原因是，如果从索引中清除索引记录，则必须合并由不同事务保留在记录上的间隙锁定。

Next-Key Locks

Next-Key Lock 是索引记录上的记录锁定(Record Locks)和索引记录之前的间隙上的间隙锁定(Gap Locks)的组合。

简单理解：当使用范围条件检索数据，并请求共享和排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙(GAP), InnoDB 也会对这个间隙**加锁，这种锁机制就是Next-Key锁。

索引记录上的 Next-Key Lock 也会影响索引记录之前的 **Gap(间隙)**，也就是说， Next-Key 是索引记录锁定加上索引记录之前的间隙上的间隙锁定。如果一个 **session ** 拥有索引记录 R 上的共享或排他锁，则另一个 session 不能在索引顺序中的 R 之前的间隙中插入新的索引记录。

此种加锁机制会阻塞符合条件范围内键值的并发插入，往往会造成严重的锁等待。所以在实际应用开发中，尤其并发插入较多的应用，尽量优化业务逻辑，使用相等条件来访问更新数据，避免使用范围条件。

InnoDB 执行行级锁定：当搜索或扫描表索引时，在命中的索引记录上设置 共享锁 或 排它锁，因此，行级锁实际上是索引记录锁。

InnoDB 默认在可重复读取事务隔离级别下运行。在这种情况下，InnoDB 使用next-key locks进行搜索和索引扫描，以防止出现幻象行。也就是说默认的事务隔离级采用的是 Next-Key Locks 实现行锁。

InnoDB 使用Next-Key锁的目的，一方面是为了防止幻读；另一方面，是为了满足其恢复和复制的需要。

特别说明： InnoDB　通过范围条件加锁时使用Next-Key锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用Next-Key。

例：emp 表只有101条记录，其 empid 是自增序列 1到101。范围查询语句如下：

1
select * from emp where empid >100 for update;

例子中 InnoDB 不仅会对符合条件的 empid 值为101的记录加锁，也会对 empid 大于 101(这些记录并不存在)的 间隙加锁。

Insert Intention Locks

插入意向锁(insert intention locks) 是在行插入之前，由 INSERT 操作设置的一种间隙锁。插入意向锁 的目的是使多个事务插入到相同索引间隙但不同位置，不需要等待彼此。

例：假设存在值为4和7的索引记录，尝试分别插入值5和6的单独事务，每个事务在获取插入行的排它锁之前使用插入意向锁锁定4和7之间的间隙， 不会互相阻塞，因为是非冲突的行。

AUTO-INC Locks

AUTO-INC Locks 是一种特殊的表级锁，是由插入到具有自增列的表中的事务获取。

在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务都必须等待自己对该表执行自己的插入，以便第一个事务插入的行接收到连续的主键值。

innodb_autoinc_lock_mode配置选项控制用于自增锁定的算法。 它允许选择如何在自增值的可预测序列和插入操作的最大并发之间进行权衡。

更多信息，请参见 Section 14.6.1.4, “AUTO_INCREMENT Handling in InnoDB”.

Predicate Locks

InnoDB 支持包含空间列(spatial columns)的列的 SPATIAL 索引（参考 Section 11.5.8, “Optimizing Spatial Analysis”)

在处理涉及SPATIAL 索引的操作的锁定时，Next-Key Lock 不能很好地支持 REPEATABLE READ 或SERIALIZABLE 事务隔离级别。因为多维数据中没有绝对排序概念，因此不清楚哪个是“ next ” key。

为了支持具有空间索引的表的隔离级别，InnoDB使用 Predicate Locks。空间索引包含最小边界矩形（MBR）值，因此 InnoDB 通过对用于查询的 MBR 值设置 Predicate Locks 来强制对索引执行一致的读取。其他事务不能插入或修改与查询条件匹配的行。

InnoDB锁与索引

  1. InnoDB这种行锁特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果也就跟表锁一样。所以应明确地指定检索条件的索引，MySQL才会执行Row lock(只锁定被选取的数据)，否则将会执行表级锁(table lock)，把整个表单给锁住。
  2. InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同的行，但如果使用相同的索引键，会出现锁冲突。
  3. 当表中有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
  4. 即使条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果认为全表扫描效率更高，就不会使用索引，这种情况下InnoDB也会对所有记录加锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确定是否真正使用了索引。　

InnoDB中的死锁

死锁概念

　　死锁：每个事务都持有另一个事务需要的锁，事务之间都在等待对方释放资源，所以都不会释放所持有的锁，导致事务无法继续执行。简单理解，两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环等待就是典型的死锁。

死锁检测

　　InnoDB 默认启用了死锁检测，当发生死锁时，InnoDB 会检测到并回滚其中一个事务(释放锁并回退)，另一个事务获得锁，继承完成事务。如果使用innodb_deadlock_detect配置选项禁用死锁检测，则 InnoDB 极据innodb_lock_wait_timeout设置的死锁等待超时时长来回滚事务。因此，即使应用的业务逻辑正确，仍必须重试事务。

　　在涉及外部锁或涉及表锁的情况下，InnoDB 并不能完全自动检测死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务无法立即获得所需的锁而挂起，会占用大量的系统资源，造成严重的性能问题，拖垮数据库。通过设置合适的锁等待超时阀值，可以避免此情况发生。

　　要查看 InnoDB 用户事务中的最后一个死锁，可使用 SHOW ENGINE INNODB STATUS 。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL，事务已经获得的锁，现在等待什么锁，以及被回滚的事务等等。据此可以分析死锁产生的原因和改进描施。

　　如果频繁出现死锁，表示事务结构或应用错误处理存在问题。可以启用 innodb_print_all_deadlocks 运行，将有关死锁的所有信息打印到 mysqld 锁误日志中。更多关于如何自动检测和处理死锁的信息，请参考Section 14.7.5.2,“Deadlock Detection and Rollback”。

死锁示例

　　通常来说，死锁大多是应用设计的问题，通过调整业务流程，数据库对象，事务大小以及访问数据库的SQL语句,绝大部分死锁可以避免。

　　如果事务锁定多个表中的行时(如 update 或 select … for update 之类的语句)，多个事务锁定资源的顺序相反，都等待对方锁定的资源，会发生死锁；这样的语句锁定索引记录和间隙范围 时,也会发生死锁。

  1. 客户端A 查询加共享锁

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    -- 客户端A
    mysql> START TRANSACTION;
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
    +------+
    | i    |
    +------+
    |    1 |
    +------+

  2. 客户端B 对相同的行执行删除操作

    1
    2
    3
    4
    5
    -- 客户端B
    mysql> START TRANSACTION;
    Query OK, 0 rows affected (0.00 sec)

    mysql> DELETE FROM t WHERE i = 1;

    删除操作需要获取 排它锁(X lock)，客户端B不能获取到，因这与 客户端A 持有的 共享锁(S lock)冲突，因此，事务请求将进入锁请求队列并阻塞客户端B。

  3. 最后，客户端A 也试着删除该行

    1
    2
    3
    4
    -- 客户端A
    mysql> DELETE FROM t WHERE i = 1;
    ERROR 1213 (40001): Deadlock found when trying to get lock;
    try restarting transaction

    这样就产生了死锁，因为客户端A 需要 X锁 来删除该行，但无法获取；因为该行的 X锁 已经被客户端B请求，并且正等待客户端A 释放其 S锁。由于客户端B 先请求 X锁，客户端持有的 S锁 也不能升级到 X 锁。

    B 等 A 释放 S锁，A 等 B释放 X锁，而 A 的 S锁 不能升级到 X锁，这就就造成了死锁。

    这样，InnoDB 会为其中一个客户端生成错误并回滚结束事务并释放其持有的锁。客户端返回如下错误：

    1
    2
    ERROR 1213 (40001): Deadlock found when trying to get lock;
    try restarting transaction

    之后，InnoDB 可以授予其他客户端的锁请求，执行表中该行的删除操作。

降低死锁

　　尽量使用事务，不要使用导致锁表的语句(lock table)，插入或更新操作尽可能保持最小粒度，不要长期打开（占用）数据行(锁)。

　　当不同的事务更新多个表或大范围数据行时，每个事务中使用相同的操作顺序（例如：select … for update）；在 select … for update 和 update … where 语句中的条件列上创建索引。

　　产生死锁的可能性不受事务隔离级别的影响，因为隔离级别只是改变了读操作的行为，而死锁是写操作时发生的。有关避免和从死锁条件中恢复的更多信息，请参见Section 14.7.5.3, “How to Minimize and Handle Deadlocks”。

表锁使用

  1. 什么时候选择表锁
    InnoDB 表在绝大部分情况下应使用行级锁，因为事务和行锁是我们选择 InnoDB 表的理由。但在特殊事务中，也可以考虑用表级锁。
    • 第一种情况：事务需要更新大部分或全部数据，表又比较大时可考虑使用表锁来提高该事务的执行速度。如果使用认的行锁执行效率低，可能造成较长时间的锁等待和锁冲突。
    • 第二种情况：事务涉及多个表，比较复杂，可能引起死锁，造成大量的事务回滚，可以考虑使用一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。
      以上两种事务不能太多，否则就应考虑使用MyISAM表了。
  2. 使用 InnoDB 表锁注意如下两点
    • Lock Tables表锁不是 InnoDB 存储引擎层管理的，是上一层 MySQL Server负责的，仅当autocmmit = 0,innodb_table_locks = 1(默认设置)时，InnoDB才能知道 MySQL 加的表锁，MySQL Server也才能感知道 InnoDB 加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的列锁；否则，InnoDB 将无法自动检测并处理这种死锁。
    • Lock Table对 InnoDB 表加锁时要注意，要将 MySQL 的事务自动提交关闭，将autocommit设为0，否则MySQL不会加表锁；事务结束前，不要用unlock tables释放表锁，因为会隐含地提交事务；commit或rollback并不会释放用lock table加的表锁，必须使用 unlock tables 释放表锁。

更多加锁操作

表锁

  1. 查询表锁：show status like ‘table%’
  • Table_locks_immediate：表锁使用次数。
  • Table_locks_waited：表锁申请失败次数，说明存在较严重的表级锁争用的情况。

  1. 加写表锁(需手动unclock tables，用 commit 不会自动解锁)
    会阻塞其他用户对同一表的读请求，也会阻塞对同一表的写操作。

    1
    lock table tb_name write;

  2. 加读表锁（需手动unclock tables，用 commit 不会自动解锁）

    1
    lock table tb_name read;

  3. 加读表锁（需手动unclock tables，用 commit 不会自动解锁），并支持另一个事务的 insert操作。
    加了local选项，作用是在满足并发插入条件下，允许其他用户在表尾并发插入记录。

    1
    lock table tb_name read local;

  4. 其他说明
    如果对加锁的表使用了别名操作，则别名也需要加锁，否则报错。

    1
    lock table actor as a read, actor as b read;

    INSERT…SELECT…和 CREATE TABLE…SELECT… 语句，会锁原表

    1
    2
    3
    4
    5
    6
    -- 设置是否开启并发insert，0=不开启，1=没有空洞的情况下，允许insert，2=不管有没有空洞，都允许插入  
    set global concurrent_insert=2;
    -- 默认情况下，写锁比读锁优先级高，所以写锁会先得到锁，用这个命令可以降低写锁的级别   
    select @@LOW_PRIORITY_UPDATES; 
    -- 当写锁达到指定数量，会降低写锁优先级  
    select @@max_write_lock_count;

行锁

  1. 加共享锁（提交事务，自动解锁）
    不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作。 

    1
    select * from tb_name where id=2 lock in share mode;

  2. 加排他行锁（提交事务，自动解锁）

    1
    delete from tb_name where id=2 for update;

相关参考

  1. InnoDB Locking
  2. Locks Set by Different SQL Statements in InnoDB
  3. Deadlocks in InnoDB
  4. MySQL半一致性读原理解析
  5. MySQL锁详解
  6. Mysql加锁过程详解（1）-基本知识
  7. MySQL中的锁（表锁、行锁）

MySQL系列(10)： MySQL InnoDB 锁机制

http://blog.gxitsky.com/2019/01/29/MySQL-10-lock-InnoDB/

作者

光星

发布于

2019-01-29

更新于

2023-03-06

许可协议

#

喜欢这篇文章？打赏一下作者吧

评论

关注我
×