MySQL事务隔离级别

数据库事务

MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关。

  1. MyISAM:不支持事务,用于只读程序提高性能
  2. InnoDB:支持ACID事务、行级锁、并发
  3. Berkeley DB:支持事务

一 事务的四种隔离级别

隔离级别 脏读 不可重复读 幻读
未提交读(Read uncommitted) 可能 可能 可能
已提交读(Read committed) 不可能 可能 可能
可重复读(Repeatable read) 不可能 不可能 可能
可串行化(Serializable ) 不可能 不可能 不可能
  1. 未提交读(Read Uncommitted)

    • 允许脏读,也就是可能读取到其他会话中未提交事务修改的数据
  2. 提交读(Read Committed)

    • 只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别
    • 同一个事务中,同一 SELECT 可能返回不同结果
    • 在RC级别中,数据的读取都是不加锁的,但是数据的写入、修改和删除是需要加锁的
  3. 可重复读(Repeated Read)
    • InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读
  4. 串行读(Serializable)
    • 完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

不可重复读

A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

不可重复读,是指在数据库访问中,一个事务范围内两个相同的查询返回了不同数据。

幻读

A phantom read occurs when, in the course of a transaction, new rows are added by another transaction to the records being read.

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

幻读问题是指一个事务的不同时刻的相同查询返回了不同的的结果集。例如:一个 select 语句执行了两次,但是在第二次返回了第一次没有返回的行,那么这些行就是“phantom” row。

二 事务相关SQL

  1. 事务的控制

    • 开启事务
      BEGIN; 或者 START TRANSACTION;
    • 提交事务
      COMMIT;
    • 回滚事务
      ROLLBACK;
  2. 事务的隔离级别

1
2
3
4
5
6
7
8
9
10
11
--语法
-- SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
-- isolation-level 可选值:
-- READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 查看事务隔离级别
select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;

不带SESSION、GLOBAL的SET命令只对下一个事务有效
SET SESSION为当前会话设置隔离模式
SET GLOBAL为以后新建的所有MYSQL连接设置隔离模式(当前连接不包括在内)

三 一致性读

一致性读,又称为快照读。使用的是MVCC机制读取undo中的已经提交的数据。所以它的读取是非阻塞的。

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction.
Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.

MYSQL官方资料

RC下的一致性读

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.
事务中每一次读取都是以当前的时间点作为判断是否提交的时间点

RR下的一致性读

Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.

RR隔离级别下的一致性读,不是以begin开始的时间点作为snapshot建立时间点,而是以第一条select语句的时间点作为snapshot建立的时间点。
RR隔离级别下的一致性读,是以第一条select语句的执行点作为snapshot建立的时间点的,即使是不同表的select语句。
本事务中进行修改的数据,即使没有提交,在本事务中的后面也可以读取到。update 语句因为进行的是“当前读”,所以它可以修改成功。

一致性读是指普通的select语句,不带 for update, in share mode 等等子句。使用的是undo中的提交的数据,不需要使用锁(MDL除外)。
而当前读,是指update, delete, select for update, select in share mode等等语句进行的读,它们读取的是数据库中的最新的数据,并且会锁住读取的行和gap(RR隔离时)。如果不能获得锁,则会一直等待,直到获得或者超时。
RC隔离级别的当前读没有gap lock,RC的update语句进行的是“半一致性读”,和RR的update语句的当前读不一样。

四 Autocommit

Normally, MySQL runs in autocommit mode. Each query you run is effectively isolated in a transaction. You can think of it as adding START TRANSACTION and COMMIT to each of your queries for you.

The autocommit variable is local to a single session, so changing the mode will affect only queries run from your session and only for as long as your session is connected.

If you turn autocommit off, you will not need to call START TRANSACTION to start a transaction. It is very important, though, that you remember to call COMMIT periodically to commit any changes you have made to the database.

五 参考

Innodb中的事务隔离级别和锁的关系
MySQL 一致性读 深入研究