深入 MySQL (五) 锁

数据库 MySQL 数据库

发布时间 : 2021-02-06 22:19

字数:1.3k 阅读 :

评论:

本文我们介绍 MySQL 锁的相关内容。

锁

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。其中全局锁和表锁都是Server层支持的，而行锁是引擎层实现的，InnoDB支持行锁，而MyISAM不支持行锁。

加全局锁办法：flush table with read lock； （解锁使用unlock table;)会使得整个数据库实例处于只读状态。一般用在MySIAM引擎下进行全库逻辑备份，而在InnoDB下一般不采用这种办法，应该使用mysqldump –single-transaction 通过事务来获得一致性视图，而不用加全局锁。
不要使用 set global readonly=true； 来加全局锁。因为readonly值会被用在其他用途，并且当客户端发生异常，readonly值会持久化到数据库上，导致数据库锁不能释放。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MySQL支持表锁，一般使用 lock tables [tablename] read/write 进行加锁。该命令的加锁会对所有的线程生效。
MDL（metadata lock)是另一类表锁，不需要显式使用，在访问一个表的时候会被自动加上。在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。因此，MDL锁作用是防止增删改数据（DML)和加字段等修改表结构的操作（DDL）互相冲突。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。因此，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
行锁的算法可以分为三种：Record Lock（单行记录上的锁）、Gap Lock(间隙锁，锁定一个范围不包含记录本身)和Next-Key Lock(锁定一个范围并包含记录本身)。具体参考：MySQL探秘(七):InnoDB行锁算法
InnoDB通过给索引项加锁来实现行锁，如果没有索引，则通过隐藏的聚簇索引来对记录加锁。如果操作不通过索引条件检索数据，InnoDB 则对表中的所有记录加锁，实际效果就和表锁一样。
当查询的索引是唯一索引(不存在两个数据行具有完全相同的键值)时，InnoDB存储引擎会将Next-Key Lock降级为Record Lock，即只锁住索引本身，而不是范围。如果是范围查询，则边界的Next-Key Lock也有可能不会降级。21 | 为什么我只改一行的语句，锁这么多？
InnoDB对于辅助索引使用Next-Key Lock锁，也就是说不仅会锁住辅助索引值所在的范围以及记录本身，还会将其下一键值加上Gap LOCK。
Next-Key Lock本质是Record Lock + Gap Lock；
Next-Key Lock是前开后闭区间

两个“原则”、两个“优化”和一个“bug”。

InnoDB支持多粒度锁，允许行锁和表锁共存。而意向锁属于一种表锁，意向锁互相之间不会排斥，但意向锁和表级的X锁存在互斥关系。具体参考：详解 MySql InnoDB 中意向锁的作用

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。但注意，如果打开死锁检测，事务执行过程中发生阻塞，都会进行检测，从而CPU性能损耗。
尽管会带来性能损耗，但正常来说都应该开启死锁检测。
减少死锁的主要方向，就是控制访问相同资源的并发事务量。

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 duval1024@gmail.com