MySQL全局锁、表级锁、行锁、死锁、索引选择

RenX000

于 2024-05-09 22:38:32 发布

阅读量1k

点赞数 31

分类专栏：自我总结文章标签： mysql 数据库笔记后端

本文链接：https://blog.csdn.net/m0_73976305/article/details/138634905

版权

自我总结专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本文为MySQL45讲 6-10的总结
根据加锁的范围可以分为全局锁、表级锁、行锁

全局锁

定义：对整个数据库实例加锁，让整个库处于只读状态

命令：Flush tables with read lock （FTWRL）

使用场景：做全库逻辑备份

不建议使用set global readonly=true的原因：

在有些系统中readonly会被用为其他逻辑
在异常处理机制上面有差异，FTWRL若客户端异常导致断开，MySQL会自动释放，但是如果用此方法，状态不会改变

存在的问题：当全局备份时，相关业务都会停摆，但是不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。如果开启事务，那么可重复读级别下是可以拿到一致性试图

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁

语法： lock tables … read/write

特点：可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象；是最常用的处理并发的方式

元数据锁 MDL

特点：不需要显示使用，在访问表时会自动加上

作用：保证读写的正确性

在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
事务中的MDL锁，在语句执行开始时申请，等到整个事务提交后再释放。

如何安全的给小表添加字段

1. 理解和监控长事务

在进行任何DDL操作前，我们需要监控并处理可能存在的长事务。长事务会持有元数据锁（MDL），这会阻止DDL操作的执行。您可以通过以下SQL查询来检查长事务：

SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(timediff(now(), trx_started)) > N;

这里的N是你定义的长事务的阈值（例如，60秒）。

2. 使用NOWAIT和WAIT语法

MySQL原生并不支持NOWAIT和WAIT语法。这是MariaDB一个特有的功能，继承自AliSQL。这些选项允许你控制DDL操作的等待行为：

NOWAIT：尝试立即获取锁，如果无法立即获得则操作失败。
WAIT N：等待指定的秒数以获取锁，如果指定时间内无法获取锁，操作也会失败。

示例

如果你正在使用MariaDB，可以这样使用：

ALTER TABLE tbl_name NOWAIT ADD COLUMN new_column INT;

或者：

ALTER TABLE tbl_name WAIT 10 ADD COLUMN new_column INT;

3. 选择合适的时间窗口

尽管NOWAIT和WAIT提供了更多的灵活性，选择执行DDL操作的时间仍然至关重要。选择低峰时段进行操作可以显著减少对业务的影响。

4. 分阶段执行

对于非常大或非常活跃的表，考虑分阶段进行字段添加：

第一阶段：添加字段，但暂不使用新字段。
第二阶段：逐步在应用中实施使用新字段的代码。
第三阶段：在确认新字段稳定运行后，清理或迁移旧数据。

5. 使用在线DDL工具

对于MySQL用户，可以考虑使用诸如pt-online-schema-change这类工具，它可以在不锁表的情况下进行表结构的变更。这对于需要24/7运行的应用尤为重要。

行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这也是MyISAM被InnoDB替代的重要原因之一。

特点：在InnoDB事务中，行锁是在需要的时候才加上的，要等到事务结束时才释放。这个就是两阶段锁协议

使用：将最经常修改的数据放到最后提交，能最大程度减少锁等待

死锁

定义：并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态

解决方法

直接进入等待，直到超时，相关参数为：innodb\_lock\_wait\_timeout 默认值为50s
正常情况下采用：发起死锁检测，主动回滚某一个事务，相关参数：innodb\_deadlock\_detect默认值为on

在秒杀情况下，可以采取如下方法

临时关闭死锁检测，但是带有风险性
控制并发度，使用中间件，或者直接修改源码
将一条记录分为多条，但需要注意临界值处理

普通索引和唯一索引的选择

索引基础

在数据库设计中，普通索引和唯一索引是两种常见的索引类型，它们在应用时会根据业务需求和数据的特性来选择。普通索引允许索引列包含重复的值，而唯一索引则保证索引列的值全局唯一。

业务场景分析

例如，一个市民系统中，每个人的身份证号都是唯一的。如果系统需要通过身份证号查询个人信息，可以在id_card字段上建立索引。这里存在两个选择：

普通索引：如果业务层已经保证了身份证号的唯一性，普通索引可以有效支持查找操作。
唯一索引：额外保证数据的唯一性，防止由于错误或漏洞导致重复数据的插入。

性能考量

查询性能：唯一索引和普通索引在查找数据时的性能差异微乎其微，因为InnoDB存储引擎的数据管理是基于数据页的，即使是普通索引在达到查询条件后的额外检索操作，也仅涉及内存中的简单计算。
更新性能：普通索引可以使用change buffer优化更新操作，尤其是当数据页不在内存中时。唯一索引需要检查唯一性约束，因此不能使用change buffer，可能导致更频繁的磁盘I/O。