innodb 中的锁以及事务隔离级别底层原理

事务隔离级别

写在前面：好烦啊，事务隔离级别底层原理，在网上搜索了N篇博客，写的都不相同。搞得我心烦意乱，算了直接看官网了。

mysql innodb 事务隔离级别原理官网：MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels

REPEATABLE READ

• This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 15.7.2.3, “Consistent Nonlocking Reads”.

  For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.

  • For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

  • For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.

可重读是数据库默认的隔离级别。

对于读操作使用的是mvcc，但是事务中间的所有select 操作读的都是事务开始的第一次select 版本。 如下图

  

 对于加锁的读（SELECT with FOR UPDATE or FOR SHARE）或者update，delete，加锁方式依赖于where 条件后查出的记录数。

如果是唯一的值，innodb用的行记录锁，并不会用间隙锁或者临建锁。

如果不是唯一值，innodb 会用间隙锁或者临建锁，不但锁住记录还锁住间隙。

READ COMMITTED

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads, see Section 15.7.2.3, “Consistent Nonlocking Reads”.

For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

Because gap locking is disabled, phantom row problems may occur, as other sessions can insert new rows into the gaps. For information about phantom rows, see Section 15.7.4, “Phantom Rows”.

Only row-based binary logging is supported with the READ COMMITTED isolation level. If you use READ COMMITTED with binlog_format=MIXED, the server automatically uses row-based logging.

Using READ COMMITTED has additional effects:

• For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.

• For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.

读已提交

对于读操作 ,每次读的都是当前的数据版本。如下图

  对于加锁的读（SELECT with FOR UPDATE or FOR SHARE）或者update，delete。innodb 只锁记录，不锁间隙。间隙锁定仅用于外键约束检查和重复键检查。

对于更新删除操作，innodb仅锁住他需要更新或者删除的记录和update/delete后面where条件查询的记录，其他的记录不会锁住，间隙更不会锁住，这样大大减少了死锁。

• READ UNCOMMITTED

  SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.

读未提交

对于读不加任何锁。

对于写和读已提交一样。

SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

串行化

   串行化像可重复读一样，但是如果自动提交关闭的 话，innodb 会隐式的把普通的select 转为select for share 模式。如果自动提交开启，select 有它自己的事务。

Innodb中的锁

innodb中的锁可以分为7中类型（实际8种）

共享锁和排他锁，记录锁，间隙锁，临建锁，插入意向锁，自增锁，意向锁。