MySQL transaction level

석헌주·2021년 11월 24일

mysql 을 사용할 때 데이터양이 커지면서 발견되는 특징적인 문제들

이러한 문제가 생기는 원인은 mysql transaction isolation level의 특징 때문이다. transaction isolation level은 transaction의 격리 수준을 말한다.

commit 되지 않은 데이터에 다른 트랜잭션에서 접근할 수 있다.
- 다른 트랜잭션에서 commit되지 않은 데이터들을 읽어올 수 있음
- 만약 다른 트랜잭션에서 rollback이 된다면 존재하지 말아야 할 데이터를 읽어올 수도 있음 (dirty read)
- 결과적으로 한 트랜잭션에서 동일한 select문의 query 결과가 다르다.
insert, update, delete 후 commit이나 rollback에 상관없이 현재의 데이터를 읽어온다.
rollback이 될 데이터도 읽어올 수 있으므로 주의가 필요
lock이 발생하지 않는다.

commit된 데이터에 다른 트랜잭션에서 접근할 수 있다.
- 다른 트랜잭션에서 commit한 데이터만 읽어올 수 있음
- 아직 commit되지 않은 신뢰할 수 없는 데이터는 읽어올 수 없음
- READ UNCOMMITTED와 마찬가지로 한 트랜잭션에서 동일한 select 문의 결과가 달라질 수 있음
구현 방식의 차이 때문에 쿼리를 수행한 시점의 데이터와 정확하게 일치하지 않을 수 있다.
lock이 발생하지 않는다
mysql에서 많은 양의 데이터를 복제하거나 이동할 때 이 level을 추천한다.

default level
select시 현재 시점의 스냅샷을 만들고 스냅샷을 조회한다.
- REPEATABLE READ와 READ COMMITTED level에 대해서 한 트랜잭션에서 select 쿼리로 데이터를 읽어올 때 테이블에 lock을 걸지 않고, 해당 시점의 데이터 상태를 의미하는 snapshot을 구축하여 거기서 데이터를 읽어온다.
- READ COMMITTED의 SELECT 쿼리는 그때그때 최신의 snapshot을 구축하여 데이터를 읽어오기 때문에 동일한 트랜잭션 에서 SELECT 쿼리의 결과가 다르지만, REPEATABLE READ는 한 트랜잭션에서 처음데이터를 읽어올 때 구축한 snapshot에서 모두 데이터를 읽어오기 때문에 SELECT 쿼리의 결과가 항상 처음과 동일했고 phantom read도 발생하지 않았던 것
- REPEATABLE READ 에서 같은 트랜잭션의 SELECT쿼리가 항상 동일하더라도 다른 트랜잭션에서 건드린 row에 대한 update, delete의 결과는 출력될 수 있다.
동일 트랜잭션 내에서 일관성을 보장한다.
record lock과 gap lock이 발생한다.
create select, insert select시 lock이 발생한다.

가장 강력한 level
select문에 사용하는 모든 테이블에 shared lock이 발생한다.
- SERIALIZE level로 트랜잭션을 시작하면, 해당 트랜잭션이 commit되지 않는 이상 다른 트랜잭션에서는 SELECT문으로 조회만 가능하고 update나 insert는 동작하지 않는다.