Mysql之Innodb锁场景

mysql锁分类

　　基于锁的属性分类：共享锁（读锁）、排他锁（写锁）

　　基于锁的粒度分类：行级锁（innodb）、表锁（innodb、myisam）、页级锁（innodb引擎）、记录锁、间隙锁、临建锁。

　　mysql中的绝大部分锁都是悲观锁，按照粒度可以分为行锁和表锁:

　　行锁:

　　　　共享锁:当读取一行记录的时候，为了防止别人修改，则需要添加S锁

　　　　排它锁:当修改一行记录的时候，为了防止别人同时进行修改，则需要添加X锁　　　　

	X	S
X	不兼容	不兼容
S	不兼容	兼容

　　　　记录锁:添加在行索引上的锁

　　　　间隙锁:锁定范围是索引记录之间的间隙，针对可重复读以上隔离级别

　　　　临键锁:记录锁+间隙锁

　　表锁:

　　　　意向锁:在获取某行的锁之前，必须要获取表的锁，分为意向共享锁，意向排它锁

　　　　自增锁：对自增字段所采用的特殊表级锁。

　　锁模式的含义:

　　　　IX:意向排它锁

　　　　X:锁定记录本身和记录之前的间隙（重点用）

　　　　S:锁定记录本身和记录之前的间隙

　　　　X,REC_NOT_GAP:只锁定记录本身（重点用）

　　　　S，REC_NOT_GAP:只锁定记录本身

　　　　X，GAP:间隙锁，不锁定记录本身（重点用）

　　　　S，GAP:间隙锁，不锁定记录本身

　　　　X，GAP,INSERT_INTENTION:插入意向锁

　　监控锁：　　　　　

show engine innodb status\G  //显示锁

　　加锁场景：

　　分情况来说主要是RR和RC的区别：

　　先介绍RR下的查询锁场景

　　　　查询格式：

1. select * from table for update
2. select * from table where id ='10' for update
3. select * from table where name ='10' for update

RR+表无显式主键没有索引

　　　　初始化sql：

　　　　　　create table t1(id int default null, name char(20) default null);

　　　　　　insert into t1 values ( 10, '10' ),(20,'20' ),(30, '30’) ;

　　　　结论：

　　　　　　查询1和查询2（id不是索引列）加锁一致：

　　　　　　加的是:临键锁 (-∞,10],(10,20],(20,30],（30,+∞), 所以锁的是表，插入新数据超时，为了组织幻读问题。

　　2.RR+表有显式主键没有索引

　　　　初始化sql：

　　　　　　create table t2(id int primary key not null, name char(20) default null);

　　　　　　insert into t2 values ( 10, '10' ),(20,'20' ),(30, '30’) ;

　　　　结论：

　　　　　　查询1：加的是:临键锁 (-∞,10],(10,20],(20,30],（30,+∞),

　　　　　　查询2：加的是:id=10的行锁,保证10的主键不能再插入了

　　3.RR+表无显式主键有索引

　　　　初始化sql：

　　　　　　create table t3(id int default null, name char(20) default null);

　　　　　　create index idx_id on t3 (id);

　　　　　　insert into t3 values ( 10, '10' ),(20,'20' ),(30, '30’) ;

　　　　结论：

　　　　　　查询1(锁的row_id)：加的是:临键锁 (-∞,10],(10,20],(20,30],（30,+∞),

　　　　　　查询2：加的是:1.临键锁：(-∞,10],2.索引：10 ，3.间隙锁：(10,20),减少幻读的可能，插入小于20不允许。

　　4.RR+表无显式主键有唯一索引

　　　　初始化sql：

　　　　　　create table t4(id int default null, name char(20) default null);

　　　　　　create unique idx_id on tt (id);

　　　　　　insert into t4 values ( 10, '10' ),(20,'20' ),(30, '30’) ;

　　　　结果：

　　　　　　查询1(锁的row_id)：加的是:临键锁 (-∞,10],(10,20],(20,30],（30,+∞),

　　　　　　查询2：加的是: 行锁：10被锁定

　　　5.RR+表有显式主键有索引

　　　　初始化sql：

　　　　　　create table t5(id int not null, name char(20) default null,primary key(id),key idx_name(name));

　　　　　　create unique idx_id on tt (id);

　　　　　　insert into t5 values ( 10, '10' ),(20,'20' ),(30, '30’) ;

　　　　结果：

　　　　　　查询1(锁的row_id)：加的是:临键锁 (-∞,10],(10,20],(20,30],（30,+∞);以及10 20 30 的行锁

　　　　　　查询2：加的是: 行锁

　　　　　　查询3：加的是：

　　　　　　　　　　临键锁：(-∞,10],

　　　　　　　　　　索引：10

　　　　　　　　　　间隙锁：(10,20),

　　　　6.RR+表有显式主键有唯一索引

　　　　　　初始化sql：

　　　　　　　　create table t6(id int not null,name char(20) default null,primary key(id) , nique key idx_name(name);

　　　　　　　　insert into t6 values ( 10, '10' ),(20,'20' ),(30, '30’) ;

　　　　　　结果：

　　　　　　　　查询1(锁的row_id)：加的是:加的是:临键锁 (-∞,10],(10,20],(20,30],（30,+∞);以及10 20 30 的行锁

　　　　　　　　查询2：加的是: 行锁

　　RC情况下查询1和2---都是行锁（因为临建锁和间隙锁都是解决幻读问题）

　　　　1.RC+表无显式主键没有索引

　　　　2.RC+表有显式主键没有索引

　　　　3.RC+表无显式主键有索引

　　　　4.RC+表无显式主键有唯一索引

　　　　5.RC+表有显式主键有索引

　　　　6.RC+表有显式主键有唯一索引

记录自己

每天进步一点点...

Mysql之Innodb锁场景