Post

MySQL 트랜잭션과 잠금 메커니즘 완벽 이해하기

Posted Updated
By green 30 min read

Real MySQL 8.0 5장 트랜잭션을 학습한 내용입니다.

트랜잭션

트랜잭션은 논리적인 작업 셋이 저장을 하거나 실패 시 원상태로 복구해서 작업의 일부만 적용되는 현상을 발생하지 않게 해준다. 잠금(Lock)과 비슷하나 목적에서 차이가 있는데 잠금은 동시성을 제어하기 위한 기능이고, 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.

격리 수준이라는 것도 알아야 하는데 하나 트랜잭션 내에서 또는 여러개 트랜잭션 간 작업 내용을 어떻게 공유하고 차단할 것인지 결정하는 레벨을 뜻한다.

단 주의할 점은 트랜잭션의 범위를 최소화하는 것이 좋다. 저장하는 작업은 하나의 트랜잭션으로 묶이는 게 좋으며, 단순 조회는 트랜잭션을 적용할 필요가 없다. 또한 네트워크 작업은 DBMS 트랜잭션 내에서 제거하는게 좋다.

Mysql 엔진의 잠금

Mysql 잠금은 1. 스토리지 엔진 레벨과 2. MySQL 엔진 레벨로 나눌 수 있다. 스토리지 엔진을 제외한 나머지 부분이 MySQL 엔진이라고 보면 된다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 반대의 경우엔 영향을 미치지 않는다.

MySQL엔진에서는 테이블 데이터를 동기화하기 위한 테이블락, 테이블 구조를 담그는 메타데이터 락, 사용자 필요에 맞게 사용할 수 있는 네임드 락이 있다.

글로벌 락

글로벌 락은 FLUSH TABLES WITH READ LOCK 명령어로 획득할 수 있으며 범위는 MySQL 서버 전체이다. 한 세션에서 글로벌 락을 획득하게 되면 다른 세션에서 select 를 제외한 대부분의 DDL 이나 DML을 실행할 때 글로벌 락이 풀릴 때까지 기다린다.

글로벌락은 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY테이블에 대해 mysqldump로 일관된 백업을 받을 때 사용된다. 만약 글로벌 락보다 먼저 실행된 락이 있다면 해당 락이 풀릴 때까지 기다려야 한다. 글로벌 락은 가급적 사용을 피하는 게 좋고, mysqldump 프로그램은 내부적으로 글로벌 락 명령어를 실행하여 백업할 때도 있기 때문에 mysqldump 의 옵션을 확인하여 어떤 락을 사용하는지 자세히 확인해보는 것이 좋다.

특정 세션에서 백업 락을 획득하면 아래 테이블의 스키마나 사용자 인증 정보를 변경할 수 없다. 하지만, 일반적인 테이블의 데이터 변경은 허용된다.

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

일반적인 MySQL 서버 구성은 소스 서버와 레플리카 서버로 구성되는데 주로 백업은 레플리카 서버에서 실행된다. 만약 백업이 글로벌락 명령어를 실행하게 되면 복제는 백업 시간만큼 지연이 될 수 있다.

테이블 락

테이블락은 개별 테이블 단위로 설정되는 잠금으로, 명시적으로 얻거나 묵시적으로 테이블 락을 획득할 수 있다. 명시해서 얻는 방법은 LOCK TABLES table_name [ READ | WRITE ] 명령어를 실행한다. 반납은 UNLOCK TABLE 명령어이다. 명시적으로 테이블을 잡그는 작업은 글로벌 락과 동일하게 상당한 영향을 미치기 때문에 특별한 이유가 없다면 잘 사용하지 않는다.

묵시적인 테이블락은 MyISAM 이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다. 묵시적 테이블락은 쿼리가 완료된 후 자동 해제 된다. 하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반 락을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블락이 설정되지 않는다.

즉, InnoDB 테이블에도 테이블락이 설정되긴하나 대부분 DML 쿼리에서는 무시되고, 스키마 변경 쿼리인 DDL의 경우 영향을 미친다.

네임드 락

네임드락은 GET_LOCK() 명령어로 임의의 문자열에 대해 잠금을 설정할 수 있다. 데이터베이스 객체가 아니라 단순히 사용자가 지정한 문자열에 대해 락을 걸거나 반납한다. 자주 사용되는 락은 아니고, 여러 클라이언트가 상호 동기화를 처리할 때 네임드락을 활용할 수 있다.

1
2
3
4
5

SELECT GET_LOCK('mylock'. 2); //2초 락 점유 후 자동 반납

SELECT IS_FREE_LOCK('mylock');

SELECT RELEASE_LCOK('mylock');

배치 프로그램처럼 한번에 많은 레코드를 변경하는 쿼리는 데드락의 원인이 될 수 있는데 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락 쿼리를 걸고 쿼리를 실행하면 안전하게 데이터를 변경할 수 있다.

MySQL 8.0 버전에서는 네임드 락을 중첩하여 사용할 수 있으며 SELECT RELEASE_ALL_LOCKS() 명령어로 한번에 모두 해제도 가능하다.

메타데이터 락

메타데이터 락은 데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우 획득하는 락이다. 명시적으로 락을 얻거나 반납할 순 없다.

때로는 메타데이터 락과 InnoDB의 트랜잭션을 동시에 사용해야 할 경우도 있다. 예를들어 로그 테이블에 로그를 저장하는 Insert 쿼리만 서비스에 존재한다고 가정하는 경우, 테이블 구조를 변경 할 때 Online DDL을 사용하면 undo 로그가 증가하고 Online DDL 버퍼 크기 등을 고민해야 한다. 또한, MySQL 서버 DDL은 단일 스레드로 동작하여 시간 소모도 상당하다. 이 때는 새로운 구조의 테이블을 생성하고 최근 데이터까지는 프라이머리 키인 id 값을 범위로 나눠서 여러개 쓰레드로 빠르게 복사한다

나머지 데이터는 트랜잭션 테이블 잠금, RENAME TABLE 명령으로 응용 프로그램의 중단 없이 실행할 수 있다. 이때 남은 데이터를 복사하는 시간동안은 테이블 잠금으로 Insert는 할 수 없다. 가능하면 미리 아주 최근 데이터를 복사해둬야 잠금 시간을 최소화할 수 있다.

InnoDB 스토리지 엔진 잠금

InnoDB 엔진은 MySQL에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 가지고 있다. 최근 버전에서 InnoDB 트랜잭션과 잠금, 잠금 대기 중인 트랜잭션을 목록을 조회하는 방법이 도입되었다.

information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 잠금을 가지고 있고, 어떤 트랜잭션이 잠금을 대기하고 있는지, 장금을 가지고 있는 클라이언트를 찾아서 종료까지 시킬 수 있다. 또한, Performance Schema를 이용해 InnoDB 스토리지 엔진의 내부 잠금에 대한 모니터링 방법도 추가 됐다.

InnoDB 스토리지 엔진의 잠금

잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락 또는 테이블 락으로 락 에스컬레이션 될 수는 없다. 일반 상용 DBMS와 조금 다른점은 InnoDB 스토리지 엔진에서는 레코드 락뿐만 아니라 레코드와 레코드 사이의 간격을 잠그는Gap 락이 존재한다.

레코드락

레코드 락은 레코드 자체만 잠근다. 다른 DBMS 레코드락과 중요한 차이점은 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다.

인덱스가 하나도 없는 테이블이면 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다. InnoDB에서 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭락을 사용하고, 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업은 갭 락이 아니라 레코드 자체에 대해서만 락을 건다.

갭 락

갭 락은 레코드 자체가 아니라 레코드와 인접한 레코드 사이의 간격만을 잠그는 것을 뜻한다. 갭락을 통해 레코드와 레코드 사이의 간격에 새로운 래코드가 insert 되는 것을 제어한다. 주로 넥스트 키 락의 일부로 사용된다.

넥스트 키 락

넥스트 키 락은 레코드 락과 갭락을 합쳐놓은 형태의 잠금이다. Mysql 서버에서는 Repeatable read 격리 수준을 사용해야 한다. 또한 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화(0)되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.

이 락을 사용하는 주목적은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행 될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들기 위해서이다. 주의점은 넥스트 키락과 갭 락으로 인해 데드락 또는 다른 트랜잭션을 기다리는 일이 자주 발생한다. 가능하다면 바이너리 로그 포맷을 row 형태로 바꾸어 넥스트 키락이나 갭 락을 줄이는 게 좋다.

Mysql 8.0버전에서는 ROW 포맷의 바이너리 로그에 대한 안정성이 높아졌고, Statement 포맷의 바이너리 로그가 가지는 단점을 많이 해결할 수 있는 방식이기 때문에 기본 설정으로 변경되었다.

자동 증가 락

자동 증가하는 숫자값을 추출하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공한다. 이때 InnoDB 스토리지 엔진에서는 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

  • MySQL 5.0 이하 또는 innodb_autoinc_lock_mode=0 에서 자동 증가 락 동작 방식 자동 증가 락은 Insert나 Replace와 같은 새로운 레코드를 저장하는 쿼리에서만 필요하다. 이 락은 트랙잭션과 관계없이 Insert나 Replace 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락을 걸고 바로 반납한다. 이 락을 명시적으로 사용할 수 있는 방법은 없으며, 아주 짧은 시간동안만 락을 잡기 때문에 대부분의 경우 문제가 되지 않는 락이다.

  • Mysql 5.1 이상 innodb_autoinc_lock_mode=1 MySQL 서버가 insert 되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가락을 사용하지 않고 더 가볍고 빠른 래치(뮤텍스)를 이용한다. 개선된 래치는 자동 증가 락에 비해 아주 짧은 시간 동안만 락을 하고, 필요한 자동증가값을 가져오면서 락을 해제 한다. 하지만 Insert .. Select와 같이 쿼리를 실행하기 전에 서버가 건수를 예측 할 수 없을 때는 Mysql 5.0에서와 같은 동작의 자동증가락을 사용한다. 이때는 Insert 쿼리가 완료되기 전까지 자동 증가락은 해지 되지 않기 때문에 다른 커넥션에서 Insert가 끝나기를 기다린다.

이런 대량 Insert가 수행될 땐는 InnoDB 스토리지 엔진은 여러 개의 자동 증가 값을 한번에 할당받아 Insert 되는 레코드에 사용한다. 그래서 대량 Insert되는 레코드는 자동 증가 값이 누락되지 않고 Insert된다. 하지만 한 번에 할당 받은 자동 증가 값이 남아서 사용되지 못하면 폐기 하므로 대량 Insert 문장을 실행 이후에 Insert 되는 레코드의 자동 증가 값은 연속되지 않고 누락된 값이 발생할 수 있다. 이 설정에서는 최소한 하나의 Insert 문장으로 insert 되는 레코드는 연속된 자동 증가값을 가지게 되고, 이러한 특징 때문에 연속 모드라고 부르기도 한다.

  • innodb_autoinc_lock_mode=2 InnoDB 스토리지 엔진은 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다. 이 설정은 하나의 Insert문장으로 Insert 되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하지 않는다. 이러한 특징때문에 인터리빙(Interleaved) 모드라고도 한다. 이 모드는Insert .. Select 와 같은 대량 insert 문장이 실행되는 중에도 다른 커넥션에서 insert를 할 수 있으므로 동시 처리 성능이 높아지는 장점이 있다. 하지만 이 모드의 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장한다. Statement 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동 증가값이 달라질 수도 있기 때문에 주의해야한다.

MySql 8.0 버전부터는 innodb_autoinc_lock_mode=2 가 기본값이다. 만약 ROW 포맷이 아닌 Statement 포맷의 바이너리 로그를 사용한다면 mode=1 사용이 권장된다.

인덱스와 잠금

변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다. ex) firstname에만 인덱스가 걸려있는 테이블에서 first_name이 동일한 사원은 253명, last_name 조건까지 추가 할 경우 딱 1명의 사원이 있는 상태에서 해당 사원 정보를 업데이트 하는 경우가 있다고 가정해보자.

1
2
3
4
5
6
7

SELECT COUNT(*) FROM employees WHERE first_name = 'lee';
253

SELECT COUNT(*) FROM employees WHERE first_name = 'lee' AND last_name='jeon';
1

UPDATE employees SET hire_date=NOW() WHERE first_name = 'lee' AND last_name='jeon';

이 update 쿼리에서 인덱스는 first_name이며 last_name은 인덱스가 없어 first_name인 레코드 253건의 레코드가 모두 잠긴다. update 문을 위해 적절히 인덱스가 준비되어 있지 않다면 각 클라이언트 간 동시성이 상당히 떨어져 한 세션에서 update 하는 중에는 다른 클라이언트는 대기해야 하는 상황이 발생할 것이다.

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

레코드 수준의 잠금은 테이블 레코드 각각에 잠금이 걸려 자주 사용되지 않은 레코드는 오래 잠겨 있어도 파악하기 어렵다. MySQL 5.1 부터는 레코드 잠금과 잠금 대기에 대한 조회 쿼리를 실행해볼 수 있다. 쿼리는 performance_schema의 data_locks 테이블과 data_lock_waits 테이블을 조인해서 실행한다.

만약 17번 스레드가 잠금을 가진 상태로 확인이 되었다면 KILL 명령어를 통해 해당 스레드를 강제로 종료하여 나머지 쓰레드의 update 명령을 진행시킬 수 있다. KILL 17

MySQL의 격리 수준

트랜잭션의 격리 수준은 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경 or 조회하는 데이터를 볼 수 있게 허용할 것인지 말것인지를 결정하는 것을 의미한다.

격리 수준은 크게 4가지이다.

  • READ UNCOMMITTED
    • DIRTY READ라고도 한다. 일반적인 데이터베이스에서 거의 사용하지 않는다.
    • NON-REPEATABLE READ 발생
    • PHANTOM READ 발생
  • READ COMMITTED
    • NON-REPEATABLE READ 발생
    • PHANTOM READ 발생
  • REPEATABLE READ
    • NON-REPEATABLE READ 없음
    • PHANTOM READ 발생하나 InnoDB에는 없다.
  • SERIALIZABLE
    • NON-REPEATABLE READ 없음
    • PHANTOM READ 없음
    • 동시성이 중요한 데이터베이스에서는 거의 사용하지 않는다.

일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITED나 REPEATABLE READ 중 하나를 사용한다.

READ UNCOMMITED

사용자 B는 사용자 A가 Insert한 데이터를 커밋하기 전에 조회할 수 있다. 이 경우 문제점은 사용자 A가 Insert 쿼리를 롤백해도 사용자 B는 조회한 데이터를 정상 데이터로 판단할 수 있다는 점이다. 즉, 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 Dirty Read라고한다.

READ COMMITED

Commit이 완료된 데이터만 조회할 수 있기 때문에 Dirty Read 현상이 발생하지 않는다. 단 NON-REPEATABLE READ 가 발생할 수 있다.

사용자 B가 트랜잭션을 시작하여 2번의 조회 쿼리를 실행하는 경우를 생각해보자. 우선 처음에는 lee라는 이름으로 조회 시 아무런 레코드를 가져오지 못했다. 두 번째 동일 쿼리를 실행 하기 전 사용자 A가 특정 레코드의 이름을 lee로 바꾸었을 경우, 두 번째 쿼리문에서는 lee라는 이름으로 레코드를 가져온다. 즉, 하나의 트랜잭션안에서 같은 select 쿼리가 다른 결과를 가져오는 경우 REPEATABLE READ 정합성을 해치게 된다.

READ COMMITED 에서는 트랜잭션 내부와 트랜잭션 외부에서 실행되는 Select의 차이가 별로 없다. 하지만 REPEATABLE READ 수준에서는 기본적으로 Select 문장도 트랜잭션 범위 내에서만 작동한다. 즉, START TRANSACTION을 시작한 상태에서 다른 트랜잭션에서 해당 데이터를 변경하고 COMMIT해도 동일한 결과만 나타나게 된다.

REPEATABLE READ

InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL 서버는 최소 REPEATABLE READ 격리 수준 이상을 사용해야한다. InnoDB 스토리지 엔진은 트랜잭션이 롤백될 가능성을 대비해 변경되기 전 레코드를 Undo 라는 공간에 백업해두고 실제 레코드 값을 변경한다. 이 Undo 공간에 백업된 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여준다.

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

사용자 B가 트랜잭션 10번으로 트랜잭션 id 6인 lee를 조회했다고 가정해보자. 사용자 A가 사용자 B의 다음 Select 조회 전 해당 값을 kim으로 바꾸면 커밋하면 lee라는 값과 트랜잭션 id 6을 Undo 영역에 저장한다. 사용자 B가 두 번째 Select 시 트랜잭션 10번보다 작은 트랜잭션 번호에서 변경한 것만 보게 되고 첫 번째 조회 쿼리와 동일하게 lee를 읽게 된다.

다만, 부등호 조건으로 조회 시 1건이 조회되던게 다른 트랜잭션에서 commit을하여 2건이 나오는 부정합이 발생할 수 있다. 또한 삭제 시에도 부정합이 발생할 수 있다. 이처럼 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안보였다 하는 현상을 PHANTOM READ라고 한다.

InnoDB 스토리지 엔진에서는 갭락과 넥스트 키락 덕분에 범위에 대해 락을 걸 수 있어 REPEATABLE READ에서도 PHANTOM READ가 발생하진 않는다.

SERIALIZABLE

가장 단순하면서 엄격한 격리 수준이다. 읽기 작업도 공유 잠금을 획득해야 하며, 동시에 다른 트랜잭션은 해당 레코드를 변경하지 못하게 된다. 즉, 한 트랜잭션이 레코드에 대해 읽고 쓰는 중엔 다른 트랜잭션은 절대 접근 할 수 없다.

database
database MySQL transaction InnoDB 트랜잭션 잠금 격리수준 ACID
This post is licensed under CC BY 4.0 by the author.