10. InnoDB 스토리지 엔진의 잠금

de_sj_awa·2021년 9월 21일

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. InnoDB는 레코드 기반의 잠금 방식 때문에 MyISAM보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있다. 하지만 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다롭다. MySQL 5.0 이하 버전에서 InnoDB 잠금 정보를 진달할 수 있는 도구라고는 lock_monitor(innodb_lock_monitor라는 이름의 InnodDB 테이블을 생성하는 방법)를 이용한 "SHOW ENGINE INNODB STATUS" 명령이 전부다. 하지만 이 내용도 거의 어셈블리 코드를 보는 것 같아서 이해하기가 상당히 어렵다.

하지만 MySQL 5.1부터 InnoDB 플러그인 스토리지 엔진이 도입되면서부터 InnoDB의 트랜잭션과 잠금 그리고 잠금 대기중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입왰다. MySQL 서버의 INFORMATION_SCHEMA라는 테이블에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있으며, 장시간 잠금을 가지고 있는 클라이언트를 종료시키는 것도 가능하다.

1. InnoDB의 잠금 방식

비관적 잠금

현재 트랜잭션에서 변경하고자 하는 레코드에 대해 잠금을 획득하고 변경 작업을 처리하는 방식을 비관적 잠금이라고 한다. 이 처리 방식에서 느낄 수 있듯이 현재 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할 수 있다라는 비관적인 가정을 하기 때문에 먼저 잠금을 획득한 것이다. 그래서 비관적 잠금(Pessimistic locking)이라고 부른다. 일반적으로 높은 동시성 처리에는 비관적 잠금이 유리하다고 알려져 있으며 InnoDB는 비관적 잠금 방식을 채택하고 있다.

낙관적 잠금

낙관적 잠금에서는 기본적으로 각 트랜잭션이 같은 레코드를 변경할 가능성은 상당히 희박할 것이라고(낙관적으로) 가정한다. 그래서 우선 변경 작업을 수행하고 마지막에 잠금 충돌이 있었는지 확인해 문제가 있었다면 ROLLBACK 처리하는 방식을 의미한다.

2. InnoDB의 잠금 종류

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로 또는 테이블 락으로 레벨업되는 경우(락 에스컬레이션)는 없다. 일반 상용 DBMS와는 조금 다르게 InnoDB 스토리지 엔진에서는 레코드 락뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이라는 것이 존재하는데, 간단히 그림으로 살펴보자.

(점선의 레코드는 실제 존재하지 않는 레코드를 가정한 것임)

레코드 락(Record lock, Record only lock)

레코드 자체만을 잠그는 것을 레코드 락이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 한다. 한 가지 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다. 만약 인덱스가 하나도 없는 테이블이라 하더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다. 많은 사용자가 간과하는 부분이지만 레코드 자체를 잠그느냐 아니면 인덱스를 잠그느냐는 상당히 크고 중요한 차이를 만들어 낸다. InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락(Next key lock) 또는 갭 락(Gap lock)을 사용하지만, 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업은 갭(Gap, 간격)에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.

갭 락(Gap lock)

다른 DBMS와의 또 다른 차이가 바로 갭(GAP) 락이라는 것이다. 갭 락은 레코드 그 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다. 갭 락이라는 것은 개념일 뿐이지 자체적으로 사용되지는 않고, 넥스트 키 락의 일부로 사용된다.

넥스트 키 락(Next key lock)

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다. STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다. 또한 Innodb_locks_unsafe_for_binlog 파라미터가 비활성화되면(파라미터 값이 0으로 설정되면) 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적이다. 그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다. 가능하면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다. 하지만 아직 ROW 포맷의 바이너리 로그는 그다지 널리 사용되지 않기 때문에 안정성을 확인하는 것이 어려운 상태이며, 또한 STATEMENT 포맷의 파이너리 로그에 비해 로그 파일의 크기가 상당히 커질 가능성이 많다.

자동 증가 락(Auto increment lock)

MySQL에서는 자동 증가하는 숫자 값을 추출(채번)하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공한다. AUTO_INCREMENT 칼럼이 사용된 테이블에서 동시에 여러 레코드가 INSERT되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가한 일련번호 값을 가져야 한다. InnoDB 스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

AUTO_INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같은 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE나 DELETE 등의 쿼리에는 걸리지 않는다. InnoDB의 다른 잠금(레코드 락이나 넥스트 키 락)과는 달리, AUTO_INCREMENT 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 AUTO_INCREMENT 락이 걸렸다가 즉시 해제된다. AUTO_INCREMENT 락은 테이블에서 단 하나만 존재하기 때문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 AUTO_INCREMENT 락을 걸게 되면 나머지 쿼리는 AUTO_INCREMENT 락을 기다려야 한다(AUTO_INCREMENT 칼럼에 명시적으로 값을 설정해 주더라도 자동 증가 락을 걸게 된다).

AUTO_INCREMENT 락은 명시적으로 획득하고 해제하는 방법은 없다. AUTO_INCREMENT 락은 아주 짧은 시간 동안만 걸렸다가 해제되는 잠금이라서 대부분의 경우 문제가 되지 않는다.

자동 증가 락에 대한 지금까지의 설명은 MySQL 5.0 이하 버전에 국한된 것이었다. MySQL 5.1 이상부터는 "innodb_autoinc_look_mode"라는 파라미터를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.

innodb_autonic_lock_mode=0
MySQL 5.0과 동일한 잠금 방식으로 모든 INSERT 문장은 자동 증가 락을 사용한다.
innodb_autonic_lock_mode=1
단순히 한 건 또는 여러 건의 데이터를 INSERT하는 SQL 중에서 MySQL 서버가 INSERT 되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락(Auto increment lock)을 사용하지 않고, 훨씬 가볍과 빠른 래치(뮤텍스)를 이용해 처리한다. 개선된 래치는 자동 증가 락과 달리 아주 짧은 시간 동안만 잠금을 걸고 필요한 자동 증가 값을 가져오면 즉시 잠금이 해제된다. 하지만 INSERT ... SELECT와 같이 MySQL 서버가 건수를 (쿼리를 실행하기 전에) 예측할 수 없을 때는 MySQL 5.0에서와 같이 자동 증가 락을 사용하게 된다. 이때는 INSERT 문장이 완료되기 전까지는 자동 증가 락은 해제되지 않기 때문에 다른 커넥션에서는 INSERT를 실행하지 못하고 대기하게 된다. 이렇게 대량 INSERT가 수행될 때는 InnoDB 스토리지 엔진은 한번에 여러 개의 자동 증가 값을 한번에 할당받아서 INSERT되는 레코드에 사용한다. 그래서 대량 INSERT되는 레코드는 자동 증가 값이 누락되지 않고 연속되게 INSERT된다. 하지만 한번에 할당받은 자동 증가 값이 남아서 사용되지 못하면 폐기하므로 대량 INSERT 문장의 실행 이후에 INSERT되는 레코드의 자동 증가 값은 연속되지 않고 누락된 값이 발생할 수 있다.
이 설정에서는 최소한 하나의 INSERT 문장으로 INSERT되는 레코드는 연속된 자동 증가 값을 가지게 된다. 그래서 이 설정 모드를 연속 모드(Consecutive mode)라고도 한다.
innodb_autonic_lock_mode=2
innodb_autonic_lock_mode가 2로 설정되면 InnoDB 스토리지 엔진은 절대 자동 증가 락을 걸지 않고 항상 경량화된 래치(뮤텍스)를 사용한다. 하지만 이 설정에서는 하나의 INSERT 문장으로 INSERT되는 레코드라 하더라도 연속된 자동 증가 값을 보장하지는 않는다. 그래서 이 설정 모드를 인터리빙(Interleaved mode)라고도 한다. 이 설정 모드에서는 INSERT ... SELECT와 같은 대량의 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있게 되므로 동시 처리 성능이 높아지게 된다. 하지만 이 설정에서 작동하는 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장하며, 복제를 사용하는 경우에는 마스터와 슬레이브의 자동 증가 값이 달라질 가능성도 있기 때문에 주의하는 것이 좋다.

더 자세한 내용은 MySQL 매뉴얼의 "Configuration InnoDB Auto-Increment Locking"을 참조하면 된다. 별로 관계 없는 내용 같지만 자동 증가 값은 한번 증가하면 절대 줄어들지 않는 이유가 AUTO_INCREMENT 잠금을 최소화하기 위해서다. 설령 INSERT 쿼리가 실패했더라도 한번 증가된 AUTO_INCREMENT 값은 다시 줄어들지 않고 그대로 남게 된다.

3. 인덱스와 잠금

InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계가 있기 때문에 다시 한번 더 자세히 살펴보자. "레코드 락"을 소개하면서 잠깐 언급했듯이 InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리한다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠가야 한다. 정확한 이해를 위해 다음 UPDATE 문장을 한번 살펴보자.

-- // 예제 데이터베이스의 employees 테이블에는 아래와 같이 first_name 칼럼만
-- // 멤버로 담긴 ix_firstname이라는 인덱스가 준비돼 있다.
-- // KEY ix_firstname (first_name_

-- // employees 테이블에서 first_name='Georgi'인 사원은 전체 253명이 있으며,
-- // first_name='Georgi'이고 last_name='Klassen'인 사원은ㅇ 딱 1명만 있는 것을 아래 쿼리로 확인할 수 있다.
mysql > SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
+---------+
| 253     |
+---------+

mysql > SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
+---------+
| 1       |
+---------+
-- // employees 테이블에서 first_name이 'Georgi'이고 last_name이 'Klassen'인 사원의
-- // 입사 일자를 오늘로 변경하는 쿼리를 실행해보자.
mysql > UPDATE employees SET hire_date=NOW()
        WHERE first_name='Georgi' AND last_name='klassen';

UPDATE 문장이 실행되면 1건의 레코드가 업데이트될 것이다. 하지만 이 1건의 업데이트를 위해 몇 개의 레코드에 락을 걸어야 할까? 이 UPDATE 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi'이며, last_name 칼럼은 인덱스에 없기 때문에 first_name='Georgi'인 레코드 253건의 레코드가 모두 잠긴다. 아마 오라클과 같은 DBMS에 익숙한 사용자라면 상당히 이상하게 생각할 것이며, 이러한 부분을 잘 모르고 개발하게 되면 MySQL 서버를 제대로 이용하지 못하게 될 것이다. 또한 이러한 MysQL의 특성을 알지 못하면 "MySQL은 정말 이상한 DBMS다"라고 생각하게 될 것이다. 이 예제에서는 몇 건 안 되는 레코드만 잠그지만 UPDATE 문장을 위해 적절히 인덱스가 준비돼 있지 않다면 각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 UPDATE 작업을 하고 있는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생할 것이다.

만약 이 테이블에 인덱스가 하나도 없다면 어떻게 될까? 이러한 경우에는 테이블을 풀 스캔하면서 UPDATE 작업을 하는데, 이 과정에서 테이블에 있는 30여 만 건의 모든 레코드를 잠그게 된다. 이것이 MySQL의 방식인 것이며, 또한 MySQL의 InnoDB에서 인덱스 설계가 중요한 이유 또한 이 때문이다.

4. 트랜잭션 격리 수준과 잠금

위의 그림에서 살펴본 불필요한 레코드의 잠금 현상은 InnoDB의 넥스트 키 락 때문에 발생하는 것이다. 하지만 InnoDB에서 넥스트 키 락을 필요하게 만드는 주 원인은 바로 복제를 위한 바이너리 로그 때문이다. 아직 많이 사용되지는 않지만 레코드 기반의 바이너리 로그(Row based binary log)를 사용하거나 바이너리 로그를 사용하지 않는 경우에는 InnoDB의 갭 락이나 넥스트 키 락의 사용을 대폭 줄일 수 있다. InnoDB의 갭 락이나 넥스트 키 락을 줄일 수 있다는 것은 사용자의 쿼리 요청을 동시에 더 많이 처리할 수 있음을 의미한다.

다음 조합으로 MySQL 서버가 기동하는 경우에는 InnoDB에서 사용되는 대부분의 갭 락이나 넥스트 키 락을 제거할 수 있다.

버전	설정의 조합
MySQL 5.0	innodb_locks_unsafe_for_binlog=1 트랜잭션 격리 수준을 READ-COMMITTED로 설정
MySQL 5.1 이상	바이너리 로그를 비활성화 트랜잭션 격리 수준을 READ-COMMITTED로 설정
MySQL 5.1 이상	레코드 기반의 바이너리 로그 사용 innodb_locks_unsafe_for_binlog=1 트랜잭션 격리 수준을 READ-COMMITTED로 설정

MySQL 5.0 버전에서는 바이너리 로그가 비활성화되지 않아도 트랜잭션의 격리 수준을 READ-COMMIITTED로 설정하는 것이 가능했다. 그래서 바이너리 로그의 사용 여부와 관계 없이 innodb_locks_unsafe_for_binlog 시스템 설정 값을 1로 설정하고 트랜잭션의 격리 수준을 READ-COMMITTED로 설정해 대부분의 갭 락이나 넥스트 키 락을 제거할 수 있었다. 하지만 MySQL 5.1 이상의 버전에서는 바이너리 로그(문장 기반의 바이너리 로그의 경우만)가 활성화되면 최소 REPEATABLE-READ 이상의 격리 수준을 사용하도록 강제되고 있기 때문에 조금 내용이 달라진 것이다. 여기서 "대부분"의 갭 락이나 넥스트 키 락이 없어진다고 한 것은 이 조합의 설정에서도 유니크키나 외래키에 대한 갭락은 없어지지 않기 때문이다.

또한 위 조합으로 설정되면 3번에서 언급했던 불필요한 잠금도 일부 없어진다. UPDATE 문장을 처리하기 위해 일치하는 레코드를 인덱스를 이용해 검색할 때, 우선 인덱스만을 비교해서 일치하는 레코드에 대한 배타적 잠금을 걸게 되지만, 그 다음 나머지 조건을 비교해서 일치하지 않는 레코드는 즉시 잠금을 해제한다. 즉 아래 그림에서 인덱스만으로 일치 여부를 판단하는 1차 비교 단계에서는 first_name='Georgi'인 레코드를 모두 잠그게 된다. 하지만 인덱스를 이용하지 않는 나머지 조건의 일치 여부를 판단하는 2차 비교에서 실제 업데이트 대상이 아니라는 것을 알게 됨과 동시에 1차 비교에서 걸었던 잠금을 해제한다. 최종적으로 이 쿼리는 first_name='Georgi' AND last_name='Klassen'인 레코드에 대해서만 배타적 잠금을 가지게 되며, 비효율적으로 불필요한 잠금이 생기는 현상은 없어지는 것이다.

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

InnoDB 스토리지 엔진을 사용하는 테이블의 레코드 수준 잠금은 테이블 수준의 잠금보다는 조금 더 복잡하다. 테이블 잠금에서는 잠금의 대상이 테이블 자체이므로 쉽게 문제의 원인이 발견되고 해결될 수 있다. 하지만 레코드 수준의 잠금은 테이블 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다.

MysQL 5.0 이하 버전에서는 레코드 잠금에 대한 메타 정보(딕셔너리 테이블)를 제공하지 않기 때문에 더더욱 어렵게 만드는 부분이기도 한다. 하지만 MySQL 5.1부터는(더 정확하게는 InnoDB 플러그인 버전부터는) 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행해 보면 잠금과 잠금 대기를 바로 확인할 수 있다. 그럼 버전별로 레코드 잠금과 잠금을 대기하고 있는 클라이언트의 정보를 확인하는 방법을 알아보자. 강제로 잠금을 해제하려면 KILL 명령을 이용해 MySQL 서버의 프로세스를 강제로 종료하면 된다.

MySQL 5.0 이하의 잠금 확인 및 해제

MySQL 5.0 이하 버전의 InnoDB에서는 레코드 잠금을 기다리고 있는 것인지 쿼리를 실행하고 있는 것인지, 그리고 어떤 커넥션이 레코드 잠금을 가지고 있는지 찾아내는 것이 쉽지 않다. 우선 다음과 같은 잠금 시나리오를 가정해보자.

커넥션1	커넥션2	커넥션3
BEGIN;
UPDATE employees SET birth_date=NOW() WHERE emp_no=100001;
	UPDATE employees SET hire_date=NOW() WHERE emp_no=100001;
		UPDATE employees SET birth_date=NOW(), hire_date=NOW() WHERE emp_no=100001;

1번 커넥션에서 employees 테이블에서 프라이머리 키(emp_no)가 100001인 사원의 레코드를 업데이트하면서 쓰기 잠금을 획득하게 되며, 2번과 3번 커넥션은 각각 동일한 레코드를 변경하기 위해 쓰기 잠금을 기다리게 된다. 이 상태에서 우선 SHOW PROCESSLIST 명령의 결과를 한번 살펴보자.

mysql > SHOW PROCESSLIST;
+-----+------+-----------+-----------+----------+------+----------+--------------------------+
| Id  | User | Host      | db        | Command  | Time | State    | Info                     |
+-----+------+-----------+-----------+----------+------+----------+--------------------------+
| 1   | root | localhost | employees | Sleep    | 45   |          | NULL                     |
| 3   | root | localhost | employees | Query    | 2    | Updating | UPDATE employees         |
|     |      |           |           |          |      |          |   SET hire_date=now()    |
|     |      |           |           |          |      |          |   WHERE emp_no=1000001   |
| 4   | root | localhost | employees | Query    | 1    | Updating | UPDATE employees         |
|     |      |           |           |          |      |          |   SET birth_date=now(),  |
|     |      |           |           |          |      |          |       hire_date=now()    |
|     |      |           |           |          |      |          |  WHERE emp_no=1000001    |
| 5   | root | localhost | employees | Query    | 0    | NULL     |  show processlist        |
+-----+------+-----------+----------------------+------+----------+--------------------------+

위의 결과를 보면, 3번과 4번 커넥션이 실제 레코드 잠금을 대기하고 있는 프로세스이지만 State 값은 "Updating"으로 표시되어 잠금을 기다리는 것인지 실제 쿼리를 실행하고 있는 것인지 알 수 없다. 단, 느낌으로 State가 "Updating" 상태이면서 쿼리의 실행 시간(Time 칼럼의 값)이 2~3초 이상 된다면 "잠금 때문에 기다리고 있구나"라는 것을 알 수 있다. 단 UPDATE나 DELETE 문장이 적절히 프라이머리 키나 인덱스를 이용할 수 있는 경우에 한해서 말이다. 인덱스를 이용할 수 없는 UPDATE나 DELETE 문장이라면 잠금 대기 없이 순수한 처리에만 그 이상의 시간이 소요될 수 있기 때문이다.

그럼 이보다 더 자세한 각 커넥션의 트랜잭션 상황을 살펴보기 위한 "SHOW ENGINE INNODB STATUS" 명령의 결과를 한번 살펴보자. 상당히 암호문 같은 문장이 출력될 것이다.

mysql > SHOW ENGINE INNODB STATUS\G

중요한 것은 레코드를 오랫동안 잠그고 있는 프로세스가 있는지 여부이므로 최대한 트랜잭션이 오랜 시간 동안 실행되고 있는 줄을 찾으면 된다. 하지만 중요한 것은 각 트랜잭션이 어떤 잠금을 가지고 있는 여부인데, 안타깝게도 이를 명확하게 알려주지는 않는다.

그래서 트랜잭션이 활성화된 프로세스에서 실행하려고 하는 쿼리로, 필요한 잠금을 예측해 봐야 한다. 일반적으로 프로세스들은 공통적으로 특정 테이블의 레코드를 변경하거나 삭제하려고 하는 쿼리가 표시되는 것이 일반적인 상황이며, 그 중에서 활성화 상태이지만 아무런 SQL도 실행하지 않거나 혼자 독특하게 다른 테이블의 레코드를 기다리고 있는 트랜잭션이 있다면 이것이 문제의 원인일 가능성이 높다. 이와 같은 방법으로 문제의 원인으로 예상되는 트랜잭션을 찾으면, 해당 트랜잭션의 프로세스를 KILL 명령으로 종료하면 문제가 해결된다. 만약 가장 근본적인 원인에 해당하는 트랜잭션을 찾기가 어렵다는 오래 기다리고 있는 트랜잭션의 커넥션을 모두 종료해 버리는 것이 가장 빠른 해결책이 될 것이다.

MySQL 5.1 이상의 잠금 확인 및 해제

위에서 봤겠지만 MySQL 5.0 이하 버전에서는 트랜잭션의 잠금을 추적하고 해결하는 것이 상당히 어렵다. 하지만 MySQL 5.1 버전부터는 이런 잠금이나 잠금을 대기하고 있는 트랜잭션에 대해 상세한 메타 정보를 제공한다. 다음은 앞 절에서 살펴본 것과 동일한 예제다.

커넥션1	커넥션2	커넥션3
BEGIN;
UPDATE employees SET birth_date=NOW() WHERE emp_no=100001;
	UPDATE employees SET hire_date=NOW() WHERE emp_no=100001;
		UPDATE employees SET birth_date=NOW(), hire_date=NOW() WHERE emp_no=100001;

"SHOW PROCESSLIST" 명령으로 출력되는 결과는 동일하다. 하지만 각 트랜잭션이 어떤 잠금을 기다리고 있는지, 기다리고 있는 잠금은 어떤 트랜잭션이 가지고 있는지를 쉽게 메타 정보를 통해 조회할 수 있다. 우선 MySQL 5.1부터는 INFORMATION_SCHEMA라는 DB에 INNODB_TRX라는 테이블과 INNODB_LOCKS, 그리고 INNODB_LOCK_WAITS라는 테이블이 준비돼 있다. 잠금이나 대기가 발생할 경우 InnoDB 스토리지 엔진에서 관련 정보를 계속 이 테이블로 업데이트하기 때문에 다음과 같이 간단히 SELECT 해서 확인할 수 있다.

mysql > SELECT * FROM information_schema.innodb_locks;

mysql > SELECT * FROM information_schema.inndb_trx;

INNODB_LOCKS 테이블은 어떤 잠금이 존재하는지를 관리하며, INNODB_TRX 테이블은 어떤 트랜잭션이 어떤 클라이언트 (프로세스)에 의해 기동되고 있으며, 어떤 잠금을 기다리고 있는지를 관리한다. 사실 이 테이블의 각 내용은 그다지 중요하지 않고, 이 3개의 테이블을 조합해서 어떤 커넥션이 어떤 커넥션을 기다리게 만드는지를 알아낼 수 있다는 것이 중요하다.

mysql > 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 information_schema.innodb_lock_waits w
  INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

이 쿼리의 결과에서 "waiting.." 칼럼은 잠금을 기다리는 트랜잭션이나 프로세스를 의미하며, "blocking..." 칼럼은 잠금을 해제하지 않아서 다른 트랜잭션을 막고(기다리게 하고) 있는 트랜잭션이나 프로세스를 의미한다.

참고

Real MySQL

de_sj_awa

이것저것 관심많은 개발자.

이전 포스트

MyISAM과 MEMORY 스토리지 엔진의 잠금

다음 포스트