mysql 隔离级别

 

The isolation level is used for operations on InnoDB tables.

SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

access_mode: {
     READ WRITE
   | READ ONLY
}

SET global TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 

1.READ UNCOMMITTED

2.READ COMMITTED

3.REPEATABLE READ

SET global TRANSACTION ISOLATION LEVEL REPEATABLE READ;

当两个事务同时发起的时候，即使session 1已经commit; session2没有结束事务之前，读到的数据是begin开始的。session 1做了修改的数据，session 2没看到。

4.SERIALIZABLE

当两个事务同时发起的时候，即使select语句，也会锁定行锁，阻塞session 2 update,insert (整张表）

SERIALIZABLE

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

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)