Mysql transaction isolation level _ MySQL

Scott @ PRODselect * fromdept1; DEPTNODNAMELOC-------------------------------------10ACCOUNTINGNEWYORK20RES

scott@PROD>select * from dept1;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        50 OPERATIONS     BOSTON        20 DBA            Bei Jing

scott@PROD>update dept1 set deptno=21 where dname='DBA';1 row updated.scott@PROD>SELECT s.sid, s.serial#, 2 CASE BITAND(t.flag, POWER(2, 28)) 3 WHEN 0 THEN 'READ COMMITTED' 4 ELSE 'SERIALIZABLE' 5 END AS isolation_level 6 FROM v$transaction t 7 JOIN v$session s ON t.addr = s.taddr 8 AND s.sid = sys_context('USERENV', 'SID'); SID SERIAL# ISOLATION_LEVE---------- ---------- -------------- 41 5973 READ COMMITTED

The Oracle Database Supports read committed and SERIALIZABLE transaction isolation levels.
Mysql supports four transaction isolation levels: read uncommited, read commited, repeatable read, and SERIALIZABLE.

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

Read commited (READ submitted content)
The default isolation level of ORACLE. When a transaction starts, it can only "see" the changes that have been committed by the transaction. any data changes made by a transaction before it starts to be committed are invisible unless it has been committed. This isolation level is not readable.

Repeatable read (repeable)
The repeatable read isolation level solves the problems caused by the read uncommitted isolation level. It ensures that an instance of the same transaction reads data concurrently and "sees" the same data row. However, theoretically, this will lead to another tricky problem: Phantom reading. In short, Phantom read refers to when a user reads data in a certain range, another transaction inserts a salary row in this range. when the user reads data in this range, A new "restore" line is found.

SERIALIZABLE (SERIALIZABLE)
Serializable is the highest level of isolation. it forces transaction sorting to make it impossible to conflict with each other, thus solving the Phantom read problem.

The default isolation level of Mysql is:

(mysql@localhost) [fandb]> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+

Session A :( mysql @ localhost) [fandb]> begin; Query OK, 0 rows affected (0.00 sec) (mysql @ localhost) [fandb]> update per1 set name = 'fan1' where id = 1->; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 (mysql @ localhost) [fandb]> select * from per1 limit 1; + ---- + ------ + | id | name | + ---- + ------ + | 1 | fan1 | + ---- + ------ + 1 row in set (0.00 sec) A session updates A row

Session B :( mysql @ localhost) [fandb]> begin; Query OK, 0 rows affected (0.00 sec) (mysql @ localhost) [fandb]> select * from per1 limit 1; + ---- + ------ + | id | name | + ---- + ------ + | 1 | fan | + ---- + ------ + 1 row in set (0.00 sec) at this time, the transaction starts and queries at B. the name column of id = 1 does not change.

Session A :( mysql @ localhost) [fandb]> commit; Query OK, 0 rows affected (0.00 sec) then A session is submitted

Session B :( mysql @ localhost) [fandb]> select * from per1 limit 1; + ---- + ------ + | id | name | + ---- + ------ + | 1 | fan | + ---- + ------ + 1 row in set (0.00 sec) query data in Session B, still unchanged

(Mysql @ localhost) [fandb]> commit; Query OK, 0 rows affected (0.00 sec) (mysql @ localhost) [fandb]> select * from per1 limit 1; + ---- + ------ + | id | name | + ---- + ------ + | 1 | fan1 | + ---- + ------ + 1 row in set (0.00 sec) only when the B session transaction ends, record query will change