MySQL锁机制

MySQL主要有表锁,行锁和页锁,页锁用得少,本文主要介绍表锁和行锁。

一、锁的分类

从对数据的操作类型来分,可以分为读锁和写锁;从对数据操作粒度来分,可分为表锁和行锁。

  • 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响;

  • 写锁(排他锁):当前写操作没有完成前,会阻断其他写锁和读锁;

  • 表锁:锁住被操作的整张表;

  • 行锁:锁住被操作表中的被操作行,其他行不受影响。

二、表锁

1. 介绍:

表锁偏向MyISAM存储引擎,开销小,加锁快,无死锁,粒度大,并发性差。下面建表演示表锁的用法。

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20)
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

这里用了MyISAM引擎,这个引擎是写优先的,加了写锁后,其他线程不能对被锁的表做任何操作,即使是查询,所以如果写操作很多,就会导致其他线程的读操作难以执行,大量的查询sql被阻塞。

  • 增加表锁的语法:
lock table 表名1 read(write), 表名2 read(write) ……;
  • 查看表上加过的锁;
show open tables;
  • 给mylock表加读锁,tblA加写锁:
lock table mylock read, tblA write;
  • 释放锁:
unlock tables;

2. 表锁演示:

读锁:

首先给mylock表加上读锁,然后打开两个session,暂且将左边的称为session1,右边的称为session2,如下:

xshell

然后进行如下操作:

  • 在session1中执行lock table mylock read,然后执行select * from mylock;,结果是可以查询出数据。即自己加了读锁,自己是可以查的;

  • 在session2中执行select * from mylock;,结果也是可以查询出数据。说明读锁,大家都可以读数据;

  • 在session1中执行update mylock set name = 'aa' where id = 1;,结果报了如下错误:

ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
  • session1给mylock表加了读锁,那么session1能读其他的表吗?我现在执行select * from tblA;,结果是不行的,报了如下的错误:
ERROR 1100 (HY000): Table 'tblA' was not locked with LOCK TABLES
  • session2能读tblA表吗?执行select * from tblA;,结果是可以的。

  • session2中执行update mylock set name = 'aa' where id = 1;,结果如下:

结果

一直卡着不动,说明阻塞了,要直到mylock表解锁才能成功。

表读锁总结:

操作 当前session 其他session
读当前表 Y Y
读其他表 N Y
写当前表 N 阻塞,直到锁被释放
写其他表 N Y

写锁:

mylock表加上写锁,lock table mylock write,然后在session1和session2中对当前表和其他表进行读写操作,最后结论如下:

操作 当前session 其他session
读当前表 Y 阻塞,直至锁被释放
读其他表 N Y
写当前表 Y 阻塞,直到锁被释放
写其他表 N Y

对于表读锁和表写锁,总结起来就是加了读锁,当前session只能读当前表,其他session只有写当前表会被阻塞;加了写锁,当前session只能对当前表进行读写,其他session对当前表的读写都会被阻塞。所以表锁一般偏读,也就是一般不会加表写锁,加写锁可能会导致大量的查询被阻塞。

3. 表锁分析:

MySQL中有两个变量,可以记录表的锁定情况,如下:

  • Table_locks_immediate:表示可以立即获取锁的查询次数,每次加1;

  • Table_locks_waited:出现表级锁争用而发生等待的次数,每次加1;

查看这两个变量的值的sql:

show status like 'table%';
执行结果

三、行锁

1. 介绍:

行锁偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁,粒度小,并发性好。InnoDB支持事务,而MyISAM是不支持事务的,InnoDB默认采用的也是行锁,下面建表演示表锁的用法。

create table col_lock(
    id int not null primary key auto_increment,
    name varchar(20)
) engine innodb;

insert into col_lock(name) values('a');
insert into col_lock(name) values('b');
insert into col_lock(name) values('c');
insert into col_lock(name) values('d');
insert into col_lock(name) values('e');

2. 行锁总结:

innodb支持事务,并且默认是自动提交,为了演示行锁,先执行下面的sql把自动提交关闭。

set autocommint = 0;

接下来看看session1和session2的各种操作情况:

操作 当前session 其他session
读当前行 Y Y
写当前行 Y 阻塞,直到锁被释放
两个session操作不同的行 Y Y

3. 分析行锁:

我们可以通过如下sql查看行锁的争夺情况:

show status like 'innodb_row_lock%';

执行结果是:

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 57446 |
| Innodb_row_lock_time_avg      | 28723 |
| Innodb_row_lock_time_max      | 51618 |
| Innodb_row_lock_waits         | 2     |
+-------------------------------+-------+
  • Innodb_row_lock_current_waits:当前正在等待锁定的数量
  • Innodb_row_lock_time:从系统启动到现在锁定总时长
  • Innodb_row_lock_time_avg:每次等待所花的平均时间
  • Innodb_row_lock_time_max:从系统启动到现在获取锁等待最久的一次花的时间
  • Innodb_row_lock_waits:系统启动到现在获取锁等待的总次数

四、索引失效行锁变表锁问题

这个是比较隐蔽的问题,很难发现,但确实存在。比如之前说的varchar类型的没加单引号,会导致索引失效,那么这时候行锁就会变为表锁。比如col_lock表的name字段是varchar类型的,先在name字段加索引,然后关闭自动提交,执行下面的语句:

update col_lock set name = aa where id = 1;

然后再另一个session中执行:

update col_lock set name = 'bb' where id = 2;

本来操作的是不同的行,即使第一条语句还没commit,第二条应该也能执行,但实际上不行,因为aa没加单引号,索引失效了,行锁变成了表锁。

五、间隙锁的危害

有个tblA表,age字段是加了索引的,数据如下:

执行结果

我们在这session1中执行下面的update操作:

update tblA set birth = now() where age > 20 and age < 25;

其实也就是3条记录都会被更新。执行后,先不提交,在session2中执行如下语句:

insert tblA(age,birth) values(22,now());

表中没有age为22的,那现在就插入一条age为22的记录,行锁,两边操作不同的行,应该不会有任何影响的,但是现在情况如下:

结果

直接等待锁都超时了,这就是间隙锁。session1中commit了之后,session2中的insert语句才能执行成功。

  • 间隙:当我们使用范围条件检索数据,请求共享或排他锁时,innodb会给符合条件的已有数据记录的索引项加锁,对于在条件范围内但是不存在的记录,比如age为22在age > 20 and age <25这个范围内,但是不存在这条记录,这个就叫做间隙。innodb会对这个间隙加锁,这就叫间隙锁。

推荐阅读更多精彩内容

  • 1、概述 锁的定义 1.锁是计算机协调多个进程或线程并发访问某一资源的机制。2.在数据库中,除传统的计算资源(如C...
    L_又不是不能用阅读 59评论 0 0
  • 1、MySQL锁的基本介绍 ​ 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统...
    zzj0990阅读 351评论 1 13
  • 来自公众号:非科班的科班作者:黎杜 前言 前几天有粉丝和我聊到他找工作面试大厂时被问的问题,因为现在疫情期间,找工...
    码农小光阅读 1,133评论 1 12
  • 一、MySQL锁机制 1.1 锁分类 按锁的粒度 划分: 表锁:表锁是粒度最大的锁,开销小,加锁快,不会出现死锁,...
    CJ21阅读 857评论 2 8
  • 今天主要学习了flex布局,学习笔记如下: 1.指定flex布局: display:flex(任意容器)...
    riku_lu阅读 1,717评论 2 3