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

墨蓝 2023-05-28 14:13 29阅读 0赞

### MySql架构 ###

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5JSLokUB-1584870601970)(C:\\Users\\Vergi\\AppData\\Roaming\\Typora\\typora-user-images\\1583037326866.png)\]][img-5JSLokUB-1584870601970_C_Users_Vergi_AppData_Roaming_Typora_typora-user-images_1583037326866.png]

*  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执行成功
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70]
    
    注意：如上图所示，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][https_blog.csdn.net_q2878948_article_details_96430129]）
    
    观察发现，如果先开启事务，在事务里执行DDL，先不提交当前事务。再另起一个线程，执行DML，发现DML不会被阻塞。
    
    这是因为DDL在执行完成后，会自动立刻commit（自动commit后会释放MDL写锁）。参考：[https://blog.csdn.net/finalkof1983/article/details/88063328][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%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70 1]

##### 意向锁 #####

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

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

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

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

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

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

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

意向锁只和表锁互斥。

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QL3Cb7Qe-1584870601982)(C:\\Users\\Vergi\\AppData\\Roaming\\Typora\\typora-user-images\\1583597865914.png)\]][img-QL3Cb7Qe-1584870601982_C_Users_Vergi_AppData_Roaming_Typora_typora-user-images_1583597865914.png]

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

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

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

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

[img-5JSLokUB-1584870601970_C_Users_Vergi_AppData_Roaming_Typora_typora-user-images_1583037326866.png]: /images/20230528/c85ba70fb07341679bab1c949f84a6a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70]: /images/20230528/c2145c1724b640c1b6a2442f38b51682.png
[https_blog.csdn.net_q2878948_article_details_96430129]: https://blog.csdn.net/q2878948/article/details/96430129
[https_blog.csdn.net_finalkof1983_article_details_88063328]: https://blog.csdn.net/finalkof1983/article/details/88063328
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZjajEwMDk3ODQ4MTQ_size_16_color_FFFFFF_t_70 1]: /images/20230528/2ddae155c7ac47fc944234861c989d57.png
[img-QL3Cb7Qe-1584870601982_C_Users_Vergi_AppData_Roaming_Typora_typora-user-images_1583597865914.png]: /images/20230528/b643d9d6e94847aab2f5a1831df5fae3.png