[ MYSQL / Database] READ UNCOMMITTED, COMMITTED, REPEATABLE READ

 READ UNCOMMITTED

- READ UNCOMMITTED 수준에서는 트랜잭션에서의 변경내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보여진다. 그리고 이러한 현상때문에 DIRTY READ가 발생한다.
- 더티 리드 현상은 데이터가 나타났다가 사라졌다가 하는 현상을 초래하므로
  애플리케이션 개발자와 사용자를 상당히 혼랍스럽게 만든다.

 

/실습용 데이터 베이스 생성/

drop database db01;
   create database db01;


   use db01;
   create table t1(id int);
   create table t2(id int);

   create user user1@'%' identified by '1234';
   grant all privileges on DB01.* to user1@'%';

   flush privileges;

 

ROOT 세션(세션 레벨 수정)	USER1 세션
SET autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @@SESSION.transaction_isolation;
USE DB01; BEGIN; SELECT * FROM T1; 결과 1  => EMPTY SET
	USE DB01; BEGIN; INSERT INTO T1 VALUES(10);
SELECT * FROM T1;    =>  커밋을 하지 않은 상태에서 다른 사용자에게 내용이 보이게 됩니다.  결과2  -> 그림 1확인
	ROLLBACK;
SELECT * FROM T1; 결과 1의 모습으로 돌아오게 됩니다.
COMMIT;	COMMIT;

그림 1 . 결과2 DRITY READ가 일어남

 

DIRTY READ 없애는 방법

 READ COMMITED

- 이 레벨에서는 더티리드와 같은 현상은 발생하지 않는다.

- 어떤 트랜잭션에서 데이터를 변경했더라도   COMMIT 이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.

- READ COMMITTED은 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 "REPEATABLE READ" 정합성에 어긋난다. 

 

ROOT 세션(세션 레벨 수정)	USER1 세션
SET autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT @@SESSION.transaction_isolation;
USE DB01; BEGIN; SELECT * FROM T1; 결과 1  => EMPTY SET
	USE DB01; BEGIN; INSERT INTO T1 VALUES(50);
SELECT * FROM T1;    =>  커밋을 하지 않은 상태에서 다른 사용자에게 내용이 보이게 됩니다.  결과1과 동일   -> ROOT 에서는 확인불가
COMMIT	COMMIT
SELECT * FROM T1; => 결과2 로 동일하게 나온다.

 

그림 2

그림2. - 1번 라인에서 COMMIT을 하지 않은 상태에서 확인하였을 때, DIRTY READ가 일어나지 않는 것을 확인할 수 있습니다.

그림2.- 2번에서 COMMIT을 확인하고 난뒤 ROOT SESSION에서 확인할 수 있는 것을 확인할 수 있습니다. 

 

 

 REPEATABLE READ

- 이 격리수준에서는 위에서 설명한 NON-REPEABLE READ이 발생하지 않는다.
- NON-REPEABLE READ가 발생하지 않는 이유는 
  트랜잭션을 생성할때 트랜잭션 번호를 부여받게 되는데 그때부터 트랜잭션 안에서 실행되는 
  모든 SELECT 쿼리는 트랜잭션 번호가 작은 트랜잭션 번호에서 변경한 것만 보이게 되기 때문이다.

 

DEFAULT = NON-REPEABLE READ

ROOT 세션(세션 레벨 수정)	USER1 세션
SET autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL  REPEATABLE READ; SELECT @@SESSION.transaction_isolation;
USE DB01; BEGIN; SELECT * FROM T1; 결과 1
	USE DB01; BEGIN; INSERT INTO T1 VALUES(60);
SELECT * FROM T1;   결과1과 동일   -> ROOT 에서는 USER1의 내용은 확인불가
	COMMIT
SELECT * FROM T1; => 결과1로 나온다.  커밋의 결과 가 제대로 반영되지 않아 이전의 테이블이 반복적으로 보이게 된다.

그림3

그림 3 과 같이 USER1에서 COMMIT을 하여도 ROOT 계정에서는 확인이 되지 않는 상황이 나옵니다, 

 

 

 SERIALIZABLE

- 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 
  동시에 다른 트랜잭션은 그러한 레코드를 변경할 수 없습니다. 
  즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없습니다.

ROOT 세션(세션 레벨 수정)	USER1 세션
SET autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL  SERIABLIZABLE; SELECT @@SESSION.transaction_isolation;
USE DB01; BEGIN; SELECT * FROM T1; 결과 1
	USE DB01; BEGIN; INSERT INTO T1 VALUES(80); => 작동을 멈추게 됨
SELECT * FROM T1;   결과1과 동일   -> ROOT 에서는 확인불가
	COMMIT
SELECT * FROM T1; => 결과1과 동일하게 나옴

 

그림 4 . USER1 MYSQL 접속 상태

그림 5 양쪽 모두 적용되지 않는다.

 

2023.04.14 - [Server/Database] - [MYSQL /Database] 트랜잭션의 격리 수준

[MYSQL /Database] 트랜잭션의 격리 수준