[MySQL] 트랜잭션 고립 레벨이란 무엇일까?

MySQL에서의 트랜잭션

MySQL의 동시성에 영향을 미치는 잠금(Lock), 트랜잭션, 그리고 트랜잭션의 격리 수준을 살펴보겠습니다. 트랜잭션은 작업의 완전성을 보장해 주는 것입니다.

• 잠금: 트랜잭션은 서로 비슷한 개념 같지만 사실 잠금은 동시성을 제어하기 위한 기능입니다.
• 트랜잭션: 데이터의 정합성을 보장하기 위한 기능입니다.

잠금은 여러 커넥션에서 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할

격리 수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨

 

 

MySQL의 격리 수준

트랜잭션의 격리 수준(isolation level)이란 동시에 여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것입니다.
격리 수준은 크게 아래와 같이 4가지로 나눌 수 있습니다.

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

 

READ UNCOMMITTED 일반적인 데이터베이스에서는 거의 사용하지 않고, SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서는 사용하지 않습니다. 4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리 정도가 높아지며,
동시에 동시성도 떨어지는 것이 일반적이라고 볼 수 있습니다.

 

 

 

READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관 없이 다른 트랜잭션에서 보여집니다.

 

위의 그림에서 보면, A 사용자가 INSERT 한 데이터가 COMMIT이 되기 전에 B 라는 사용자가 SELECT를 통해서 읽어오는 것을 볼 수 있습니다. 만약에 여기서 A 사용자가 INSERT 한 데이터가 롤백이 된다면 어떻게 될까요?

그래도 여전히 B 사용자는 롤백 전의 데이터를 가지고 계속 처리할 것입니다. 이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상을 더티 리드(Dirty read)라고 합니다.

 

즉, MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장합니다.

 

 

 

READ COMMITTED

READ COMMITTED은 오라클 DBMS에서 기본적으로 사용되고 있는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준입니다.
어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문입니다.

사용자 A는 emp_no=500000 사원의 이름을 Lara -> Toto로 변경했는데, 이 때 새로운 값인 Toto는 employees 테이블에 즉시 기록되고 이전 값인 Lara는 언두 영역으로 백업됩니다. 사용자 A는 커밋을 수행하기 전에 사용자 B가 emp_no=500000인 사원을 SELECT 하면 조회된 결과의 first_name 컬럼의 값은 Toto가 아니라 Lara로 조회됩니다.

 

READ COMMITTED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없기 때문입니다. 최종적으로 사용자 A가 변경된 내용을 커밋하면 그때부터는 다른 트랜잭션에서도 새롭게 변경된 값을 읽어올 수 있게 됩니다.

 

 

 

NON-REPEATABLE READ

B가 BEGIN 명령으로 트랜잭션을 시작하고 first_name이 Toto인 사용자를 검색했는데 일치하는 사용자가 없었습니다. 하지만 A가 사원번호가 500000인 사원의 이름을 Toto로 변경하고 커밋을 실행한 이후 사용자 B가 다시 SELECT로 같은 사원을 조회하면 이번에는 Toto가 반환이 되는 것을 볼 수 있습니다.

 

이는 사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋나는 것입니다. 이러한 부정합 현상은 일반적인 상황에서는 크게 중요하지 않을 수 있지만 금전적인 처리와 연결되면 문제가 될 수도 있습니다.

 

 

 

REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준입니다. 바이너리 로그를 가진 MySQL의 장비에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다고 합니다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경합니다. 이러한 변경 방식을 MVCC라고 합니다.

REPEATABLE READ는 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장합니다. READ COMMITTED와 MVCC를 이용해 COMMIT되기 전의 데이터를 보여줍니다. REPEATABLE READ와 READ COMMITTED의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하는지에 있다고 합니다.

위의 그림은 REPEATABLE READ 격리 수준이 작동하는 방식을 보여줍니다. 기존에 INSERT 되어 있는 데이터는 트랜잭션 ID가 6이라고 가정하겠습니다. 여기서 사용자 A가 emp_no가 500000인 사원의 이름을 변경하는 과정에서 사용자 B가 emp_no=500000인 사원을 SELECT할 때 어떤 과정을 거쳐서 처리되는지 보여줍니다.

 

사용자 A의 트랜잭의 트랜잭션 번호는 12였으며 사용자 B의 트랜잭션의 번호는 10이었습니다. 이 때 사용자 A는 사원의 이름을 Toto로 변경하고 커밋을 했는데요. 이 때 사용자 B는 emp_no=500000인 사람을 커밋 전후로 SELECT 한 것을 볼 수 있습니다. 그런데 같은 결과를 반환받고 있는데요. 즉, 사용자 B가 BEGIN을 통해 트랜잭션 ID 10번을 받고, 트랜잭션 10번 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10(자신의 트랜잭션 번호) 보다 작은 트랜잭션 번호에서 변경한 것만 보게 되는 것입니다.

 

 

 

REPEATABLE READ 부정합

REPEATABLE READ 격리 수준에서도 부정합이 발생할 수 있습니다. 아래는 사용자 A가 employees 테이블에 INSERT를 실행하는 도중에 사용자 B가 SELECT ... FOR UPDATE 쿼리로 employees 테이블을 조회했을 때 어떤 결과를 나타내는지에 대한 그림입니다.

 

아래 그림을 보면 사용자 B는 BEGIN으로 트랜잭션을 시작한 후, SELECT를 수행하고 있습니다. 두 SELECT의 결과가 같아야 하지만, 결과가 다르게 나오는 것을 볼 수 있습니다.

이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안 보였다가 하는 현상을 PHANTOM READ라고 합니다.

 

 

 

PHANTOM READ vs NON REPEATABLE READ 차이

NON REPEATABLE READ는 트랜잭션이 진행되는 동안 행이 두 번 검색되고 행 내의 값이 읽기 간에 다를 때 발생합니다.

 

PHANTOM READ는 트랜잭션 과정에서 두 개의 동일한 쿼리가 실행되고 두 번째 쿼리에서 반환된 행 모음이 첫 번째 쿼리와 다를 때 발생합니다.

 

여기 글을 같이 참고하면 이해하는데 많은 도움이 될 것 같습니다.

 

 

SERIALIZABLE

가장 단순한 격리 수준이지만 가장 엄격한 격리 수준입니다. 또한 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어집니다.
InnoDB 테이블에서 SELECT 쿼리는 레코드 잠금도 설정하지 않고 실행되지만, SERIALIZABLE 격리 수준에서는 읽기 작업도 공유 작업 을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 합니다.

Reference

• Real MySQL