InnoDB 锁及锁的类型（读MySQL技术内幕-InnoDB存储引擎）

锁的类型：

 共享锁（S Lock）：允许事务读一行数据

 排他锁（X Lock）：允许事务删除或更新一行数据

 意向共享锁（IS Lock）：事务想要获取一张表中某几行的共享锁

 意向排他锁（IX Lock）：事务想要获取一张表中某几行的排他锁

 事务T1已经获取了R记录的共享锁，事务T2任然能获取R记录的共享锁，即为兼容，反之不兼容

从表格中看出：排他锁均不兼容，意向排他锁和共享锁不兼容，其他均兼容

mysql中information_schema和performance_schema分别为系统表，性能表，8.0版本中performance_schema可查看锁和事务

 

DataGrip新建俩个session,将其中一个session切换，模拟俩个事务

 

 

一致性非锁定读（consistent nonlocking read）：

     通过多版本控制（multi versioning）方式来读取数据：

         若读取的行正在执行DELETE或者UPDATE操作，这时读取操作不会等待行上锁的释放，InnoDB存储引擎会读取此行的一个快照数据（此行之前版本的数据，通过undo段完成，undo用来在事务中回滚数据，因此快照数据本事没有额外的消耗），没有操作会修改历史数据，所以读取快照数据不需要上锁

   一行记录可能不止有一个快照数据，一般称这种技术为行多版本技术，由此带来的并发控制，称为多版本并发控制（Multi Version Concurrency Control，MVCC）--（MVCC后面需要深入了解下）

    隔离级别为：READ-COMMITED 和 REPEATABLE-READ（默认隔离级别）时，用此种读取方式

#查询，设置隔离级别
select @@transaction_isolation;

set session transaction isolation level read committed;
set session transaction isolation level repeatable read ;

在session1中：

# 一：设置不自动提交，OFF
set @@autocommit=0 ;
show variables like '%autocommit%';

# 三：开始事务，查询数据结果为test
begin ;
select * from payment where id ='31';

# 五：再次查询数据,结果为test
select * from payment where id ='31';

# 七：再次查询数据
# 此时为默认的隔离级别REPEATABLE READ，结果任然为test
# 若隔离级别为READ-COMMITTED，则：test999，（此时也违反了ACID里面的I隔离性）
select * from payment where id ='31';

# 八：提交事务
commit ;

# 九：再次查询数据，此时结果肯定为test999
select * from payment where id ='31';

在session2中：

# 二：设置不自动提交,OFF
set @@autocommit=0 ;
show variables like '%autocommit%';

# 四：开始事务,更新数据为test999
begin ;
update payment set serial = 'test999' where id = '31';

# 六：提交事务
commit ;

第七步可以看出REPEATABLE-READ和READ-COMMITTED俩种隔离级别的不同：

READ-COMMITTED读取的是该行版本最新的一个快照(session2已经提交了，有新的快照数据了)，所以为test999

REPEATABLE-READ读取的是事务开始时的行数据（session1的事务begin时为test），所以还是test

 

一致性锁定读：

     显示的对数据库读取操作也加锁（自己手动加锁）

    SELECT .... FOR UPDATE  （方式一）

    这种方式是对读操作也加了一个X锁（排他锁），因为排他锁的不兼容，所以其他事务不能再加任何锁

    此时数据库已是手动提交方式：

  session1：

# 一：开始事务（加上了X锁），查询数据结果为test
begin ;
select * from payment where id = '31' for update ;

session2：

# 二：开始事务,更新数据为test999---一直处于阻塞状态
begin ;
update payment set serial = 'test999' where id = '31';

 

SELECT ... LOCK IN SHARE MODE   （方式二）

对读取的行记录加S锁（共享锁），则其他事务可以再添加S锁，但不能添加X锁（排他锁）

验证不能加X锁：

session1：

# 一：开始事务（加上了S锁），查询数据结果为test
begin ;
select * from payment where id = '31' lock in share mode ;

session2：

# 二：开始事务,更新数据为test999---一直处于阻塞状态,因为update是加X锁的
begin ;
update payment set serial = 'test999' where id = '31';

验证可以加S锁：

session1：

# 一：开始事务（加上了S锁），查询数据结果为test
begin ;
select * from payment where id = '31' lock in share mode ;

session2：

# 二：开始事务,再次查需，此时再添加S锁，不处于阻塞状态
begin ;
select * from payment where id = '31' lock in share mode ;

  

自增长与锁：

  关于自增长列的锁

   

外键和锁：

  对于一个外键列，如果没有显示的对这个列加索引，InnoDB会自动对其加一个索引，这样来避免表锁

（因为InnoDB在使用索引的情况下，用的是行锁，而不使用索引的情况下，用的是表锁）

 分析：

外键未加索引时（默认主键加索引了）：

  session1：

一：                                                         
delete from son where son.id=100; //son表加行锁
delete from father where father.id=100;//father表主键等于100的加上了行锁 

三：

delete from father where faher.id=200;//sessoin2已经加了行锁，也会阻塞

session2：

二：

delete from father where father.id=200; //父表主键等于200的加上了行锁
delete from son where father.id=200;//没有加索引，则是锁表，session1中已经加了行锁，会阻塞

 

外键加索引后（默认主键加索引了）：

session1：

一：                                                         
delete from son where son.id=100; //son表主键等于100的加行锁
delete from father where father.id=100;//father表主键等于100的加上了行锁

三：

delete from father where faher.id=200;//这条记录已经被session2执行，但此时不会报错

session2:

二：

delete from father where father.id=200; //父表主键等于200的加上了行锁
delete from son where father.id=200;//加了索引，则是锁行，不会阻塞

 

对于外键的更新和插入，则首先需要查需父表中的记录，此时对于父表的SELECT操作，使用的时SELECT...LOCK IN SHARE MODE 方式，而 不是一致性非锁定读的方式

分析：

session1：
# 一：
delete from father where faher.id=200;//会在father表200这一行加X锁

session2：
# 二：
insert into son (sonId,fatherId) values (200,200);//插入son记录前，会先查询father200记录

使用一致性非锁定读时:
会读到father表200这条记录，则可以进行插入操作，最后father表中没有200这条记录，son中却有这条记录的外键，导致数据不一致
使用LOCK IN SHARE MODE时：
查询father表需要对father表中200这一行加S锁，但session1中已经添加了X锁，所以会阻塞，结果正常