关于MySQL-关于锁的知识你都懂了吗

一、前言MySQL 的锁按照范围可以分为全局锁、表锁、行锁，其中行锁是由数据库引擎实现的，并不是所有的引擎都提供行锁，MyISAM 就不支持行锁，所以文章介绍行锁会以InnoDB引擎为例来介绍行锁。二、全局锁MySQL 提供全局锁来对整个数据库实例加锁。语法：FLUSH TABLES WITH READ LOCK这条语句一般都是用来备份的，当执行这条语句后，数据库所有打开的表都会被关闭，并且使用全局读锁锁定数据库的所有表，同时，其他线程的更新语句（增删改），数据定义语句（建表，修改表结构）和更新类的事务提交都会被阻塞。在mysql 8.0 以后，对于备份，mysql可以直接使用备份锁。语句：LOCK INSTANCE FOR BACKUPUNLOCK INSTANCE这个锁的作用范围更广，这个锁会阻止文件的创建，重命名，删除，包括 REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE操作以及账户的管理都会被阻塞。当然这些操作对于内存临时表来说是可以执行的，为什么内存表不受这些限制呢？因为内存表不需要备份，所以也就没必要满足这些条件。三、表锁Mysql的表级别锁分为两类，一类是元数据锁（Metadata Lock，MDL），一种是表锁。元数据锁(MDL)不需要显式使用，在访问一个表的时候会被自动加上。这个特性需要MySQL5.5版本以上才会支持，当对一个表做增删改查的时候，该表会被加MDL读锁；当对表做结构变更的时候，加MDL写锁。MDL锁有一些规则： 读锁之间不互斥，所以可以多线程多同一张表进行增删改查。 读写锁、写锁之间是互斥的，为了保证表结构变更的安全性，所以如果要多线程对同一个表加字段等表结构操作，就会变成串行化，需要进行锁等待。 MDL的写锁优先级比MDL读锁的优先级，但是可以设置max_write_lock_count系统变量来改变这种情况，当写锁请求超过这个变量设置的数后，MDL读锁的优先级会比MDL写锁的优先级高。（默认情况下，这个数字会很大，所以不用担心写锁的优先级下降） MDL的锁释放必须要等到事务结束才会释放所以我们在操作数据库表结构时候必须要注意不要使用长事务，这里具体是什么意思呢？我举个例子说明下：上图表示演示了4个session执行语句，首先SessionA开启了事务没有提交，接着sessionB执行查询，因为是获取MDL读锁，所以互相不影响，可以正常执行，SessionC新增一个字段，由于MDL写和读是互斥的，所以SessionC会被阻塞，之后SessionD开始执行一个查询语句，由于SessionC的阻塞，所以SessionD也阻塞了。所以，我们模拟的SessionA的事务是长事务，然后后面执行了修改表结构，会导致后续对该表所有的读写操作都不可行了。所以在实际场景中，如果业务请求比较频繁的时候，对表结构进行修改的时候就有可能导致该库的线程被阻塞满。表锁的语法如下：LOCK TABLES tbl_name AS alias lock_type , tbl_name AS alias lock_type .lock_type: READ LOCAL | LOW_PRIORITY WRITEUNLOCK TABLES表锁分为读锁和写锁，读锁不互斥，但是获取读锁不能写入数据,其他没有获取到读锁的session也是可以读取表的，所以读锁的目的就是限制表被写。如果表被读锁锁住后，再执行插入语句会报错，报错如下：1099 - Table XXXX was locked with a READ lock and cant be updated写锁被获取后可以对表进行读写，写锁是互斥的，一旦某个session获取到表的写锁，另外的session无法访问这个表，直到写锁被释放。表的解锁可以使用unlock tables解锁，也可以客户端口自动解锁。lock tables锁表会独占式的锁住表，除了限制其他线程对该表的读写，也会限制本线程接下来的操作对象。四、行锁(InnoDB)MySQL的行锁是在引擎层面实现的，所以这里讨论的也是InnoDB引擎下的行锁，下面会详细介绍InnoDB下常见的几种行锁4.1 共享锁共享锁能允许事务获取到锁后进行读操作，共享锁是不互斥的，一个事务获取到共享锁后，另外一个事务也可以获取共享锁，获取共享锁后不能进行写操作。4.2 排它锁排他锁允许事务获取到锁后进行更新一行或者删除某一行操作，排他锁顾名思义是互斥的，一个事务获取到排他锁后，其他事务不能获取到排他锁，直到这个锁被释放。4.3 意向锁InnoDB支持多种粒度的锁，允许行锁和表锁共存，这里说的意向锁其实是一种表级别的锁，但是我把它放在行锁里面是因为它不会单独存在，它的出现肯定会伴随着行锁（共享锁或者排他锁），它主要的目的就是表示将要锁定表中的行或者正在锁定表中的行。意向锁根据和行锁的组合可以分为： 意向排他锁：表明将要在表中的某些行获取排他锁 意向共享锁：表明将要在表中的某些行获取共享锁意向锁的获取必须在行锁获取之前，也就是说获取共享锁之前必须先要获取共享意向锁，对于排他锁也是一样的道理。那么这个意向锁到底有什么作用呢？解释这个之前，我们先看看意向锁和行锁之前的兼容关系：-排他锁（X）意向排他锁（IX)共享锁(S)意向共享锁(IS)排他锁(X）冲突冲突冲突冲突意向排他锁（IX)冲突兼容冲突兼容共享锁(S)冲突冲突兼容兼容意向共享锁(IS)冲突兼容兼容兼容我们假设有2个事务A和事务B，事务获取到了共享锁，锁住了表中的某一行，这一行只能读，不能写，现在事务B要申请整个表的写锁。如果事务B申请成功，那么肯定是可以对表中所有的行进行写操作的，那么肯定与A获取的行锁冲突。数据库为了避免这种冲突，就会进行冲突检测，那么如何去检测呢？有两种方式： 判断表是否已经被其他事务用表级锁锁住。 判断表中的每一行是否被行锁锁住。判断表中的每一行需要遍历所有记录，效率太差，所以数据库就用第一种方式去做冲突检测，也就是用到了意向锁。总结本文主要从MySQL的加锁范围来分析了MySQL的锁，MySQL根据加锁范围可以分为全局锁、表锁、行锁。全局锁和表锁是MySQL自己实现，行锁都是由引擎层面去实现。InnoDB下的行锁主要分为共享锁和排他锁。共享锁请求后，行只能读，共享锁之间不互斥。排他锁