[Real MySQL 1권] MySQL의 격리 수준 - READ UNCOMMITTED, READ COMMITTED
1) MySQL의 격리 수준
- 트랜잭션의 격리 수준(isolation level)이란 여러 트랜잭션이 동시에 처리될 때,
특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.
격리 수준은 크게 "READ UNCOMMITTED", "READ COMMITTED", "REPETABLE READ", "SERIALIZABLE"
의 4가지로 나뉜다.
- "DIRTY READ"라고도 하는 READ UNCOMMITTED는 일반적인 데이터베이스에서는 거의 사용하지 않고,
SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다.
- 4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며,
동시 처리 성능도 떨어지는 것이 일반적이라고 볼 수 있다.
격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것으로 생각하는 사용자가 많은데,
사실 SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.
- 데이터베이스의 격리 수준을 이야기하면 항상 함께 언급되는 세 가지 부정합의 문제점이 있다.
이 세 가지 부정합의 문제는 격리 수준의 레벨에 따라 발생할 수도 있고 발생하지 않을 수도 있다.
2) READ UNCOMMITTED
- READ UNCOMMITTED 격리 수준에서는 위 그림과 같이 각 트랜잭션에서의 변경 내용이
COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.
- 위의 그림에서 사용자 A는 emp_no가 500000이고, first_name이 "Lara"인 새로운 사원을 INSERT한다.
사용자 B가 변경된 내용을 커밋하기도 전에 사용자 B는 emp_no=500000인 사원을 검색하고 있다.
하지만 사용자 B는 사용자 A가 INSERT한 사원의 정보를 커밋되지 않은 상태에서도 조회할 수 있다.
- 이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty read)
라고 하고, 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED다.
- 더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로
애플리케이션 개발자와 사용자를 상당히 혼란스럽게 만든다.
더티 리드를 유발하는 READ UNCOMMITTED는 RDBMS 표준에서는
트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다.
- MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장한다.
3) READ COMMITTED
- READ COMMITTED는 오라클 DBMS에서 기본으로 사용되는 격리 수준이며,
온라인 서비스에서 가장 많이 선택되는 격리 수준이다.
이 레벨에서는 위에서 언급한 더티 리드(Dirty read) 같은 현상은 발생하지 않는다.
- 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.
- 위의 그림에서 사용자 A는 emp_no=500000인 사원의 first_name을 "Lara"에서 "Toto"로 변경했는데,
이 때 새로운 값인 "Toto"는 employees 테이블에 즉시 기록되고 이전 값인 "Lara"는 언두 영역으로 백업된다.
- 사용자 A가 커밋을 수행하기 전에 사용자 B가 emp_no=500000인 사원을 SELECT하면
조회된 결과의 first_name 칼럼의 값은 "Toto"가 아니라 "Lara"로 조회된다.
여기서 사용자 B의 SELECT 쿼리 결과는 employees 테이블이 아니라 언두 영역에 백업된 레코드에서 가져온 것이다.
- READ COMMITED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는
다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없다.
최종적으로 사용자 A가 변경된 내용을 커밋하면 그때부터는 다른 트랜잭션에서도 백업된 언두 레코드("Lara")가 아니라
새롭게 변경된 "Toto"라는 값을 참조하게 된다.
- READ COMMITTED 격리 수준에서도 "NON-REPEATABLE READ"라는 부정합의 문제가 있다.
아래 그림은 "NON-REPETABLE READ"가 왜 발생하고 어떤 문제를 만들어낼 수 있는지 보여준다.
- 처음 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하고, first_name이 "Toto"인 사용자를 검색했는데,
일치하는 결과가 없었다.
하지만 사용자 A가 사원 번호가 500000인 사원의 이름을 "Toto"로 변경하고 커밋을 실행한 후,
사용자 B가 똑같은 SELECT 쿼리로 다시 조회하면 이번에는 결과가 1건이 조회된다.
- 이는 별다른 문제가 없어 보이지만,
사실 사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는
항상 같은 결과를 가져와야 한다는 "REPETABLE READ" 정합성에 어긋난다.
- 이러한 부정합 현상은 일반적인 웹 프로그램에서는 크게 문제되지 않을 수 있지만
하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이
금전적인 처리와 연결되면 문제가 될 수 있다.
- 예를 들어, 다른 트랜잭션에서 입금과 출금 처리가 계속 진행될 때
다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회한다고 가정해보자.
그런데 "REPETABLE READ"가 보장되지 않기 때문에
총합을 계산하는 SELECT 쿼리는 실행될때마다 다른 결과를 가져온다.
- 중요한 것은 사용중인 트랜잭션의 격리 수준에 의해 실행하는 SQL 문장이 어떤 결과를 가져오게 되는지를
정확하게 예측할 수 있어야 한다는 것이다.
그리고 당연히 이를 위해서는 각 트랜잭션의 격리 수준이 어떻게 작동하는지 알아야 한다.