MySQL之4--Innodb锁分析

现在MySQL的默认存储引擎是InnoDB，其提供了一个很强大的功能就是行级锁。可以通过show status like 'innodb_row_lock%';来查看当前系统的行锁争用情况。如果Innodb_row_lock_waits和Innodb_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 Lock和Gap 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字段无索引。

共享锁与排它锁

建表语句如下：

session1	session2	session3
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表的索引说明：

表中数据如下：

session1	session2
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会一直等待获取锁。

行锁

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

session1	session2
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也会对这个间隙加锁。

session1	session2
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中插入记录，则会导致插入失败，因为获取不到这记录之间的锁。