MySQL之4--Innodb锁分析

现在MySQL的默认存储引擎是InnoDB,其提供了一个很强大的功能就是行级锁。可以通过show status like 'innodb_row_lock%';来查看当前系统的行锁争用情况。如果Innodb_row_lock_waitsInnodb_row_lock_time_avg两个值比较高,则说明锁争用比较严重。

概述

InnoDB提供了两种类型的行锁:

  • 共享锁:对于一行已经加了共享锁的记录,可以允许其他共享锁的存在,却不允许其他排他锁的存在。这个可以对比读锁来理解。SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE
  • 排他锁:对于一行已经加了排他锁的记录。既不允许其他的共享锁存在,也不允许其他排他锁的存在。这个可以对比写锁来理解。SELECT * FROM table WHERE id=1 FOR UPDATE
    InnoDB行锁是通过对索引进行加锁来执行的,如果没有索引,InnoDB将通过隐藏的聚族索引来对记录进行加锁。InnoDB的行锁分为三种情况:
  • Record Lock : 对唯一索引进行加锁。
  • Gap Lock :对索引之间的间隙加锁
  • Next Key Lock :是Record LockGap Lock的合集。
    注意:如果对表的某一行没有任务索引的列进行加锁,那么并不会只对这一行加锁。而是对全表加锁

加锁分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table lock_test(
id int primary key not null auto_increment ,
name varchar(32) not null ,
age int not null default 0 ,
key `idx_name`(`name`)
);
insert into lock_test(name,age)values('gcl',26);
insert into lock_test(name,age)values('lisi',54);
insert into lock_test(name,age)values('ww',12);
insert into lock_test(name,age)values('zl',9);
insert into lock_test(name,age)values('sq',20);
insert into lock_test(name,age)values('hh',18);
insert into lock_test(name,age)values('zh',15);
insert into lock_test(name,age)values('mm',23);

其中id字段加唯一索引。name字段加非唯一索引。age字段无索引。

共享锁与排它锁

建表语句如下:

session1session2session3
set autocommit=0;set autocommit=0;
对id=1的事物加共享锁
select * from lock_test where id = 1 lock in share mode;
其他事物仍然可以对id=1的记录加共享锁
select * from lock_test where id = 1 lock in share mode;
其他事物对id=1的记录加排它锁,则报错
select * from lock_test where id = 1 for update;

表锁

Innodb的行锁是通过索引来实现的,如果不通过索引检索数据,那么InnoDB将对表中的所有记录加锁,也就成了表锁。下面是lock_test表的索引说明:

表中数据如下:

session1session2
set autocommit = 0 ;set autocommit = 0 ;
select * from lock_test where age=26 for update;select * from lock_test where age=54 for update;
commit;commit;

因为age字段没有索引,所以session1的查询条件会对全表加索引,会发现在session1没有commit之前,session2会一直等待获取锁。

行锁

session1session2
set autocommit = 0 ;set autocommit = 0 ;
select * from lock_test where id=1 for update;select * from lock_test where id=2 for update;

commit;commit;

因为id字段是唯一索引,所以session1的查询条件id=1时候只会只第一条记录加锁,不会影响其他记录加锁。

间隙锁

间隙锁是InnoDB中行锁的一种,但是这种锁锁住的却不止一行,他锁住的是多行,他的主要作用是为了防止幻读出现。当我们对数据库中不存在的一行记录加锁的时候,数据库就会扫描索引,向左扫描到第一个比给定参数小的值,向右扫描第一个比给定参数大的值,构建一个区间,然后对整个区间加锁。这就是间隙锁。

session1session2
set autocommit = 0 ;set autocommit = 0 ;
select * from lock_test where name = ‘zi’ for update ;insert into lock_test(name,age)values(‘zj’,17);
commit;commit;

在session1中我们对name='zi'的记录加锁,但是这行记录并不存在,所以锁住区间是 zh<= name <= zl 中的所有记录。当插入name='zj'记录时。在间隙锁的区间内。所以会出现插入获取不到锁而失败的情况。

Next Key锁

当我们使用范围条件而不是相等条件检索数据,InnoDB会给符合条件的已有数据记录的索引加锁,而对于键值在条件内但是并不存在的记录,innoDB也会对这个间隙加锁。

session1session2
set autocommit = 0 ;set autocommit = 0 ;
select * from lock_test where id=1 for update;select * from lock_test where id=2 for update;
commit;commit;

在session1中我们对'ww' < name < 'zz' 的记录加锁。则会对字典序在这之间的所有记录加锁。在session2中插入记录,则会导致插入失败,因为获取不到这记录之间的锁。