• 트랜잭션은 작업의 완전성을 보장해준다.
  • 잠금(Lock)은 트랜잭션과 비슷한 개념 같지만 동시성을 제어하기 위한 기능이다.
    • 반면, 트랜잭션은 데이터의 정합성을 보장한다.

트랜잭션

MySQL에서의 트랜잭션

  • 트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미 있는 개념은 아니다.
  • 아래의 예를 본다.
    • SLECT * FROM tab_innodb
      • 트랜잭션을 지원하지 않는다면 1, 2가 부분 저장되고 3만 저장되지 않지만, 트랜잭션이 지원되기 때문에 1, 2, 3 모두 롤백이 되었다.
  • 트랜잭션이 지원되는 않는 코드
    • 부분 업데이트가 발생해서 쓰레기 데이터가 남을 수 있기 때문에, 복잡한 분기처리를 해줘야된다.
  • 트랜잭션이 지원되는 코드
    • 깔끔하게 작성이 가능하다.

주의사항

  • 트랜잭션은 DBMS 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.
    • 데이터베이스 커넥션 수는 제한적이어서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션의 개수는 줄어들 것이다.
  • 네트워크작업이 있거나 처리가 오래걸리는 코드가 트랜잭션에 있으면, DBMS 서버가 높은 부하 상태로 빠지거나 위험한 상태에 빠지는 경우가 빈번히 발생한다.
  • 트랜잭션은 최대한 작은게 적용하고, 관련 있는 쿼리 끼리만 묶어주는 것이 좋다.
  • 예시

MySQL 엔진의 잠금

  • MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.
  • MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면된다.
  • MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.

글로벌 락

  • FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
  • 한 세션에서 글로벌 락을 획득하면 달느 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.
  • FLUSH TABLES WITH READ LOCK 명령이 실행되기 전에 테이블이나 레코드에 쓰기 잠금을 거는 SQL이 실행됐다면 이 명령은 해당 테이블의 읽기 잠금을 걸기 위해 먼저 실행된 SQL과 그 트랜잭션이 완료될 때까지 기다려야 한다.
  • 여러 테이블에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 한다.
  • InnoDB는 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요가 없다.
  • MySQL 8.0부터는 InnoDB가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성이 생겼다.
    • Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.
    • 백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 된다.
      • 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
      • REPAIR TABLE과 OPTIMIZE TABLE 명령
      • 사용자 관리 및 비밀번호 변경
    • 하지만 백업 락은 테이블의 데이터 변경은 허용된다.

테이블 락

  • 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.
  • 명시적 방법: LOCK TABLES table_name [ READ | WRITE ]
    • 명시적으로 획득한 잠금은 UNLOCK TABLES 명령으로 잠금을 해제할 수 있다.
    • 명시적인 테이블락은 특별한 상황이 아니라면 애플리케이션에서 사용할 필요가 거의 없다.
  • MyISAM이나 MEMORY 테이블은 데이터를 변경하는 쿼리를 실행하면 묵시적인 테이블락이 발생한다.
  • InnoDB 테이블은 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지 않는다.
    • 대부분의 데이터 변경(DML) 쿼리에서는 테이블 락이 발생하지 않고, 스키마를 변경하는 쿼리(DDL)의 경우에는 발생한다.

네임드 락

  • GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.
    • 잠금의 대상이 레코드나 AUTO_INCREMENT 같은 데이터베이스 객체가 아닌 것이 특징이다.
  • 복잡한 요건으로 레코드를 변경하는 트랜잭션에서 유용하다.
    • 예시: 한꺼번에 많은 레코드를 변경하는 배치 프로그램에서 데드락을 방지하기 위해서
  • MySQL 8.0 버전부터는 다음과 같이 네임드 락을 중첩해서 사용할 수 있게 됐으며, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가됐다.

메타데이터 락

  • 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우 획득하는 잠금이다.
  • 명시적으로는 획득할 수 없다.
  • RENAME TRABLE tab_a TO tab_b 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.
    • RENAME TABLE 명령의 경우 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정한다.

InnoDB 스토리지 엔진 잠금

  • InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에 레코드 기반의 잠금 방식을 탑재하고 있다.
  • 최근 버전에서는 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다.
    • MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있으며, 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수 있다.

InnoDB 스토리지 엔진의 잠금

  • InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업되는 경우는 없다.
  • 레코드 락
    • 레코드 자체만을 잠근다.
    • 다른 사용 DBMS의 레코드 락과 동일한 역할을 한다.
    • 한 가지 중요한 차이점은 InnoDB 스토리지 안젠은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다.
      • 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
    • 보조 인덱스를 이용한 변경 작업은 이어서 설명할 넥스트 키 락 또는 갭 락을 사용하지만, 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.
  • 갭 락
    • 갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.
    • 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어한다.
  • 넥스트 키 락
    • 레코드 락과 갭 락을 합친 잠금
    • 갭 락은 그 자체를 사용하기보다는 넥스트 키 락의 일부로 자주 사용한다.
    • 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 떄 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.
  • 자동 증가 락
    • AURO_INCREMENT 칼럼이 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다.
    • 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.
    • AUTO_INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE나 DELETE 등의 쿼리에는 걸리지 않는다.
    • AUTO_INCREMENT 락은 명시적으로 획득하고 해제하는 방법은 없다.
    • AUTO_INCREMENT 락은 아주 짧은 시간 동안 걸렸다가 해제되는 잠금이라서 대부분의 경우 문제가 되지 않는다.
    • MySQL 5.1 이상부터는 자동 증가 락을 사용하지 않고 innodb_autoinc_lock_mod라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.
      • innodb_autoinc_lock_mod=0: 모든 INSERT 문장에 자동 증가 락을 사용한다.
      • innodb_autoinc_lock_mod=1: 자동 증가 락을 사용하지 않고 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다. 하지만 INSERT ... SELECT 같이 MySQL 서버가 INSERT 되는 건수를 예측할 수 없을 때는 자동 증가 락을 사용한다. 이때 INSERT 문장이 완료되기 전까지 자동 증가 락은 해제되지 않기 때문에 다른 커넥션에서는 INSERT를 실행하지 못하고 대기하게 된다.
      • innodb_autoinc_lock_mod=2: 무조건 경량화된 래치(뮤텍스)를 사용한다. 하지만 이 설정에서는 하나의 INSERT 문장으로 INSERT 되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하지 못한다. 또한, STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수도 있기 떄문에 주의해야 한다.
      • MySQL 5.7 까지는 innodb_autoinc_lock_mod의 기본값이 1이었지만, MySQL 8.0 버전부터는 바이너리 로그 포맷이 STATEMENT가 아니라 ROW 포맷이 기본값이 되어서 innodb_autoinc_lock_mod의 기본값이 2로 바뀌었다.
    • 자동 증가 값이 한 번 증가하면 절대 줄어들지 않는 이유가 AUTO_INCREMENT 잠금을 최소화하기 위해서다. 설령 INSERT 쿼리가 실패했더라도 한 번 증가된 AUTO_INCREMENT 값은 다시 줄어들지 않고 그대로 남는다.

인덱스와 잠금

  • 레코드 락은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.
    • 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.
    • 아래의 예에서 인덱스에는 first_name 컬럼 밖에 없으므로 253개의 레코드에 모두 락이 잡힌다.
  • 또한 테이블을 풀 스캔하면서 UPDATE 작업을 하면, 테이블의 모든 레코드를 잠그게 되니 주의해야된다.

레코드 수준의 잠금 확인 및 해제

  • MySQL 5.1부터는 information_schema라는 DB에 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 통해 확인이 가능하다.
  • MySQL 8.0부터는 information_schema의 정보드는 조금씩 deprecated되고 있으며, 그 대신 performance_schema의 data_locks와 data_lock_waits 테이블로 대체되고 있다.

  • 위 시나리오로 예시를 작성해본다.
  • 실행 중인 스레드 목록 확인은 SHOW PROCESSLIST 로 확인할 수 있다.
    • Id가 스레드 id이다.
  • performance_schema의 data_locks 테이블과 data_lock_waits 테이블을 조인해서 잠금 대기 순서를 확인할 수 있다.
    • 현재 18번 스레드는 17번 스레드를 기다리고 있고, 19번 스레드는 17번 스레드와 18번 스레드를 기다리고 있다는 것을 알 수 있다.
  • 17번 스레드가 어떤 잠금을 가지고 있는지 더 상세히 확인하고 싶다면 performance_schema의 data_locks 테이블이 가진 컬럼을 모두 살펴보면 된다.
    • 아래 사진을 보면 employees 테이블에 대해서 IX 잠금(Intentional Exclusive)을 가지고 있으며, 특정 레코드에 대해서 쓰기 잠금을 가지고 있다는 것을 확인할 수 있다.
    • 이떄 REC_NOT_GAP 표시가 있으므로 레코드 잠금은 갭이 포함되지 않은 순수 레코드에 대해서만 잠금을 가지고 있음을 알 수 있다.

  • 만약 이 상황에서 17번 스레드가 잠금을 가진 상태에서 상당히 오랜 시간 멈춰 있다면 다음과 같이 17번 스레드를 강제 종료하면 나머지 UPDATE 명령들이 진행되면서 잠금 경합이 끝날 것이다.
    • KILL 17;

MySQL의 격리 수준

  • 격리 수준은 크게 “READ UNCOMMITTED”, “READ COMMITTED”, “REPEATABLE READ”, “SERIALIZABLE"의 4가지로 나뉜다.
  • 일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITTED와 REPEATABLE READ 중 하나를 사용한다.
    • 오라클 같은 DBMS에서는 주로 READ COMMITTED 수준을 많이 사용하며, MySQL에서는 REPEATABLE READ를 주로 사용한다.

READ UNCOMMITTTED

  • 각 트랜잭션에서 변경 내용이 COMMIT이나 ROLLACK 여부에 상관없이 다른 트랜잭션에서 보인다.
  • 더티 리드(Dirty read)가 발생한다.
    • 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상
  • 더티 리드를 유발하는 READ UNCOMMITED는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다.
    • MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장한다.

READ COMMITTED

  • 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.
    • 사용자 A가 “Lara"에서 “Toto"로 변경했을 때, 이때 새로운 값인 “Toto"는 employees 테이블에 즉시 기록되고 이전 값인 “Lara"는 언두 영역으로 백업된다.
    • 사용자 A가 커밋을 수행하기 전에는 사용자 B는 언두 영역에 백업된 레코드를 조회한다.
  • READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ라는 부정합의 문제가 있다.
    • 한 트랜잭션에서 같은 데이터를 여러 번 읽었을 때 값이 바뀔 수 있다.
  • NON-REPEATABLE READ는 일반적인 웹 프로그램에서는 크게 문제가 되지 않을 수 있지만 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수 있다.

REPEATABLE READ

  • InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경한다.
    • 이러한 방식을 MVCC(Multi Version Concurrency Control) 이라고 부른다.
  • REPEATABLE READ는 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.
    • 모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)을 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다.
    • 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.
    • REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다.
  • READ COMMITTED도 MVCC를 이용해 COMMIT되기 전의 데이터를 보여준다.
    • REPEATABLE READ와 READ COMMITTED의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가냐 하느냐에 있다.
  • 언두 영역에는 백업이 하나 이상 얼마든지 존재할 수 있다.
    • 한 사용자가 BEGIN으로 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수도 있다. 이렇게 언두에 백업된 레코드가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다.
  • REPEATABLE READ 격리 수준에서도 PHANTOM READ 부정합이 발생할 수 있다.
    • PHANTOM READ: 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상.
    • 일반적인 SELECT는 괜찮지만 SELECT ... FOR UPDATE 쿼리는 PHANTOM READ가 발생한다.
    • SELECT ... FOR UPDATE 나 SELECT ... LOCK IN SHARE MODE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없기 때문이다.

SERIALIZABLE

  • 읽기 작업도 공유 잠금을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.
  • 엄격한 격리 수준이지만 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다.