MySQL의 4가지 트랜잭션 격리 수준에 대한 자세한 설명 및 비교-MySQL 튜토리얼-php.cn

Mysql 관계형 데이터베이스 관리 시스템

MySQL은 스웨덴 MySQL AB 회사에서 개발한 오픈 소스 소규모 관계형 데이터베이스 관리 시스템입니다. MySQL은 인터넷상의 중소규모 웹사이트에서 널리 사용되고 있습니다. 작은 크기, 빠른 속도, 낮은 총 소유 비용, 특히 오픈 소스의 특성으로 인해 많은 중소 웹 사이트에서는 웹 사이트 총 소유 비용을 줄이기 위해 MySQL을 웹 사이트 데이터베이스로 선택합니다.

이 글에서는 MySQL의 4가지 트랜잭션 격리 수준에 대한 자세한 설명과 비교를 주로 소개합니다. 다음은 MySQL의 기본 지식과 트랜잭션 격리에 대한 자세한 소개입니다. 비교가 필요한 친구는

MySQL의 4가지 트랜잭션 격리 수준에 대한 자세한 설명 및 비교를 참조하세요.

SQL:1992 트랜잭션 격리 수준에 따르면 InnoDB는 기본적으로 반복 가능합니다(REPEATABLE READ ). MySQL/InnoDB는 SQL 표준에 설명된 네 가지 트랜잭션 격리 수준을 모두 제공합니다. 명령줄이나 옵션 파일에서 --transaction-isolation 옵션을 사용하여 모든 연결에 대한 기본 격리 수준을 설정할 수 있습니다.
예를 들어 my.inf 파일의 [mysqld] 섹션에서 다음과 같이 이 옵션을 설정할 수 있습니다.

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

로그인 후 복사

사용자는 SET TRANSACTION 문을 사용하여 단일 세션의 격리 수준을 변경하거나 들어오는 모든 연결. 구문은 다음과 같습니다.

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

로그인 후 복사

참고: 기본 동작(세션 및 전역 제외)은 다음(시작되지 않은) 트랜잭션에 대한 격리 수준을 설정하는 것입니다. GLOBAL 키워드를 사용하는 경우 명령문은 해당 시점부터 생성된 모든 새 연결에 대해 전역적으로 기본 트랜잭션 수준을 설정합니다(존재하지 않는 연결 제외). 이를 위해서는 SUPER 권한이 필요합니다. SESSION 키워드를 사용하여 현재 연결에서 수행되는 향후 트랜잭션에 대한 기본 트랜잭션 수준을 설정합니다. 모든 클라이언트는 자유롭게 세션 격리 수준을 변경하거나(트랜잭션 중에도) 다음 트랜잭션에 대한 격리 수준을 설정할 수 있습니다.

다음 명령문을 사용하여 전역 및 세션 트랜잭션 격리 수준을 쿼리할 수 있습니다.

SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

로그인 후 복사

----위 매뉴얼의 이론적 지식;
======= === ============================================== === ===============================
격리 수준 Dirty Read NonRepeatable Read Fantasy Phantom Read
= ========================================== ======= ==========================================

커밋되지 않은 읽기 가능 가능

읽기 커밋 불가능 가능

반복 읽기 불가능 가능

직렬화 불가능 불가능

=========== =============== =================================== =============== ================

·Read Uncommitted: 더티 읽기가 허용됩니다. 즉, 데이터 다른 세션에서 커밋되지 않은 트랜잭션에 의해 수정된 내용을 읽을 수 있습니다.

·커밋된 읽기: 제출된 데이터만 읽을 수 있습니다. Oracle과 같은 대부분의 데이터베이스는 기본적으로 이 수준(반복되지 않는 읽기)

·반복 읽기: 반복 가능한 읽기입니다. 동일한 트랜잭션 내의 쿼리는 트랜잭션 시작 시 InnoDB 기본 수준에서 일관됩니다. SQL 표준에서 이 격리 수준은 반복 불가능한 읽기를 제거하지만 가상 읽기는 여전히 존재합니다.

·직렬 읽기(직렬화 가능): 완전히 직렬화된 읽기, 각 읽기에는 테이블 수준 공유 잠금이 필요하며 읽기 및 쓰기는 서로 차단

예를 사용하여 각 수준의 상황을 설명하세요.

① 脏读: 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

session 1:
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ    |
+-----------------------+
1 row in set (0.00 sec)

mysql> select @@session.tx_isolation;
+-----------------------+
| @@session.tx_isolation |
+-----------------------+
| REPEATABLE-READ    |
+-----------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ttd values(1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |
+------+
1 row in set (0.00 sec)

session 2:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ    |
+------------------------+
1 row in set (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ  |    --------该隔离级别下(除了 read uncommitted)
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from ttd;
Empty set (0.00 sec)       --------不会出现脏读

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED    |  --------该隔离级别下
+------------------------+
1 row in set (0.00 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |                    --------REPEATABLE-READ级别出现脏读

+------+
1 row in set (0.00 sec)

로그인 후 복사

结论:session 2 在READ-UNCOMMITTED 下读取到session 1 中未提交事务修改的数据.

② 不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

session 1:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED     |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |
+------+
1 row in set (0.00 sec)

session 2 :

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ    |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |
+------+
1 row in set (0.00 sec)

mysql> insert into ttd values(2); /也可以更新数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |
|  2 |
+------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

session 2 提交后,查看session 1 的结果;

session 1:

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |               --------和第一次的结果不一样,READ-COMMITTED 级别出现了不重复读
|  2 |
+------+
2 rows in set (0.00 sec)
③ 可重复读:
session 1:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ    |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |
|  2 |
+------+
2 rows in set (0.00 sec)

session 2 :

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ    |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ttd values(3);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

session 2 提交后,查看session 1 的结果;

session 1:

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |                   --------和第一次的结果一样,REPEATABLE-READ级别出现了重复读
|  2 |
+------+
2 rows in set (0.00 sec)

(commit session 1 之后 再select * from ttd 可以看到session 2 插入的数据3)

로그인 후 복사

④ 幻读:第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

mysql>CREATE TABLE `t_bitfly` (
`id` bigint(20) NOT NULL default &#39;0&#39;,
`value` varchar(32) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ    | REPEATABLE-READ |
+-----------------------+-----------------+

实验一：

t Session A          Session B
|
| START TRANSACTION;     START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| empty set
|               INSERT INTO t_bitfly
|               VALUES (1, &#39;a&#39;);
|
| SELECT * FROM t_bitfly;
| empty set
|               COMMIT;
|
| SELECT * FROM t_bitfly;
| empty set
|
| INSERT INTO t_bitfly VALUES (1, &#39;a&#39;);
| ERROR 1062 (23000):
| Duplicate entry &#39;1&#39; for key 1
v (shit, 刚刚明明告诉我没有这条记录的)

如此就出现了幻读，以为表里没有数据，其实数据已经存在了，傻乎乎的提交后，才发现数据冲突了。

实验二：

t Session A         Session B
|
| START TRANSACTION;     START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|              INSERT INTO t_bitfly
|              VALUES (2, &#39;b&#39;);
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|              COMMIT;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|
| UPDATE t_bitfly SET value=&#39;z&#39;;
| Rows matched: 2 Changed: 2 Warnings: 0
| (怎么多出来一行)
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | z   |
| |  2 | z   |
| +------+-------+

로그인 후 복사

本事务中第一次读取出一行，做了一次更新后，另一个事务里提交的数据就出现了。也可以看做是一种幻读。

当隔离级别是可重复读，且禁用innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描index的时候使用的next-key locks可以避免幻读。

再看一个实验，要注意，表t_bitfly里的id为主键字段。

实验三：
t Session A         Session B
|
| START TRANSACTION;    START TRANSACTION;
|
| SELECT * FROM t_bitfly
| WHERE id<=1
| FOR UPDATE;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|              INSERT INTO t_bitfly
|              VALUES (2, &#39;b&#39;);
|              Query OK, 1 row affected
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|              INSERT INTO t_bitfly
|              VALUES (0, &#39;0&#39;);
|              (waiting for lock ...then timeout)
|              ERROR 1205 (HY000):
|              Lock wait timeout exceeded;
|              try restarting transaction
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|              COMMIT;
|
| SELECT * FROM t_bitfly;

| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+

로그인 후 복사

可以看到，用id<=1加的锁，只锁住了id<=1的范围，可以成功添加id为2的记录，添加id为0的记录时就会等待锁的释放。

实验四：一致性读和提交读
t Session A           Session B
|
| START TRANSACTION;       START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 | a   |
| +----+-------+
|                INSERT INTO t_bitfly
|                VALUES (2, &#39;b&#39;);
|                COMMIT;
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 | a   |
| +----+-------+
|
| SELECT * FROM t_bitfly LOCK IN SHARE MODE;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 | a   |
| | 2 | b   |
| +----+-------+
|
| SELECT * FROM t_bitfly FOR UPDATE;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 | a   |
| | 2 | b   |
| +----+-------+
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 | a   |
| +----+-------+

로그인 후 복사

如果使用普通的读，会得到一致性的结果，如果使用了加锁的读，就会读到“最新的”“提交”读的结果。

本身，可重复读和提交读是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了提交读，就会导致前后两次读到的结果不一致，违背了可重复读。

可以这么讲，InnoDB提供了这样的机制，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据（提交读）。
MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。

以上就是MySQL 四种事务隔离级别详解及对比的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！