Mysql之Innodb锁场景

mysql锁分类

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

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

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

　　行锁:

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

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

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

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

　　　　临键锁:记录锁+间隙锁

　　表锁:

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

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

　　锁模式的含义:

　　　　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的区别：

　　　　查询格式：

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

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

　　　　初始化sql： 

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

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

　　　　结论： 

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

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

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

　　　　初始化sql： 

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

　　　　　　insert into t1 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 t4 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 t4 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+表有显式主键有唯一索引