Published on

5장. 트랜잭션과 잠금

5장. 트랜잭션과 잠금

  • 잠금(Lock): 동시성 제어
    • 동시에 같은 레코드를 변경하게 되면 해당 레코드의 값을 예측할 수 없게 됩니다.
  • 트랜잭션: 데이터 정합성 보장
    • 최소 작업 단위로써, 하나의 트랜잭션은 다같이 COMMIT 되거나 ROLLBACK 되어야 합니다.

트랜잭션

MySQL에서의 트랜잭션

트랜잭션은 쿼리의 개수와 상관없이 전체가 COMMIT 되거나 ROLLBACK 됨을 보장해 주는 것입니다.

MyISAM엔진 같은 경우 전체가 롤백되지 않고 실패한 쿼리만 ROLLBACK되는 반면 InnoDB엔진은 트랜잭션 단위로 묶여 모두 롤백 됩니다.

주의사항

트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋습니다.

보통 애플리케이션을 만들 때 Service 객체에 통으로 트랜잭션을 걸고 사용하는 경우가 많습니다. (저 또한 그럽니다.)

실행 속도가 짧다면 괜찮지만 파일을 업로드 등의 작업이 들어가게 된다면 생각이 조금 필요합니다.

파일을 업로드 하는 동안 트랜잭션은 유지 될 것이고, 그럼 커넥션을 계속 물고 있게 됩니다. 또한, 격리 수준에 따라 다르지만 기본 값이라면 변경 전 데이터를 반환하기 위해 언두 로그에 계속 데이터가 쌓이게 됩니다.

이런 경우 네트워크를 여러 번 타더라도 트랜잭션을 분리하여 작성하는 것이 더 좋습니다.

추가로 단순 조회의 경우 별도로 트랜잭션을 사용하지 않아도 괜찮습니다.

MySQL 엔진의 잠금

잠금은 크게 두 가지로 나눌 수 있습니다

  • 스토리지 엔진 레벨
  • MySQL 엔진 레벨

MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 됩니다.

MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만 스토리지 엔진 레벨의 잠근은 스토리지 엔진 간 상호 영향을 미치지는 않습니다.

글로벌 락

글로벌 락은 이름에서 부터 알 수 있듯 MySQL에서 제공하는 Lock중 가장 범위가 큽니다.

글로벌 락은 FLUSH TABLES WITH READ LOCK 명려으로 획득할 수 있습니다.

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

글로벌 락을 거는 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 겁니다.

글로벌 락을 걸기 위해 먼저 실행된 SQL과 그 트랜잭션이 완료될 떄까지 대기하게 되는데 해당 트랜잭션이 오래 걸린다면 MySQL 서버의 모든 테이블에 대한 쿼리(SELECT 제외)가 아주 오랜 시간 실행되지 못하고 대기 할 수 있으니 주의 해야 합니다.

MySQL 8.0 부터는 InnoDB가 기본 엔진으로 채택 되면서 조금 더 가벼운 글로벌 락의 필요성이 생겼습니다.

그래서 Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입 되었습니다.

특정 세션에서 백업 락을 획득하면 모든 세션에서 아래의 정보를 변경할 수 없게 됩니다.

  • DB 및 테이블 등 모든 객체 생성 및 변경, 삭제
  • REPAIR TABLE과 OPTIMIZE TABLE 명령
  • 사용자 관리 및 비밀번호 변경

하지만 백업 락은 일반적인 테이블의 데이터 변경은 허용됩니다.

테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금입니다.

명시적으로 LOCK TABLES table_name [ READ | WRITE ] 명령을 통해 획득 할 수 있습니다.

명시적으로 획득하면 UNLOCK TABLES 명령으로 잠금을 반납할 수 있습니다.

또한, 테이블에 데이터를 변경하는 쿼리를 실행하면 묵시적 테이블 락을 획득하고 변경이 끝나면 자동으로 해제합니다.

InnoDB 엔진은 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지 않고, DDL의 경우에만 영향을 미칩니다.

네임드 락

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

네임드 락의 특징은 대상이 데이터베이스 객체가 아니라 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금이라는 것입니다.

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

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

메타데이터 락

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

명시적으로 획득하거나 해제할 수는 없고, RENAME TABLE tab_a TO tab_b같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금입니다.

RENAME
TABLE rank To rank_backup, rank_new TO rank;

위 처럼 하나의 명령문에 두 개의 RENAME 작업을 한꺼번에 실행하면 실제 애플리케이션에서는 "Table not found 'rank"'같은 상황을 발생시키지 않고 적용이 가능합니다.

하지만 위 문장을 2개로 분리하여 작성하면 찰나의 순간이지만 rank 테이블이 존재하지 않아 "Table not found 'rank"'오류가 발생할 수도 있습니다.

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑개하고 있습니다.

InnoDB는 레코드 기반의 잠금 방식 때문에 MyISAM보다는 훨씬 뛰어난 동시성 처리를 제공하지만 이원화된 잠금 처리 탓에 InnoDB에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다롭습니다.

최근 버전에서는 InnoDB의 트랜잭션과 잠금, 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입되었습니다.

information_schema DB에 존재하는 INNODB_TRX, INNODB_LOCK, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있으며, 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수도 있습니다.

Performance Schema를 이용해 InnoDB의 내부 잠금(세마포어)에 대한 모니터링 방법도 추가 되었습니다.

InnoDB 스토리지 엔진의 잠금

InnoDB는 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업 되는 경우(락 에스컬레이터)는 없습니다.

레코드 락

레코드 자체만을 잠그는 것을 레코드 락이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 합니다.

한 가지 중요한 차이는 InnoDB는 레코드 자체가 아니라 인덱스의 레코드를 잠금다는 점입니다.

인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설장하빈다.

레코드를 잠그느냐, 인덱스를 잠그느냐는 상당히 크고 중요한 차이를 만들어 냅니다.

갭 락

다른 DBMS와의 또 다른 차이는 갭 락입니다.

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

즉 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것입니다.

갭 락은 그 자체 보다는 넥스트 키 락의 일부로 자주 사용됩니다.

넥스트 키 락

넥스트 키 락은 레코드 락 + 갭 락 입니다.

STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 합니다.

또한 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화(0) 되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸립니다.

InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어 내도록보장하는 것이 주목적입니다.

그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생하기 때문에 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋습니다.

자동 증가 락

MySQL에서 보통 PK로 AUTO_INCREMENT를 자주 사용합니다.

AUTO_INCREMENT컬럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우 저장되는 각 레코드는 중복되지 않고 저장된 순대로 증가하는 일련번호 값을 가져야 합니다.

이를 위해 InnoDB는 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용합니다.

InnoDB의 다른 잠금과는 달리 AUTO_INCREMENT 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 값을 가져오는 순간만 락이 걸렸다가 즉시 해제 됩니다.

이후 쿼리가 롤백 되어도 한 번 증가된 AUTO_INCREMENT 값을 롤백되지 않아 증가된 상태로 유지됩니다.

AUTO_INCREMENT 락을 명시적으로 획득하고 해제하는 방법은 없습니다.

innodb_autoinc_lock_mode 시스템 변수를 이용해 자동 증가 락의 장동 방식을 변경할 수 있습니다.

  • 0: MySQL 5.0고 ㅏ동일한 잠금 방식으로 모든 INSERT 문장은 자동 증가 락을 사용
  • 1: INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때는 래치(뮤텍스)를 이용해 처리.
  • 2: 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용

MySQL 8.0 부터는 기본적으로 2를 사용합니다.

인덱스와 잠금

레코드 락은 위에서 언급했듯이 레코드가 아니린 인덱스를 잠그는 방식으로 처리됩니다.

즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 합니다.

UPDATE employees
SET hire_date=NOW()
WHERE first_name = 'Georgi'
  AND last_name = 'Klassen'

first_name만 인덱스가 걸려 있다고 할 때, last_name은 인덱스가 아니기 때문에 락 대상에서 제외됩니다.

예를들어 first_name='Georgi'에 해당하는 레코드가 200건이고, first_name='Georgi' AND last_name='Klassen'에 해당하는 레코드가 1건이라면

위 쿼리는 200건이 락이 걸리게 되는 것입니다.

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

InnoDB에서 사용하는 테이블의 레코드 수준 잠금은 테이블 수준의 잠금보다는 조금 더 복잡합니다.

테이블 잠금에서는 잠금의 대상이 테이블 자체 이므로 쉽게 문제의 원인이 발견되고 해결할 수 있습니다. 하지만 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리기 떄문에 해당 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않습니다.

각 트랜잭션이 어떤 잠금을 기다리고 있는지, 기다리고 있는 잠금을 어떤 트랜잭션이 가지고 있는지 메타 정보를 통해 쉽게 조회할 수 있습니다.

MySQL 5.1 부터는 information_schema라는 DB의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 통해 확인이 가능했습니다.

하지만 MySQL 8.0 부터는 information_schema의 정보들은 조금씩 제거 되고 있습니다.

그 대신 MySQL 8.0부터는 performance_schema DB의 data_locks, data_lock_waits 테이블로 대체되고 있습니다.

SELECT r.trx_id              waiting_trx_id,
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query           waiting_query,
       b.trx_id              blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query           blocking_query
FROM performance_schema.data_lock_waits w
         INNER JOIN information_schema.innodb_trx b
                    ON b.trx_id = w.blocking_engine_transaction_id
         INNER JOIN information_schema.innodb_trx r
                    ON r.trx_id = w.requesting_engine_transaction_id;

여기서 특정 스레드가 어떤 잠금을 가지고 있는지 더 상세히 확인하고 싶다면 performance_schema의 data_lock 테이블이 가진 컬럼을 모두 살펴보면 됩니다.

SELECT *
FROM performance_schema.data_locks\ G

MySQL의 격리 수준

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

DIRTY READNON-REPEATABLE READPHANTOM READ
READ UNCOMMITTED발생발생발생
READ COMMITTED없음발생발생
REPEATABLE READ없음없음발생 (InnoDB는 없음)
SERIALIZABLE없음없음없음
  • DIRTY READ: 타 트랜잭션에서 변경 후 커밋되지 않은 값을 현재 트랜잭션에서 조회 가능한 현상
  • NON-REPEATABLE READ: 현재 트랜잭션에서 조회시 A라는 결과가 나왔는데, 타 트랜잭션에서 B로 변경 후 커밋하고 다시 현재 트랜잭션에서 조회하면 B 값이 조회 되는 현상
  • PHANTOM READ: 현재 트랜잭션에서 조회 시 1건이 나왔던 쿼리가 타 트랜잭션에서 INSERT이후 2건이 나오는 등 타 트랜잭션에 의해 조회되는 값이 나왔다 사라졌다 하는 현상

READ UNCOMMITTED

각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부와 상관없이 다른 트랜잭션에서 보이는 격리 수준입니다.

READ COMMITTED

어떤 트랜잭션에서 데이터를 변경 후 COMMIT을 완료해야 다른 트랜잭션에서 조회할 수 있는 격리 수준입니다.

온라인 서비스에서 가장 많이 선택되는 격리 수준입니다.

REPEATABLE READ

같은 트랜잭션 내에서 같은 쿼리로 조회 시 같은 값이 나와함을 보장하는 격리 수준입니다.

위 READ COMMITTED는 중간에 타 트랜잭션이 COMMIT을 하면 변경된 데이터가 조회되어 정합성이 깨집니다.

하지만 이 격리 수준에서는 타 트랜잭션에서 값을 변경하면 언두 로그에 변경 전 데이터가 기록됩니다.

트랜잭션이 많아도 각 트랜잭션 별로 언두 로그에 기록이 되기 때문에 같은 값을 조회해도 변경 전 데이터가 조회되어 정합성을 유지합니다.

하지만 INSERT의 경우 언두 로그에 쓰기 잠금을 걸 수 없기 떄문에 타 트랜잭션에서 INSERT를 한다면 같은 쿼리여도 방금 INSERT된 값이 조회될 수도 있습니다. (PHANTOM READ 현상)

InnoDB는 갭 락과 넥스트 키 락을 이용하여 이 문제를 해결하기 때문에 PHANTOM READ 현상이 발생하지 않습니다.

SERIALIZABLE

가장 단순한 격리 수준이면서 가장 엄격한 격리 수준입니다.

읽기 작업도 공유 잠금을 획득해야만 하기 때문에 여러 트랜잭션에서 동시에 레코드를 변경할 수 없습니다.

변경작업을 하면 락이 걸리고 락이 풀려야 읽기가 가능하기 때문에 성능적으로 굉장히 좋지 않습니다

InnoDB는 REPEATABLE READ격리 수준에서 이미 갭 락과 넥스트 키 락을 통해 PHANTOM READ 현상을 해결 했기 때문에 굳이 해당 격리 수준을 사용할 이유는 없어 보입니다.