从一道数据库面试题彻谈MySQL加锁机制

最新推荐文章于 2023-08-05 15:26:13 发布

音视频开发

最新推荐文章于 2023-08-05 15:26:13 发布

阅读量179

点赞数

分类专栏： MySQL数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/ShuShengTuM/article/details/127669820

版权

本文详细探讨了MySQL的加锁机制，包括全局锁、表锁和行锁的类型及应用，介绍了锁的兼容性、查看锁信息的方法，并通过一个面试题分析了不同隔离级别下记录存在与不存在时的加锁情况。

摘要由CSDN通过智能技术生成

有一道关于数据库锁的面试题，发现其实很多 DBA 包括工作好几年的 DBA 都答的不太好，说明 MySQL 锁的机制其实还是比较复杂，值得深入研究。本文对 3 条简单的查询语句加锁情况进行分析，彻底搞清楚加锁细节。

首先来看这个面试题：已知表 t 是 innodb引擘，有主键：id（int 类型) ，下面 3 条语句是否加锁？加锁的话，是什么锁？

1. select _ from t where id=X;

2. begin;select _ from t where id=X;

3. begin;select * from t where id=X for update;

这里其实有很多依赖条件，并不能一开始就给出一个很确定的答复。我们一层层展开来分析。

一、MySQL 有哪些锁？

首先要知道 MySQL 有哪些锁，如

下图所示，至少有 12 类锁（其中自增锁是事务向包含了 AUTO_INCREMENT 列的表中新增数据时会持有，predicate locks for spatial index 为空间索引专用，本文不讨论这 2 类锁）。

锁按粒度可分为分为全局，表级，行级3 类。

1.1 全局锁

对整个数据库实例加锁。加锁表现：数据库处于只读状态，阻塞对数据的所有 DML/DDL；加锁方式：Flush tables with read lock 释放锁：unlock tables(发生异常时会自动释放)；作用场景：全局锁主要用于做数据库实例的逻辑备份，与设置数据库只读命令set global readonly=true相比，全局锁在发生异常时会自动释放。

1.2 表锁

对操作的整张表加锁，锁定颗粒度大，资源消耗少，不会出现死锁，但会导致写入并发度低。具体又分为 3 类： 1）显式表锁：分为共享锁（S）和排他锁（X）显示加锁方式：lock tables ... read/write 释放锁：unlock tables(连接中断也会自动释放)

2）Metadata-Lock（元数据锁）：MySQL5.5 版本开始引入，主要功能是并发条件下，防止 session1 的查询事务未结束的情况下，session2 对表结构进行修改，保护元数据的一致性。在 session1 持有 metadata-lock 的情况下，session2 处于等待状态：show processlist 可见Waiting for table metadata lock。其具体加锁机制如下：</