【原】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会对这个间隙加锁，这就叫间隙锁。

扫描二维码