데이터베이스

트랜잭션 격리수준과 락

윤밥밥 2025. 2. 21. 14:39

[트랜잭션]

트랜잭션이란 작업의 완전성을 보장해주는 것.

즉 특정 트랜잭션 안에서 여러 쿼리를 실행했다면 모두 성공하거나 모두 실패함을 보장한다.

 

  • 트랜잭션의 범위는 최대한 좁혀야 좋다.
  • 실제 DB 커넥션은 개수가 적고, 트랜잭션 안에서 메일전송등을 한다면 커넥션이 남아돌지 않을 것이다.
  • 또한 같이 묶여야하는 쿼리가 아니라면 트랜잭션은 분리하는 것이 좋다.

[트랜잭션에서 발생할 수 있는 무결성 문제]

무결성 문제에는 크게 3가지가 있다.

이는 트랜잭션의 각 격리수준에 따라 발생할 수 있는 문제이다.

  • Dirty Read
  • Phantom Read
  • Non-Repeatable Read

1. Dirty Read

Dirty Read란 아직 커밋되지 않은 데이터를 읽어 발생할 수 있는 데이터 무결성 문제.

(여기서 커밋이란 트랜잭션 안의 모든 커밋들이 완전히 DB에 반영된 상태를 의미한다. 커밋이 안되면 언제든 롤백해서 트랜잭션 안의 쿼리를 돌릴 수 있다.)

 

발생할 수 있는 문제

  • 트랜잭션1이 특정 데이터를 읽은 상황에서 다른 트랜잭션이 Rollback하면,트랜잭션1이 읽은 잘못된 데이터를 갖게 된다.

2. Phantom Read

한 트랜잭션 내에서 동일한 조건으로 여러 번 쿼리를 날렸을 때, 처음 실행한 결과와 이후 실행한 결과의 레코드 개수가 달라지는 것.

 

예를 들어

  1. A 트랜잭션에서 user에 대해 목록 조회를 한다.(아직 트랜잭션 종료 x)
  2. B 트랜잭션에서 user를 새로 저장하고 commit한다.
  3. A 트랜잭션에서 다시 조회하면 개수가 늘어난다.

이 때 A트랜잭션에서 갑자기 없던 데이터가 늘아났으므로 Phantom과 같다.

3. Non-Repeatable Read

한 트랜잭션 내에서 동일한 레코드의 값을 여러 번 읽을 때 값이 달라지는 경우.

 

예를 들어

  1. A 트랜잭션에서 user에 대해 목록 조회를 한다.(아직 트랜잭션 종료 x)
  2. B 트랜잭션에서 user를 update하고 commit한다.
  3. A 트랜잭션에서 다시 조회하면 값이 달라진다.

 

[트랜잭션의 격리 수준]

SQL의 트랜잭션 주로 다음과 같은 4가지 격리수준 중 하나를 가질 수 있다.

  • READ UNCOMMITED
  • READ COMMITED
  • REPEATABLE READ
  • SERIALIZABLE

1. READ UNCOMMITED

READ UNCOMMITED은 특정 트랜잭션이 커밋하지 않은 데이터를 버퍼 풀(성능을 위해 DB 데이터를 캐싱하는 공간) 등을 통해 다른 트랜잭션이 사용할 수 있는 것이다.

그래서 언커밋된 것을 읽는다.

 

특징

  • 커밋되지 않았으므로 실제 롤백될 수도 있다.
  • 스냅샷을 사용하지 않는다.(스냅샷이란 특정 시점의 데이터베이스 상태를 저장해두는 것이다. DB를 완전히 복제하는 게 아니라 Undo로그를 활용해 현재 DB에서 특정 시점으로 돌아갈 수 있게 한다.)

장단점

  • Dirty Read, Phantom Read, Non-Repeatable Read가 발생할 수 있다.
  • 동시성 제어를 거의 하지 않아 성능이 좋다.(기본적인 update, delete, insert에 대한 배타적 잠금만 한다.)

2. READ COMMITED

READ COMMITED는 조회 시점에서 다른 트랜잭션의 커밋된 데이터를 읽는다.

 

특징

  • select 쿼리마다 새로운 스냅샷을 생성한다.
  • 다른 트랜잭션의 의해 변경 중이면, Undo 로그를 통해 가장 최근 커밋된 데이터를 반환.
    • 이 때 가장 최근 커밋된 데이터는 트랜잭션id를 통해 판별

장단점

  • Dirty Read를 방지할 수 있다.(데이터 무결성이 더 높아진다.)
  • Phantom Read, Non-Repeatable Read가 발생할 수 있다.

3. REPEATABLE READ

트랜잭션 내에서 로우에 대해 여러번 SELECT 쿼리를 해도 항상 동일한 결과를 보장.

트랜잭션 시작 시 스냅샷과 추가되는 Undo로그를 통해 이 시점 기준으로 데이터를 읽는다.

다만, SELECT조건에 맞는 로우를 전부 가져오는 경우 로우가 추가되어 응답될 수 있음(PhantomREAD)

장단점

  • Dirty Read와 Non-Repeatable Read를 방지하지만, Phantom Read는 허용.(MySQL에서는 PhantomRead도 방지)
    • 스냅샷 기반으로 동작하여 방지하지만 예외 상황으로 발생할 수 있다.
  • 테이블 락이 아닌, MVCC(Multi Version Concurrency Control)를 통한 스냅샷 읽기 통해 충돌을 방지하므로 성능이 좋다.

4. Serializable

  • 행 락을 간다.
    • 읽기일 때는 읽기락
    • 쓰기일때는 쓰기락을 건다.

 

MySQL의 격리수준

MySQL은 기본적으로 REAPEATABLE READ 격리수준을 사용한다.(웬만한 다른 DB에서는 READ COMMITED를 사용한다.)

[DB 락이란?]

DB Lock이란 트랜잭션 처리의 순차성을 보장하기 위한 방법

1. 락 범위에 따른 분류

락 범위에 따르면 테이블 락, 행 락이 있다.

  • 테이블 락
    • 테이블 전체에 락을 거는 방식
    • 간단하나 동시성이 낮음
  • 행 락
    • 테이블의 특정 행에만 락을 거는 방식
    • 동시성이 높으나 관리가 복잡

2. 락 모드에 따른 분류

락 모드에 따라 분류하면 공유락, 배타락이 있다.

  • 공유락( Shared Lock)
    • 데이터를 읽을 때 사용하는 락.
    • 여러 트랜잭션이 동시에 읽을 수는 있나, 쓰기는 허용 x
  • 배타락(Exclusive Lock)
    • 데이터를 수정할 때 쓰는 락
    • 락을 건 트랜잭션 외에는 읽기, 쓰기 모두 불가능

3. 목적에 따른 분류

목적에 따라 분류하면 읽기 락, 쓰기 락이 있다.

  • 읽기 락(read lock)
    • 데이터를 읽기 위해 설정
    • 다른 트랜잭션이 읽기는 허용하나, 쓰기는 불가능
  • 쓰기 락(write lock)
    • 데이터를 수정하기 위한 설정
    • 다른 모든 트랜잭션을 막음

4. 레벨에 다른 분류

레벨에 따라 분류하면 낙관적 락, 비관적 락

  • 낙관적 락
    • 충돌이 적을 거라 예상하고, 충돌 시 롤백
    • 버전번호를 통해 충돌 감지
  • 비관적 락
    • 데이터 충돌이 많을거라 예상해 작업 시작 시 락을 검.

[MySQL 엔진의 잠금]

MySQL에서 사용되는 락은 크게 스토리지 엔진(InnoDB등)의 락과 MySQL엔진의 락으로 구분할 수 있다.

  • MySQL 엔진의 락은 모든 스토리지 엔진에 영향을 미친다.
  • 스토리지 엔진 락은 다른 모든 스토리지 엔진에게 영향을 주지 않는다.

MySQL엔진에서는 다음과 같은 락이 있다.

  • 글로벌 락
  • 네임드 락
  • 테이블 락
  • 메타데이터 락

1.글로벌 락

MySQL에서 제공하는 락 범위 중에 가장 크다.

MySQL 엔진 전체에 락을 거는 것이며, 읽기를 제외한 대부분의 DDL, DML문장은 거부된다. 즉 DB, 테이블 관계 없이 전체 락이다.

  • FLUSH TABLES WITH READ LOCK 이라는 명령어로 사용할 수 있다.
  • mysqldump 라는 MySQL에서 데이터베이스를 백업하거나 복원하기 위해 사용되는 유틸리티 등을 사용할 때 글로벌 락을 걸 수 있다.(내부적으로 글로벌 락을 걸기도 한다. 따라서 백업을 수행한다면 내부적으로 글로벌 락을 ㅎ사용하는 지 확인해보는 것이 좋다.)
  • 글로벌 락은 MySQL 성능에 큰 영향을 미칠 수 있다.

2.백업 락

InnoDB가 적용되기 전, MyISAM에서는 트랜잭션이 없었기에 글로벌 락을 사용했다.

하지만 InnoDB에는 트랜잭션이 존재하기에 데이터의 일관성을 위해 글로벌 락을 걸 필요가 없다.

그래서 좀 더 가벼운 백업 락이 도입됐다.

  • DB, 테이블 변경 거부
  • 사용자 관리, 비밀번호 변경 거부
  • REPAIR TABLE, OPTIMIZE TABLE 거부
  • 테이블 데이터 변경 허용

백업 시, DDL이 발생하면 백업에 실패하므로 6시간 이상 걸리는 백업 환경에서는 큰 영향을 끼칠 수 있으므로 이럴 때 백업락을 사용한다.

3.테이블 락

명시적으로는

LOCK TABLES 테이블명 READ or WRITE

위 명령어로 테이블 락을 획득할 수 있다.

묵시적으로는 테이블에 데이터 변경 쿼리시 발생한다.

  • InnoDB에서는 레코드 기반 락 기능이 있기 때문에 단순 DML에서는 테이블락이 발생하지 않는다.
  • 스키마 변경 시에 테이블 락이 발생할 수 있다.

4.네임드 락

테이블이나 레코드가 아니라 문자열 이름의 락을 거는 것.

  1. SELECT GET_LOCK("abc", 2); : abc 라는 락을 만든다.
  2. SELECT IS_FREE_LOCK("abc"); : 다른 레플리카 서버가 abc 에 대해 잠금이 되어있는 지 확인, 락이 걸려 있으면 사용 불가
  3. SELECT RELEASE_LOCK("abc") : 락해제

주로 여러 애플리케이션 서버가 DB 서버 1대에 동시 접속해서 정보를 DB에 반영해야 한다면 사용하기 좋다.

[InnoDB 스토리지 엔진 락]

InnoDB에서는 기본적으로 레코드 기반의 락을 탑재하고 있기 때문에 동시성이 좋다.

  • 또한 InnoDB에는 레코드와 레코드 사이를 잠그는 GAP 락이라는 것도 있다

락 종류

  • 레코드 락
  • 갭 락
  • 네스트 키 락
  • 자동 증가 락

1.레코드 락

레코드에 락을 거는 것이다.

  • InnoDB는 레코드 자체에 락을 걸지 않고, 레코드의 인덱스에 락을 건다.
    • 가장 먼저 인덱스로 데이터를 찾기에,
  • 기본적으로 키에 대해 클러스터 인덱스가 있으므로 이를 이용해 락을 건다.
    • 참고로 세컨더리 인덱스에 대해서는 넥스트 키 락이나, 갭 락을 건다.

주의할 점

update 테이블명 set ~~ where id = 1 and name = "찬호" 라는 쿼리를 날릴 때 인덱스의 따라 락이 걸리는 레코드 범위가 다르다.

  • id에 대한 인덱스만 있는 경우 : id가 1인 모든 레코드에 락이 걸린다.
  • id, name에 대한 인덱스가 있는 경우 : id, name이 일치한 레코드에 대해 락이 걸린다.
  • 인덱스가 아예 없을 경우 : 모든 레코드에 락이 걸린다.

2.갭 락

레코드 자체가 아니라, 레코드와 인접 레코드 사이에만 락을 거는 것이다.

  • 이 사이에 새로운 레코드가 생성되는 것을 제어하는 것.

3.넥스트 키 락

레코드 락과 갭 락을 합쳐놓은 형태

4.자동증가 락(Auto Increment Lock)

MySQL 5.0 이하에서는

MySQL에서는 AUTO_INCREMENT라는 자동 증가 컬럼 속성을 제공한다. 이 때 중복되지 않고 순서대로 증가하는 값을 위해 자동 증가 락이라는 테이블 수준의 잠금 기능이 존재한다.

  • Insert, Replace 등의 새로운 데이터를 저장될 때만 락이 걸린다.(update 등은 걸리지 않는다.)
  • AUTO_INCREMENT 값을 가져오는 순간에만 락이 걸렸다가 바로 해제된다,

MySQL 5.1부터

자동증가 락이 아닌 뮤텍스를 사용한다.

아주 짧은 순간에만 잠금이 걸린다.

'데이터베이스'의 다른글

  • 현재글트랜잭션 격리수준과 락

티스토리툴바