MySQL InnoDB와 더티 읽기, 비반복 읽기, 팬텀 읽기의 4가지 트랜잭션 수준은 무엇입니까?

1. MySQL InnoDB 트랜잭션 격리 수준 더티 읽기, 반복 읽기, 팬텀 읽기

MySQL InnoDB 트랜잭션 격리 수준에는 4가지 수준이 있으며 기본값은 "REPEATABLE READ"입니다.

· 1) 커밋되지 않은 읽기(READUNCOMMITTED). 또 다른 트랜잭션이 데이터를 수정했지만 아직 커밋하지 않았으며 이 트랜잭션의 SELECT는 커밋되지 않은 데이터를 읽습니다(더티 읽기)( 가장 낮은 격리 수준, 높은 동시성 성능).

· 2) 읽기(READCOMMITTED)를 제출합니다. 이 트랜잭션이 읽는 것은 최신 데이터입니다(다른 트랜잭션이 커밋된 후). 문제는 동일한 트랜잭션에서 동일한 SELECT가 다른 결과를 두 번(반복 읽기 없이) 읽는다는 것입니다. 반복 불가능 읽기 및 팬텀 읽기 문제가 발생합니다(읽고 있는 행 잠금)

· 3) 반복 가능 읽기(REPEATABLEREAD). 동일한 트랜잭션에서 SELECT의 결과는 트랜잭션이 시작될 당시의 상태이므로 동일한 SELECT 작업으로 읽은 결과는 일관됩니다. 그러나 팬텀 판독이 수행됩니다(나중에 설명됨). 가상 읽기가 발생합니다(읽은 모든 행이 잠김).

· 4).직렬화(SERIALIZABLE). 읽기 작업은 암시적으로 공유 잠금을 획득하여 서로 다른 트랜잭션 간의 상호 배제(잠금 테이블)를 보장합니다.

'

4개의 레벨이 점차 강해지며, 각 레벨은 문제를 해결합니다.

· 1) 더러운 독서. 또 다른 트랜잭션이 데이터를 수정했지만 아직 커밋하지 않았으며 이 트랜잭션의 SELECT는 커밋되지 않은 데이터를 읽습니다.

· 2) 반복해서 읽지 마세요. 더티 읽기 문제를 해결한 후 동일한 트랜잭션을 실행하는 동안 다른 트랜잭션이 새 데이터를 제출했기 때문에 이 트랜잭션에서 두 번 읽은 데이터 결과가 일치하지 않는 것을 보게 됩니다.

· 3) 환상의 독서. 반복되지 않는 읽기 문제를 해결하고 동일한 트랜잭션에서 query 결과가 트랜잭션 시작 시의 상태(일관성)임을 보장합니다. 그러나 다른 트랜잭션이 동시에 새 데이터를 제출하고 이 트랜잭션이 다시 업데이트되면 이러한 새 데이터를 발견하면 "놀라게" 됩니다. 이전에 읽은 데이터는 "유령" 환상인 것 같습니다.

구체적으로:

1) 더티 읽기

먼저 더티 페이지와 더티 데이터를 구별하세요.

더티 페이지는 메모리 버퍼입니다. 풀에서 수정된 페이지는 변경되지 않았습니다. 적시에 업데이트되어 하드 디스크에 플러시되었지만 다시 실행 로그에 기록되었습니다. 버퍼 풀의 페이지를 읽고 수정하는 것은 일반적이며 플러시를 동기화할 수 있어 효율성이 향상됩니다. 더티 데이터는 트랜잭션이 버퍼 풀의 행 레코드를 수정했지만 아직 제출하지 않았음을 의미합니다. ! ! , 이때 버퍼 풀의 커밋되지 않은 행 데이터를 읽는 경우 이를 더티 읽기(dirty read)라고 하며 트랜잭션 격리를 위반합니다. 더티 읽기(Dirty Reading)는 트랜잭션이 데이터에 액세스하고 데이터를 수정했지만 수정 사항이 아직 데이터베이스에 제출되지 않은 경우 다른 트랜잭션도 해당 데이터에 액세스한 후 해당 데이터를 사용하는 것을 의미합니다.

2) 반복 불가능 읽기

는 트랜잭션 내에서 동일한 데이터를 여러 번 읽는 것을 의미합니다. 이 트랜잭션이 끝나기 전에 다른 트랜잭션도 동일한 데이터에 액세스합니다. 그런 다음 첫 번째 트랜잭션에서 두 번의 데이터 읽기 사이에 두 번째 트랜잭션의 수정으로 인해 두 번째 트랜잭션이 커밋되었습니다. 그러면 첫 번째 트랜잭션에서 두 번 읽은 데이터가 다를 수 있습니다. 이와 같이 트랜잭션 내에서 두 번 읽은 데이터가 다르기 때문에 반복 불가능 읽기라고 합니다. 예를 들어, 편집자는 동일한 문서를 두 번 읽지만, 읽는 사이에 작성자는 문서를 다시 작성합니다. 편집자가 문서를 두 번째로 읽으면 문서가 변경된 것입니다. 원시 읽기는 반복할 수 없습니다. 작성자가 모든 작성을 마친 후에만 편집자가 문서를 읽을 수 있다면 이러한 문제를 피할 수 있습니다

3) 환상 읽기:

트랜잭션이 독립적으로 실행되지 않을 때 발생하는 현상을 말합니다. 첫 번째 트랜잭션은 테이블의 데이터를 수정하며, 이 수정에는 테이블의 모든 데이터 행이 포함됩니다. 동시에 두 번째 트랜잭션도 이 테이블의 데이터를 수정합니다. 이 수정으로 인해 테이블에 새 데이터 행이 삽입됩니다. 그러면 나중에 첫 번째 트랜잭션을 수행한 사용자는 마치 환각이 발생한 것처럼 테이블에 아직 수정되지 않은 데이터 행이 있다는 것을 알게 될 것입니다. 예를 들어, 편집자는 작성자가 제출한 문서를 변경했지만 프로덕션에서 변경 내용을 문서의 마스터 사본에 병합할 때 작성자가 편집되지 않은 새 자료를 문서에 추가했음이 발견되었습니다. 편집자와 제작 부서가 원본 문서 작업을 마칠 때까지 누구도 문서에 새 자료를 추가할 수 없다면 이 문제를 피할 수 있습니다.

2. 격리 수준 실험

다음 실험은 블로거 MySQL Server 5.6

을 기반으로 합니다.

먼저 다음과 같이 테이블을 생성합니다.

USE test;  
CREATE TABLE `t` (  
  
  `a` int(11) NOT NULL PRIMARY KEY  
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.1: 더티 읽기 및 반복 읽기 문제를 설명합니다.

업무 B READ-COMMITTED,

트랜잭션 C-1 REPEATABLE-READactionselect * from t;커밋 ;

	트랜잭션 C-2 REPEATABLE-READ	트랜잭션 D SERIALIZABLE	set autocommit =0;
	거래 시작	;
거래 시작 t(a)values(4)에 삽입;
t에서 *를 선택하세요;	1 ,2,3,4(더티 읽기: 커밋되지 않은 트랜잭션의 데이터 읽기)		select * from t;	1,2,3(더티 읽기 해결)
1, 2,3	t에서 * 선택; 1,2,3	t에서 * 선택; 1,2,3
t:	1,2에서 *를 선택하세요. 3,4	select * from t: 1,2,3,4	select * from t: 1,2,3,4 (위와 동일한 트랜잭션이 아니므로 트랜잭션으로 읽음) commit 최신 것을 읽을 수 있도록 4)	select * from t: 1,2,3 (반복 읽기: 위와 같은 트랜잭션에 있으므로 트랜잭션이 시작되는 데이터만 트랜잭션을 읽고 그냥 반복 읽기)	select * from t: 1,2,3,4
				commit(트랜잭션 커밋, 다음은 새로운 거래이므로 거래가 제출된 후에 최신 데이터를 읽을 수 있습니다)
				select * from t: 1,2,3,4
READ- UNCOMMITTED는 더티 읽기를 생성하며 실제 시나리오에 거의 적용되지 않으므로 기본적으로 사용되지 않습니다.

2.2. 실험 2: READ-COMMITTED 및 REPEATABLE-READ 테스트

commit (커밋 트랜잭션, 다음은 새로운 거래이므로 거래가 제출된 후 최신 데이터를 읽을 수 있습니다)select * from t:REPEATABLE -READ는 트랜잭션에서 읽은 데이터가 반복 가능한지, 즉 동일한 읽기인지 확인할 수 있습니다(첫 번째 읽기 이후에는 다른 트랜잭션이 새 데이터를 제출하더라도 동일한 트랜잭션에서 다시 읽히지 않습니다). READ-COMMITTED는 최신 트랜잭션에 의해 커밋된 데이터를 읽도록 보장합니다.

트랜잭션 A

트랜잭션 B READ-COMMITTED

트랜잭션 C REPEATABLE-READ

세트 autocommit =0;

start transaction;

start transaction;

start transaction;

t(a)값에 삽입 ​(4);

t에서 *를 선택하세요.

커밋;

t:

1 , 2에서 * 선택 , 3 (반복읽기 : 위와 같은 트랜잭션에 있으므로 트랜잭션 시작 트랜잭션의 데이터만 읽음, 즉 반복읽기)

1,2,3,4

当然数据的可见性都是对不同事务来说的，同一个事务，都是可以读到此事务中最新数据的。如下，

1. start transaction;  
   insert into t(a)values(4);  
   select *from t;    
   1,2,3,4;  
   insert into t(a)values(5);  
   select *from t;  
   1,2,3,4,5;

2.3、实验三：测试SERIALIZABLE事务对其他的影响

事务A SERIALIZABLE	事务B READ-UNCOMMITTED	事务C READ-COMMITTED,	事务D REPEATABLE-READ	事务E SERIALIZABLE
set autocommit =0;
start transaction ;			start transaction;
select a from t union all select sleep(1000) from dual;
	insert into t(a)values(5);	insert into t(a)values(5);	insert into t(a)values(5);	insert into t(a)values(5);
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	SERIALIZABLE 串行化执行，导致所有其他事务不得不等待事务A结束才行可以执行，这里特意使用了sleep函数，直接导致事务B,C,D,E等待事务A持有释放的锁。由于我sleep了1000秒，而innodb_lock_wait_timeout为120s。所以120s到了就报错HY000错误。
SERIALIZABLE은 매우 엄격한 직렬화 실행 모드입니다. 읽기든 쓰기든 동일한 테이블을 읽는 다른 트랜잭션에 영향을 미칩니다. 이는 엄격한 테이블 수준 읽기-쓰기 배타 잠금입니다. 또한 innodb 엔진의 장점도 상실됩니다. 실용적인 응용 프로그램은 거의 없습니다.

2.4. 실험 4: 팬텀 읽기

일부 기사에서는 InnoDB의 반복 읽기가 "팬텀 읽기"(팬텀 읽기)를 방지한다고 기록합니다. 실험을 수행하십시오. (다음 모든 실험은 스토리지 엔진 및 격리 수준에 주의해야 합니다.)

1. CREATE TABLE `t_bitfly` (

2. `id` bigint ( 20) NOTNULL default '0',

3. `값` varchar (32) default NULL ,

4. PRIMARY KEY (`id`)

5. ) 엔진=InnoDB DEFAULTCHARSET=utf8

7. select @@global.tx_isolation, @@tx_isolation;  

8. +------------+----------------- +  

9. | @@global.tx_isolation | @@tx_isolation  |  

10. +------------+----------------- +  

11. | REPEATABLE-READ       | REPEATABLE-READ |  

12. +------------+----------------- +  

实验4-1：

Session A	Session B
거래 시작 ;	거래 시작 ;
SELECT * FROM t_bitfly; empty set
	INSERT INTO t_bitfly VALUES(1, 'a');COMMIT;
SELECT * FROM t_bitfly; | 빈 세트
INSERT INTO t_bitfly VALUES (1, 'a'); |ERROR 1062 (23000): |키 1에 '1' 항목이 중복되었습니다 (그런 기록이 없다고 방금 말씀하셨습니다)	I

이렇게 테이블에 데이터가 없다고 생각하고 실제로는 이미 데이터가 존재하는 팬텀리딩이 발생하게 되는데, 제출 후 데이터 충돌이 발견됩니다.

실험 4-2:

| 1 |a || +| 1 |a || + ------+-------+UPDATE t_bitfly SET 값='z';SELECT * FROM t_bitfly;|

이 트랜잭션에서 처음으로 행을 읽었습니다. 업데이트가 이루어진 후 다른 트랜잭션에 제출된 데이터가 나타납니다. 일종의 환상읽기라고도 볼 수 있다.

설명과 함께

그럼 InnoDB가 팬텀리드를 피할 수 있다고 지적한 이유는 무엇일까요?

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

기본적으로 InnoDB는 REPEATABLE READ 트랜잭션 격리 수준에서 작동하며 innodb_locks_unsafe_for_binlog 시스템 변수는 비활성화되어 있습니다. 이 경우 InnoDB는 검색 및 인덱스 스캔에 다음 키 잠금을 사용하여 팬텀 행을 방지합니다(섹션 13.6.8.5, "다음 키 잠금을 사용하여 팬텀 문제 방지") 준비된 이해는 격리 수준이 반복 가능한 읽기이고 innodb_locks_unsafe_for_binlog가 비활성화된 경우

search

및 검색 인덱스 중 next-keylock을 사용하여 팬텀 읽기를 피할 수 있다는 것입니다. 핵심은 InnoDB도 기본적으로 일반 쿼리에 다음 키 잠금을 추가합니까, 아니면 애플리케이션이 잠금 자체를 추가해야 합니까?입니다. 이 문장만 읽어도 InnoDB가 일반 쿼리에도 잠금을 추가한다고 생각할 수 있습니다. 그렇다면 직렬화(SERIALIZABLE)와 차이점은 무엇입니까?

MySQL 매뉴얼에는 또 다른 단락이 있습니다:

13.2.8.5 Next-Key 잠금을 사용하여 PhantomProblem 방지(http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key- locking.html)

팬텀을 방지하려면

next-key라는 알고리즘을 사용하세요. locking

은 인덱스 행 잠금과 간격 잠금을 결합합니다.InnoDB다음 키 잠금을 사용하여 애플리케이션에서 고유성 검사를 구현할 수 있습니다. 공유 모드에서 데이터를 읽었지만 행에 대한 중복이 표시되지 않는 경우 insert를 사용하면 행을 안전하게 삽입하고 다음 키 잠금이 설정되어 있음을 알 수 있습니다. 읽기 중 행의 성공 또는 행의 중복을 삽입하는 동안 누군가가 의미하는 것을 방지합니다. 따라서 다음 키 잠금을 사용하면 테이블에 존재하지 않는 항목을 "잠글" 수 있습니다.

제가 이해한 바에 따르면 InnoDB는 다음을 제공합니다. 키 잠금을 사용할 수 있지만 애플리케이션이 자체적으로 잠궈야 합니다. 매뉴얼에 예시가 나와 있습니다:

SELECT * FROM child WHERE id> 100 FOR UPDATE;

这样，InnoDB会给id大于100的行（假如child表里有一行id为102），以及100-102，102+的gap都加上锁。

可以使用show engine innodb status来查看是否给表加上了锁。

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

实验4-3：

세션 A	세션 B
거래 시작 ;	거래 시작 ;
SELECT * t_bitfly; | +------+-------+ | ID | +------+-------+ | |a  | | * t_bitfly에서; | +------+-------+ | ID | +------+------ -+	| ------+------+
| :2 경고: 0 (왜 한 줄이 더 있나요?)
| +------+-------+ | 아이디 | 값 | | +-------+

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, 'b'); | Query OK, 1 row affected
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly VALUES (0, '0'); |  (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; | +------+---------+ | | 아이디   | 가치 | | +------+---------+ | |    1 |a     | | +------+-------+

id

첨부된 참고 사항:

MySQL 매뉴얼의 반복 읽기 잠금에 대한 자세한 설명:

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read

읽기 잠금의 경우(SELECT with FOR UPDATE or LOCK IN SHARE MODE),UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB 잠금 스캔된 인덱스 범위, 갭 잠금 또는 다음 키(갭 + 인덱스 레코드) 잠금을 사용하여 범위에 포함된 갭에 다른 세션이 삽입되는 것을 차단합니다.

일관성 읽기 및 커밋된 읽기, 먼저 실험을 살펴보세요.

실험 4-4:

세션 A	세션 B
거래 시작 ;	거래 시작 ;
SELECT * FROM t_bitfly; | +------+---------+ | | 아이디   | 가치 | | +------+---------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly   값 (2, 'b');
	COMMIT;
SELECT * FROM t_bitfly; | +------+---------+ | | 아이디   | 가치 | | +------+---------+ | |    1 |a     | | +------+-------+
SELECT * t_bitfly에서 공유 모드로 잠금; | +----+---------+ | | 아이디 | 가치 | | +----+---------+ | |  1 |a     | | |  2 |b     | | +----+-------+
SELECT * FROM t_bitfly FOR UPDATE; | +----+---------+ | | 아이디 | 가치 | | +----+---------+ | |  1 |a     | | |  2 |b     | | +----+-------+
SELECT * FROM t_bitfly; | +----+---------+ | | 아이디 | 가치 | | +----+---------+ | |  1 |a     | | +----+-------+

추가 참고 사항: 일반 읽기를 사용하면 일관된 결과를 얻을 수 있습니다. 잠긴 읽기를 사용하면 "최신" "커밋" 읽기 결과를 읽게 됩니다.

반복 읽기와 헌신 읽기는 그 자체로 모순됩니다. 동일한 트랜잭션에서 반복 읽기가 보장되면 다른 트랜잭션의 커밋이 표시되지 않으며 이는 커밋된 읽기를 위반합니다. 커밋된 읽기가 보장되면 이전 두 읽기의 결과가 일치하지 않아 반복 읽기가 위반됩니다.

InnoDB는 이러한 메커니즘을 제공한다고 할 수 있습니다. 기본 반복 읽기 격리 수준에서는 잠긴 읽기를 사용하여 최신 데이터를 쿼리할 수 있습니다.

http://dev.mysql.com/doc/refman/5.0/en/innodb-contant-read.html

데이터베이스의 "최신" 상태를 보려면 READ COMMITTED 격리 중 하나를 사용해야 합니다. 레벨 또는 잠금 읽기:
SELECT * FROM t_bitfly LOCK IN SHARE MODE;

------

3. 요약

결론: MySQL InnoDB 트랜잭션의 기본 격리 수준은 반복 가능한 읽기이며 그렇지 않습니다. 팬텀 판독을 방지하려면 애플리케이션에서 잠금 판독을 사용하여 이를 보장해야 합니다. 이 잠금 수준에 사용되는 메커니즘은 다음 키 잠금입니다.

위 내용은 MySQL InnoDB와 더티 읽기, 비반복 읽기, 팬텀 읽기의 4가지 트랜잭션 수준은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!