MySQL의 4가지 트랜잭션 격리 수준에 대한 분석 예-MySQL 튜토리얼-php.cn

MySQL의 4가지 트랜잭션 격리 수준에 대한 분석 예

小云云

풀어 주다： 2017-12-22 14:27:24

원래의

1865명이 탐색했습니다.

데이터베이스 운영에서는 동시에 읽은 데이터의 정확성을 효과적으로 보장하기 위해 트랜잭션 격리 수준이 제안됩니다. 데이터베이스 트랜잭션에는 네 가지 격리 수준이 있습니다. SQL 표준은 트랜잭션 내부 및 외부의 변경 사항이 표시되고 표시되지 않도록 제한하는 몇 가지 특정 규칙을 포함하여 네 가지 격리 수준을 정의합니다. 다음 기사에서는 MySQL의 4가지 트랜잭션 격리 수준에 대한 관련 정보를 예제를 통해 자세히 분석합니다.

머리말

아래에는 할 말이 많지 않으나, 자세한 소개를 살펴보겠습니다.

데이터베이스 트랜잭션에는 네 가지 격리 수준이 있습니다.

Read Uncommitted(Read Uncommitted): 더티 읽기가 허용됩니다. 즉, 다른 세션의 커밋되지 않은 트랜잭션에 의해 수정된 데이터를 읽을 수 있습니다.
읽기 커밋: 제출된 데이터만 읽을 수 있으며 Oracle과 같은 대부분의 데이터베이스는 이 수준으로 기본 설정되어 있습니다.
반복 읽기: 반복 읽기. 동일한 트랜잭션 내의 쿼리는 트랜잭션 시작 시 InnoDB 기본 수준에서 일관됩니다. SQL 표준에서 이 격리 수준은 반복 불가능한 읽기를 제거하지만 가상 읽기는 여전히 존재합니다.
직렬화 가능: 완전히 직렬화된 읽기입니다. 각 읽기에는 테이블 수준 공유 잠금이 필요하며 읽기와 쓰기가 서로 차단됩니다.

트랜잭션 격리 개념을 처음 접하는 친구들은 위의 교과서 정의를 읽고 혼란스러울 수 있습니다. 구체적인 예를 통해 4가지 격리 수준을 설명해 보겠습니다.

먼저 사용자 테이블을 생성합니다:

CREATE TABLE user (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE `uniq_name` USING BTREE (name)
) ENGINE=`InnoDB` AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

로그인 후 복사

Read uncommitted 격리 수준

먼저 트랜잭션의 격리 수준을 커밋된 읽기로 설정합니다.

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)

로그인 후 복사

아래에서는 트랜잭션 1과 트랜잭션 2를 각각 시뮬레이션하기 위해 두 개의 터미널을 엽니다. p.s: 작업 1과 2는 시간순으로 실행되도록 되어 있습니다.

트랜잭션 1

mysql> start transaction; # 操作1
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name) values('ziwenxie'); # 操作3
Query OK, 1 row affected (0.05 sec)

로그인 후 복사

트랜잭션 2

mysql> start transaction; # 操作2
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作4
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

로그인 후 복사

위의 실행 결과에서 명확하게 알 수 있듯이 커밋되지 않은 읽기 수준에서는 더티 읽기인 트랜잭션 2에서 커밋되지 않은 데이터를 읽을 수 있습니다.

Read commit된 격리 수준

위의 더티 읽기 문제는 격리 수준을 commit으로 설정하면 해결될 수 있습니다.

mysql> set session transaction isolation level read committed;

로그인 후 복사

트랜잭션 1

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作三
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作五，操作四的修改并没有影响到事务一
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.00 sec)

로그인 후 복사

트랜잭션 2

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='lisi' where id=10; # 操作四
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作六
Query OK, 0 rows affected (0.08 sec)

로그인 후 복사

더티 읽기 문제는 해결되었지만 트랜잭션 1의 작업 7에서 트랜잭션 2로 인해 작업 6이 커밋된 후 동일한 트랜잭션에서 트랜잭션 1이 두 번 읽히게 된다는 점에 유의하세요. 이는 반복 불가능한 읽기 문제입니다. 세 번째 트랜잭션 격리 수준 반복 읽기를 사용하면 이 문제를 해결할 수 있습니다.

반복 읽기 격리 수준

MySQL Innodb 스토리지 엔진의 기본 트랜잭션 격리 수준은 반복 읽기 격리 수준이므로 추가 설정이 필요하지 않습니다.

트랜잭션 1

mysql> start tansactoin; # 操作一
mysql> select * from user; # 操作五
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> commit; # 操作六
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)

로그인 후 복사

트랜잭션 2

mysql> start tansactoin; # 操作二
mysql> update user set name='lisi' where id=10; # 操作三
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作四

로그인 후 복사

트랜잭션 1의 작업 5에서 작업 3의 트랜잭션 2의 업데이트를 읽지 않았습니다. 업데이트된 데이터는 커밋 후에만 읽을 수 있습니다.

Innodb가 팬텀 읽기를 해결합니까? 실제로 RR 레벨은 팬텀 읽기를 생성할 수 있습니다. InnoDB 엔진은 공식적으로 MVCC 다중 버전 동시성 제어를 사용하여 이 문제를 해결한다고 주장합니다.

표시의 편의를 위해 위의 사용자 테이블을 수정했습니다.

mysql> alter table user add salary int(11);
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> delete from user;
Query OK, 1 rows affected (0.07 sec)
mysql> insert into user(name, salary) value('ziwenxie', 88888888);
Query OK, 1 row affected (0.07 sec)
mysql> select * from user;
+----+----------+----------+
| id | name  | salary |
+----+----------+----------+
| 10 | ziwenxie | 88888888 |
+----+----------+----------+
1 row in set (0.00 sec)

로그인 후 복사

Transaction 1

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> update user set salary='4444'; # 操作六，竟然影响了两行，不是说解决了幻读么？
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from user; # 操作七， Innodb并没有完全解决幻读
+----+----------+--------+
| id | name  | salary |
+----+----------+--------+
| 10 | ziwenxie | 4444 |
| 11 | zhangsan | 4444 |
+----+----------+--------+
2 rows in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.04 sec)

로그인 후 복사

Transaction 2

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name, salary) value('zhangsan', '666666'); # 操作四
Query OK, 1 row affected (0.00 sec)
mysql> commit; # 操作五
Query OK, 0 rows affected (0.04 sec)

로그인 후 복사

위의 예에서 볼 수 있듯이 Innodb는 공식적으로 명시된 대로 phantom read를 해결하지 않지만 위와 같습니다. 현장에서는 흔하지 않으니 너무 걱정하지 않으셔도 됩니다.

직렬화된 격리 수준

모든 트랜잭션은 순차적으로 실행됩니다. 가장 높은 격리 수준에서는 팬텀 읽기가 발생하지 않으며 실제 개발에서는 거의 사용되지 않습니다.