新吴区建设局网站,番禺做网站系统,网站做等保测评,it外包服务商10强MySQL知识点总结#xff08;五#xff09;——锁 锁分类表锁 行锁如何添加表锁#xff1f;如何添加行锁#xff1f; 读锁 写锁行锁 间隙锁#xff08;gap lock#xff09; 临键锁#xff08;next-key lock#xff09; 加锁机制分析可重复读隔离… MySQL知识点总结五——锁 锁分类表锁 行锁如何添加表锁如何添加行锁 读锁 写锁行锁 间隙锁gap lock 临键锁next-key lock 加锁机制分析可重复读隔离级别下的加锁机制唯一索引等值查询非唯一索引等值查询范围查询 读已提交隔离级别下的加锁机制 如何强行释放锁 锁是每一个关系型数据库都肯定会有的一种处理并发读写冲突的机制。通过加锁当前事务可以保证它加了锁的行记录不被别的事务修改保证了事务的隔离性。
保证事务隔离性的机制除了加锁以外还有MVCC那什么时候加锁什么时候使用MVCC机制呢在一个事务中当我们的查询语句显式的加锁也就是“select … lock in share mode”或者“select … for update”以及增删改操作都会加锁而普通的“select …”读操作则通过MVCC来保证基本的隔离性。 锁分类
首先我们了解一下锁的分类。需要注意的是基于不同的角度有不同的锁分类方式。比较常见的锁分类方式有表锁 行锁、读锁 写锁、行锁 间隙锁gap lock 临键锁next-key lock。
表锁 行锁
这是基于锁粒度的一种锁分类方式表锁锁的是整张表而行锁锁的是一行或者多行记录。表锁的加锁速度较快但是并发度不高容易发生锁冲突而行锁的加锁速度较慢但是并发度较高发生锁冲突的概率较小。 如何添加表锁
如果是MyISAM存储引擎的话由于MyISAM是不支持行锁的一般的增删改SQL以及我们显式加锁的查询语句MyISAM都会加表锁锁住整张表。
如果是InnoDB存储引擎的话我们想要加表锁需要通过SQL语句“lock tables … read/write”声明加表锁。
比如我们要对student表加一个写锁那么SQL语句就是如下这样
LOCK TABLES student WRITE;如果我们要对student加一个读锁那么SQL语句就是如下这样
LOCK TABLES student READ;如何添加行锁
MyISAM存储引擎是不支持行锁的所以如果要用MyISAM去加行锁的话可以洗洗睡了。
InnoDB默认就是加行锁的如果我们不显式声明加表锁增删改操作以及显式声明加锁的select语句都是加行锁的。
读锁 写锁
这是另一种锁分类的方式读写也叫共享锁读锁和读锁之间并不冲突也就是说两个读锁可以同时对同一条记录上锁而写锁与写锁之间是互斥的一行记录同一时刻只能有一个写锁对其上锁因此写锁也叫排他锁。 但是要注意的时并非读操作就加读锁写操作就加写锁当我们提交的是“select … for update”语句时虽然该语句是一个读操作但是加的是写锁也叫排他锁。
行锁 间隙锁gap lock 临键锁next-key lock
这是InnoDB行锁里的分类
行锁只对一行记录加锁。间隙锁gap lock对两条记录中间的间隙加锁锁住一个区间但是不包含记录本身。临键锁next-key lock间隙锁 行锁不仅对两条记录间的区间加锁还会锁住记录本身前开后闭也就是锁住后面一条记录前面的记录不会锁住。
我们通过画图去理解就知道了。
行锁是锁住一行记录 间隙锁是锁住一段区间但是不包含区间前后的记录本身 next-key lock的加锁范围是前开后闭的也就是两条记录间的区间间隙再加上该区间的后面一条记录 加锁机制分析
接下来分析一下各种不同情况下InnoDB都是如何加锁的加的是行锁间隙锁还是next-key lock然后分别对哪些行记录加锁。
在分析之前我们首先要明白一点InnoDB的行锁都是加在索引上的也就是对索引上锁InnoDB只会对扫描到的索引加锁没有扫描到的索引是不会加锁的并且只会对索引中被扫描到的行记录和间隙上锁。
可重复读隔离级别下的加锁机制
可重复读隔离级别下加锁的原则是对被扫描到的索引行记录以及它前面的区间加临键锁next-key lock但是等值查询时扫描到最后的一条不满足条件的记录则不用上锁范围查询则连同最后一条扫描到的不满足条件的行记录也给锁上。
除此以外唯一索引的等值查询如果命中索引中的某个行记录则只对该索引行记录加行锁不再加间隙锁相当于是临键锁优化成了行锁。
下面一一分析。
唯一索引等值查询
比如我们现在有一张表student我们查询id等于5的这一条记录
select * from student where id 5 for update;由于是唯一索引InnoDB当匹配到满足条件的索引行记录时就不会再扫描因此在唯一索引等值查询匹配到满足条件的记录时会对该记录加行锁不会加间隙锁。 如果匹配不成功就会加间隙锁但是不会加next-key lock因为是等值查询扫描到最后的一条不满足条件的记录是不用上锁的。 非唯一索引等值查询
比如我们的表student中有一列age年龄字段age字段建立了非唯一索引现在我们查询age等于30的记录
select id,age from student age 30 lock in share mode;假设student表存在age30的行记录并且是这样的排列顺序 在age字段的索引中与age30的行记录紧挨着的分别是前面的age20的行记录和后面的age40的行记录。
那么加锁情况就是这样 首先通过age字段的索引定位到了age30的行记录由于不是唯一索引等值查询因此要加临键锁那么首先对age30的字段连同它前面大于age20的这一段区间也就是(20, 30]这一段左开右闭的区间加一个临键锁。然后由于该索引不是唯一索引因此还要继续往后扫描。
然后扫描到age40这一行记录发现不满足条件由于当前查询是等值查询不需要对age40这一行记录上锁因此对(30,40)这一段左开右开的区间上间隙锁。
由于这里的sql使用的是“lock in share mode”加的读锁所以只对二级索引加锁如果使用的时“for update”加的写锁还会给主键索引上满足条件的行加上行锁。
范围查询
比如我们查询student表中id大于等于5小于10的记录
select * from student where id 5 and id 10 for update;假设student表中的行记录是这样 id字段是student表中的主键在student表的主键索引中存在id5的行记录然后id5的行记录前面是id1的行记录后面是id10的行记录。
那么加锁情况是这样 由于主键id的索引是唯一索引查找首先匹配到id5的行记录会加一个行锁。然后因为查询是范围查询查找id大于等于5又小于10的记录。于是扫描到id10的行记录发现不满足“id 10”的查询条件于是不再往后扫描但是由于不是等值查询所以会把最后扫描到的id10的行记录锁上于是就添加了一个id在(5, 10]区间范围的临键锁。
读已提交隔离级别下的加锁机制
读已提交隔离级别下的加锁规则就相对简单了读已提交隔离级别下只会加行锁不会加间隙锁和临键锁。也就是说无论是等值查询还是范围查询都只会对扫描到的行记录加行锁。
比如上面这个范围查询的例子如果是在读已提交隔离级别下加锁情况就变成这样 如何强行释放锁
如果我们遇到了SQL执行一直被卡住执行不下去然后报了等待锁超时的错误比如下面这种情况 此时如果我们想强行把被持有的锁释放掉该如何操作呢
如果是MySQL 5.7 版本我们可以通过下面这个SQL查询到锁被哪个线程占有
select * from sys.innodb_lock_waits where locked_table 库名.表名 \G;这个SQL会显示是几号线程占有着锁导致阻塞然后我们可以使用KILL命令加上对应线程的pid上面的查询语句会显示线程的pid强行断开该线程对应的连接比如占有锁的线程的pid是5那么我们执行如下命令断开pid为5的线程的连接
KILL 5;连接一旦断开占有的锁就自动被释放。
