mysql的锁

mysql的锁

• 由锁来实现事务的隔离性，同时锁机制也为实现MySQL 的各个隔离级别提供了保证。 锁冲突也是影响数据库并发访问性能 的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。

从锁的类型可以划分为：

• 读锁和写锁：

• 读锁是共享锁，英文用 S 表示，多个用户在同一时刻可以读取同一资源，相互不受干扰，也可以手动给查询语句增加写锁或者读锁，但是不建议这么做

• 写锁是排他锁，英文用 X 表示，写锁会阻塞其他的写锁和读锁，这样可以确保在指定的时间内，只有一个用户可以写入

  • insert
    • 一般情况下，新插入数据并不需要加锁，通过一种隐式锁结构来保护这样条数据在事务提交之前不被其他事务访问
  • update
    • 情况一：没有修改记录的键值，并且被更新的列占用的存储空间在修改前后未发生变化
      • 先在b+树中找到数据的位置，然后获取写锁，最后在原位置进行操作
    • 情况二：没有修改记录的键值，但有被更新的列占用的存储空间在修改前后发生变化，
      • 先在b+树中找到数据的位置，然后获取写锁，将该记录彻底删除，也就是移动到垃圾链表，最后在插入一条新的记录，新插入的记录由insert操作提供隐式锁进行保护
    • 情况三：修改了记录的键值，则相当于在原记录上做delete操作，在进行一次insert操作
  • delete
    • 删除的时候，需要现在b+树中找到这条记录的位置，然后获取写锁，在把 delete mark改为1
    • 其实执行delete的时候，数据并没有被真正的删除，只是对应数据的删除标识 deleteMark，这样每次执行查询的时候，如果发现数据存在但是deleteMark是开启的话，那么依然返回空，
    • 不直接删除，而是打个标记的原因是：
      • 如果有事务通过undo log 回滚，真正删除会导致找不到数据
      • 还会破坏可重复读
    • mysql里面有个purge线程，它的工作中有一项任务就是专门检查这些有deleteMark的数据，当有deleteMark的数据如果没有被其他事务引用时，那么会被标记成可复用，因为叶子节点数据是有序的原因，这样当下次有同样位置的数据插入时，可以直接复用这块磁盘空间。当整个页都可以复用的时候，也不会把它还回去，会把可复用的页留下来，当下次需要新页时可以直接使用，从而减少频繁的页申请。
    • 但是如果一直没有被复用，而且删除的数据很多，就会造成页空间有大量碎片，为了解决这种情况，mysql提出了页合并功能，也就是如果相邻的两个页都有很多可以复用的空间，就会把页合并，合并功能受MERGR_THRESHOLD影响，默认值为百分之50，因为合并就意味着会发生数据移动，所以必须在两个页都有大量碎片空间时进行
    • 不管是页的合并还是页的分裂，都是相对耗时的操作，除了移动数据的开销外，InnoDB也会在索引树上加锁。

页分裂会导致页的利用率降低，造成页分裂的原因有很多，比如：

• 比如离散的插入，导致数据不连续。
• 把记录更新成一个更大记录，导致空间不够用

mysql的锁的颗粒度：

• 想要提高并发性，就需要尽可能的只锁住需要修改的资源，而不是所有资源，同时加锁也需要消耗资源，锁的各种操作都会增加系统的开销，影响系统的性能，所以锁的策略就是在锁的开销和数据安全性之间寻找平衡

• MySQL提供了多种选择，每种存储引擎都可以实现自己的锁策略和锁粒度，针对不同场景有不同的解决方案

  • 表锁

    • 表锁是mysql最基本的锁策略，它的开销最小，但他会锁定整张表，想要进行写操作，必须先获取写锁，这会阻塞其他用户对表的读写操作，能够避免死锁问题，但是并发度也是最差的
    • 虽然存储引擎有自己的锁策略，但是例如 Alter table 这种操作仍然是表锁，会忽略存储引擎的锁机制

  • 页级锁

    • 页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。
    • 使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销 介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

  • 行级锁

    • 行级锁可以最大程度的支持并发处理，但同时也带来了最大的锁开销
    • 行级锁只在存储引擎层实现，mysql服务层没有实现

  • 锁升级

    • 每个层级的锁数量是有限的，因为锁会占用内存空间，而锁空间的大小是有限的， 当某个层级的锁数量超过了阈值，就会进行锁升级
    • 锁升级也就是用更大粒度的锁来代替小粒度的锁，从而减少锁的数量，好处是锁占用空间降低了，但是并发性也会降低

写锁：

• 当一个事务想对这条记录做改动时，首先会看看内存中有没有与这条记录关联的锁结构 ，当没有的时候就会在内存中生成一个锁结构 与之关联。
• 获取锁失败，或者加锁失败，或者没有获取到锁，意思就是在内存中生成了对应的 锁结构 ，不过锁结构的 is_waiting 属性为 true ，也就是事务 需要等待，不可以继续执行操作。
• 事务提交后，会把生成的锁释放掉，然后看有没有其他事务在等待获取锁，如果有就把对应事务的is_waiting改为false，然后把该事务的线程唤起，使事务串行化执行
• 锁结构包含：
  • 事务id
  • is_waiting，也就是事务 可以继续执行操作。

表级锁：

• 表级别的读写锁

  • innodb
    • 一般情况下，不会使用InnoDB存储引擎提供的表锁，只会在一些特殊情况下，比方说崩溃恢复过程中用到。
    • 同时需要尽量避免在InnoDB存储引擎的表上使用 LOCK TABLES 这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力
  • myisam
    • 表共享读锁（Table Read Lock）
    • 表独占写锁（Table Write Lock）

• 意向锁

  • InnoDB 支持多粒度锁 ，它允许行级锁与表级锁共存，意向锁就是其中的一种表锁
  • 意向锁就是为了协调行锁和表锁的关系，意向锁是一种不与行级锁冲突的表级锁，
  • 意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁 互斥 。
  • 意向锁是表级锁，不会和行级的读写锁发生冲突。只会和表级的读写锁发生冲突。
  • 意向锁在保证并发性的前提下，实现了 行锁和表锁共存 且 满足事务隔离性 的要求。
  • 意向锁是由存储引擎 自己维护的 ，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前， InooDB 会先获取该数据行所在数据表的对应意向锁 。
  • 意向锁解决的问题：
    • 如果给某行数据加上了排他锁，数据库会自动给更大一级空间（例如页或表）增加意向锁，告诉其他事务这个页或表已经被加了排他锁，表明某个事务在某些行持有了锁或该事务准备去持有锁
    • 这样其他事务想要获取这个表或页的排他锁时，只需要了解是否有其他事务获取了这个表的意向排他锁即可，不需要去一条条的访问数据看是否有行存在排他锁
  • 意向锁分为两种：
    • 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁，如果想要获取数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁
    • 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁，如果想要获取数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁

• 自增锁

  • 当向含有 AUTO_INCREMENT 列的表插入数据时，需要获取的一种特殊表级锁 AUTO-INC 锁，然后为每条待插入记录的自增列分配递增的值，在语句执行结束后再把锁释放掉

  • 一个事务在持有AUTO-INC 锁的过程中，其他事务插入语句都会被阻塞，可以保证自增分配的值都是连续的，所以并发性不高，如果是自增主键，每条插入语句都需要竞争

  • 可以通过调整 innodb_autoinc_lock_mode 的取值进行优化

    • 等于0，就是传统的锁定模式，所有的insert语句都会获取特殊表级锁 AUTO-INC 锁，用于插入具有自增列的表，因为是表级锁，会限制并发能力
    • 等于1，连续锁定模式，是8.0以前默认模式，
      • 对于简单的插入，因为事先已经知道了要插入的行数，通过 mutx（轻量锁）控制所需数量的自增值来避免 AUTO-INC 锁表，除非AUTO-INC由另一个事务保持，就需要等待另一个事务释放AUTO-INC，
      • 对于批量插入和混合模式插入仍然需要持有AUTO-INC
    • 等于 2，交错锁模式，是8.0后默认的模式，
      • 所有的insert都不会使用 AUTO-INC ，并且可以同时执行多个语句，
      • 但是如果是基于语句的复制，是不安全的
      • 对于简单插入可以保证插入值的连续，其他类型的插入分配的子增值可能会有间隙

  • 所有插入数据的方式总共分为三类， 分别是：

    • Simple inserts （ 简单插入）
      • 可以 预先确定要插入的行数 （当语句被初始处理时）的语句。包括没有嵌套子查询的单行和多行 INSERT…VALUES() 和 REPLACE 语句。比如我们上面举的例子就属于该类插入，已经确定要插入的行 数。
    • Bulk inserts （批量插入）
      • 事先不知道要插入的行数（和所需自动递增值的数量）的语句。比如 INSERT … SELECT 等，但不包括纯INSERT。 InnoDB在每处理一行，为AUTO_INCREMENT列分配一个新值。
    • Mixed-mode inserts（混合模式插入）
      • 这些是“Simple inserts”语句但是指定部分新行的自动递增值。例如 INSERT INTO teacher (id,name) VALUES (1,‘a’), (NULL,‘b’), (5,‘c’), (NULL,‘d’); 只是指定了部分id的值。

• 元数据锁

  • MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴，不需要显示的添加
  • MDL 的作用是，保证读写的正确性。
  • 如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更 ，增加了一 列，那么查询线程拿到的结果跟表结构对不上。
  • 因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写 锁。
  • 所以 Alter table 这种操作仍然是表锁

	意向共享锁（ls）	意向排他锁（lx）
共享锁（s）	兼容	互斥
排他锁（x）	互斥	互斥
意向共享锁（ls）	兼容	兼容
意向排他锁 （lx）	兼容	兼容

行级锁：

• 行级锁可以最大程度的支持并发处理，但同时也带来了最大的锁开销，行级锁只在存储引擎层实现，mysql服务层没有实现
• 记录锁
  • 记录锁也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP
  • 记录锁是有S锁和X锁之分的，称之为 S型记录锁 和 X型记录锁 。
  • 当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；
  • 当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁
• 间隙锁（Gap Locks）
  • MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方 案解决，也可以采用 加锁方案解决。
  • 但是无法给不存在的记录加锁，所以InnoDB提出了一种称之为 Gap Locks 的锁，简称为 gap锁 ，也就是锁定数据的间隙，防止在事务过程中有数据插入，出现幻读
  • 间隙锁有可能导致死锁
    • 例如A、B两个事务都持有间隙锁，还都向间隙里插入数据，A需要等B结束才能继续执行,B同样需要等A结束才能继续执行，就会导致相互等待
    • 一旦出现死锁，要么等到事务超时，要么会回滚排他锁最少的事务
• 临键锁
  • 既想锁住某条记录 ，又想阻止其他事务在该记录前边的间隙插入新记录 ，InnoDB就提出了一种称之为 Next-Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY ，简称为 next-key锁 。
  • Next-Key Locks是在存储引擎 innodb 、事务级别在可重复读的情况下使用的数据库锁，也就是默认使用Next-Key locks。
  • 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。
  • 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
  • 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。
• 插入意向锁
  • 一个事务在 插入一条记录时需要判断一下插入位置是不是被别的事务加了间隙锁（ next-key锁 也包含 gap锁 ），
  • 如果有的话，插入操作需要等待，直到拥有gap锁的那个事务提交。
  • 但是InnoDB规 定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。官方的类型名称为： LOCK_INSERT_INTENTION ，也称为插入意向锁 。
  • 插入意向锁是一种 Gap锁 ，不是意向锁，在insert 操作时产生。 插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁 。
  • 事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

从对待锁的态度划分:

• 从对待锁的态度来看锁的话，可以将锁分成乐观锁和悲观锁，乐观锁和悲观锁并不是锁，而是锁的设计思想

• 悲观锁

  • 悲观锁是一种思想，顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身 的锁机制来实现，从而保证数据操作的排它性
  • 悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞， 用完后再把资源转让给其它线程）。
  • 行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当 其他线程想要访问数据时，都需要阻塞挂起
  • 悲观锁适合写操作多的场景，因为写的操作具有排它性 。采用悲观锁的方式，可以在数据库层 面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突，但是会降低并发性
  • 此外sql语句在执行过程中所有被扫描的行都会上锁，因此在mysql使用悲观锁必须确定使用了索引，如果是全表扫描，就会锁住整张表

• 乐观锁

  • 乐观锁（Optimistic Locking） 乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新 的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。
  • 可以采用版本号机制 或者 CAS机制 实现。乐观锁适用于多读的应用类型， 这样可以提高吞吐量
    • 版本号：给表数据设置一个版本号的字段，每次修改前获取版本号，修改后再去比较版本号，如果版本号相同操作成功，否则重试操作
    • 时间戳和版本号类似
  • 乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现 ， 不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作

按加锁的方式划分：

• 隐式锁

  • 为了防止新增的数据在没有提交前，需要加隐式锁，防止被其他事务读取
    • 聚簇索引有一个隐藏例trx_id，记录了最后改动该事务的id；当A事务插入一条新数据后，trx_id就是A，如果此时B事务想要访问这行数据，就要对这个数据加写锁或者读锁，会首先查看trx_id，如果A事务仍然活跃，就会帮助A创建一个写锁，然后B事务会进入等待状态，把自己的is_waiting 改为true
    • 对于非聚簇索引，并没有trx_id，但是在页上有一个 page_max_trx_id 属性，也就是对该页做改动的最大事务id，如果 page_max_trx_id 小于当前事务id，说明对该页面做的修改都提交了，否则就需要定位到二级索引的记录，回表找到聚簇索引的记录，重复聚簇索引上的操作
  • 所以隐式锁其实是一种延迟加载的锁，只有其他事务访问正在插入的数据，才会被加锁

• 显式锁：能通过特定语句进行加锁的，都是显示锁

  显式共享锁： sql + lock in share mode
  显式排他锁： sql + for update
  

全局锁：

• 就是对整个数据库实例 加锁。当你需要让整个库处于只读状态 的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结 构等）和更新类事务的提交语句。
• 全局锁的典型使用场景是：做 全库逻辑备份，全局锁的命令： Flush tables with read lock

mysql的死锁：

• 当多个事务试图以不同的顺序锁定资源时，就有可能产生死锁，多个事务同时锁定一个资源时也有可能产生死锁

• 例如A事务先更新第一行数据，在更新第二行数据，B事务先更新第二行数据，在更新第一行数据，这样就会导致两个事务都在等待对方释放锁，同时又持有对方需要的锁，陷入死循环

• 事务的最终状态一定是回滚或者提交，如果出现死锁，一定需要解决，为了解决这种问题，数据库实现了各种死锁检查和超时机制

  • 方式一：当两个事务相互等待时，其中一个事务达到超时阈值时（默认50s）,就会将其回滚，另外一个事务就可以正常执行

  • 方式二：进行死锁检测，主动的进行死锁检测，通过事务等待链表和锁的信息链表，构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在就说明有死锁，这个术后就需要回滚undo量最小的事务，但是这种操作如果并发量很高，检测的成本也会非常高，所以需要控制并发量

  • innodb目前处理死锁的方法是，将持有最少行级排他锁的事务回滚

• 死锁一旦发生，只有部分或者完全回滚其中一个事务，才能打破死锁，对于事务型的系统，这是无法避免的，所以需要在程序设计时考虑如何避免死锁

  • 合理设计索引，通过索引来定位更少的行，来减少锁的竞争
  • 调整sql顺序，避免长时间的更新删除操作在事务的前面
  • 避免大事务，把大事务拆成多个小事务，冲突的几率也更小
  • 尽量不要加显示锁
  • 所过允许，可以降低隔离级别

锁的内存结构：

• 如果每访问一行，就要加一个锁，这是极大的浪费，所以如果在同一个事务中进行加锁操作，或者被加锁的记录在同一个页面中，或者加锁的类型是一样的，或者等待状态是一样的，会把这些锁放在同一个锁结构中
• 锁结构包括：
  • 锁所在的事务信息：不管是表锁还是行锁，都是在事务执行过程中生成的，在内存中锁所在的事务信息只是一个指针，可以通过指针获取详细的事务信息
  • 索引的信息：记录了加锁的行属于哪一个索引，同样只是一个指针
  • 表锁或者行锁的信息：表锁和行锁在这里的内容是不同的
    • 表锁记录了对哪个表进行了加锁
    • 行锁记录了 所在的表空间、记录所在的页号、使用了多少bit位
  • type_mode：这是一个32位的数，被分成了 lock_mode 、 lock_type 和 rec_lock_type 三个部分，
    • 锁的模式（ lock_mode ），占用低4位，可选的值如：共享意向锁、独占意向锁、共享锁、独占锁等
    • 锁的类型（ lock_type ），占用第5～8位，不过现阶段只有第5位和第6位被使用，当第5个比特位置为1时，表示表级锁，当第6个比特位置为1时，表示行级锁。
    • 行锁的具体类型（ rec_lock_type ），使用其余的位来表示。只有在 lock_type 为行级锁时，才会被细分为更多的类型，例如： next-key锁 、gap锁、记录锁 、插入意向锁等
  • 其他信息 ： 为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表。
  • 一堆bit位：在行锁中，一条记录就是一个bit位，一个页面包含了很多记录，用不同的bit位来记录到底那一条加了锁