首页 > mysql教程 > 正文

Mysql存储引擎MyISAM锁机制详解

转载 2019-02-11 0 22

准备:

测试环境:Mysql 5.7.20-log

本来准备把InnoDB和MyISAM放在一块详解,发现MyISAM要写的东西太多,就下一次详解InnoDB吧,好了,废话不多说。

有过开发经验的小伙伴肯定经常听到锁的概念吧,那到底什么是锁?

锁的概念

锁是处理器协调协调多个进程或线程并发访问某一资源的一种机制或者手段。在数据库中,除了传统的计算资源(CPU,IO,RAM)外,还存在数据的共享,so保证并发环境下数据的一致性,有效性便是数据库很重要的一个指标,同时锁带来便利的同时,锁冲突也是影响数据库并发性能的一个重要因素,从这个角度来看,锁对数据库显得至关重要,但也很复杂。

Mysql中的锁

相较于计算机而言,数据库中的锁相对来说就比较简单了,但不同的存储引擎其锁机制也不尽相同。

Mysql大致可化为以下3中锁:

1>表锁:开销小,加锁开,不会出现死锁,但其粒度比较大,发生冲突的几率最高,并发支持最差

2>行级锁:开销大,加锁慢,会出现死锁,粒度最小,发生冲突的几辆车最低,并发支持也是最好的

3>页面锁:开销,加锁速度,锁粒度都介于表锁和行级锁之间,会死锁,并发支持一般

由于篇幅限制,今天只讲表锁

Mysql的表锁(代表引擎MyISAM)

MyISAM的表锁分为 表共享读锁和表独占写锁,其兼容性如下表:

Mysql存储引擎MyISAM锁机制详解

读锁和写锁的兼容性

由上表可以得出以下结论:

1>MyISAM在执行读操作时,不会阻塞其他用户对同一表的读请求,但会阻塞写请求。

2>MyISAM在执行写操作时,会阻塞其他用户对此表的读和写请求。

3>MyISAM读写,写写之间是串行的,不可同时进行。

MyISAM锁测试

共享读锁

1>创建表,sql如下:

create table myisam(

id int auto_increment primary key,

name varchar(10)

)engine='MyISAM'

2>任意插入几条数据,查看锁竞争情况

Mysql存储引擎MyISAM锁机制详解

查看锁竞争状态

Table_locks_waited 值越大,说明锁竞争情况越严重。

3>使用共享读锁锁定此表(客户端A锁表,客户端A可以读表,不可写表,客户端B可以读表但不能写表)

Mysql存储引擎MyISAM锁机制详解

读锁锁定

4>解锁后,都可以读写表

Mysql存储引擎MyISAM锁机制详解

解锁后

备注:但有一特例,使用lock table myisam read local, 客户端A可以读表,但不可以写表,其他客户端可以写表也可以读表。MyISAM会自动在执行sql的时候进行表的加锁和释放锁。MyISAM在执行查询(SELECT)时会自动给涉及到的表加上读锁,在执行更新(UPDATE、DELETE、INSERT)等,会自动给涉及到的表加上写锁


独占写锁

Mysql存储引擎MyISAM锁机制详解

写锁锁定

可以看出,客户端A可以读表和写表,客户端B不可读表,也不可写表

除此之外,无论给表加共享读锁还是独占写锁,都只能访问加锁的表,其他没锁的表不能被访问

Mysql存储引擎MyISAM锁机制详解

未加锁的表不能访问

凡是用到的别名都要加一次锁,在使用别名时,必须对别名也加锁,而且即使是同一个表,如果别名不一样也需要另外加锁

Mysql存储引擎MyISAM锁机制详解

别名锁也要加锁

加锁后

Mysql存储引擎MyISAM锁机制详解

别名表进行加锁

并发插入

MyISAM表的读和写是串行的,但在一定条件下,MyISAM表也支持查询和插入的并发执行,MyISAM引擎有一个concurrent_insert变量,专门用来控制其并发插入的行为,取值为0(或NEVER),1(或AUTO)和2(或ALWAYS),当concurrent_insert为0时,不允许并发插入,当concurrent_insert为1时如果表中没有空洞(即表的中间没有被删除的行),那么允许一个会话读表的同时,另一个表在表尾插入记录,这是默认值;当concurrent_insert为2时,允许在表尾并发插入记录。

如果你的应用中用了MyISAM的表,并且你想有一定的插入并发,那么就可以设置concurrent_insert为2,并且定期地在空闲时期执行OPTIMIZE TABLE table_name来整理碎片。

MyISAM锁调度问题(即优先级)

另外值得注意的是,当一个会话A请求MyISAM表的读锁,另一个会话B也请求同一个表的写锁时,MySQL会让会话B获得写锁,甚至,假如锁等待队列中读请求先到,写请求后到,MySQL同样也会让写请求插入到读请求前面。这也是为什么MyISAM表不太适合于有大量更新操作和查询操作应用的原因,大量的更新操作会早场查询操作很难获得锁从而可能阻塞很长一段时间。对此,我们可以做一些设置来打破这个规则:

  1. 通过启动参数low-priority-updates,使MyISAM引擎默认给予读请求更高的优先级
  2. 通过set low-priority-updates = 1,使当前连接发出的所有更新请求的优先级降低
  3. INSERT、UPDATE、DELETE语句都有一个LOW_PRIORITY属性,通过该属性,降低该语句的优先级,例如insert low_priority into user values ...

好了,学习了表锁,你也应该能了解到它的使用场景了吧

下一篇带来InnoDB的锁机制,敬请期待,谢谢支持。

相关文章


  • mysql取日期间隔的方法小结
  • MySQL数据库每天自动备份定时备份
  • 关于不同的MySQL复制解决方案概述
  • Navicat for MySQL for Mac 破解版(数据库管理及
  • 分享 | mac重置mysql的root 密码
  • MariaDB/MySQL安全配置以及账户管理
  • MySQL 8.0 新特性之统计直方图
  • MySQL句柄恢复文件的简单尝试