Mysql事务隔离级别_MySQL

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

登录后复制

Mysql默认的隔离级别是：

(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 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)
此时在B开始事务并查询，id=1的name列并没有变化

登录后复制

session A:
(mysql@localhost) [fandb]> commit;
Query OK, 0 rows affected (0.00 sec)
接着A会话提交

登录后复制

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

登录后复制

(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)
只有当B会话事务结束，再次查询记录才会变化

登录后复制