MySQL - 락과 격리 수준

수수한개발자 2023. 7. 28.

728x90

트랜잭션은 하나의 논리적인 작업에 대해서 몇 개의 쿼리가 실행되든 관계없이 논리적인 작업 자체가 100% 적용되거나(COMMIT을 실행했을 때) 아무것도 적용되지 않아야 (ROLLBACK 또는 트랜잭션을 ROLLBACK 시키는 오류가 발생했을 때) 함을 보장해 주는 것이다.

MySQL에서의 잠금은 테이블 데이터 동기화를 위한 데이블 락 이외에도 테이블의 구조를 잠그는 메타데이터 락, 그리고 사용자의 필요에 맞게 사용할 수 있는 네임드 락이라는 잠금 기능을 제공한다.

글로벌락

글로벌락은

FLUSH TABLES WITH READ LOCK

명령으로 획득할 수 있으며 제공하는 잠금 가운데 가장 범위가 크다.

일단 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 DDL문장이나 DML문장을 실행하는 경우 글로벌락을이 해제될 때까지 해당 문장이 대기 상태로 남는다.

테이블락

테이블락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획들할 수 있다.

LOCK TABLES table_name [ READ | WRITE]

UNLOCK TABLES

명시적으로는 위와 같은 명령으로 락을 획득하고 반납할 수 있다. 명시적인 테이블락도 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없다.

네임드락

네임드락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대한 잠금을 설정할 수 있다.

이 잠금의 특직은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것이다.

보통 네임드락은 분산락이라고도 하며 데이터베이스 서버 1개와 웹 서버가 5개일 때 분산된 서버에서의 데이터 정합성을 맞추기 위해서 사용한다.

네임드락의 경우 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다.

배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 한다.

이러한 경우 동일 데이터를 변경하거나 참조하는 프로그램 끼지 분류해서 네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있다.

스프링에서의 네임드락

public interface LockRepository extends JpaRepository<Stock, Long> {

    @Query(value = "select get_lock(:key, 3000)", nativeQuery = true)
    void getLock(String key);

    @Query(value = "select release_lock(:key)", nativeQuery = true)
    void releaseLock(String key);
}

@Service
public class NamedLockStockFacade {

    private final LockRepository lockRepository;
    private final StockService service;

    public NamedLockStockFacade(LockRepository lockRepository, StockService service) {
        this.lockRepository = lockRepository;
        this.service = service;
    }

    @Transactional
    public void decrease(Long id, Long quantity) throws InterruptedException {
        try {
            lockRepository.getLock(id.toString());
            service.decrease(id, quantity);
        } finally {
            lockRepository.releaseLock(id.toString());
        }
    }
}

위와 같이 재고감소로직에서 락을 획득한 뒤 해제해 주면 분산환경에서의 데이터 정합성을 맞출 수 있다.

또한 MySQL 8.0부터는 중첩해서 사용할 수 있고 해제할 때 RELESE_ALL_ROCK()으로 한꺼번에 해제할 수 있다.

메타데이터 락

메타데이터락은 데이터베이스의 객체(테이블이나 뷰)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.

InnoDB 스토리지 엔진 잠금

최근 버전에서는 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법도 도입됐다.

information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAIT라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있다.

INNODB의 중요도가 높아지면서 Performance Schema를 이용해 InnoDB 스토리;지 엔진의 내부잠금에 대한 모니터링 방법도 추가 됐다.

레코드락

레코드 자체만을 잠그는 것을 레코드락이라고 한다.

INNODB에서는 인덱스의 레코드를 잠근다.

INNODB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키락 또는 갭 락을 사용하지만 프라이머리키 또는 유니크 인덱스에 의한 변경 작업에서는 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 락은 건다.

갭락

갭락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.

갭락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)을 제어하는 것이다. 갭락은 갭락자체보단 넥스트 키락의 일부로 자주 사용된다.

넥스트 키락

레코드락과 갭락을 합쳐놓은 형태의 잠금을 넥스트 키락이라고 한다.

REPEATABLE READ 격리 수준을 사용하고 innodb_unsafe_for_binlog 시스템 변수가 비활성화되면(0으로 설정되면)

변경을 위해 검색하는 레코드에는 넥스트 키 락방식으로 잠금이 걸린다. INNODB의 갭락이나 넥스트 키락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다. 여기서 데드락이 자주 발생한다는데 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키락이나 갭락을 줄이는것이 좋다고 한다.

자동증가락

MySQL에서는 자동 증가하는 숫자 값을 추출(채번) 하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공한다.

자동증가 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우 저장되는 각 레코드는 중복되지 않고 저장된

순서대로 증가하는 일련 된 값을 가져야 한다. INNODB 스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

자동 증가락은 INSERT, REPLACE 쿼리 같은 새로운 레코드를 저장하는 쿼리에서만 발생하며 UPDATE, DELETE 등의 쿼리에서는 걸리지 않는다.

MySQL의 격리 수준

트랜잭션의 격리 수준(isolation level)이란 여러 트랜잭션이 동시에 처리도리 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.

격리 수준은 크게 "READ UNCOMMITTED", "READ COMMITTED", "REPEATABLE READ", "SERIALIZABLE"의 4가지로 나뉜다.

순서대로 트랜잭션같의 데이터 격리(고립) 정도가 높아지며 동시 처리 성능도 떨어진다고 볼 수 있다.

격리 수준이 높아질수록 성능이 많이 떨어진다고 생각하지만 SERIALIZABLE 아니면 큰 차이가 없다고 한다.

	DIRTY READ	NON-REPEATBLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(InnoDB는 없음)
SERIALIZABLE	없음	없음	없음

일반적인 어플리케이션에서는 READ COMMITTED, REPEATABLE READ를 사용한다.

READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 각 트랜잭션에서의 변경 내용이 커밋이나 롤백 여부에 상관없이 다른 트랜잭션에서 보인다.

트랜잭션1		트랜잭션2
1. BEAGIN ->
2. INSERT -> (TEST)	TEST	TEST <- 3. SELCET WHERE NAME ='TEST'
4. COMMIT

위에 상황에서 3번에서는 조회가 된다. 문제는 트랜잭션 1이 4번에서 롤백이 나버려도 트랜잭션 2는 TEST를 조회한뒤라 정상적인 데이터라고 처리하고 있다.

이러한 현상을 더티리드(DIRTY READ)라 한다.

READ COMMITTED

READ COMMITTED는 오라클 DBMS에서 기본적으로 사용하는 격리 수준이며 온라인서비스에서 가장 많이 선택되는 격리 수준이라고 한다.

이 레벨에서는 더티리드는 발생하지 않는다.

트랜잭션1		트랜잭션2
	TEST
1. BEAGIN ->
2. UPDATE -> TEST2	TEST2	<- 3. SELECT TEST
4. COMMIT

이 레벨에서는 TEST를 TEST2로 바꿨는데 TEST2는 테이블에 즉시 기록되고 트랜잭션 2는 언두 영억에 백업된 레코드에서 에서 조회해 온다. 최종적으로 트랜잭션 1이 변경된 내용을 커밋하면 그때부터는 다른 트랜잭션에서도 백업된 언두로그가 아니라 실제 테이블에서 값을 참조하게 된다.

그리고 이 레벨의 문제점은 NON-REPEATBLE READ가 발생한다는 것이다.

예를 들어 트랜잭션 2가 TEST2를 조회해서 없다가 트랜잭션이1이 업데이트를 하고 다시 트랜잭션2가 조회하면 결과를 반환받는다. 이것에 대한 문제는 같은 트랜잭션 내에서 똑같은 조회에 대해서 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋난다.

REPEATABLE READ

REPEATABLE READ는 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다.

이 레벨에서는 위에서 말한 NON-REPEATABLE READ가 발생하지 않는다.

이 엔진은 트랜잭션이 ROLLBACK 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해 두고 실제 레코드 값을 변경한다.

모든 InnoDB의 트랜잭션에는 고유한 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제된다.

이 레벨에서는 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다.

여기서 문제는 PHANTOM READ, PHANTOM ROW 문제가 발생할 수 있는 상황이 있다.

예를 들면 트랜잭션 1에서 인서트 하고 나서 트랜잭션 2에서 SELCET... FOR UPDATE 쿼리로 조회한다면 인서트한 값이 조회될 수 있다. 왜냐하면 SELCET ... FOR UPDATE는 레코드에 쓰기 잠금을 걸지만 언두 레코드에는 잠금을 걸 수 없기 때문이다. SELCET ... FOR UPDATE 나 SELECT... LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져온다.

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다.

그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다.

기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행된다.

하지만 이 격리 레벨에서는 읽기 작업도 공유 잠금을 획득해야 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다. 그래서 위에서 말한 PHANTOM READ 문제가 발생하지 않는다.

하지만 InnoDB 스토리지 엔진에서는 갭락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않기 때문에 굳이 이 격리 수준을 사용할 필요성은 없어 보인다.

개발을 하며 어떻게 데이터 정합성을 맞춰야 할까?라는 고민으로 시작해 이전에 사둔 Real MySQL 8.0 책을 읽으며 오랜만에 블로그를 작성했다.

요즘에 뭐를 해야할지 모르겠는데,, 앞으로 뭐를 해야할지모르겠으면 블로그를 작성해보도록해야겠다.