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
的合集。注意:如果对表的某一行没有任务索引的列进行加锁,那么并不会只对这一行加锁。而是对全表加锁
加锁分析
其中id
字段加唯一索引。name
字段加非唯一索引。age字段无索引。
共享锁与排它锁
建表语句如下:
表锁
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中插入记录,则会导致插入失败,因为获取不到这记录之间的锁。
- Labels: MySQL