MySQL学习笔记（二）—— MySQL基本架构与锁

MySql架构

• SQL Layer
  • Connection Pool : 连接池，用于接收连接请求和管理连接。
  • ManagementService&Utilities 管理服务组件和工具组件，主要提供了一些备份，安全，主从，集群，等功能。
  • SQL Interface：主要提供了SQL语句接口。包括SQL解析器，优化器，缓存等。将我们输入的SQL语句，解析成节点树，然后传递给存储引擎执行。
• Storage Engine Layer：可插拔式的存储引擎。常用的有InnoDB，MyISAM等
• File System : 底层文件系统。保存数据，索引，日志等文件。

MySQL锁

锁的分类

按粒度分

• 全局锁（锁database，由SQL Layer层实现）

• 表级锁（由SQL Layer层实现）

  • 表数据锁
  • 元数据锁

• 行级锁（由存储引擎实现，如InnoDB）:

  可以锁行，也可以锁行与行之间的间隙

按功能分

• 共享锁（S锁）（读锁）
• 排他锁（X锁）（写锁）

锁的演示

表锁

• 表数据锁

  -- 对product表加读锁
  -- 其他进程仍然可以对product表进行读取，但不能写(包括加读锁本身这个进程，也无法写)
  lock table product read;
  -- 加锁后，该进程只能访问product表,无法访问其他表。
  select * from seller;
  ERROR 1100 (HY000): Table 'seller' was not locked with LOCK TABLES
  -- 当然可以让当前进程给seller表加锁
  lock table seller read;
  -- 如此以来，便可以访问seller表，但由于一个进程只能持有一个表锁，故原先的product表锁被释放，product表无法访问
  
  
  -- 其他未持有锁的session可以访问任意表
  
  -- 释放锁
  unlock tables;
  -- 或者
  unlock table; 
  -- 上面两句效果一样
  -- 只会释放当前连接进程所持有的表锁，而不是释放所有锁，
  
  -- 对product表加写锁
  lock table product write;
  -- 其他线程对product表既不能读，也不能写
  
  
  -- 查看表锁状态
  show open tables;
  
  -- 注意，一个连接进程，最多只能持有1个表锁
  

• 元数据锁（Meta Data Lock）

  MDL无需显式使用，在访问一个表时，会自动加元数据锁。MDL的作用是为了保证读写的正确性。

  MDL读锁：在对某个表进行增删改查操作时，加MDL读锁。

  MDL写锁：在对某个表的结构进行修改(DDL)时，加MDL写锁。

  读锁之间不互斥，读写，写写之间互斥。这样是为了保证对表结构操作的安全性。

  MDL可以认为是表结构锁。需要改表结构时，自动加MDL写锁，其他时候加MDL读锁

  DML,DQL语句，会自动加MDL的读锁

  DDL语句，会自动加MDL的写锁

  -- 线程A
  begin;
  select * from product;
  -- 在一个事务内，MDL锁是一直被持有的
  
  -- 此时另起一个，线程B
  alter table product add type varchar(10);
  -- 执行上面的sql，会发现被阻塞住，因为线程B这一句需要MDL写锁
  
  -- 再回到线程A
  commit;
  -- 线程A提交事务后，释放MDL读锁
  -- 此时能看到线程B的sql执行成功
  

  

  注意：如上图所示，session A 和 session B可以正常执行，session C 就被阻塞了，因为session C需要申请MDL写锁，关键是，session D也会被阻塞。当session A 提交后，会先执行session D，最后再执行session C。（这是由于online DDL导致的插队现象，参考：https://blog.csdn.net/q2878948/article/details/96430129）

  观察发现，如果先开启事务，在事务里执行DDL，先不提交当前事务。再另起一个线程，执行DML，发现DML不会被阻塞。

  这是因为DDL在执行完成后，会自动立刻commit（自动commit后会释放MDL写锁）。参考：https://blog.csdn.net/finalkof1983/article/details/88063328

  申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放。

行锁

行锁是由存储引擎实现的。InnoDB支持行锁和事务，MyISAM不支持行锁和事务。

InnoDB的行锁是通过给索引项加锁实现的。所以，若不是通过索引条件检索的数据，InnoDB会使用表锁。

InnoDB的行锁

按照锁定范围分3种：

• Record Lock：记录锁，锁定索引中的一条记录
• Gap Lock：间隙锁，锁定记录间的间隙
• Next-Key Locks：记录锁+间隙锁组合

按功能分为

• 共享读锁
• 排他写锁

DML语句（INSERT/UPDATE/DELETE）会自动加上排他锁

对于普通SELECT语句，InnoDB不加锁（是通过MVCC的一致性非锁定读的方式完成的，这个后序再做总结），可以通过以下方式，手动添加锁

-- 共享读锁
SELECT * FROM product LOCK IN SHARE MODE;

-- 排他写锁
SELECT * FROM product FOR UPDATE;

-- 查看行锁情况
show status like '%innodb_row_lock%';

意向锁

是InnoDB实现的表级锁，在内部使用，无需用户干预。

MySQL有多粒度的锁实现，即行锁和表锁。那么意向锁存在的意义是为了协调行锁和表锁。试想事务A申请了某表某一行的写锁X，事务B申请了该表的写锁X，那么事务B按理说也能修改事务A锁定的某一行，这就产生了冲突。如果没有意向锁，某事务申请表锁时，可能就得一行一行的扫描，看看是不是所有行都没有锁，所有行都没锁时，才能成功加表锁。这样效率就会很低。

所以意向锁的作用就是表明某个事务有加行锁的意图，即，有人锁住了某一行，或者将要锁住某一行，这样在其他人在加表锁时，就能直接根据意向锁的情况，判断是否能够加表锁，而不必一行一行扫描了。

意向共享锁 （IS）：加行共享锁前，必须先取得IS锁

意向排他锁（IX）：加行排他锁前，必须先取得IX锁

意向锁的作用主要是为了在针对全表操作时获得性能提升。

比如：事务A对某一行加了锁（无论是读锁还是写锁），事务B尝试加表锁，这时如果没有意向锁，就需要遍历检测每一行是否持有行锁，这样性能是极低的。

意向锁只和表锁互斥。

对于上表，可以做如下理解：

若某个表存在IS锁，说明有个事务对某一行加了读锁，此时若要对该表加表锁，只能加S锁，不能加X锁。所以IS和S兼容，和X互斥。

若某个表存在IX锁，说明有个事务对某一行加了写锁，此时若要对该表加表锁，都会被阻塞，S锁和X锁都不能加。所以IX和S和X都互斥。

IX和IX可以共存，可以理解为，有2个事务分别对不同的行加了写锁。