InnoDB 锁

INNODB的几种锁

共享锁（S-LOCKING）允许一个事务去读一行,阻止其它事务获得相同数据集的排它锁

排它锁（X-LOCKING）允许获得排它锁的事务更新数据,阻止其它事务取得相同数据集的共享读锁和排它锁

INNODB还独有实现了2种锁

意向共享锁（IS)事务打算给数据行加共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁

意向独占锁（IX）事务打算给数据行加排它锁,事务在给一个数据行加排它锁前必须先取得该表的IX锁

Innodb 行级别的锁基于索引实现的支持并发和一致性

注意：

1）  在不通过索引条件查询的时候,innodb使用的是表锁

2）  由于MySQL的行锁针对索引加锁,不是针对记录加的锁,所以虽然时访问不同行的记录,但是如果是使用相同的索引键,则会出现锁冲突

加共享锁:select * from xx where ,….. lock in share mode

加排它锁:select * from xx where ….. for update，update delete 也是加排它锁

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

可以通过show full processlist,show engine innodb status等命令查看锁状态

也可以从视图查看锁 事务状态 information_schma 库下面

innodb_trx  innodb_locks innodb_lock_waits

innodb_trx

Trx_id:innodb存储引擎内部唯一事务ID

Trx_state：当前事务的状态

Trx_started：事务开始时间

Trx_wait_started：事务开始等待时间

Trx_mysql_thread_id MySQL中的线程ID show processlist 显示结果

Trx_query：事务运行的SQL语句

innodb_locks

Lock_id：锁的ID

Lock_trx_id：事务ID

LOCK_MODE:锁的模式

LOCK_TYPE:所得类型表锁还是行锁

LOCK_TABLE：要加锁的表

LOCK_INDEX:锁的索引

LOCK_SPACE：innodb存储引擎表空间ID号

LOCK_PAGE：被锁住的页的数量，若是表锁，则该值为null

LOCK_REC：被锁住行的数量，若是表锁则该值为NULL

LOCK_DATA:被锁住的行的主键值，若是表锁时，则该值为NULL;

通过select* from information_schema.INNODB_LOCK可查看

innodb_lock_waits

Requesting_trx_id：申请资源的事务ID

Request_lock_id：申请锁的ID

Blocking_trx_id：阻塞锁的ID

哪个事务被哪个事务阻塞很明显通过该innodb_lock_waits看

一致性非锁定在MVCC读取当前数据库里面的数据在读取的数据正在被修改不会产生锁等待（对当前数据拍照片）读没有加锁 没有加共享锁 没有被阻塞

访问不同的记录不会发生等待 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然时访问不同的行记录。但是如果是使用相同的索引键，会出现锁冲突的，应用设计的时候要注意

不同隔离级别,和不同索引类型的加锁处理分析

结论：

RR   2.innodb_locks_unsafe_for_binlog=0

1.任何辅助索引上的锁，或者非索引列上的锁，最终都要回溯到主键上，在主键上也要加一把锁

2.任何叶子节点上的S或X锁之前，都会在根节点上加一个IS或IX锁，也就是表级别的IS,IX锁

3.主键索引=record lock（但外键约束，唯一性约束检测仍然使用 gap lock）

4.唯一辅助索引=record lock（但外键约束，唯一性约束检测仍然使用 gap lock）

5.非唯一辅助索引=next-key-lock(RC隔离级别=record lock）

Recordlock：单个记录上的锁，至少锁定一行记录；

Gap lock（间隙锁）:在索引记录间隙上的锁，或者是第一条索引记录之前，最后一条索引记录之后上的间隙锁（两条记录中间的缝隙） 锁定两个记录中间的缝隙；

Next-keylock（下一键锁）索引记录锁以及索引记录之间的间隙锁，二者的组合锁；

记录锁至少锁定一条记录（普通，主键，唯一 索引）或是无任何索引innodb会对rowid加锁（左右两边加自身的记录）；

设置RC隔离级别或者是启用innodb_lock_unsafe_for_binlog的其他影响

1.在mysql评估完where条件后，会释放找不到相应记录的记录锁

2.在update语句中，innodb使用“半一致性读“，会返回提交后的最新版本号，以便判是否匹配update语句中的where条件

gap lock避免幻读：

假设一个SQL：select * from child where id>=100 for update；

Id字段当前有2个值：90,102 这时候gap是90----102之间，如果只有recode lock 就没办法再阻止101这个id （就会发生幻读再次读取后可以看到101这个id值）；

有了next-key lock后，可以阻止写入101这个id确保两次读取的结果是一样的，不会发生幻读；

有唯一属性索引时，就无需使用gap lock（扫描包含多个字段的唯一索引中的部分字段除外）；

还有一种叫做意向插入（insertionintention）的gap lock，如果两个事务往同一个gap lock中写入数据，但写入位置不一样时，是无须等待，可以直接写入因此没有冲突

设定pkid =3

T1：insert into t（pkid）values（4）

T2：insert into t （pkid） values（5）

Gap lock仅用于防止往gap上写入新记录（避免幻读），因此无论是S-GAP 还是X-GAP锁其实作用是一样的。

Innode引擎监控的开启的方法   
锁监控：
打开innodb的锁监控：
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;    
5.6.16可以使用：  --两个都需要打开
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON; 
表空间监控：  
打开innodb表空间监控：
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;
表监控：
打开innodb表监控：
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
打开监视器以后
innodb_monitor和innodb_lock_monitor会每隔15秒会向错误日志中记录InnoDB监控信息；
innodb_table_monitor和innodb_tablespace_monitor是每隔64秒；
innodb_monitor和innodb_lock_monitor两种监视器的输出结果基本类似，后者会有更多关于锁的信息，而前一个实际上就是show innodb status；
innodb_table_monitor会将系统中所有innodb的表的一些结构和内部信息输出；
innodb_tablespace_monitor输出的是tablespace的信息，注意该monitor输出的只是共享表空间的信息，如果使用innodb_file_per_table为每个表使用独立的表空间，则这些表空间的信息是不会包含在输出中的。
停止InnoDB监控
drop table innodb_monitor;
drop table innodb_lock_monitor;
drop table innodb_table_monitor;
drop table innodb_tablespace_monitor;
重点的锁类型
如果辅助索引上的搜索及锁定是排它的，则会取回其相应的聚集索引，并且在它上面加锁；
对无索引的字段检索更新时升级成表级别锁（表中全部记录被锁，除非在RC或innodb_locks_unsafe_for_binlog=1 模式下 采用semi-consitent read机制）；
insert into T select … from S where T表上排它record lock 事务隔离级别为RC或者启用innodb_locks_unsafe_for_binlog并且隔离级别不是serializable时，S表上采用无锁一致性读，否则（rr），加排它next-key lock（RC不加锁。RR加next-key lock）；
insert 排它record lock，而非next-key lock，但在写入新记录之前需要加意向gap lock（insertion intention gap lock）；
insert…on duplicate key update 排它next-key lock（即使被update的记录上）会同时并发执行；
create table…select 和insert…select 一样；
replace 没冲突/重复时 和insert一样 否则（有冲突时先delete后insert）加next-key-lock；
replace into t select … from S where 或者update T … where col IN（SELECT…FROM S..），都会在S表上加next-key lock；
auto..increment列上写新数据时，索引末尾设置排它锁，请求自增列计数器时，INNODB使用一个AUTO-INC表锁，只对请求的那个SQL有影响，不会影响整个事务，该锁被持有时，其他会话不能往INNODB表中写入新行；
select…from 一致性非锁定读除非是serializable隔离级别，在其影响的索引记录上设置一个共享锁（简单的select…from是不加锁的）；
lock in shared mode，使用共享next-key lock；
for update使用排它next-key lock锁，会阻止lock in shared mode请求；
update/delete,排它next-key lock.
死锁 
死锁不会卡,有一个会立刻回滚,再次提交即可,show engine innodb status 只显示最后死锁的信息，设置innodb_print_all_deadlocks=1，在日志中记录全部死锁信息；
自动检测死锁，并优先回滚最小事务（影响较小的事务）,加表锁时，不会发生死锁；
事务中如果select调用存储函数/存储过程失败了，对用的SQL会回滚事务，如果再显示执行ROLLBACK，那么整个事务都回滚；
事务回滚时，会释放全部的锁，个别情况下，如果个别SQL因为某些错误回滚事务的话它所持有的行锁可能无法释放，因为INNODB的行锁信息并没有记录时那个SQL持有的，这时候建议执行一次显示的ROLL BACK。
避免死锁
事务尽快提交，小事务越不容易发生死锁；
加for update lock in shared mode读锁时最好降低事务隔离级别，例如用RC级别降低死锁发生概率；
事务中涉及多个表，或者涉及多行记录时，每个事务的操作顺序都要保持一致，降低死锁发生概率，最好用存储过程/存储函数固化；
通过索引等方式优化SQL效率，降低死锁发生概率,减少扫描/锁范围，降低概率。