MySQL 격리 수준

Woody·2024년 8월 2일

TIL

목록 보기

5/19

트랜잭션의 격리 수준이란?

특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것으로 크게 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 4가지 격리 수준이 있다.

READ UNCOMMITTED

특정 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 격리 수준으로 이를 더티 리드(DIRTY READ)라 한다.

READ COMMITTED

가장 많이 선택되는 격리 수준으로 커밋이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있는 격리 수준이기 때문에 더티 리드가 발생하지 않는다.

하지만 NON-REPEATABLE READ 문제가 발생할 수 있다.

NON-REPEATABLE READ가 발생하는 과정

	SESSION 1	SESSION 2
1	BEGIN;	BEGIN;
2	SELECT … FROM A WHERE NAME = ‘KWY’;
[결과 0건]
3		UPDATE SET NAME = ‘KWY’
4		COMMIT;
5	SELECT … FROM A WHERE NAME = ‘KWY’;
[결과 1건]
6	COMMIT;

A 트랜잭션에서 특정 레코드를 조회한 결과와 B 트랜잭션이 해당 데이터를 변경하고 커밋한 이후, 다시 A 트랜잭션에서 동일한 레코드를 조회했을 때 결과가 달라질 수 있다.

이를 NON-REPEATABLE READ라 하며, 이는 항상 같은 결과를 가져와야 한다는 “REPEATABLE READ” 정합성에 어긋난다.

REPEATABLE READ

MySQL의 InnoDB에서 기본으로 사용되는 격리 수준으로 트랜잭션에서는 항상 동일한 데이터를 조회할 수 있는 격리 수준으로 “NON-REPEATABLE READ”가 발생하지 않는다.

하지만 다른 트랜잭션에서 수행한 작업으로 인해 레코드가 보였다 안 보였다 하는 현상인 팬텀 리드(PHANTOM READ)가 발생할 수 있다.

NON-REPEATABLE READ , 팬텀 리드 차이

Non-repeatable reads are when your transaction reads committed UPDATES from another transaction. The same row now has different values than it did when your transaction began.*

Phantom reads are similar but when reading from committed INSERTS and/or DELETES from another transaction. There are new rows or rows that have disappeared since you began the transaction.*

NON-REPEATABLE READ는 특정 트랜잭션에서 레코드를 변경(UPDATE)한 후 커밋한 결과로 변경된 레코드를 불러오는 현상을 말한다.

팬텀 리드는 특정 트랜잭션에서 레코드를 INSERT 또는 DELETE한 후 커밋한 결과로 레코드가 추가로 조회되거나 이전보다 덜 조회되는 현상을 말한다.

팬텀 리드가 발생하는 과정

	SESSION 1	SESSION 2
1	BEGIN;	BEGIN;
2	SELECT … FROM A WHERE NAME = ‘KWY’ FOR UPDATE; [0건]
3		INSERT INTO A (’KWY’)
4		COMMIT;
5	SELECT … FROM A WHERE NAME = ‘KWY’ FOR UPDATE; [1건]
6	COMMIT;

A 트랜잭션에서 특정 레코드를 조회한 결과와 B 트랜잭션이 레코드를 추가 또는 제거하고 커밋한 이후 다시 A 트랜잭션에서 SELECT … FOR UPDATE 쿼리로 동일한 데이터를 조회했을 때 팬텀 리드가 발생한다.

SELECT … FOR UPDATE 쿼리는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오기 때문에 팬텀 리드가 발생한다.

하지만 InnoDB에서는 REPEATABLE READ 격리 수준에서도 팬텀 리드 현상을 배제할 수 있다.

어떻게 REPEATABLE READ 격리 수준에서 팬텀 리드 현상을 배제할 수 있을까?

InnoDB에서는 갭 락과 넥스트 락 덕분에 REPEATABLE READ 격리 수준에서도 일반적으로 팬텀 리드가 발생하지 않는다.

따라서 위 예제에서 2번 쿼리가 실행되면 3번 쿼리는 session1 트랜잭션이 끝날 때까지 기다리게 되어 팬텀 리드가 발생하는 것을 막는다.

하지만 다음과 같은 경우에는 팬텀 리드가 발생한다.

	SESSION 1	SESSION 2
1	BEGIN;	BEGIN;
2	SELECT … FROM A WHERE NAME = ‘KWY’;
[결과 0건]
3		INSERT INTO A (’KWY’)
4		COMMIT;
5	SELECT … FROM A WHERE NAME = ‘KWY’ FOR UPDATE;
[결과 1건]
6	COMMIT;

2번 과정에서 락을 걸지 않기 때문에 5번 과정에서 팬텀 리드 현상이 발견될 것이다.

레코드 락

레코드 자체를 잠그는 것으로 다른 DBMS와 마찬가지로 공유 락(Shared Lock), 배타 락(Exclusive Lock)이 있다. 차이점으로는 이전 글에서 말했듯 MySQL에서의 레코드 락은 인덱스의 레코드를 잠근다.

갭 락

레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격에 있는 빈 공간을 잠그는 것을 의미하며, 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어한다.

예를 들어 id가 13, 17인 레코드만 있는 테이블에 아래의 쿼리를 실행할 때,

SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE;

10과 20 사이 중 레코드가 없는 빈 공간(GAP)을 잠근다. 즉, 위 트랜잭션이 끝날 때까지 10에서 12사이, 14에서 16사이, 18에서 20사이의 빈 공간을 잠근다.

넥스트 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다. 다음 그림을 통해 자세히 알아보자

만일 위 예제에서 SELECT * WHERE pk > 99 AND pk < 102 FOR UPDATE를 실행시켰었다면, 발견 직전의 인덱스 레코드 97과 직후 발견되는 인덱스 레코드인 103 사이인 97 < pk < 103의 범위에 대해서 Lock이 걸린다.