MySQL Transaction ISOLATION LEVEL

[email protected]>Select *  from dept1;    DEPTNO dname          LOC---------- -------------- -------------        Ten ACCOUNTING     NEW YORK         -  the       DALLAS         - SALES          CHICAGO         - OPERATIONS     BOSTON         - DBA            Bei Jing

[Email protected]>update dept1Setdeptno= + whereDname=' DBA ';1Row updated. [Email protected]>SELECTS.sid, S.serial#,  2      CaseBitand (T.flag, POWER (2, -))3         when 0  Then ' READ COMMITTED '  4        ELSE ' SERIALIZABLE '  5     END  asIsolation_level6   fromV$transaction T7  JOINV$session s onT.ADDR = s.taddr8   andS.sid = Sys_context (' USERENV ', ' SID ');SID SERIAL# Isolation_leve---------- ---------- -------------- A       5973READ COMMITTED

The Oracle database supports both transaction isolation levels of Read COMMITTED and serializable.
While MySQL supports READ uncommited,read commited,repeatable read,serializable Four transaction isolation levels

READ UNCOMMITTED (Read UNCOMMITTED content)
At the READ UNCOMMITTED isolation level, all things can "see" the execution results of uncommitted things. That is, dirty reads (Read UNCOMMITTED transactions)

Read commited (read submit content)
The default isolation level for Oracle. When a thing starts, it can only "see" the changes that have been made to the firm, and any changes to the data that have been made before the start of a transaction are not visible unless they have been submitted. This isolation level is also non-repeatable read

Repeatable READ (can be reread)
The repeatable read isolation level addresses the problem caused by the READ UNCOMMITTED isolation level. It ensures that an instance of the same transaction "sees" the same data row when it reads data concurrently. In theory, however, this can lead to another tricky problem: Phantom reading. In simple terms, phantom reading refers to the fact that when a user reads a range of data rows, another transaction inserts a pay line within that range, and when the user reads the data row for that range again, a new "restore" row is found.

SERIALIZABLE (Serializable)
Serializable is the highest level of isolation, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another.

The default isolation level for MySQL is:

([email protected]) [fandb]> select @@tx_isolation;+-----------------+| @@tx_isolation  |+-----------------+| REPEATABLE-READ |+-----------------+

session A:([email protected]) [fandb]> begin;Query OK, 0 rows affected (0.00 sec)([email protected]) [fandb]> update per1 set name=‘fan1‘ where id=1    -> ;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0([email protected]) [fandb]> select * from per1 limit 1;+----+------+| id | name |+----+------+|  1 | fan1 |+----+------+1 row in set (0.00 sec)A会话更新一行

session B:([email protected]) [fandb]> begin;Query OK, 0 rows affected (0.00 sec)([email protected]) [fandb]> select * from per1 limit 1;+----+------+| id | name |+----+------+|  1 | fan  |+----+------+1 row in set (0.00 sec)此时在B开始事务并查询，id=1的name列并没有变化

sessionA:(mysql@localhost) [fandb]> commit;QueryOKrowsaffected (0.00sec)接着A会话提交

session B:([email protected]) [fandb]> select * from per1 limit 1;+----+------+| id | name |+----+------+|  1 | fan  |+----+------+1 row in set (0.00 sec)在去B会话查询，还是没有变化

([email protected]) [fandb]> commit;Query OK, 0 rows affected (0.00 sec)([email protected]) [fandb]> select * from per1 limit 1;+----+------+| id | name |+----+------+|  1 | fan1 |+----+------+1 row in set (0.00 sec)只有当B会话事务结束，再次查询记录才会变化

MySQL Transaction ISOLATION LEVEL