mysql

MySQL锁机制

2021-06-17  本文已影响0人  贪挽懒月

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;

lock table mylock read, tblA write;

unlock tables;

2. 表锁演示：

读锁：

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

xshell

然后进行如下操作：

ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

ERROR 1100 (HY000): Table 'tblA' was not locked with LOCK TABLES
结果

一直卡着不动，说明阻塞了，要直到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中有两个变量，可以记录表的锁定情况，如下：

查看这两个变量的值的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     |
+-------------------------------+-------+

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

这个是比较隐蔽的问题，很难发现，但确实存在。比如之前说的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语句才能执行成功。

上一篇 下一篇

猜你喜欢

热点阅读