MySQL 트랜잭션 격리 수준(Isolation level) 확인하기

 

트랜잭션 격리 수준(Isolation level)에 대한 내용은 Isolation level에서 확인

 

✔ 트랜잭션 격리 수준(Isolation level)

트랜잭션 격리 수준 실습을 하기 전에 앞서 MySQL은 "autocommit=ON(1: ON, 0: OFF)"이 기본으로 되어있습니다.

autocommit이 "on"이면 데이터를 변경하는 쿼리(insert, update, delete)가 실행될 때마다 자동으로 commit이 처리된다.

실습을 위해 아래의 방법으로 "off"로 변경하고 진행하고 끝나면 다시 on로 변경하겠습니다.

아래의 두 가지 방법으로 autocommit을 확인할 수 있습니다.
mysql > select @@autocommit;
mysql > show variables like 'autocommit%';
mysql > set autocommit = false or true; -- 로 설정할 수 있습니다.

트랜잭션(transaction)의 확인은 아래의 방법으로 확인할 수 있습니다.
mysql > select @@global.transaction_isolation; -- 글로벌 격리 수준
mysql > select @@session.transaction_isolation; -- 세션 격리 수준
mysql > show variables like '%isolation'; -- 격리 수준
mysql > set session transaction isolation level <변경할 격리 수준> -- 격리 수준 대문자로 입력(MySQL 기본: REPEATABLE READ)

 

✔ READ UNCOMMITTED(level 0)

[session 38] tx-1

[session 41] tx-2

[session 38] tx-1

[session 41] tx-2

순서는 다음과 같습니다.

1. tx-1에서 level을 "READ UNCOMMITTED"로 변경 / "users" 테이블 전체 조회
2. tx-2에서 level을 "READ UNCOMMITTED"로 변경 / "users" 테이블 전체 조회
3. tx-1에서 트랜잭션 시작
4. tx-2에서 트랜잭션 시작
5. tx-1에서 insert('jang') 쿼리 실행
6. tx-2에서 "users" 테이블 전체 조회

그림과 같이 tx-1에서 insert 쿼리 실행 후 Commit을 실행하지 않아도 tx-2에서 데이터를 조회해옵니다.

tx-1에서 insert 쿼리 실행 후에 어떤 오류로 인해 Rollback이 된다면 tx-2에서는 없는 데이터로 처리 로직을 진행할 수도 있는 불상사가 생길 수도 있습니다.

즉, 트랜잭션이 끝나지 않은 상태임에도 불구하고 다른 트랜잭션에서 볼 수 있는 현상을 "Drity Read"라고 합니다.(신뢰할 수 없는 데이터를 읽어온다)

그리고 첫 번째 select쿼리의 결과 값과 두 번째 select쿼리의 결과 값이 다른 두 쿼리의 결과가 상이하게 나타나는 비 일관성 현상인 "Non-Repeatable Read"도 발생합니다. 또, 첫 번째 select쿼리에 없던 레코드(유령)가 두 번째 select 쿼리에서 나타나는 현상인 "Phantom Read"도 발생합니다.

 

✔ READ COMMITTED(level 1)

[session 38] tx-1

[session 41] tx-2

[session 38] tx-1

[session 41] tx-2

순서는 다음과 같습니다.

1. tx-1에서 level을 "READ COMMITTED"로 변경
2. tx-2에서 level을 "READ COMMITTED"로 변경
3. tx-1에서 트랜잭션 시작 / "users" 테이블 전체 조회
4. tx-2에서 트랜잭션 시작
5. tx-1에서 update('kim') 쿼리 실행(jang -> kim 변경) / select 쿼리 실행으로 update 확인
6. tx-2에서 "users" 테이블 전체 조회(tx-1에서 commit을 완료하지 않아서 name이 "kim"이 아닌 "jang"으로 가져온 것을 확인할 수 있다)
7. tx-1에서 commit 완료 / "users" 테이블 전체 조회
8. tx-2에서 "users" 테이블 전체 조회(이번에는 commit을 완료했기 때문에 "jang"이 아닌 "kim"을 가져온 것을 확인할 수 있다)

"READ COMMITTED"에서는 위와 같이 "Dirty Read" 현상은 발생하지 않는다. update 쿼리를 실행하게 되면 해당 레코드는 변경되고 이전 값인 "jang"은 undo 영역에 백업이 됩니다. Commit이 완료되기 전에 접근 시 해당 레코드 값이 아닌 undo 영역에 있는 데이터를 가져오는 것을 확인할 수 있고 Commit이 완료된 후에야 undo 영역이 아닌 해당 레코드의 데이터 값을 가져옵니다.

 

하지만 "READ COMMITTED" 문제는 첫 번째 select쿼리의 결과 값과 두 번째 select쿼리의 결과 값이 다른 두 쿼리의 결과가 상이하게 나타나는 비 일관성 현상인 "Non-Repeatable Read"도 발생합니다. 마찬가지로 insert 쿼리의 수행이 있었다면 "Phantom Read" 현상도 일어나게 됩니다.

 

✔ REPEATABLE READ(level 2)

[session 38] tx-1

[session 41] tx-2

[session 38] tx-1

[session 41] tx-2

 

순서는 다음과 같습니다.

1. tx-1에서 level을 "REPEATABLE READ"로 변경
2. tx-2에서 level을 "REPEATABLE READ"로 변경
3. tx-1에서 트랜잭션 시작 / "users" 테이블 전체 조회
4. tx-2에서 트랜잭션 시작
5. tx-2에서 update('kim') 쿼리 실행(jang -> kim 변경) / select 쿼리 실행으로 update 확인
6. tx-1에서 "users" 테이블 전체 조회(tx-2에서 commit을 완료하지 않은 상태이며 name은 undo 영역의 "jang"을 가져온 것을 확인할 수 있다)
7. tx-2에서 commit 완료 / "users" 테이블 전체 조회
8. tx-1에서 "users" 테이블 전체 조회(commit을 완료했지만 "READ COMMITTED"과는 다르게 "kim"이 아닌 "jang"을 가져온 것을 확인할 수 있다)

"REPEATABLE READ"에서는 "Dirty Read", "Non-Repeatable Read" 현상이 발생하지 않는 것을 확인할 수 있습니다.

"READ COMMITED"과는 다르게 tx-1에서 두 쿼리 모두 동일하게 "jang"을 가져온 것을 확인할 수 있습니다.

이러한 이유는 "REPEATABLE READ"에서는 트랜잭션 시작 시 시작되기 전에 Commit 된 데이터에 대해서만 조회할 수 있는 격리 수준이고, InnoDB의 트랜잭션은 자신만의 고유한 트랜잭션의 id가 부여되는데 자신보다 낮은 트랜잭션 번호에서 변경된 Commit 데이터만 볼 수 있습니다.

 

그리고 실습에서 확인한 결과 InnoDB인 "REPEATABLE READ"에서는 "Phantom Read" 현상이 발생하지 않는다.  Oracle DB에서는 "Phantom Read" 현상이 발생한다고 한다.

 

위에 대한 정확한 이유는 MySQL 공식문서에 자세한 내용이 나와있습니다. 다른 트랜잭션에 의해 변경 사항이 커밋된 경우에도 동일하게 유지하기 위해 "Snapshot"으로 기록하여 사용한다.

 

✔ SERIALIZABLE(level 3)

"SERIALIZABLE"의 경우는 위에서 발생하는 "Dirty Read", "Non-Repeatable Read", "Phantom Read" 현상이 발생하지 않아 가장 엄격한 격리 수준으로 완벽한 읽기 일관성 모드를 제공합니다.

안정성은 높지만 대신에 동시 처리 성능은 다른 격리 수준에 비해 현저히 떨어지게 됩니다.

 

 

 

🔗 Reference

• MySQL 공식문서