MySQL有哪些锁全局锁全局锁怎么使用执行flushtableswithreadlock执行后整个数据库就处于只读状态。其他线程就无法执行对数据的增删改查操作insertdeleteupdate对表结构的更改操作alert tabledrop table释放全局锁执行unlocktables全局锁的应用场景全库逻辑备份在备份数据库期间不会因为数据或表结构的更新造成备份文件的数据于预期不一样。加全局锁带来的缺点造成业务停滞当数据库中数据很多备份就会花费很多时间业务在此时处于只读状态不能更新数据。如何避免如果数据库的引擎事务机制支持可重复读隔离界别在备份数据库之前先开启事务会先创建Read View然后整个事务执行期间都在用Read View由于MVCC的支持备份期间还是可以对数据进行更新。表级锁表级锁的种类表锁元数据锁MDL意向锁AUTO-INC锁表锁对具体某一张表进行加锁如果我们想对学生表t_student加表锁可以使用下面的命令//表级别的共享锁也就是读锁//允许当前会话读取被锁定的表但阻止其他会话对这些表进行写操作。locktablest_studentread;//表级别的独占锁也就是写锁//允许当前会话对表进行读写操作但阻止其他会话对这些表进行任何操作读或写。locktablest_stuentwrite;注意表锁除了会限制别的线程读写外也会限制本线程接下来的读写操作。总结尽量避免在使用innodb引擎的表使用表锁业务表锁的颗粒多太大会影响并发性能innodb有颗粒对度更细的行级锁元数据锁MDLMDL是为了保证当永辉对表执行CRUD操作时防止其他线程对这个表结构做了变更。• 对一张表进行 CRUD 操作时加的是 MDL 读锁• 对一张表做结构变更操作的时候加的是 MDL 写锁MDL不需要显示调用那他是在什么时候释放的MDL时在事务提交后才会释放这说明了事务执行期间MDL时一致持有的那如果数据库有一个长事务所谓的长事务就是开启了事务但是一直还没提交那在对表结构做变更操作的时候可能会发生意想不到的事情比如下面这个顺序的场景首先线程 A 先启用了事务但是一直不提交然后执行一条 select 语句此时就先对该表加上 MDL 读锁然后线程 B 也执行了同样的 select 语句此时并不会阻塞因为「读读」并不冲突接着线程 C 修改了表字段此时由于线程 A 的事务并没有提交也就是 MDL 读锁还在占用着这时线程 C 就无法申请到 MDL 写锁就会被阻塞那么在线程 C 阻塞后后续有对该表的 select 语句就都会被阻塞如果此时有大量该表的 select 语句的请求到来就会有大量的线程被阻塞住这时数据库的线程很快就会爆满了。我为什么线程c因为申请不到MDL写锁而导致后续的申请读锁的查询操作做也会被阻塞答申请MDL锁的操作会形成一个队列队列中写锁获取优先级高于读锁一旦出现MDL写锁等待会阻塞后续该列表的所有CRUD操作。为了能安全的对表结构进行变更在对表结构变更前先要看看数据库中的长事务是否有事务已经对表加上了 MDL 读锁如果可以考虑 kill 掉这个长事务然后再做表结构的变更。意向锁所用为了快速判断表里是否有记录被加锁• 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前需要先在表级别加上一个「意向共享锁」• 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前需要先在表级别加上一个「意向独占锁」也就是当执行插入、更新、删除操作需要先对表加上「意向独占锁」然后对该记录加独占锁。而普通的 select 是不会加行级锁的普通的 select 语句是利用 MVCC 实现一致性读是无锁的。不过select 也是可以对记录加共享锁和独占锁的具体方式如下//先在表上加上意向共享锁然后对读取的记录加共享锁select...lockinsharemode;//先表上加上意向独占锁然后对读取的记录加独占锁select...forupdate;意向共享锁和意向独占锁是表级锁不会和行级的共享锁和独占锁发生冲突而且意向锁之间也不会发生冲突只会和共享表锁lock tables … read和独占表锁lock tables … write发生冲突。表锁和行锁是满足读读共享、读写互斥、写写互斥的。如果没有「意向锁」那么加「独占表锁」时就需要遍历表里所有记录查看是否有记录存在独占锁这样效率会很慢。那么有了「意向锁」由于在对记录加独占锁前先会加上表级别的意向独占锁那么在加「独占表锁」时直接查该表是否有意向独占锁如果有就意味着表里已经有记录被加了独占锁这样就不用去遍历表里的记录。AUTO-INC 锁AUTO-INC 锁是 InnoDB 给自增列分配唯一值的“排队锁”确保多个事务插入时不会重复或冲突。什么是 AUTO-INC 锁AUTO-INC 锁也叫自增锁是 MySQL 为了保证 AUTO_INCREMENT 列在高并发插入时生成连续唯一值而加的一种表级锁。它是 InnoDB 的表级意向锁的一种特殊实现用于序列化对自增列的访问防止并发插入导致自增值冲突。为什么需要AUTO-INC 锁假设有一个表CREATETABLEt1(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(20))ENGINEInnoDB;当多个事务同时执行INSERTINTOt1(name)VALUES(A),(B),(C);MySQL必须保证每一条记录获得唯一的id自增值不会跳号或重复为了实现innodb会对表加AUTO-INC 锁让同一时刻只有一个线程分配自增值行级锁InnoDB 引擎是支持行级锁的而 MyISAM 引擎并不支持行级锁。普通的select语句是不会对记录加锁的因为它属于快照读。如果要在查询时对记录加行锁可以使用下面这两个方式这种查询会加锁的语句称为锁定读。//对读取的记录加共享锁select...lockinsharemode;//对读取的记录加独占锁select...forupdate;上面这两条语句必须在一个事务中因为当事务提交了锁就会被释放所以在使用这两条语句的时候要加上 begin、start transaction 或者 set autocommit 0。共享锁S锁满足读读共享读写互斥。独占锁X锁满足写写互斥、读写互斥。行级锁主要有三类Recode Lock:记录所也就是仅仅把一条记录锁上Gap Lock间隙锁锁定一个范围但不包含记录本身Next-key Lock:Record Lock Gap Lock的组合锁定一个范围并锁定记录本身Gap Lock间隙锁存在于可重复读与串行化隔离级别目的是为了解决可重复读隔离级别下幻读的现象。假设表中有一个范围 id 为35间隙锁那么其他事务就无法插入 id 4 这条记录了这样就有效的防止幻读现象的发生。gap lock页存在x型/s型间隙锁但没什么区别。间隙锁之间是兼容的两个事务可以同时持有包含共同间隙范围的间隙锁并不存在互斥关系因为间隙锁的目的是防止插入幻影记录而提出的Next-Key Lock假设表中有一个范围 id 为35] 的 next-key lock那么其他事务即不能插入 id 4 记录也不能修改 id 5 这条记录。所以next-key lock 即能保护该记录又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。next-key lock 是包含间隙锁记录锁的如果一个事务获取了 X 型的 next-key lock那么另外一个事务在获取相同范围的 X 型的 next-key lock 时是会被阻塞的。