您的位置：首页 > 数据库 > mysql  >  深入理解mysql各种锁

深入理解mysql各种锁

 更新时间：2021年12月7日 09:48  点击：382 作者：奔跑的蜗牛...

锁的概述

锁是计算机协调多个进程或线程并访问某一资源的机制

在数据库中，除传统的计算机资源（如cpu、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源，如果保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂

锁分类

对数据库操作的粒度分

表锁：
	操作时，会锁定整个表
行锁：
	操作时，会锁定当前操作行

对数据操作的类型分

读锁（共享锁）：
	针对同一份数据，多个读操作可以同时进行而不会相互影响
写锁（排它锁）：
	当操作没有完成之前，它会阻断其他写锁和读锁

mysql锁

相对其他数据库而言，Mysql的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

不同存储引擎支持锁级别

存储引擎 表级锁 行级锁 页面锁
MyISAM 支持 不支持 不支持
InnoDB 支持 支持 不支持
MEMORY 支持 不支持 不支持
BDB 支持 不支持 支持

锁介绍

很难笼统说哪种锁更好，需要根据特定的应用场景来分析哪种锁更合适。

锁类型 特点
表级锁 偏向MyISAM存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发送锁冲突的概率最高，并发度最低。
行级锁 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁 开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

MyISAM表锁

如何添加表锁

MyISAM在执行查询语句（select）前，会自动给涉的所有表加锁，在执行更新操作（update、delete、insert等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接使用LOCK TABLE命令给MyISAM表显示加锁

加解锁

加读锁：
	lock table table_name read; 
	---解锁 unlock tables;
加写锁：
	lock table table_name write;
	添加写锁当前会话可以读写操作，别的会话会处于阻塞（等待）状态
读锁限制写，写锁限制读写

锁竞争

1、对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
2、对MyISAM的写操作，则会阻塞其他用户对表一表的读和写操作；
3、MyISAM锁调度是写优先。
	不适合作为主表，写锁后其他线程大量的更新会使查询很难得到锁，可能会造成永远阻塞。

锁的使用情况

查看锁的使用情况
	show open tables;
查看表的锁定情况
	show status like 'table_locks%';
		Table_locks_immediate:可以获取表级锁的次数，每立即获取锁，值加1
		Table_locks_waited:不能立即获取锁需要等待的次数，每等待一次，值加1，可以判断比较严重的表级锁争用情况

InnoDB锁

InnoDB与MyISAM的最大不同有亮点：一是支持事务；二是采用行级锁

行锁

共享锁：
	又称读锁，多个事务可以共享一把锁，但是只能读，不能写
排它锁：
	又称写锁，锁不共用，获取不到锁的事务不能进行读写操作
如果进行update、delete和insert 语句，innoDB会自动给涉及数据集加排它锁
对不同的select语句不会添加任何锁
显示的给查询添加锁
	添加共享锁：
		select * from table_name where ... Lock IN SHARE MODE
	添加排它锁：
		select * from table_name where ... FOR UPDATE

锁升级

索引失效，行锁升级表锁
where后面没索引也升级为表锁

间隙锁

InnoDB会对间隙不存在的数据也会加锁，称之为间隙锁

锁争用

show status like 'innodb_row_lock%';
	Innodb_row_lock_current_waits当前正在等待锁的数量
	Innodb_row_lock_time 锁定的总时长
	Innodb_row_lock_time_avg 锁定的平均时长
	Innodb_row_lock_time_max 锁定的最大时长
	Innodb_row_lock_waits 系统启动到现在总共等待次数

总结

innoDB存储引擎实现了行锁，虽然锁定机制的实现方面带来了性能消耗可能比较表锁会更高些，但是在整体并发处理能力方面要远优于MyISAM表锁，当系统并发比较高的时候，InnoDB的整体性能和MyISAM会有比较明显的优势

优化建议

尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少索引条件，及索引范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间的长度
尽可能使用低级别事务隔离（需要业务能满足需求）

到此这篇关于深入理解mysql各种锁的文章就介绍到这了,更多相关mysql锁内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞！

原文出处：https://blog.csdn.net/xiaomengxin521/article/details/1217195

[!--infotagslink--]

相关文章

  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14

  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持，可以创建 JSON 类型的字段，并有一套函数支持对JSON的查询、修改等操作，下面就实际体验一下...2016-08-23

  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢？commen sence指的是那些大家都应该知道的事情，但往往大家又会会略这些东西，或者对这些东西一知半解，今天我总结下自己在mysql中遇到的一些commen sense类型的问题。　...2015-03-15

  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取)，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22

  • mysql的3种分表方案

    一、先说一下为什么要分表：当一张的数据达到几百万时，你查询一次所花的时间会变多，如果有联合查询的话，有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。根据个人经验，mysql执行一个sql的过程如下：1...2014-05-31

  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时，总免不了会遇到这个问题：插入中文字符出现乱码，虽然这是运维先给配好的环境，但是在自己机子上玩的时候咧，总得知道个一二吧，不然以后如何优雅的吹牛B。...2015-03-15

  • Centos5.5中安装Mysql5.5过程分享

    这几天在centos下装mysql，这里记录一下安装的过程，方便以后查阅Mysql5.5.37安装需要cmake，5.6版本开始都需要cmake来编译，5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15

  • 用VirtualBox构建MySQL测试环境

    宿主机使用网线的时候，客户机在Bridged Adapter模式下，使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候，客户机在Bridged Adapter模式下，使用可选项里唯一一个WIFI选项，Microsoft Virtual Wifi Miniport Adapter也无法上网，故弃之。...2013-09-19

  • 忘记MYSQL密码的6种常用解决方法总结

    首先要声明一点，大部分情况下，修改MySQL密码是需要有mysql里的root权限的...2013-09-11

  • MySQL数据库备份还原方法

    MySQL命令行导出数据库： 1，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录 如我输入的命令行：cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26

  • Mysql命令大全(详细篇)

    一、连接Mysql格式： mysql -h主机地址 -u用户名 －p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口，然后进入目录mysql/bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密...2015-11-08

  • Navicat for MySQL 11注册码\激活码汇总

    Navicat for MySQL注册码用来激活 Navicat for MySQL 软件，只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23

  • mysql IS NULL使用索引案例讲解

    这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14

  • 基于PostgreSQL和mysql数据类型对比兼容

    这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25

  • Mysql中 show table status 获取表信息的方法

    这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12

  • 20分钟MySQL基础入门

    这篇文章主要为大家分享了20分钟MySQL基础入门教程，快速掌握MySQL基础知识，真正了解MySQL，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2016-12-02

  • RHEL6.5编译安装MySQL5.6.26教程

    一、准备编译环境，安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21

  • mongodb与mysql命令详细对比

    传统的关系数据库一般由数据库（database）、表（table）、记录（record）三个层次概念组成，MongoDB是由数据库（database）、集合（collection）、文档对象（document）三个层次组成。MongoDB对于关系型数据库里的表，但是集合中没有列、行和关...2013-09-11

  • MySQL远程连接不上的解决方法

    这篇文章主要为大家详细介绍了MySQL远程连接不上的解决方法，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2017-01-26

  • Delphi远程连接Mysql的实现方法

    这篇文章主要介绍了Delphi远程连接Mysql的实现方法,需要的朋友可以参考下...2020-06-30