专栏首页JavaEEMySQL锁机制

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会对这个间隙加锁,这就叫间隙锁。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql锁机制

    锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中数据其实是一种供大量用户共享的资源,所以在并发访问时我们需要保证数据的一致性和有效性,而锁冲突是影...

    创译科技
  • Mysql锁机制分析

    用户1263954
  • 谈谈 MySQL 锁机制

    因为数据也是一种供许多用户共享的资源,如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素,所以进...

    iMike
  • 【MySQL】Mysql锁机制简介

    InnoDB存储引擎不仅会在行级别上对表数据上锁,还会在数据库内部其他多个地方使用锁,从而允许对多种不同资源提供并发访问。如:操作缓冲池中LRU列表,删除、添加...

    用户5522200
  • MySQL锁机制及优化

    总的来说,MySQL各存储引擎使用了三种类型(级别)的锁定机制:行级锁定,页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。

    lyb-geek
  • MySQL 锁机制——必知必会

    MyISAM表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。

    高广超
  • 史上最全MySQL锁机制

    因为数据也是一种供许多用户共享的资源,如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素,所以进...

    数据和云
  • Mysql锁机制简单了解一下

    Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_G...

    用户2164320
  • Mysql锁机制分析【面试+工作】

    数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则;对于任何一种数据库来说都需要有相应的锁定机制,My...

    Java帮帮

扫码关注云+社区

领取腾讯云代金券