欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql优化二之锁机制

程序员文章站 2023-10-17 08:37:48
mysql优化二之锁机制 mysql提供了锁机制和MVCC机制来保证并发操作的安全性,这里主要讨论锁机制, MVCC见下篇文章 mysql的锁按照锁粒度可分为行锁与表锁,按照操作类型划分可读锁和写锁 InnoDB存储引擎支持表锁和行锁,默认锁为行锁,MyIsam只支持表锁 锁粒度越高则并发性越好 表 ......

mysql优化二之锁机制

mysql提供了锁机制和mvcc机制来保证并发操作的安全性,这里主要讨论锁机制,
mvcc见下篇文章

mysql的锁按照锁粒度可分为行锁与表锁,按照操作类型划分可读锁和写锁
innodb存储引擎支持表锁和行锁,默认锁为行锁,myisam只支持表锁
锁粒度越高则并发性越好

表锁

一、操作语法

1、 show open tables;查看数据库中哪些表加了锁

mysql优化二之锁机制
in-use为0则表示未加锁

2、 lock table (table_name) read(write)

mysql优化二之锁机制

mysql优化二之锁机制

3、 unlock tables;解锁

二、示例

1、 读锁
开了两个mysql客户端,左边客户端中给mytest数据库中的test1表加了读锁

  • 左客户端执行读操作,右客户端执行读操作,可以看到两者都可以执行
    mysql优化二之锁机制

  • 对于同一数据库中的其他表操作,左客户端拒绝执行,右客户端可以执行

mysql优化二之锁机制

  • 对于被锁住的表执行写操作

mysql优化二之锁机制
左客户端拒绝执行

mysql优化二之锁机制

右客户端阻塞住

此时我们解锁

mysql优化二之锁机制

可以看到右客户端立即被释放并正确执行操作


2、 写锁
给左客户端test1表加上写锁

  • 左客户端读/写正常,右客户端读/写均被阻塞
    mysql优化二之锁机制

  • 左客户端读/写该数据库中的其他表拒绝执行,右客户端可以正常执行
    mysql优化二之锁机制


行锁

因为mysql默认的存储引擎是innodb,而innodb默认为行锁,我们要测试行锁首先需要把mysql的自动提交关闭

mysql优化二之锁机制

  • 行级锁读操作两个客户端互不影响

  • 写操作如果操作的不是同一行,也不影响,若操作的是同一行则后一个客户端的请求被阻塞,直到前一个客户端的请求提交

mysql优化二之锁机制


注意点

1、 间隙锁

mysql优化二之锁机制

我们在左边客户端对id在(0,5]范围内的数据做写操作,但是由于数据表中没有id为4的数据行,按理说右边数据库插入一个id为4的数据行不会被锁住,但是事实表明它被锁住了。这既是mysql的间隙锁机制。因此我们在数据库操作时其实应该避免这种间隙的产生,我们可以在表里设置一个状态位,当要删除某一数据行时,可以选择将该状态位设置为无效而不是真正的删除。

2、 索引失效

  • 当where查询条件没有索引时,行锁变表锁
    mysql优化二之锁机制
    mysql优化二之锁机制

  • 当where查询条件有索引但是索引失效时,行锁仍然变表锁
    mysql优化二之锁机制

mysql优化二之锁机制
当左客户端在已经减了索引的loc列用int型来查找时,索引失效就会导致整张表被锁住

行锁变表锁的原因:mysql的行锁是用索引实现的

3、 如何锁住一行

mysql优化二之锁机制
在select语句找出某一行之后加一个for update

mysql优化二之锁机制