> For the complete documentation index, see [llms.txt](https://john-mao.gitbook.io/my-handbook/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://john-mao.gitbook.io/my-handbook/mysql/mysqlshi-wu-ge-li-ji-bie.md). # MySQL事务隔离级别 MySQL四种事务隔离级别 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | | :--: | :-: | :---: | :-: | | 未提交读 | 可能 | 可能 | 可能 | | 已提交读 | 不可能 | 可能 | 可能 | | 可重复读 | 不可能 | 不可能 | 可能 | | 可串行化 | 不可能 | 不可能 | 不可能 | * 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据 * 已提交读(Read Committed)：只能读取到已经提交的数据。**Oracle等多数数据库默认都是该级别 (不重复读)** * 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读（**MySQL默认隔离级别为可重复读**） * 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻 **创建测试数据库，并插入数据：** ``` mysql> desc account; +--------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | amount | float | YES | | NULL | | +--------+---------+------+-----+---------+-------+ 2 rows in set mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 1000 | | 2 | 1000 | +----+--------+ 2 rows in set ``` 我们打开2个终端模拟2个用户操作。 **未提交读(Read Uncommitted)** 用户A操作如下： ``` mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected mysql> start transaction; Query OK, 0 rows affected mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 1000 | | 2 | 1000 | +----+--------+ 2 rows in set ``` 用户B操作如下（不提交事务）： ``` mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected mysql> start transaction; Query OK, 0 rows affected mysql> update account set amount=amount+200 where id = 1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 ``` 用户A查询数据： ``` mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 1200 | | 2 | 1000 | +----+--------+ 2 rows in set ``` 我们将事务隔离级别设置为Read Uncommitted的时候，即使事务并没有commit，但是我们仍然能够读到未提交的数据。所以这是所有隔离级别中最低的。 我们在一个事务中可以读取到其他事务未提交的数据，这种我们称之为**脏读（Dirty Read）。**因为事务并未提交，所以数据库中的数据并未发生变化。 **已提交读(Read Committed)** 用户B将隔离级别设置为Read Committed： ``` mysql> set session transaction isolation level read committed; Query OK, 0 rows affected mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 1000 | | 2 | 1000 | +----+--------+ 2 rows in set ``` 用户A执行更新操作，先不commit： ``` mysql> start transaction; Query OK, 0 rows affected mysql> update account set amount=amount-200 where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 ``` 用户B查询数据库： ``` mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 1000 | | 2 | 1000 | +----+--------+ 2 rows in set ``` 用户A执行commit操作： ``` mysql> commit; Query OK, 0 rows affected ``` 用户B再次查询数据库： ``` mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 800 | | 2 | 1000 | +----+--------+ 2 rows in set ``` 我们将事务隔离级别设置为Read Committed的时候，当前事务只能读取到其他事务提交的数据，未提交的数据读取不到。 用户B在同一事务中2次读取的结果不同，我们称之为**不可重复读（NonRepeatable Read）。** **可重复读(Repeated Read)** 用户B将隔离级别设置为Repeated Read： ``` mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected mysql> start transaction; Query OK, 0 rows affected mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 800 | | 2 | 1000 | +----+--------+ 2 rows in set ``` 用户A插入数据： ``` mysql> insert into account(id, amount) values(3, 1000); Query OK, 1 row affected mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 800 | | 2 | 1000 | | 3 | 1000 | +----+--------+ 3 rows in set ``` 用户B再次查询，但是仍然得到是2条数据： ``` mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 800 | | 2 | 1000 | +----+--------+ 2 rows in set ``` 当我们执行插入操作时，报错： ``` mysql> insert into account(id, amount) values(3, 1000); 1062 - Duplicate entry '3' for key 'PRIMARY' mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 800 | | 2 | 1000 | +----+--------+ 2 rows in set ``` 我们将事务隔离级别设置为Repeated Read的时候，不论其他事务有没有提交，每次读取的结果集都相同。 数据已经发生变化，但是读取的结果集还是保持一致，出现上面的现象，我们称之为**幻读（Phantom Read） 。** **串行读(Serializable)** 用户B将隔离级别设置为串行读(Serializable)： ``` mysql> set session transaction isolation level serializable; Query OK, 0 rows affected mysql> start transaction; Query OK, 0 rows affected mysql> select * from account; +----+--------+ | id | amount | +----+--------+ | 1 | 800 | | 2 | 1000 | +----+--------+ 2 rows in set ``` 用户A插入数据，则会出现等待，直到出现超时报错： ``` mysql> insert into account(id, amount) values (3, 1000); 1205 - Lock wait timeout exceeded; try restarting transaction ``` 如果在A等待的过程中，B提交了事务，则A会执行成功。 我们将事务隔离级别设置为Serializable的时候，事务将会挂起对表的写操作，这是隔离级别中最严的隔离级别，但是也会对性能造成影响。 在实际的情况中，我们需要根据具体的操作情况，选择隔离级别。 --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://john-mao.gitbook.io/my-handbook/mysql/mysqlshi-wu-ge-li-ji-bie.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.