[MySQL] 트랜잭션 격리수준 적용

현재 커뮤니티 서비스를 개발하면서 데이터베이스를 구현 중이다.
MySQL 쿼리문을 통해 데이터를 다루는 코드 중에서 트랜잭션을 적용해야하는 쿼리를 작성하던 도중 트랜잭션의 격리성 및 동시성으로 인해 문제가 발생할 수 있다는 것을 알게되었다.

따라서 격리성은 트랜잭션 격리수준을, 동시성은 트랜잭션 잠금을 통해 해결해보도록 하겠다.

다음은 트랜잭션 격리수준, 트랜잭션 잠금을 적용하기 전의 코드이다.

START TRANSACTION;

INSERT INTO comments (
    comment,
    postId,
    userId,
    created_at
) VALUES (
    '${data.comment}',
    ${data.postId},
    ${data.userId},
    NOW()
);

UPDATE posts
    SET comment_count = comment_count + 1
    WHERE postId = ${data.postId}

COMMIT;

트랜잭션 격리 수준 적용

위의 코드는 Phantom Read의 문제를 발생시킬 수 있다.

Phantom Read란 같은 트랜잭션 내에서 범위를 포함하는 쿼리의 결과가 트랜잭션 동안 다른 트랜잭션에 의해 변경되는 경우이다.
예를 들어, 댓글을 삽입하는 동안 다른 트랜잭션이 새로운 댓글을 삽입하거나 기존 댓글을 삭제할 수 있고. 이 경우, 트랜잭션이 시작될 때와 끝날 때의 댓글 수가 다를 수 있습니다.
그렇다면 이러한 현상을 방지하기 위해 트랜잭션 격리수준을 적용할 수 있다.

여기서는 트랜잭션 내에서 삭제, 변경은 Undo 로그에 넣고 앞서 발생한 트랜잭션에 대해서는 실제 데이터가 아닌 Undo 로그에 있는 데이터를 읽게 하는 Repeatable Read 격리 수준을 적용하겠다.

적용하는 방법은 간단하다.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
위의 코드를 START TRANSACTION 아래에 사용을 하면 된다.

//트랜잭션 격리성 설정 -> Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

INSERT INTO comments (
    comment,
    postId,
    userId,
    created_at
) VALUES (
    '${data.comment}',
    ${data.postId},
    ${data.userId},
    NOW()
);

UPDATE posts
    SET comment_count = comment_count + 1
    WHERE postId = ${data.postId}

COMMIT;

이렇게 한다면 대부분의 격리서 문제는 해결이 된다.

트랜잭션 잠금 적용

트랜잭션 잠금을 적용하는 이유는 여러 트랜잭션이 동시에 실행될 때 발생할 수 있는 race condition을 방지하기 위해 사용된다.

트랜잭션 잠금 예시
SELECT ... FROM UPDATE 는 특정 행에 대해 배타적 잠금을 설정하여 다른 트랜잭션이 해당 행을 읽거나 수정하지 못하게 하는 잠금이다.

다음은 트랜잭션 잠금을 적용한 나의 코드이다.

//트랜잭션 격리성 설정 -> Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

//트랜잭션 잠금
SELECT * FROM posts WHERE postId = ${postId} FOR UPDATE;

INSERT INTO comments (
    comment,
    postId,
    userId,
    created_at
) VALUES (
    '${data.comment}',
    ${data.post_id},
    ${data.user_id},
    NOW()
);

UPDATE posts
    SET comment_count = comment_count + 1
    WHERE post_id = ${data.post_id}

COMMIT;

트랜잭션 잠금을 사용하면 주어진 post_id에 해당하는 행에 대해 배타적 잠금을 건다. 이렇게 한다면 다른 트랜잭션이 이 행을 읽거나 수정하지 못해 트랜잭션의 무결성을 유지할 수 있게 한다.

또한 경쟁 조건을 방지한다. 두 개의 트랜잭션이 동시에 댓글을 추가하고 댓글 수를 증가시키려고 할 때, 배타적 잠금이 없으면 두 트랜잭션이 동시에 댓글 수를 증가시키는 문제가 발생할 수 있다.

마지막으로 Phantom Read를 방지한다. 해당 행이 트랜잭셔 동안 변경되지 않도록 보장한다.