MySQL使用表锁和行锁的场景详解
MySQL Innodb 的锁可以说是执行引擎的并发基础了,有了锁才能保证数据的一致性。众所周知,我们都知道 Innodb 有全局锁、表级锁、行级锁三种,但你知道什么时候会用表锁,什么时候会用行锁吗?
虽然对 MySQL 的知识点挺熟悉的,但一开始看到这个问题,树哥也是有点懵,我还真没从这个角度去思考过。大家可以暂时 1 分钟思考下答案,后面我将带大家弄清楚这个问题。
对于这个问题,我只能粗略地想起一些片段,例如:
对于表级锁而言,当执行 DDL 语句去修改表结构时,会使用表级锁。 对于行级锁而言,一般情况下都会默认使用行级锁,貌似是需要有索引匹配到才行。上面就是我粗略想到的答案,不知道大家思考的答案是否和我一样呢?下面就让我带着大家来温习下 MySQL 的锁吧!
文章思维导图
对于数据库而言,其锁范围可以分为:
全局锁就是对整个数据库实例加锁。 MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。你可以理解为,全局锁基本上把数据所所有的变更语句都锁住了。
全局锁的典型场景应用场景是全库逻辑备份,也就是把整个库每个表都 select 出来存起来。上面说到全局锁会锁住所有变更语句,但这只是对于 MyISAM 存储引擎而言的。对于 Innodb 而言,其可以利用 MVCC 实现数据的一致性视图,从而不需要锁整个库就可以实现全库的数据备份。
表级锁可以分为:表锁、元数据锁、意向锁三种。
表锁,顾名思义就是对某个表加锁。
那什么时候会使用表锁呢?
一般情况是对应的存储引擎没有行级锁(例如:MyIASM),或者是对应的 SQL 语句没有匹配到索引。
对于第一种情况而言,因为对应存储引擎不支持行锁,所以只能是使用更粗粒度的锁来实现,这也比较好理解。
对于第二种情况而言,如果存储引擎支持行锁,但对应的 SQL 就没有使用索引,那么此时也是会全表扫描,那此时也是会使用表锁。例如下面的语句没有指定查询列,或者指定了查询列但是并没有用到索引,那么也是会直接锁定整个表。
// 没有指定查询列select * from user;
// 指定查询列,但是没有用到索引
select * from user where name = zhangsan
上面说的索引,可以说是判断是否会用行级锁的关键。但我想到一个问题:如果查询或更新用到了索引,但是查询或更新的数据特别多,占全表的 80% 甚至更多,这时候是会用表锁,还是行锁呢? 这是一个很有意思的问题,感兴趣的朋友自行弄个测试表验证一下,后续有机会我们再聊聊这个问题。
元数据,指的是我们的表结构这些元数据。元数据锁(Metadata Lock)自然是执行 DDL 表结构变更语句时,我们对表加上的一个锁了。
那什么时候会使用元数据锁这个表级锁呢?
当我们对一个表做增删改查操作的时候,会加上 MDL 读锁;当我们要对表结构做变更时,就会加 MDL 写锁。
意向锁,本质上就是空间换时间的产物,是为了提高行锁效率的一个东西。
在 InnoDB 中,我们对某条记录进行锁定时,为了提高并发度,通常都只是锁定这一行记录,而不是锁定整个表。而当我们需要为整个表加 X 锁的时候,我们就需要遍历整个表的记录,如果每条记录都没有被加锁,才可以给整个表加 X 锁。而这个遍历过程就很费时间,这时候就有了意向锁的诞生。
意向锁其实就是标记这个表有没有被锁,如果有某条记录被锁住了,那么就必须获取该表的意向锁。所以当我们需要判断这个表的记录有没有被加锁时,直接判断意向锁就可以了,减少了遍历的时间,提高了效率,是典型的用空间换时间的做法。
那么什么时候会用到意向锁呢?
很简单,就是在对表中的行记录加锁的时候,就会用到意向锁。
千呼万唤,终于来到了行级锁。
要知道的是,行级锁是存储引擎级别的锁,需要存储引擎支持才有效。目前 MyISAM 存储引擎不支持行级锁,而 Innodb 存储引擎则支持行级锁。而全局锁、表级锁,则是 MySQL 层面就支持的锁。
那么什么时候会使用行级锁呢?
当增删改查匹配到索引时,Innodb 会使用行级锁。
如果没有匹配不到索引,那么就会直接使用表级锁。
文章最后,我们回顾一下开头提出的问题:Innodb 啥时候用表锁,啥时候用行锁?
表级锁包括:表锁、元数据锁、意向锁。
对于表锁而言,当存储引擎不支持行级锁时,使用表锁。SQL 语句没有匹配到索引时,使用表锁。
对于元数据锁而言,对表做增删改查时,会加上 MDL 读锁。对表结构做变更时,会加上 MDL 写锁。
对于意向锁而言,对表中的行记录加锁时,会用到意向锁。
而对于行级锁而言,增删改查匹配到索引时,会使用行级锁。
文章思维导图
本篇文章到此结束,如果您有相关技术方面疑问可以联系我们技术人员远程解决,感谢大家支持本站!
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 MySQL使用表锁和行锁的场景详解
相关文章
- MySQL管理电话号码的最佳实践(mysql电话号码)
- 关于MySQL中AND和OR的使用(mysql中andor)
- MySQL数据类型深度剖析(mysql数据类型详解)
- MySQL 服务重启命令指南(mysql重启服务的命令)
- MySQL中如何删除外键字段(mysql删除外键字段)
- Mysql安装成功后如何使用(mysql安装完怎么用)
- MySQL修改数据列长度的技巧(mysql修改字段长度)
- MySQL杀死顽固的进程(mysql杀进程)
- Yum更新MySQL,King完美操作升级!(yum升级mysql)
- 如何使用HTML5连接MySQL数据库:同步数据更加高效和安全。(html5连接mysql数据库)
- MySQL读写优化:提高性能的可能性(mysql读写优化)
- 文件MySQL 使用之禅 利用HTML文件实现完美保存(mysql保存html)
- MySQL中Bit类型的使用及优势(mysql中的bit类型)
- 使用MySQL生成ER图,轻松设计数据库结构(mysql生成er图)
- MySQL:掌握索引名长度的要点(mysql 索引名长度)
- MySQL连接符的使用方法和注意事项(mysql连接符)
- MySQL中使用NOWAIT关键字避免锁等待问题(mysqlnowait)
- MySQL测试数据库:简单快捷的稳定性分析(mysql 测试数据库)
- MySQL连接之旅:如何掌握字符串连接技巧(mysql 字符连接)
- C MySQL函数使用中的问题及解决方案(c mysql函数的问题)
- MySQL数据库简介及其重要性(mysql ・・)
- MySQL中的IF函数 – 数据库中条件判断使用技巧(mysql z中的if)
- MySQL查询必备不包含语句的使用技巧(mysql 不包含查询)
- MySQL下载需要多长时间(mysql下载要多久)
- MySQL中MyISAM和InnoDB引擎的区别(mysql 两种引擎区别)
- 如何在Windows中使用MySQL而不必安装服务(mysql不装服务)
- 为什么MySQL不推荐使用枚举(mysql不推荐使用枚举)