MySQL 트랜잭션 격리 수준 및 더티 읽기, 팬텀 읽기, 반복 불가능 읽기 예

권장(무료): mysql 비디오 튜토리얼

트랜잭션 격리

MySQL은 클라이언트/서버 아키텍처 소프트웨어입니다. 동일한 서버에 여러 개가 연결될 수 있습니다. 각 클라이언트가 서버에 연결되면 이를 세션이라고 부를 수 있습니다. 각 클라이언트는 자체 세션에서 서버에 요청 문을 발행할 수 있습니다. 요청 문은 트랜잭션의 일부일 수 있습니다. 즉, 서버는 동시에 여러 트랜잭션을 처리할 수 있습니다. 데이터베이스에서 여러 개의 트랜잭션이 동시에 실행되면 더티 읽기(Dirty Read), 반복 불가능 읽기(Non-repeatable Read), 팬텀 읽기(Phantom Read) 등의 문제가 발생할 수 있다. 이러한 문제를 해결하기 위해 '격리 수준(isolation level)'이라는 개념이 있다.

이론적으로 트랜잭션이 특정 데이터에 액세스하면 다른 트랜잭션이 대기열에 있어야 하며, 해당 트랜잭션이 제출된 후에만 다른 트랜잭션이 계속해서 데이터에 액세스할 수 있습니다. 그러나 일반적으로 절연이 엄격할수록 효율성은 낮아집니다. 따라서 우리는 격리와 효율성 사이의 균형을 찾아야 하는 경우가 많습니다.

동시 트랜잭션 실행 시 발생하는 문제

더티 읽기: 더티 읽기는 트랜잭션이 커밋되지 않은 다른 트랜잭션에 의해 수정된 데이터를 읽는다는 의미입니다.

예를 들어 Xiao Wang의 계정 잔액이 100인 경우 Xiao Wang의 계정에 액세스하려면 두 번의 거래가 발생합니다.

세션 A	세션 B
begin;
update xxx set 잔액 = 잔액+50 여기서 client_no = 'Xiao Wang 고객 번호' ;	시작하세요
	client_no = 'Xiao Wang 고객 번호'인 xxx에서 잔액 선택 ; (150으로 읽으면 더티 읽기가 발생했음을 의미)
rollback;	commit;

위와 같이 , 세션 A와 세션 B는 각각 트랜잭션을 열었습니다. 세션 A는 먼저 Xiao Wang의 계정 잔액에 50을 추가했습니다. 이때 계정 B는 Xiao Wang의 계정 잔액이 150인지 확인했습니다. 그런 다음 세션 A는 롤백되었고 세션 B는 잔액을 쿼리했습니다. Xiao Wang의 계정은 150이었습니다. 150은 잘못된 더티 데이터가 됩니다.

반복 불가능 읽기: 반복 불가능 읽기는 동일한 트랜잭션 내에서 동일한 데이터 세트를 여러 번 읽지만 결과가 다른 것을 의미합니다. 반복 불가능한 읽기는 여러 검색 중에 쿼리된 데이터가 다른 트랜잭션에 의해 수정되었기 때문에 발생합니다.

아래 두 세션 요청을 살펴보세요.

(150으로 읽으면 반복 불가능한 읽기가 발생했음을 의미)commit;세션A의 동일한 트랜잭션에서 동일한 쿼리를 두 번 실행하면 결과가 달라서 반복 불가능한 읽기가 발생했다는 의미입니다. 소위 팬텀 읽기는 트랜잭션이 특정 범위의 레코드를 읽을 때 다른 트랜잭션이 해당 범위에 새 레코드를 삽입하는 것을 의미합니다. 이전 트랜잭션이 이 범위의 레코드를 다시 읽으면 이전에 읽지 않은 데이터를 읽습니다.

세션 A	세션 B
begin;
client_no = 'Xiao Wang 고객 번호'인 xxx에서 잔액을 선택하세요. ; (읽기 잔액은 10입니다. 0)	start;
	update xxx set 잔액 = 잔액+50 여기서 client_no = 'Xiao Wang' 고객 번호' ;
팬텀 읽기:	현재 계정 테이블에 Xiao Wang의 잔액만 100이라고 가정하고 다음 두 세션 요청을 살펴보세요.

Session A

Session B

begin;balance = 100인 xxx에서 이름 선택;(읽기 이름은 '작은왕') 시작하세요; insert into xxx(client_no,name,balance) 값('Xiao Zhang 고객 번호','Xiao Zhang',100);commit;xxx에서 이름 선택 여기서 잔액 = 100 ; th>

('Xiao Wang'과 'Xiao Zhang'이 읽혀지면 팬텀 읽기가 발생했음을 의미)

commit;

세션의 두 번째 쿼리 트랜잭션이 첫 번째 쿼리에서 발견되지 않았던 'Xiao Zhang'이라는 이름을 발견했는데, 이는 팬텀 읽기가 발생했음을 의미합니다.

SQL 표준에 의해 설정된 네 가지 격리 수준

ISO 및 ANIS SQL 표준에서는 커밋되지 않은 읽기(커밋되지 않은 읽기), 커밋된 읽기(커밋된 읽기), 반복 가능한 읽기(반복 가능한 읽기) 및 직렬화라는 네 가지 트랜잭션 격리 수준이 설정되었습니다. (직렬화 가능).

먼저 이 네 가지 격리 수준의 의미를 살펴보겠습니다.

• 커밋되지 않은 읽기: 트랜잭션이 제출되지 않은 경우 변경 사항은 다른 트랜잭션에서 볼 수 있습니다.
• 커밋 읽기: 트랜잭션이 커밋된 후 변경 사항이 다른 트랜잭션에 표시됩니다.
• 반복 읽기: 트랜잭션 실행 중에 표시되는 데이터는 트랜잭션이 시작될 때 표시되는 데이터와 항상 일치합니다. 물론 반복 가능한 읽기 격리 수준에서는 커밋되지 않은 변경 사항도 다른 트랜잭션에서 볼 수 없습니다.
• 직렬화: 이름에서 알 수 있듯이 동일한 레코드 행에 대해 "쓰기"는 "쓰기 잠금"을 추가하고 "읽기"는 "읽기 잠금"을 추가합니다. 읽기-쓰기 잠금 충돌이 발생하면 나중에 액세스되는 트랜잭션은 계속 실행되기 전에 이전 트랜잭션이 완료될 때까지 기다려야 합니다.

SQL 표준은 서로 다른 격리 수준에서 동시 트랜잭션이 서로 다른 심각도의 문제를 일으킬 수 있다고 규정합니다. 구체적인 상황은 다음과 같습니다.
(√는 발생할 수 있음을 의미, ×는 발생할 수 없음을 의미)（ √ 表示可以发生；× 表示不可以发生）

隔离级别	脏读	不可重复读	幻读
读未提交（read uncommitted）	√	√	√
读提交（read committed）	×	√	√
可重复读（repeatable read）	×	×	√
串行化（serializable ）	×	×	×

MySQL对四种隔离级别的支持情况

虽然 ISO 和 ANIS SQL 标准制定了四种事务隔离级别的标准，但不是所有数据库厂商都遵循这些标准，比如 Oracle 数据库就不支持读未提交（read uncommitted）和可重复读（repeatable read）的事务隔离级别。

MySQL InnoDB 存储引擎支持4种隔离级别，但与 SQL 标准中定义的不同的是，InnoDB 存储引擎在默认的可重复读（repeatable read）事务隔离级别下，使用 Next-Key Lock 锁的算法，避免了幻读的产生。也就是说 InnoDB 存储引擎在可重复读（repeatable read）的事务隔离级别下，已经可以完全保证事务的隔离性要求，即达到了 SQL 标准中的串行化（serializable ）隔离级别的要求。

如何设置事务的隔离级别

在 InnoDB 存储引擎中，可以使用以下命令来设置全局或者当前会话的事务隔离级别：

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

如想设置当前会话的隔离级别为读提交，可以使用如下语句：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

如果想在 MySQL 数据库启动时就设置事务的默认隔离级别，那就需要修改配置文件中 transaction-isolation 的值，比方说，我们在启动前指定了 transaction-isolation = READ COMMITTED

격리 수준

더티 읽기

반복 불가능 읽기

팬텀 읽기

커밋되지 않은 읽기

√

√

√ td>

읽기 커밋

×

√

√

반복 가능 읽기

×

×

√

직렬화 가능

× td>

×

×

MySQL 4가지 격리 수준 지원

ISO 및 ANIS SQL 표준이 공식화되었지만 네 가지 트랜잭션 격리 수준에 대한 표준이지만 모든 데이터베이스 공급업체가 이러한 표준을 따르는 것은 아닙니다. 예를 들어 Oracle 데이터베이스는 커밋되지 않은 읽기(커밋되지 않은 읽기) 및 반복 가능한 읽기(반복 가능한 읽기) 트랜잭션 격리 수준을 지원하지 않습니다.

MySQL InnoDB 스토리지 엔진은 4가지 격리 수준을 지원하지만 SQL 표준에 정의된 것과는 달리 InnoDB 스토리지 엔진은 기본 반복 읽기(repeatable read) 트랜잭션 격리 수준에서 Next-Key Lock을 사용하여 발생을 방지하는 알고리즘입니다. 환상의 독서. 즉, InnoDB 스토리지 엔진은 반복 읽기의 트랜잭션 격리 수준에서 트랜잭션의 격리 요구 사항을 완벽하게 보장할 수 있습니다. 즉, SQL 표준의 직렬화 격리 수준 요구 사항에 도달했습니다.

트랜잭션의 격리 수준을 설정하는 방법

InnoDB 스토리지 엔진에서는 다음 명령을 사용하여 전역 또는 현재 세션의 트랜잭션 격리 수준을 설정할 수 있습니다. 🎜

SELECT @@transaction_isolation;

🎜격리를 설정하려는 경우 현재 세션 수준을 읽기-커밋하려면 다음 문을 사용할 수 있습니다. 🎜

SELECT @@global.transaction_isolation;

🎜 MySQL 데이터베이스가 시작될 때 트랜잭션의 기본 격리 수준을 설정하려면 구성에서 transaction-isolation 값을 수정해야 합니다. 예를 들어 transaction-isolation = before start.READ COMMITTED를 지정하면 트랜잭션의 기본 격리 수준이 원래 REPEATABLE READ에서 READ COMMITTED로 변경됩니다. 🎜🎜현재 세션의 트랜잭션 격리 수준을 보려면 다음 문을 사용할 수 있습니다. 🎜rrreee🎜전역 트랜잭션 격리 수준을 보려면 다음 문을 사용할 수 있습니다. 🎜rrreee🎜🎜참고: transaction_isolation은 MySQL 5.7에서 도입되었습니다. .20 tx_isolation 대체 예, 이전 버전의 MySQL을 사용하는 경우 위에 사용된 transaction_isolation을 tx_isolation으로 대체하십시오. 🎜🎜🎜더 많은 프로그래밍 관련 지식을 보려면 🎜프로그래밍 비디오🎜를 방문하세요! ! 🎜

위 내용은 MySQL 트랜잭션 격리 수준 및 더티 읽기, 팬텀 읽기, 반복 불가능 읽기 예의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!