트랜잭션 격리수준 확인 및 변경하기
트랜잭션 격리수준 확인하기
SELECT @@GLOBAL.tx_isolation;
SELECT @@SESSION.tx_isolation;
SHOW variables LIKE '%isolation';
SQL
복사
트랜잭션 격리 수준은 위의 3개 명령어를 통해 확인할 수 있다.
@@는 서버 시스템 변수로 GLOBAL은 서버 전체, SESSION은 현재 클라이언트 세션에 대한 정보를 가져올 수 있다.
GLOBAL 변수를 통해 MariaDB의 경우 default 설정이 REPEATABLE-READ로 되어있는 것을 확인할 수 있다.
트랜잭션 격리수준 변경하기
트랜잭션의 격리수준을 변경하는 방법은 서버를 띄울 때 mysqld.conf에 설정을 해주는 방법과 명령어를 통해 변경하는 방법이 있다.
[mysqld]
transaction-isolation = REPEATABLE-READ
transaction-read-only = OFF
Plain Text
복사
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SQL
복사
트랜잭션 격리수준을 변경 후 SESSION 변수에서 격리수준을 확인해보면 잘 변경된 것을 확인할 수 있다.
Serializable
먼저 두 개의 세션을 열고 autocommit을 꺼두고, 각 트랜잭션 격리수준을 Serializable로 변경한다.
트랜잭션 A에서 조회를 수행하고
트랜잭션 B에서 데이터를 수정하려고 시도하면 이처럼 락을 얻으려 잠금 대기하게 된다.
만약 일정시간동안 락을 얻지 못하면 아래와 같이 timeout이 발생한다.
이 때 트랜잭션 A에서 commit이나 rollback을 통해 트랜잭션을 종료하게 되면, 해당 락을 풀고 트랜잭션 B에서는 락을 얻어 수행을 완료하게 된다.
위 상황에서의 락을 살펴보면 트랜잭션 A에서 S Lock을 얻고, B에서 X락을 얻기 위해 대기하고 있게 된다.
REPEATABLE READ
트랜잭션 A에서 조회를 했을 때 위와 같이 조회가 된다.
이 때 트랜잭션 B에서 동일한 데이터를 수정한다.
그 후 트랜잭션 A에서 동일한 데이터를 다시 조회해도, 수정된 데이터가 조회되지 않는다.(non-repeatable read 현상 없음 - 트랜잭션 A 이전에 커밋된 내용만 읽어옴)
하지만 트랜잭션 B에서 변경된 이름으로 제약 조건을 설정하여 update 문을 실행시키면 동작에 실패하는 update 부정합이 발생한다.
또한 SELECT 쿼리 수행 시 FOR UPDATE 등과 같은 구문을 통해 잠금을 얻어 조회를 수행하게 되면, 기존의 조회와 결과가 달라지는 update 부정합도 발생하게 된다.(phantom read 현상)
Read Committed
트랜잭션 A에서 조회를 했을 때 위와 같이 조회가 된다.
이 때 트랜잭션 B에서 동일한 데이터를 수정한다.(commit 되지 않음)
트랜잭션 A에서 다시 조회를 수행해도 수정사항이 반영되어 보이지 않는다.(dirty read 현상 없음 - commit 되지 않은 데이터 수정사항 반영되지 않음)
트랜잭션 B를 commit하고
트랜잭션 A에서 다시 조회해보면 수정된 값이 조회된다.(non-repeatable read 현상)
Read Uncommitted
트랜잭션 없이 데이터를 조회하면 위와 같이 조회가 된다.
이 때 트랜잭션 A에서 데이터를 수정한다.(commit 되지 않음)
트랜잭션이 없는 상태에서 다시 조회를 해보면 이처럼 commit되지 않은 값이 조회된다.(dirty read 현상)
