2019-08-05 InnoDB isolation level

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

在MySQL 5.7.20中添加了transaction_isolation作为tx_isolation的别名，现在不推荐使用它，并在MySQL 8.0中删除。 应调整应用程序以优先于tx_isolation使用transaction_isolation。

set tx_isolation = 'read-committed' 解决了dirty read但是存在phantom read
set tx_isolation = 'repeatable-read' 解决了phantom read
通过给行加锁，阻止其他tx插入，更新，删除，解决了幻读。

serializable隔离级别解决了什么呢？
http://mysql.taobao.org/monthly/2017/06/07/
强制tx排序
Serializable（可串行化） 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。

#### 14.7.2.3 Consistent Nonlocking Reads

https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction.
repeatable-read isolation level 如果同一个tx内,本来读不到的， 但是后面执行了update或delete操作， 本来读不到的现在读到了。

isolation level

read-committed
repeatable-read
针对 locking-read, update, delete的表现不同，
read-committed 是 'semi consistent'半一致， 具体表现为先加锁，明确哪些行不修改， 则释放不修改的行的锁定。
repeatable-read不管修改不修改， 只要被加锁的行，只能在tx提交后或回滚或异常退出tx后释放对行的锁定。

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 WHEREcondition. This greatly reduces the probability of deadlocks, but they can still happen.

• For UPDATE statements, if a row is already locked, InnoDBperforms 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.

'semi-consistent'相对于 repeatable-read的'consistent'的叫法，
获取锁的时候表现一致， 但是read-committed会释放掉where条件不匹配的行锁。

mysql> select * from t;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    6 |
|    6 |
|    6 |
|    6 |
|    6 |
+------+
9 rows in set (0.00 sec)

mysql> show create table t;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> set transaction_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set i=6 where i=5;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> 
mysql> 
mysql> show engine innodb status/G

------------
TRANSACTIONS
------------
Trx id counter 152502
Purge done for trx's n:o < 152501 undo n:o < 0 state: running but idle
History list length 9
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479467061704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467059896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479467058992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 152501, ACTIVE 32 sec
2 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 5
MySQL thread id 24, OS thread handle 123145358168064, query id 3569 localhost root starting
show engine innodb status

一般生产数据库都采用 read-committed级别的隔离。