MySQL-进阶篇 ( 锁 )

• MySQL-进阶篇 ( 锁 )
  • 锁
    • 全局锁
      • 介绍
      • 使用
      • 演示
      • 特点
    • 表级锁
      • 介绍
      • 表锁
      • 元数据锁 ( meta data lock, MDL )
      • 意向锁
    • 行级锁
      • 介绍
      • 行锁
      • 间隙锁 / 临键锁

锁

• 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源 ( CPU、RAM、I/O ) 的争用以外，数据也是一种供许多用户共享的资源。
  • 如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
  • 从这个角度说，锁对数据库而言显得尤其重要，也更复杂
• MySQL 中的锁，按照锁的粒度 ( 即锁住的内容 ) 分，分为以下三类：
  • 全局锁：锁定数据库中的所有表
  • 表级锁：每次操作锁住整张表
  • 行级锁：每次操作锁住对应的行数据

全局锁

介绍

• 全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 DML 的写语句，DDL 语句，已经更新操作的事务提交语句都将被阻塞
• 其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性
  • 因为备份表是一个表一个表的备份，而不锁库的话，业务还会在备份的过程中不断执行、更改数据库，从而产生数据不一致

使用

• 加全局锁：flush tables with read lock;
• 为了备份利用数据库提供的 mysqldump 工具：mysqldump -uroot -p1234 test > D:/test.sql
  • -u 指定用户名，-p 指定密码，test 为需要备份的库，> 后面的表示备份的数据要存到 D 盘下的 test.sql 文件中
• 备份完成后解锁：unlock tables;
• 在未解锁时，此库只能查询 DQL，其余 DML、DDL 都不可执行

演示

• 打开命令行，登录 mysql 后执行 flush tables with read lock; 命令加全局锁
• 因为备份是利用了数据库提供的工具，所以要再另打开一个命令行，不登录 mysql 直接执行，即： C:\Users\dell>mysqldump -uroot -p1234 test > D:/test.sql
  • 若是访问远程的数据库非 windows 本机，就要加参数，此处以 192.168.200.202 为例指定主机地址，即执行： C:\Users\dell>mysqldump -h192.168.200.202 -uroot -p1234 test > D:/test.sql
  • 若是都在一个命令行里执行的话，就得退出 mysql> 才能执行语句，但退出了后全局锁就失效了，所有至少要两个命令窗口来操作
  • 此句执行完可能会因为语句显示了密码而会有个警告 ( 无碍 )，此时在 D 盘下就会找到备份好的 sql 文件
• 备份完成后，到施加全局锁的命令窗口，于 mysql> 后执行解锁操作 unlock tables; 即可释放

特点

• 数据库中加全局锁是一个比较重的操作，存在以下问题：
  • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
  • 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志 ( binlog )，会导致主从延迟
• 在 InnoDB 引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份，即直接打开命令行后就直接执行语句：mysqldump --single-transaction -h192.168.200.202 -uroot -p1234 test > D:/test.sql

表级锁

介绍

• 表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 应用在 MyISAM、InnoDB、BDB 等存储引擎中 ( 此处只讲述 InnoDB )
• 表级锁主要分为以下三类：
  • 表锁
  • 元数据锁 ( meta data lock, MDL )
  • 意向锁

表锁

• 对于表锁，分为两类：

  • 表共享读锁 ( read lock )
    • 各客户端只能读，不能写
    • 读锁不会阻塞其他客户端的读，单数会阻塞写
  • 表独占写锁 ( write lock )
    • 做表锁操作的客户端可以读也可以写，但是其他的客户端既不能读，也不能写
    • 写锁机会阻塞其他客户端的读，又会阻塞其他客户端的写

• 语法：

  • 加锁：lock tables 表名... read/write
  • 释放锁：unlock tables / 客户端断开连接

• 实例演示：

  • 表共享读锁：

    -- 打开用两个客户端登入mysql
    mysql -u root -p -- 然后输入密码登入
    -- 使用数据库
    use test;
    show tables; -- 看表
    select * from salgrade; -- 后面就以salgrade表为例
    
    -- 施加表锁
    lock tables salgrade read;
    -- 读取没问题(两个客户端都可读取)
    select * from salgrade;
    -- 更新此表数据
    update salgrade set losal = 20 where grade = 1;
    -- 报错：ERROR 1099 (HY000): Table 'salgrade' was locked with a READ lock and can't be updated
    -- 注意，锁单表时注意不能和其他表有关联，否则会报错另一个有关联的表没有加锁
    
    -- 另一个客户端也执行更新语句
    update salgrade set losal = 20 where grade = 1; -- 不会报错，但是会阻塞
    
    -- 释放表锁
    unlock tables;
    -- 此时就会发现那个被阻塞的客户端语句就会被执行了
    

  • 表独占写锁：

    -- 施加表锁
    lock tables salgrade write;
    -- 可读取
    select * from salgrade;
    -- 可更新
    update salgrade set losal = 20 where grade = 1;
    
    -- 其他的客户端不可读，也不可写，都会被阻塞
    
    -- 释放表锁
    unlock tables;
    -- 此时就会发现那个被阻塞的客户端语句就会被执行了
    

元数据锁 ( meta data lock, MDL )

• MDL 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。

  • MDL 锁主要是维护表元数据的数据一致性，在表上有活动事务的时候 ( 有未提交的事务时 )，不可以对元数据进行写入操作
  • 存在主要作用：避免 DML 和 DDL 冲突，保证读写 ( 还有表结构 ) 的正确性

• 在 MySQL5.5 中引入了 MDL，当对一张表进行增删改查的时候，加 MDL 读锁 ( 共享锁 )；当对表结构进行变更操作的时候，加 MDL 写锁 ( 排他锁 )

  对应 SQL	锁类型	说明
  lock tables xxx read / write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
  select、select ... lock in share mode ( 共享锁 )	SHARED_READ ( 读锁 )	与 SHARED_READ、SHARED_WRITE 兼容，与 EXCLUSIVE 互斥
  insert、update、delete、select ... for update	SHARED_WRITE ( 读锁 )	与 SHARED_READ、SHARED_WRITE 兼容，与 EXCLUSIVE 互斥
  alter table ...	EXCLUSIVE ( 排他锁 )	与其他的 MDL 都互斥

• 增删改之间的读锁是兼容的，所以两个客户端可以对一个表做操作不被阻塞

• 但若是在客户端 1 开启事务未提交时，客户端 2 执行语句改变表结构 alter table score add column java int; 就会处于阻塞状态，直到客户端 1 commit; 提交后客户端 2 的改变表结构语句才能继续执行

  • 因为排他锁与其他锁互斥

• 查看元数据锁：select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;

• 实例演示：

  • 如下图第一个表只有 SHARED_READ 元数据锁，若是另一个客户端 2 开启事务后对 test 库中的 salgrade 表进行查询语句 ( 没提交 )，此客户端再执行查看元数据锁语句就如下图第二张的结果，多了一个元数据锁
  • 
  • 此时客户端 1 可以开启事务进行增删改查，查看元数据锁可以看到对应的锁的添加，但就无法改变表的结构，直到客户端 2 提交事务

意向锁

• 实例引入：

  • 在一个客户端对一个表加以行锁后，若是另一个客户端想对此表施加表锁，那就需要从第一条数据开始检查有没有行锁冲突，这样做的效率极低，所以就引入了意向锁

• 为了避免 DML 在执行时，加的行锁与表锁的冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查

  • 在给一个表加行锁时就给表加了意向锁，这样另一个客户端过来时就检查意向锁就可以直到能不能够加以表锁 ( 看兼不兼容 )

• 意向锁又分为以下两种：

  • 意向共享锁 ( IS )：由语句 select ... lock in share mode 添加
    • 与表锁共享锁 ( read ) 兼容，与表锁排他锁 ( write ) 互斥
  • 意向排他锁 ( IX )：由 insert、update、delete、select ... for update 添加
    • 与表锁共享锁 ( read ) 及排他锁 ( write ) 都互斥。意向锁之间不会互斥

• 可以通过以下 SQL，查看意向锁及行锁的加锁情况：select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

• 实例演示：

  • 客户端 1 开始事务后，执行语句 select * from salgrade where grade = 1 lock in share mode; 给 grade 字段为 1 的加上了意向共享锁
  • 客户端 2 执行 select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks; 查看，在 lock_type 一列为 TABLE 的一行的 lock_mode 一列会看到 IS ( 就是意向共享锁 )
    • 此时再执行 lock tables salgrade read; 加读锁也不会报错 ( 兼容 )
    • 而执行 lock tables salgrade write; 加写锁就会阻塞 ( 不兼容 )
  • 客户端 1 开始事务后，执行 update 更新语句就会加上了意向排他锁
  • 客户端 2 执行 select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks; 查看，在 lock_type 一列为 TABLE 的一行的 lock_mode 一列会看到 IX ( 意向排他锁 ) 而不是 IS
    • 此时就算读锁也会报错

行级锁

介绍

• 行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB 存储引擎中
• InnoDB 的数据是基于索引组织的 ( 即数据都是在叶子上，在对应的索引下 )，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：
  • 行锁 ( Record Lock )：锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete 操作。在 RC ( Read committed )、RR ( Repeatable Read ) 隔离级别下都支持
    • 隔离级别在基础篇的事务中有提及
  • 间隙锁 ( Gap Lock )：锁定索引记录间隙 ( 不含该记录 )，确保索引记录间隙不变，防止其他事务在这个间隙进行 insert，产生幻读。在 RR 隔离级别下都支持
    • 间隙就是叶子处两个数据之间的范围，锁的也就是各数据记录之间的间隙
  • 临键锁 ( Next-Key Lock )：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap。在 RR 隔离级别下支持
    • 可以简单理解为行锁和间隙锁的组合

行锁

• InnoDB 实现了以下两种类型的行锁：

  • 共享锁 ( S )：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

    • 共享锁之间兼容，和排他锁互斥

  • 排他锁 ( X )：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

    • 和共享、排他锁都互斥

• 常见的增删改查的 SQL 加锁情况

  SQL	行锁类型	说明
  INSERT ...	排他锁	自动加锁
  UPDATE ...	排他锁	自动加锁
  DELETE ...	排他锁	自动加锁
  SELECT ( 正常 )	不加任何锁
  SELECT ... LOCK IN SHARE MODE	共享锁	需要手动在 SELECT 之后加 LOCK IN SHARE MODE
  SELECT ... FOR UPDATE	排他锁	需要手动在 SELECT 之后加 FOR UPDATE

• 默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key ( 临键锁 ) 锁进行搜索和索引扫描，以防止幻读

  • 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动把临键锁优化为行锁
  • InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁

• 实例演示 1：

  • 同样是通过以下语句从表中查看意向锁和行锁的加锁情况：select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

  • 客户端 1 开启事务执行普通的 select 查询操作，客户端 2 执行查看锁的语句会发现为空

  • 若是客户端 1 开启事务执行 select * from test_user where id = 1 lock in share mode;，客户端 2 再执行查看锁语句

    

    • 其中 S 是指共享锁，REC_NOT_GAP 是没有间隙，即：自动把临键锁优化为行锁

  • 此时客户端 2 执行更新语句，若是 id 为 1 就会阻塞 ( 共享与排他互斥 )，id 不为 1 的就不会有问题，在事务中执行更新其他 id 的语句就会查得排他锁 X

    

• 实例演示 2：

  • 客户端 1 在事务中执行更新语句，但条件没有用到索引的话，如 update test_user set name = '12' where name = 'zyz';，客户端 2 就算执行针对其他行的更改语句，也会被阻塞
    • 即：不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁

间隙锁 / 临键锁

• 默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key ( 临键锁 ) 锁进行搜索和索引扫描，以防止幻读

  • 索引上的等值查询 ( 唯一索引时 )，给不存在的记录加锁时，优化为间隙锁
  • 索引上的等值查询 ( 普通索引 )，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
    • 普通索引的话值可能会相同，即在叶子节点处，某一值的前面和后面可能会插入相同的值，向右遍历是找到第一个与此值不相同的，然后加锁
  • 索引上的范围查询 ( 唯一索引 )，会访问到不满足条件的第一个值为止
    • 唯一索引的范围查询，也会加上对应的临键锁

• 注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

  • 主要理解为：间隙锁锁间隙不包含数据记录，而临键锁不仅锁记录，也锁此记录前的间隙

• 实例演示：

  • test_user 表中只有 id 为1、2、4 的数据，此时客户端 1 开启事务执行 update test_user set name = 'dd' where id = 3;，在客户端 2 中查看得

    

    • 第二行 RECORD 行级锁，为 X 排他锁和 GAP 间隙锁，锁的就是 lock_date 中显示的 id 为 4 之前的最近的间隙，即 2 到 4 之间的间隙
    • 此时再插入 id 为 3 的数据就会阻塞，直到客户端 1 提交了释放锁了才能插入数据

  • create index idx_user_name on test_user(name); 给普通字段 name 加以普通索引，

    • 在客户端 1 开启事务并在查询时加以锁 select * from test_user where age = 40 lock in share mode; 查得两条 spring 的记录，在二级索引的叶子节点，根据顺序 spring 后面的就是 zyz

    • 然后在客户端 2 查看锁

      

    • 只看行级锁，其中先是单 S 临键锁，'spring', 4 将 4 之前的间隙和 4 这条记录加以锁，然后是 S, REC_NOT_GAP 给 id 为 4、5 的记录加以行锁没有间隙，然后 GAP 将 spring 到 zyz 之间加以间隙锁

    • —— 即：锁住了所有的 spring 和 spring 之前与之后的所有间隙

  • 客户端 1 开启事务后执行语句 select * from test_user where id >= 4 lock in share mode; 范围查询，查到 id 为4、5、6 的数据，然后客户端 2 查看锁得

    

    • 先给 4 的记录加以行锁，然后单 S 临键锁，在 lock_data 显示 supremum ... 可以理解为正无穷，表示 6 之后一直到正无穷大也加了一个临键锁，在 lock_data 显示 id 的表示锁住了 5、6 记录以及 5、6 之前的间隙
    • —— 即：把大于 4 的数据一直到正无穷大的全部锁住了