MySQL Repeatable Read 격리 수준의 트랜잭션 이해와 예제

이 글에서는 MySQL의 Repeatable Read 격리 수준에서
트랜잭션 동작 방식과 트랜잭션 락킹에 대해 설명합니다.

이해를 돕기 위해 간단한 예시를 사용하며 설명합니다.

Repeatable Read

Repeatable Read 는 InnoDB 기본 격리 수준입니다.

트랜잭션 내에 읽기에 대한 일관성을 스냅샷을 기준으로 보장하며

InnoDB 에서는 Next Key Lock 을 사용하며 Phantom Read 까지 예방합니다.

 

스냅샷을 기준으로 일관성을 보장한다는 것은,

현재 실행하고 있는 트랜잭션 id 보다 전의 id 를 갖는 언두 로그를 참조한다는 뜻입니다..

따라서 SELECT 시에 다른 트랜잭션에서 업데이트하며 해당 데이터가 바뀌더라도 현재의 트랜잭션에서는 일관성을 유지합니다.

 

위와 같은 상황은 MVCC(Multi-Version Concurrency Control) 로 관리되며,

MVCC 는 잠금을 필요로 하지 않기 때문에 보다 나은 성능을 기대할 수 있습니다.

 

다음과 같은 상황을 가정하며 확인해보겠습니다.

 

id	int pk
name	varchar

 

위의 테이블에  id = 1, name = 'test' 라는 레코드가 있다고 가정하겠습니다.

1. Transaction A 는 SELECT 합니다.
2. 도중에 Transaction B 가 name 을 test1 로 변경하여 commit 합니다.
3. 이후 다시 Transaction A 가 SELECT 쿼리를 실행합니다.

위와 같은 상황에서도 Transaction A에서는 MVCC에 의해 다시 id = 1, name = 'test' 로 조회되어야 할 것입니다.

실제로 해보며 살펴보겠습니다.

 

 

Transaction A 에서는 transaction 을 시작하며 조회합니다.

 

 

 

Transaction B 에서는 name 을 test1 로 변경하여 commit 합니다.

 

 

 

다시 Transaction A 에서 조회했을 때 기대했던 대로 조회되는 것을 확인할 수 있습니다.

 

 

 

Transaction Locking

https://liltdevs.tistory.com/190

InnoDB Locking 기법 정리

 

InnoDB Transaction 에서는 명시적으로 Lock 설정을 하지 않더라도

INSERT, DELETE, UPDATE 를 실행하며 Exclusive Lock 이 걸리게 됩니다.

정확히는, 처음 시작한 트랜잭션에서 테이블에 IX-Lock 을 걸고 있고 다음에 들어온 트랜잭션에서 IX-Lock 을 걸며

X-Lock 으로 upgrade 해야 할 Lock 이 있는지 판단하여 쓰거나 혹은 자신이 X-Lock 을 설정합니다.

이를 통해 동시에 같은 레코드를 변경하는 다른 트랜잭션의 충돌을 방지하고, 데이터 무결성을 보장합니다.

 

다음과 같은 상황을 가정하며 이해하겠습니다.

Transaction 은 3개가 있습니다.

1. Transaction A가 id(Auto Increment 가 아닌) 가 2인 레코드를 INSERT 합니다. (commit 하지 않습니다.)
2. Transaction B가 id 가 2인 레코드를 INSERT 합니다.
3. Transaction C가 id 가 2인 레코드를 SELECT 합니다.

위에서 상술한 대로면, A에서 INSERT 가 되고 B에서 Exclusive Lock에 걸려 대기하며

C에서는 MVCC 에 의해 A의 변경 사항을 보지 못하며 찾을 수 없습니다.

 

실제로 수행하며 알아보겠습니다.

 

SELECT
    dl.ENGINE_TRANSACTION_ID as trx_id, dl.thread_id,est.sql_text,
    dl.lock_type,dl.lock_mode,dl.lock_status,dl.lock_data
FROM performance_schema.data_locks dl
INNER JOIN performance_schema.events_statements_current est 
ON dl.thread_id = est.thread_id
ORDER BY est.timer_start,dl.object_instance_begin;

아래의 예제에서 확인할 Lock 을 위한 쿼리입니다.

실제로 데이터베이스에서는 Lock 을 어떻게 관리하는지 확인하기 위함입니다.

 

 

 

Transaction A 에서 insert 하며 commit 하지 않았습니다.

 

 

 

위의 쿼리를 실행해 Lock 이 걸려있는지 실제로 조회한 결과입니다.

transaction id 14908 로 테이블에 IX-Lock 을 설정한 것을 확인할 수 있습니다.

위에 링킹한 InnoDB Locking 기법이라는 글에서도 확인할 수 있듯

앞으로 들어올 transaction 에서는 위의 IX-Lock 을 확인하여 Lock 에 대한 기준을 세울 것입니다.

 

 

 

Transaction B 에서도 동일한 데이터를 insert 해본 결과 Lock wait timeout 으로 종료된 것을 확인할 수 있습니다.

 

 

 

wait timeout 이 발생하기 이전에 Lock 이 어떻게 설정되어있는지 확인한 결과입니다.

thread 3501 이 14913 transaction 을 시작하며 IX-Lock 을 설정합니다.

이에 3501 은 기존 transaction 에서 IX-Lock 이 설정된 것을 확인하고,

14908 의 레코드를 write 하기 위해 X-Lock 을 설정합니다.

(이는 IX 락을 X 락으로 upgrade 했다고 표현할 수 있습니다, IX 락보다 X 락이 레코드에 더 강력한 제한을 두기 때문입니다.)

 

또한 3501 번은 14913 으로 다시 돌아와 X-Lock 을 걸려고 시도했으나, 

다른 트랜잭션에서 X-Lock 이 걸려 있어 S-Lock 을 설정하고 대기합니다.

(이러한 것을 Lock Escalation 이라고 합니다.)

 

따라서 InnoDB 는 위와 같은 락 기법을 통해 같은 레코드에 대한 데이터의 유일성을 보장하는 것입니다.

 

 

 

Transaction C 에서도 기대한 결과값과 동일했습니다.

애초에 조회할 레코드가 없다고 판단했으므로 Empty set 을 반환합니다.

(만약, Read Uncommitted 수준이라면 조회가 가능할 것입니다.)

 

예제와 함께 Repeatable Read 에 대해서 알아보았습니다.

Repeatable Read 는 InnoDB 기본 격리 수준으로 일관성 있는 읽기 작업과

높은 동시성을 보장하며, 트랜잭션 처리에 있어 안정적인 성능을 제공합니다.

 

끝!