MySQL学习之MySQL基本架构与锁

发布时间：2024-03-27 19:23:38 来源：互联网

.

MySql架构SQL LayerConnection Pool : 连接池，用于接收连接请求和管理连接。ManagementService&Utilities

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。

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

这是因为DDL在执行完成后，会自动立刻commit（自动commit后会释放MDL写锁）。

申请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尝试加表锁，这时如果没有意向锁，就需要遍历检测每一行是否持有行锁，这样性能是极低的。

意向锁只和表锁互斥。