innodb锁和事物

• InnoDB存储引擎支持行级锁，其大类可以细分为共享锁和排它锁两类
• 共享锁(S)：允许拥有共享锁的事务读取该行数据。当一个事务拥有一行的共享锁时，另外的事务可以在同一行数据也获得共享锁，但另外的事务无法获得同一行数据上的排他锁
• 排它锁(X)：允许拥有排它锁的事务修改或删除该行数据。当一个事务拥有一行的排他锁时，另外的事务在此行数据上无法获得共享锁和排它锁，只能等待第一个事务的锁释放
• 除了共享锁和排他锁之外，InnoDB也支持意图锁。该锁类型是属于表级锁，表明事务在后期会对该表的行施加共享锁或者排它锁。

意图锁也有两种类型：
• 共享意图锁(IS)：事务将会对表的行施加共享锁
• 排他意图锁(IX)：事务将会对表的行施加排它锁

例:

select … for share mode语句就是施加了共享意图锁，而select … for update语句就是施加了排他意图锁
• 这四种锁之间的相互共存和排斥关系如下

 

InnoDB锁相关系统表

Information_schema.innodb_trx记录了InnoDB中每一个正在执行的事务，包括该事务获得的锁信息，事务开始时间，事务是否在等待锁等信息

Information_schema.innodb_locks记录了InnoDB中事务在申请但目前还没有获取到的每个锁信息，以及当前事务的锁正在阻止其他事务获得锁

Information_schema.innodb_lock_waits记录了InnoDB中事务之间相互等待锁的

 

• 行级锁
• 行级锁是施加在索引行数据上的锁

当一个InnoDB表没有任何索引时，则行级锁会施加在隐含创建的聚簇索引上，所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁

• 间隔锁
• 当 我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件 的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁
• 间隔锁是施加在索引记录之间的间隔上的锁，锁定一个范围的记录、但不包括记录本身,比如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE语句，尽管有可能对c1字段来说当前表里没有=15的值，但还是会阻止=15的数据的插入操作，是因为间隔锁已经把索引查询范围内的间隔数据也都锁住了

• 间隔锁的使用只在部分事务隔离级别才是生效的
• 间隔锁只会阻止其他事务的插入操作

gap lock的前置条件：
• 1 事务隔离级别为REPEATABLE-READ，innodb_locks_unsafe_for_binlog参数为0，且sql走的索引为非唯一索引（无论是等值检索还是范围检索）
2 事务隔离级别为REPEATABLE-READ，innodb_locks_unsafe_for_binlog参数为0，且sql是一个范围的当前读操作，这时即使不是非唯一索引也会加gap lock

例：

链接1：

mysql> update temp set name='abc' where id>4;
• 链接2：
• mysql> insert into temp values(4,‘abc’); ##等待

• Next-key锁
• 在默认情况下，mysql的事务隔离级别是可重复读，并且innodb_locks_unsafe_for_binlog参数为0，这时默认采用next-key locks。所谓Next-Key Locks，就是记录锁和间隔锁的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。

• 插入意图锁
• 插入意图锁是在插入数据时首先获得的一种间隔锁，对这种间隔锁只要不同的事务插入的数据位置是不一样的，虽然都是同一个间隔，也不会产生互斥关系

例：

• 比如有一个索引有4和7两个值，如果两个事务分别插入5和6两个值时，虽然两个事务都会在索引4和7之间施加间隔锁，但由于后续插入的数值不一样，所以两者不会互斥

• 比如下例中事务A对索引>100的值施加了排他间隔锁，而事务B在插入数据之前就试图先施加插入意图锁而必须等待

• 自增锁
• 自增锁是针对事务插入表中自增列时施加的一种特殊的表级锁，即当一个事务在插入自增数据时，另一个事务必须等待前一个事务完成插入，以便获得顺序的自增值
• 参数innodb_autoinc_lock_mode可以控制自增锁的使用方法

•innodb死锁

• 死锁的情况发生在不同的的事务相互之间拥有对方需要的锁，而导致相互一直无限等待

• 死锁可能发生在不同的事务都会对多个相同的表和相同的行上施加锁，但事务对表的操作顺序不相同

• 为了减少死锁的发生，要避免使用lock table语句，要尽量让修改数据的范围尽可能的小和快速；当不同的事务要修改多个表或者大量数据时，尽可能的保证修改的顺序在事务之间要一致
• 默认情况下InnoDB下的死锁自动侦测功能是开启的，当InnoDB发现死锁时，会将其中的一个事务作为牺牲品回滚。（InnoDB选择牺牲的事务往往是代价比较小的事务，其代价计算是根据事务insert,update, delete的数据行规模决定）

• 可以通过show engine innodb status语句查看最后一次发生死锁的情况（可以通过innodb_print_all_deadlocks参数将死锁信息打印到错误日志中）

减少死锁的发生

• 尽可能的保持事务小型化，减少事务执行的时间可以减少发生影响的概率
• 及时执行commit或者rollback，来尽快的释放锁
• 可以选用较低的隔离级别，比如如果要使用select... for update和select...lock in share mode语句时可以使用读取提交数据隔离级别
• 当要访问多个表数据或者要访问相同表的不同行集合时，尽可能的保证每次访问的顺序是相同的。比如可以将多个语句封装在存储过程中，通过调用同一个存储过程的方法可以减少死锁的发生

• 增加合适的索引以便语句执行所扫描的数据范围足够小
• 尽可能的少使用锁，比如如果可以承担幻读的情况，则直接使用select语句，而不要使用select...for update语句

• 如果没有其他更好的选择，则可以通过施加表级锁将事务执行串行化，最大限度的限制死锁发生

 

InnoDB事务隔离级别

• InnoDB存储引擎提供了四种事务隔离级别，分别是：
• READ UNCOMMITTED：读取未提交内容
• READ COMMITTED：读取提交内容
• REPEATABLE READ：可重复读，默认值。
• SERIALIZABLE：串行化

更改事物隔离级别语法格式为：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}

• REPEATABLE READ：可重复读，默认值。表明对同一个事务来说第一次读数据时会创建快照，在事务结束前的其他读操作(不加锁)会获得和第一次读相同的结果。当读操作是加锁的读语句(select … for update或者lock in share mode),或者update和delete语句时，加锁的方式依赖于语句是否使用唯一索引访问唯一值或者范围值

• 当访问的是唯一索引的唯一值时，则InnoDB会在索引行施加行锁

• 当访问唯一索引的范围值时，则会在扫描的索引行上增加间隔锁或者next-key锁以防止其他链接对此范围的插入

注：

对可重复读隔离级别来说，第一个事务的修改会在每行记录上都增加排他锁，并且直到事务结束后锁才会释放

第二个事务会一直等待前面事务的锁被释放后才能执行

• READ COMMITTED：读取提交内容。意味着每次读都会有自己最新的快照。对于加锁读语句(select … for update和lock in share mode)，或者update，delete语句会在对应的行索引上增加锁，但不像可重复读一样会增加间隔锁，因此其他的事务执行插入操作时如果是插入非索引行上的数值，则不影响插入。
• 由于该隔离级别是禁用间隔锁的，所以会导致幻读的情况
• 如果是使用此隔离级别，就必须使用行级别的二进制日志

此隔离级别还有另外的特点：
• 对于update和delete语句只会在约束条件对应的行上增加锁
• 对update语句来说，如果对应的行上已经有锁，则InnoDB会执行半一致读的操作，来确定update语句对应的行在上次commit之后的数据是否在锁的范围，如果不是，则不影响update操作，如果是，则需要等待对应的锁解开

注：

对读取提交内容事务隔离级别来说，第一个修改操作会在所有行上都加排他锁，但会在确定不修改上的行上释放对应的锁

第二个事务通过半一致读的方式判断每行的最后commit的数据是否在修改的范围里，会在未加锁的行上加上排他锁

• READ UNCOMMITTED：读取未提交内容，所读到的数据可能是脏数据
• SERIALIZABLE：串行化，此隔离级别更接近于可重复读这个级别，只是当autocommit功能被禁用后，InnoDB引擎会将每个select语句隐含的转化为select … lock in share mode

一致读

在默认的隔离级别下一致读是指InnoDB在多版本控制中在事务的首次读时产生一个镜像，在首次读时间点之前其他事务提交的修改可以读取到，而首次读时间点之后其他事务提交的修改或者是未提交的修改都读取不到

• 唯一例外的情况是在首次读时间点之前的本事务未提交的修改数据可以读取到
• 在读取提交数据隔离级别下，一致读的每个读取操作都会有自己的镜像
• 一致读操作不会施加任何的锁，所以就不会阻止其他事务的修改动作

• 一致读在某些DDL语句下不生效：
• 碰到drop table语句时，由于InnoDB不能使用被drop的表，所以无法实现一致读
• 碰到alter table语句时，也无法实现一致读
• 当碰到insert into… select, update … select和create table … select语句时，在默认的事务隔离级别下，语句的执行更类似于在读取提交数据的隔离级别下

. InnoDB 的MVCC

InnoDB 的 MVCC ，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一把保存了行的创建时间，一个保存行的过期时间(或删除时间)，当然存储的并不是真正的时间，而是系统版本号。每开始一个事务，系统版本号就会自动递增，事务开始时刻的版本号作为当前事务的版本号，用来和查询到的每行记录的版本号就行比较。

以下是 REPEATABLE READ 的隔离级别下具体操作：

• SELECT InnoDB 会根据以下两个条件检查每行记录： a. InnoDB 只查询版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版号），这样可以确保事务读取的行，要么是在事务开始前的已经存在的，要么是事务自身插入或者修改过的。 b. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。 只有符合上述两个条件的记录，才能返回作为查询结果
• INSERT InnoDB 为新插入的每一行保存当前系统版本号作为行版本号
• DELETE InnoDB 为删除的每一行保存当前系统版本号作为行删除标识
• UPDATE InnoDB 为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

保存着两个额外的系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行

 

Autocommit/commit/rollback

• 当设置autocommit属性开启时，每个SQL语句都会隐含成为独立的事务。
• 默认情况下autocommit属性是开启的，也就意味着当每个SQL语句最后执行结果不返回错误时都会执行commit语句，当返回失败时会执行rollback语句
• 而当autocommit属性开启时，可以通过执行start transaction或者begin语句来显示的开启一个事务，而事务里可以包含多个SQL语句，最终事务的结束是由commit或者rollback终结
• 而当在数据库链接里执行set autocommit=0代表当前数据库链接禁止自动提交，事务的终结由commit或者rollback决定，同时也意味着下一个事务的开始
• 如果一个事务在autocommit=0的情况下数据库链接退出而没有执行commit语句，则这个事务会回滚
• 一些特定的语句会隐含的终结事务，就好比是执行了commit语句
• commit语句代表将此事务的数据修改永久化，并对其他事务可见，而rollback则代表将此事务的数据修改回滚。
• commit和rollback都会把当前事务执行所施加的锁释放