innodb-四种隔离级别

四种隔离级别

1.innodb有四种标准的隔离级别：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.默认隔离级别是REPEATABLE READ

InnoDB offers all four transaction isolation levels described by the SQL:1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level for InnoDB is REPEATABLE READ.

2.用户可以为一个session中或所有的connection设置事务隔离级别属性

A user can change the isolation level for a single session or for all subsequent connections with the SET TRANSACTION statement.

3.innodb在不同的隔离级别下使用不同的锁策略，使用默认的RR级别对ACID要求合规的重要数据可以基本满足高度一致性。

InnoDB supports each of the transaction isolation levels described here using different locking strategies. You can enforce a high degree of consistency with the default REPEATABLE READ level, for operations on crucial data where ACID compliance is important.

4.如果对数据的一致性要求不高，可以使用较为松式的策略，例如:RC、RU

Or you can relax the consistency rules with READ COMMITTED or even READ UNCOMMITTED, in situations such as bulk reporting where precise consistency and repeatable results are less important than minimizing the amount of overhead for locking

5.innodb-SERIALIZABLE隔离级别是一种最严格的规则，一般用于XA事务或解决并发死锁故障

SERIALIZABLE enforces even stricter rules than REPEATABLE READ, and is used mainly in specialized situations, such as with XA transactions and for troubleshooting issues with concurrency and deadlocks.

RR级别：

特性1.一致性读的方式是在第一次读时建立快照

Consistent reads within the same transaction read the snapshot established by the first read.

特性2.在锁定读、update、delete时，加锁方式依赖于查询的方式，即是否是根据唯一索引查询， 如果是则锁定行，如果不是则可能会产生间隙锁

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 14.7.1, “InnoDB Locking”.

RC级别：

特性1.一致性读的方式是每次读取和更新都会刷新快照

sets and reads its own fresh snapshot

特性2.在锁定读、update、delete时，加锁方式是只锁定索引记录，不锁间隙

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), 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

特性3.在RC级别下binlog模式只支持row-based，即使配置了binlog_format=MIXED，实际也是采用row-based

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.

特性4.在RC级别下，根据非索引字段更新，只会锁定where条件下对应的行，不会锁定全表行记录

If READ COMMITTED is used instead, the first UPDATE acquires an x-lock on each row that it reads and releases those for rows that it does not modify

在RC级别下，因为没有间隙锁的保护，这时会出现幽灵问题，也可以叫做幻读

Because gap locking is disabled, phantom problems may occur, as other sessions can insert new rows into the gaps. For information about phantoms

RC级别作用和启用innodb_locks_unsafe_for_binlog配置类似，但两者也有区别：
1.innodb_locks_unsafe_for_binlog可以被设置成全局/所有session，但RC级别可以设置为所有session/单个session

Enabling innodb_locks_unsafe_for_binlog is a global setting and affects all sessions, whereas the isolation level can be set globally for all sessions, or individually per session.

2.innodb_locks_unsafe_for_binlog只能在mysql服务启动前设置，RC级别可以在服务运行时设置

innodb_locks_unsafe_for_binlog can be set only at server startup, whereas the isolation level can be set at startup or changed at runtime.

RU级别：

特性1.读取不具有一致性，存在脏读问题

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.

SERIALIZABLE级别：

特性1.在atuocommit关闭的情况下，所有的select操作都会加共享锁

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT … LOCK IN SHARE MODE if autocommit is disabled.