MySQL锁机制

  • 锁是计算机协调多个进程或线程并发访问某一资源的机制
  • 在数据库中，除传统的计算资源（如CPU、RAM、IO等）的争用以外，数据（表）也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的分类

  1. 根据数据操作类型分类：读锁、写锁
    读锁（共享锁）： 针对同一份数据，多个读操作可以同时进行而不会相互影响。
    写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
  2. 对数据操作粒度分类：表锁（偏读）、行锁（偏写）、页锁
    表锁特点：
    偏向MyISAM存储引擎，开销小，加锁快
    无死锁，因为锁的是整个表
    锁粒度大，发生锁冲突概率高
    并发度最低

表锁案例演示（偏读，偏向MyISAM存储引擎）

  1. # 查看表是否有锁
  2. show open tables ;

In_use = 0表示当前表未加锁

MySQL锁机制以及主从复制 - 图1

  1. # 手动为account表上读锁,article表上写锁
  2. lock tables account read, SqlPro.article write ;

为account表上读锁之后进行查询

MySQL锁机制以及主从复制 - 图2

解锁

  1. ## 解锁
  2. unlock tables ;

解锁之后再次进行查询

MySQL锁机制以及主从复制 - 图3

读锁和写锁究竟会如何影响我们的操作呢？

以下案例均在两个Session中实现

MySQL锁机制以及主从复制 - 图4

  1. 加读锁（共享锁）

    在Session1中为account加读锁，在Session2中读取account

    Session1和Session2均可以读取，这就说明了读锁是共享锁

    MySQL锁机制以及主从复制 - 图5 Session1写（insert或者update）加读锁的表

    结果是不允许修改

    MySQL锁机制以及主从复制 - 图6 Session1读别的表 不允许。因为目前Session1已经有了一把表锁在手上，而且还未释放锁。

    由于MySQL出于自我保护的机制，需要当前表解锁之后才能读其他的表，否则这个表可能会一直被锁而没有后续操作。

    MySQL锁机制以及主从复制 - 图7 Session2写Session1中加了读锁的表

    发现Session2一直处于阻塞状态。

    当在系统中如果有多个操作都需要写加读锁的表，那么都会处于阻塞状态，这就会大大影响系统的性能了。

    MySQL锁机制以及主从复制 - 图8

    在Session1解锁之后 Session2的写操作立即执行了 总结 Session1中某张表加读锁

Session1 Session2
当前Session可以查询加读锁的表 当前Session可以查询Session1中加读锁的表
当前Session不能查询其他没有锁定的表（因为占据着一个锁，未释放锁就不能操作其他的表） 当前Session可以查询或者更新其他未锁定的表
当前Session写加读锁的表报错（读锁不允许进行写操作） 当前Session写Session1中加读锁的表会一直等待（阻塞）
释放锁 在Session1释放锁之后，执行阻塞的对之前加锁的表的写操作
  1. 加写锁（排它锁）

    MySQL锁机制以及主从复制 - 图9 Session1读、写加写锁的表 都可以执行

    MySQL锁机制以及主从复制 - 图10

    Session1读、写其他表 均报错

    MySQL锁机制以及主从复制 - 图11 Session2读取加写锁的表 一直处于阻塞状态 Session1释放写锁之后 Session2读取命令执行

    MySQL锁机制以及主从复制 - 图12

    同样 Session2写加写锁的表 同样处于阻塞状态，等待Session1写锁释放之后就会执行

    MySQL锁机制以及主从复制 - 图13 Session1中某张表加写锁

Session1 Session2
可以读写加写锁的表 读取和写加写锁的表会阻塞
不可以读写其他表（因为占据着一个锁，未释放锁就不能操作其他的表） 读写其他的表都可以正常进行
  1. 表锁分析

    查看当前表的锁的情况

    MySQL锁机制以及主从复制 - 图14 Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加一

    Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值就加一），此值高说明当前系统存在较严重的表级锁争用情况

  1. # 查看数据库中表的加锁情况
  2. show open tables;
  4. # 查看MySQL表的锁定情况
  5. show status like 'table_locks%';
  1. 总结
    • MyISAM存储引擎在执行查询（SELECT语句）之前，会自动给涉及的所有表加读锁
    • MyISAM存储引擎在执行增删改之前，会自动给涉及的所有表加写锁
    • 因此，MyISAM存储引擎的读写调度是写优先，所以不适合作为写为主表的引擎。因为执行写操作，会加写锁，其他线程不能做任何操作（包括读写），大量的写操作会使读很难获取到锁，从而造成永远堵塞。
    • MySQL的表级锁有两种模式：表共享读锁、表独占写锁
    • {% p cyan, 读锁会阻塞写，但不会阻塞读。而写锁会阻塞读和写。%}

行锁案例演示（偏写，偏向InnoDB存储引擎）

  1. 特点
    • 偏向InnoDB存储引擎
    • 开销大，加锁慢，会出现死锁
    • 锁的粒度最小，发生锁冲突的概率最低，并发度也最高

      InnoDB和MyISAM最大的不同点：InnoDB支持事务，InnoDB采用行级别的锁 事务（Transaction）：事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常称为ACID属性。

      • 原子性（Atomicity）：事务是一个原子操作。里面的SQL语句要么同时执行，要么都不执行
      • 一致性（Consistent）：ACID里的AID都是数据库的特征,也就是依赖数据库的具体实现.而唯独这个C,实际上它依赖于应用层,也就是依赖于开发者。这里的一致性是指系统从一个正确的状态,迁移到另一个正确的状态，什么叫正确的状态呢?就是当前的状态满足预定的约束就叫做正确的状态.而事务具备ACID里C的特性是说通过事务的AID来保证我们的一致性.参考文章
      • 隔离性（Isolation）：数据库提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”的环境执行。这也同时意味着事务处理过程中的中间状态对外部是不可见的。
      • 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使系统出现故障也能够保持。

      并发事务处理带来的问题

      • 更新丢失：当多个事务更新同一行数据的时候，由于事务之间是隔离的，就会可能出现A事务和B事务在时间上有一定的执行顺序，比如事务A和事务B，修改同一个数据，事务开始的时候，查询的这一行数据是相同的，A事务执行完之后提交，此时数据已经被修改了，但是B事务还是以为数据还是刚开始的时候的。那么A事务执行的更新就丢失了，被B事务修改的内容覆盖了。解决方法：在A事务提交之前，B事务不可访问同一行数据。参考文章
      • 脏读：一个事务正在对一条记录修改，在这个事务未提交之前，这条数据就处于不一致的状态，此时另一个事务也来读取这个数据，如果不加控制，第二个事务就读到了脏数据并在此基础之上做进一步的处理了，就会产生未提交的数据依赖关系。总结：事务A读取了事务B已修改但尚未提交的数据，并在此基础之上进行操作。此时，如果A事务回滚，那么数据就不符合一致性原则。
      • 不可重复读：事务A在读取某些数据之后，过段时间（还在事务中）再次读取以前读过的数据，发现数据已经被事务B被改变并提交，那么事务A再次读取的时候，就会发现数据已经不匹配了。总结：事务A读取到了事务B已经提交的修改数据，不符合隔离性
      • 幻读：一个事务按照相同的查询条件进行查询，过段时间后，重新按照相同条件查询（还在同一个事务中），却发现了其他事务insert了新的记录。总结：事务A读取到了事务B新增的数据，不符合隔离性。

      事务的隔离级别

      MySQL默认隔离级别：可重复读

      MySQL锁机制以及主从复制 - 图15

  1. 案例演示

    InnoDB引擎 事务隔离级别是可重复度 并关闭自动提交

    分别为a、b创建单值索引，因为如果where条件没有索引的话，行锁会变成表锁

    MySQL锁机制以及主从复制 - 图16 A 修改之后未手动提交，然后查询，发现可以读取到已经修改未提交的数据

    此时A还是未提交 B去查询，发现是修改之前的数据

    MySQL锁机制以及主从复制 - 图17

    A手动提交事务之后 B手动提交事务之后

    二者再次查询 发现都能读取到A修改后的数据

    MySQL锁机制以及主从复制 - 图18 修改同一行数据

    A修改之后还未提交 B修改同一行数据 B会处于阻塞状态

    MySQL锁机制以及主从复制 - 图19

    A commit之后 B处于阻塞的更新语句也执行了

    MySQL锁机制以及主从复制 - 图20

    B commit

    MySQL锁机制以及主从复制 - 图21 A修改一行不提交 B修改另一行不提交 两者都不会冲突

    MySQL锁机制以及主从复制 - 图22

    二者都commit之后 更新都提交了

    MySQL锁机制以及主从复制 - 图23

  1. 索引失效导致行锁变表锁

    这个问题很隐蔽，需要多加注意

    首先建立一个新表 a是int型 b是varchar型，并为a、b创建索引

    MySQL锁机制以及主从复制 - 图24 正常情况下 A修改第1行数据，B修改第6行数据。二者互不影响，如下图

    A修改未提，B修改也未提交。这种情况下B并未阻塞，因为修改的不是同一行数据。

    MySQL锁机制以及主从复制 - 图25

    二者都commit之后 数据都修改了

    MySQL锁机制以及主从复制 - 图26 我们知道 如果MySQL数据进行了数据类型转换，会使得索引失效。在行锁的情况下，索引失效会导致行锁变为表锁。

    这里varchar类型必须使用''，否则会发生类型转换导致索引失效

    左边where条件b列索引使用的是number类型，原本是varchar类型，导致索引失效

    左边update执行完之后 再去执行右边的update ，右边的update被阻塞了！！

    MySQL锁机制以及主从复制 - 图27

    左侧commit之后，右边立即执行了

    MySQL锁机制以及主从复制 - 图28

    两个都commit之后 修改成功

    MySQL锁机制以及主从复制 - 图29 总结：如果索引失效，InnoDB默认会将行锁变为表锁！

  1. 间隙锁

    1. 什么是间隙锁

      当我们的查询条件是范围条件而不是相等条件的时候，InnoDB会给符合条件的已有数据记录的索引项进行加锁；对于键值条件在范围内但并不存在的记录，就叫做“间隙”。

      InnoDB也同时会对间隙进行加锁，也就是所谓的间隙锁（Next-Key锁）

    2. 危害

      间隙锁锁定某个范围的索引值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定期间，无法插入锁定键值范围内的数任何据。

    3. 演示

      首先设置数据库隔离级别：可重复读

      初始数据 a字段设置有单值索引

      MySQL锁机制以及主从复制 - 图30 左侧更新（where条件使用索引范围），不提交

      右侧insert 右侧会阻塞，即使右侧insert的键是3，在表中并不存在

      MySQL锁机制以及主从复制 - 图31

      左侧commit之后 右侧的update也立即执行了

      MySQL锁机制以及主从复制 - 图32

  1. set GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  1. 间隙锁解决的问题：幻读（隔离级别：可重复读下）
  2. 手动锁定某一行

    当我们需要确定修改某一条数据时，不希望其他人这条具体数据进行操作

    左边两条语句就是指定锁住某一行 并且未提交

    右边去更新锁定的行的时候 会被阻塞

    MySQL锁机制以及主从复制 - 图33

    左边commit ，右边也被执行了

    MySQL锁机制以及主从复制 - 图34 关于for update和for share以及lock in share mode

    for update：排它锁

    for share以及lock in share mode：均为共享锁

    MySQL锁机制以及主从复制 - 图35

行锁总结

  • 查看行锁状况 

    1. # 查看当前系统行锁的状况
    2. show status like 'innodb_row_lock%'

    参数意义

    MySQL锁机制以及主从复制 - 图36

  • 优化建议

    1. 尽可能让所有数据检索都通过索引来完成，避免无索引或者索引失效导致行锁升级为表锁
    2. 合理设计索引，尽量缩小锁的范围
    3. 尽可能减少检索条件，避免间隙锁的出现
    4. 尽量控制事务大小，减少锁定资源量和时间长度

MySQL主从复制

TODO