MySQL 트랜잭션 격리 수준, 그 차이와 특징 한눈에 보기

데이터베이스를 운영하다 보면 여러 트랜잭션이 동시에 실행될 때, 데이터 정합성과 동시성 제어를 위해 격리 수준이 중요해집니다. MySQL은 다양한 격리 수준을 제공하여 각 상황에 맞게 데이터의 일관성을 보장하는데요. 오늘은 SERIALIZABLE, REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED 네 가지 격리 수준의 특징과 장단점을 살펴보겠습니다.

1. SERIALIZABLE – 가장 엄격한 격리, 하지만 속도는 느리다

SERIALIZABLE은 이름 그대로 모든 트랜잭션을 순차적으로 실행하는 것처럼 동작합니다.

• 특징:
  • 여러 트랜잭션이 동일한 레코드에 동시에 접근하는 것을 원천 차단하여, 데이터 부정합(불일치)이 발생하지 않습니다.
  • 순차적으로 처리되므로 동시 작업의 성능은 크게 저하됩니다.
• 잠금 방식:
  • SELECT FOR SHARE 또는 SELECT FOR UPDATE를 사용하면 대상 레코드에 읽기 또는 쓰기 잠금을 걸지만, 보통 순수 SELECT는 잠금 없이 실행됩니다.
  • 단, SERIALIZABLE 수준에서는 순수 SELECT에도 넥스트 키 락을 걸어, 다른 트랜잭션이 해당 데이터를 수정하지 못하도록 합니다.

안전성은 최고지만, 동시성 요구가 높은 시스템에서는 사용하기 어려운 격리 수준입니다.

2. REPEATABLE READ – 한 번 읽은 데이터는 변하지 않는다

REPEATABLE READ는 MySQL의 기본 격리 수준으로, 트랜잭션 내에서 같은 데이터를 여러 번 읽어도 결과가 일관되게 유지됩니다.

• MVCC (다중 버전 동시성 제어):
  • 트랜잭션이 시작되면 고유 번호가 부여되고, 데이터 변경 전의 상태를 언두 로그에 백업합니다.
  • 이를 기반으로 트랜잭션은 자신이 시작되기 전의 커밋된 데이터만 조회하므로, 중간에 다른 트랜잭션이 데이터를 변경해도 처음 읽은 데이터가 그대로 유지됩니다.
• 유령 읽기(Phantom Read):
  • 기존 레코드의 일관성은 보장되지만, 트랜잭션 실행 중 다른 트랜잭션이 새 레코드를 추가하면 조회 결과에 변화가 생길 수 있습니다.
  • MySQL은 갭 락(gap lock)을 사용해, SELECT FOR UPDATE 같은 잠금이 걸린 경우 유령 읽기를 방지합니다.

즉, REPEATABLE READ는 한 번 읽은 데이터의 일관성을 보장하는 대신, 새 레코드의 추가로 인한 변화는 완벽히 막지 못할 수 있습니다.

3. READ COMMITTED – 커밋된 데이터만 보여준다

READ COMMITTED는 이름 그대로, 오직 커밋된 데이터만 읽어들이는 격리 수준입니다.

• 주요 특징:
  • 다른 트랜잭션의 변경 사항이 커밋되기 전에는 보이지 않으므로, Dirty Read(오손 읽기) 문제를 방지합니다.
  • 단, 한 트랜잭션 내에서 여러 번 SELECT을 실행하면, 중간에 다른 트랜잭션이 데이터를 변경해 커밋된 경우 이전과 다른 결과가 나타날 수 있습니다.
    → 이는 Non-Repeatable Read(반복 불가능 읽기)나 유령 읽기(Phantom Read) 현상으로 이어질 수 있습니다.
• 사용 시 고려사항:
  • READ COMMITTED는 트랜잭션 없이 실행되는 SELECT와 트랜잭션 내에서 실행되는 SELECT 간의 결과 차이가 거의 없기 때문에, 단순 조회 작업에서는 일관된 커밋 데이터를 볼 수 있습니다.
  • 다만, 동일 트랜잭션 내에서 데이터가 변경될 가능성이 있으므로, 절대적인 일관성이 필요한 경우라면 REPEATABLE READ나 SERIALIZABLE 같은 수준을 고려해야 합니다.

READ COMMITTED는 많은 DBMS에서 기본적으로 채택하는 격리 수준으로, 안전성과 성능의 균형을 맞춘 선택지라고 할 수 있습니다.

4. READ UNCOMMITTED – 가장 낮은 격리, 위험한 선택

READ UNCOMMITTED는 아직 커밋되지 않은 데이터까지 읽어들일 수 있는 격리 수준입니다.

• 특징 및 위험성:
  • Dirty Read 현상이 발생하여, 다른 트랜잭션의 변경 사항이 커밋 전에 조회될 수 있습니다.
  • 만약 해당 트랜잭션이 롤백된다면, 이미 읽어들인 데이터와 실제 데이터 사이에 불일치가 생겨 시스템에 심각한 문제가 발생할 수 있습니다.

이러한 이유로 READ UNCOMMITTED는 데이터 정합성 측면에서 매우 취약하여, 실무에서는 거의 사용되지 않는 격리 수준입니다.

결론

각 격리 수준은 데이터 정합성을 유지하는 방식과 성능에 있어 서로 다른 트레이드오프를 가지고 있습니다.

• SERIALIZABLE은 최고의 안전성을 보장하지만, 동시성이 크게 떨어집니다.
• REPEATABLE READ는 트랜잭션 내에서 읽은 데이터의 일관성을 유지해주지만, 새 레코드의 추가로 인한 변화는 완전히 막지 못할 수 있습니다.
• READ COMMITTED는 오직 커밋된 데이터만 읽어 Dirty Read를 방지하면서도, 다소 유연한 데이터 조회를 가능하게 합니다.
• READ UNCOMMITTED는 성능은 좋을 수 있으나, 데이터 정합성에 치명적인 위험을 내포하고 있습니다.

시스템의 요구 사항과 데이터의 중요도에 따라 적절한 격리 수준을 선택하는 것이 MySQL을 효율적으로 운영하는 핵심입니다.

DB에서 사용하는 정렬 알고리즘

데이터베이스 시스템은 일반적인 프로그래밍 언어에서 사용하는 정렬 알고리즘 외에도, 대규모 데이터셋과 디스크 I/O를 고려한 최적화된 정렬 방식을 사용합니다.

알고리즘/기법 설명 사용 시점

Procedure와 Function의 차이

• Procedure는 로직을 기술하여 해당 업무 처리를 직접 하는 용도로 많이 사용되고, Function은 로직을 도와주는 목적으로 사용됩니다.
• Procedure은 select, where문 등에서 사용이 불가능 하지만 Function은 사용 가능합니다.
• Procedure은 클라이언트에서 값을 건네받아 서버에서 작업을 한 뒤 클라이언트에게 전달하지만, Function은 클라이언트에서 값을 건네 받고 서버에서 필요한 값을 가져와서 클라이언트에서 작업을 하고 반환합니다.

Procedure이란?

일련의 쿼리를 하나의 함수로 실행할 수 있는 쿼리의 집합입니다. 예를 들어 네이버에서 상품을 구매하기 위해서 로그인, 상품 선택, 상품 구매 등 여러 프로세스를 한 번의 procedure에 담아 실행 할 수 있습니다.

잠정

1. 하나의 요청으로 여러 SQL문을 실행할 수 있습니다.
2. 서버에서 실행되기 때문에 속도가 빠릅니다.
3. 리턴값이 있을 수도, 없을 수도 있습니다.
4. 여러개의 리턴값을 보낼 수 있습니다.

단점

1. 재사용성이 좋지 않습니다.

코드

CREATE OR REPLACE PROCEDURE 프로시저 이름 ( 
	매개변수명1 [ IN || OUT || INOUT ] 데이터타입
	, 매개변수명2 [ IN || OUT || INOUT ] 데이터타입 ... 
) IS||AS 변수, 상수 등 선언 ( 선언부 ) 
BEGIN 실행 문장 ( 실행부 ) 
EXCEPTION 문장 //필수아님 
END ;

호출 방법

CALL 프로시저명(IN파라미터명, @OUT파라미터명)
EXECUTE 프로시저명(IN파라미터명, @OUT파라미터명)

Function이란?

각 프로세스를 수행하기 위해 필요한 기능들입니다.

Function은 리턴값이 필수입니다.

담점

1. 클라이언트에서 실행되기 때문에 프로시저보다는 느리다

코드

CREATE OR REPLACE FUNCTION 함수 이름
     ( 매개변수명1 매개변수1타입,
       매개변수명2 매개변수2타입 ... )
  RETURN 데이터타입
  IS||AS
         변수, 상수 등 선언 ( 선언부 )
  BEGIN
         실행 문장 ( 실행부 )
         RETURN 반환값    //필수
         EXCEPTION 문장   //필수아님
  END ;

호출 방법

SELECT 함수명(IN 파라미터명) FROM DUAL;

참조

https://velog.io/@devjooj/Mysql-Function과-Procedure-차이

https://fomaios.tistory.com/entry/Oracle-함수Function와-프로시저Procedure-차이