[MySQL][InnoDB] Deadlock

■ InnoDB 데드록

교착 상태는 각 트랜잭션마다 다른 잠금이 있기 때문에 다른 트랜잭션을 진행할 수 없는 상황입니다. 두 트랜잭션 모두 자원이 사용 가능할 때까지 대기하므로 보유한 잠금을 서로 해제않아 발생합니다.

 

트랜잭션이 여러 테이블에서 행을 잠글때(UPDATE 또는 SELECT ... FOR UPDATE와 같은 SQL을 통해) 교착 상태가 발생할 수 있지만 반대 순서로 발생합니다. 교착 상태는 이러한 명령문이 인덱스 레코드 및 간격 범위를 잠그고 각 트랜잭션이 타이밍 문제로 인해 일부 잠금을 획득하지만 다른 잠금은 획득하지 않는 경우에도 발생할 수 있습니다.

 

교착 상태의 가능성을 줄이려면 LOCK TABLES문 대신 트랜잭션을 사용합니다. 데이터를 삽입하거나 업데이트하는 트랜잭션을 장기간 열어 두지 않을 정도로 작게 유지시킵니다. 서로 다른 트랜잭션이 여러 테이블 또는 넓은 범위의 행을 업데이트 할 때 각 트랜잭션에서 동일한 순서의 작업(예 : SELECT ... FOR UPDATE)을 사용합니다. SELECT ... FOR UPDATE 및 UPDATE ... WHERE 문에 사용된 컬럼에 인덱스를 만듭니다. 교착 상태의 가능성은 분리 레벨의 영향을 받지 않습니다. 분리 레벨은 읽기 조작의 동작을 변경하고 교착 상태는 쓰기 조작으로 인해 발생하기 때문입니다.

 

교착 상태 감지가 활성화되고(기본값) 교착 상태가 발생하면 InnoDB는 조건을 감지하고 트랜잭션중 하나 (victim : 희생될 트랜잭션)를 롤백합니다. innodb_deadlock_detect 구성 옵션을 사용하여 교착 상태 감지를 사용하지 않는 경우 InnoDB는 innodb_lock_wait_timeout설정을 사용하여 교착 상태 발생시 트랜잭션을 롤백합니다. 따라서 응용 프로그램 논리가 올바른 경우에도 트랜잭션을 다시 시도해야하는 경우를 처리해야합니다. InnoDB 사용자 트랜잭션에서 마지막 교착 상태를 보려면 SHOW ENGINE INNODB STATUS 명령을 사용합니다. 교착 상태가 빈번하게 트랜잭션 구조 또는 응용 프로그램 오류 처리에 대해 문제가 있는 경우 innodb_print_all_deadlocks설정을 사용하고 쿼리를 실행하여 모든 교착 상태에 대한 정보를 mysqld 오류 로그에 출력합니다. 그리고 로그를 확인하여 어떤 상황일때 교착상태가 발생하는지 분석합니다.

 

■ InnoDB 교착 상태 예제

다음예는 잠금 요청으로 교착 상태가 발생할 때 오류가 발생하는 방법을 보여줍니다. 이 예에는 두 명의 클라이언트 A와 B가 포함됩니다.

 

먼저 클라이언트 A는 하나의 행을 포함하는 테이블을 만든 다음 트랜잭션을 시작합니다. 트랜잭션 내에서 A는 공유 모드에서 트랜잭션을 선택하여 행에서 S잠금을 얻습니다.

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+

 

그런 다음 클라이언트 B는 트랜잭션을 시작하고 테이블에서 행을 삭제하려고 시도합니다.

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

삭제 조작에는 X잠금이 필요합니다. 클라이언트 A가 보유한 S잠금과 호환되지 않기 때문에 잠금을 부여 할 수 없으므로 요청은 행 및 클라이언트 B블록에 대한 잠금 요청 큐로 진행됩니다.

 

마지막으로 클라이언트 A는 테이블에서 행을 삭제하려고 시도합니다.

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

 

클라이언트 A가 행을 삭제하기 위해 X잠금이 필요하기 때문에 교착 상태가 발생합니다. 그러나 클라이언트 B에 이미 X잠금 요청이 있고 클라이언트 A가 S잠금을 해제하기를 기다리고 있기 때문에 해당 잠금 요청을 승인 할 수 없습니다. B가 X잠금을 요청했기 때문에 A가 보유한 S잠금을 X잠금으로 업그레이드 할 수도 없습니다. 결과적으로 InnoDB는 클라이언트 중 하나에 대해 오류를 생성하고 잠금을 해제합니다. 클라이언트는이 오류를 반환합니다.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

이 시점에서 다른 클라이언트에 대한 잠금 요청을 승인하고 테이블에서 행을 삭제합니다.

 

■ Deadlock 검출과 롤백

교착 상태 감지가 활성화되면 (기본값)InnoDB는 트랜잭션 교착 상태를 자동으로 감지하고 트랜잭션을 롤백하여 교착 상태를 해제합니다. InnoDB는 롤백할 작은 트랜잭션을 선택하려고 합니다. 트랜잭션의 크기는 삽입, 업데이트 또는 삭제된행 수에 의해 결정됩니다.

 

InnoDB는 innodb_table_locks=1(기본값)이고 autocommit=0 인 경우 테이블 잠금을 인식하고 그 위의 MySQL계층은 row-level 잠금에 대해 알고 있습니다. 그렇지 않으면 InnoDB는 MySQL LOCK TABLES 문으로 설정된 테이블 잠금 또는 InnoDB 이외의 스토리지 엔진으로 설정된 잠금이, 관련된 교착 상태를 감지 할 수 없습니다. innodb_lock_wait_timeout 시스템 변수의 값을 설정하여 일정시간이 지나면 자동으로 timout(강제종료)이 되도록 합니다.

 

InnoDB가 트랜잭션의 전체 롤백을 수행하면 트랜잭션이 설정한 모든 잠금이 해제됩니다. 그러나 오류로 인해 단일 SQL 문만 롤백하는 경우 명령문으로 설정된 일부 잠금이 유지 될 수 있습니다. 이것은 InnoDB가 행 잠금을 어떤 명령문으로 어떤 잠금이 설정되었는지 나중에 알 수 없는 형식으로 저장하기 때문에 발생합니다.

 

SELECT가 트랜잭션에서 저장된 함수를 호출하고 함수내의 명령문이 실패하면 해당 명령문이 롤백됩니다. 또한 그 이후에 ROLLBACK이 실행되면 전체 트랜잭션이 롤백됩니다.

 

InnoDB 모니터 출력의 최신 DEADTED DEADLOCK 섹션에 “TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION(잠금 테이블 대기 시간이 너무 길거나 오래 걸리면 트랜잭션을 롤백합니다.)”라는 메시지가 포함 된 경우 대기중인 트랜잭션의 수를 나타냅니다. 200개의 트랜잭션을 초과하는 대기 목록은 교착 상태로 처리되며 대기 목록을 확인하려는 트랜잭션이 롤백됩니다. 잠금 스레드가 대기 목록에서 트랜잭션이 소유한 1,000,000개 이상의 잠금을 봐야하는 경우에도 동일한 오류가 발생할 수 있습니다.

 

교착 상태를 피하기 위해 데이터베이스 작업을 구성하는 기술에 대해서는 아래에서 설명합니다.

 

▶︎ 교착 상태 감지 비활성화

동시성이 높은 시스템에서 교착 상태 감지는 많은 스레드가 동일한 잠금을 기다릴때 속도 저하를 일으킬 수 있습니다. 때때로 교착 상태 감지를 사용하지 않고 교착 상태가 발생할 때 트랜잭션 롤백에 innodb_lock_wait_timeout 설정을 사용하는 것이 더 효율적일 수 있습니다. 교착 상태 탐지는 innodb_deadlock_detect 구성 옵션을 사용하여 비활성화 할 수 있습니다.

 

■ 교착 상태를 최소화하고 처리하는 방법

이 섹션은 14.7.5.2 절“데드락 감지 및 롤백”의 교착 상태에 대한 개념 정보를 기반으로합니다. 교착 상태 및 응용 프로그램에 필요한 후속 오류 처리를 최소화하기 위해 데이터베이스 작업을 구성하는 방법에 대해 설명합니다.

 

교착 상태는 트랜잭션 데이터베이스에서 고전적인 문제이지만 특정 트랜잭션을 전혀 실행할 수 없을 정도로 빈번하지 않으면 위험하지 않습니다. 일반적으로 교착 상태로 인해 롤백되는 트랜잭션을 항상 재발행 할 수 있도록 응용 프로그램을 작성해야합니다.

 

InnoDB는 자동 row-level 잠금을 사용합니다. 단일 행을 삽입하거나 삭제하는 트랜잭션의 경우에도 교착 상태가 발생할 수 있습니다. 이는 이러한 작업이 실제로 "원자"가 아니기 때문입니다. 삽입 또는 삭제된 행의 (아마도 여러 개의) 인덱스 레코드에 대한 잠금을 자동으로 설정합니다.

 

다음 기술을 사용하여 교착 상태에 대처하고 발생 가능성을 줄일 수 있습니다.

+ 언제든지 SHOW ENGINE INNODB STATUS 명령을 실행하여 가장 최근 교착 상태의 원인을 판별하십시오. 교착 상태를 피하기 위해 응용 프로그램을 조정하는 데 도움이 될 수 있습니다.

 

+ 교착 상태 경고가 자주 발생하는 경우 innodb_print_all_deadlocks 구성 옵션을 활성화하여보다 광범위한 디버깅 정보를 수집하십시오. 최신 교착 상태뿐만 아니라 각 교착 상태에 대한 정보는 MySQ오류 로그에 기록됩니다. 디버깅이 끝나면이 옵션을 비활성화합니다.

 

+ 교착 상태로 인해 트랜잭션이 실패하면 항상 재실행 준비를 합니다. 교착 상태는 어플리케이션의 순서나 작업방식을 교정하기 위한 작업이기 때문에 충분히 테스트를 해보아야 합니다. 만약 운영중에 발생되었다면 개발 서버나 검증 서버를 이용하여 테스트를 합니다.

 

+ 트랜잭션을 작고 짧게 유지하여 충돌 가능성을 줄입니다.

 

+ 관련 변경 사항을 적용한 후 즉시 트랜잭션을 커밋하여 충돌 가능성을 줄입니다. 특히 커밋되지 않은 트랜잭션으로 대화형 mysql 세션을 오랫동안 열어 두면 안됩니다.

 

+ 잠금 읽기 (SELECT ... FOR UPDATE 또는 SELECT ... LOCK IN SHARE 모드)를 사용하는 경우 READ COMMITTED와 같은 낮은 격리 수준을 사용하면서 테스트합니다.

 

+ 트랜잭션 내에서 여러 테이블을 수정하거나 동일한 테이블에서 다른 행 세트를 수정할 때마다 매번 일관된 순서로 해당 작업을 수행합니다. 이렇게 하면 다음 트랜잭션은 잘 정의된 큐를 형성하고 교착 상태에 빠질 가능성이 매우 낮게 됩니다. 예를 들어, 데이터베이스 작업을 응용 프로그램 내에서 함수로 구성하거나 다른 여러 위치에서 INSERT, UPDATE 및 DELETE 문의 여러 시퀀스를 코딩하는 대신 저장된 루틴을 호출합니다.

 

+ 테이블에 알맞은 인덱스를 추가합니다. 그런 다음 쿼리는 더 적은 수의 인덱스 레코드를 스캔하고 결과적으로 더 적은 잠금을 설정해야합니다. EXPLAIN SELECT를 사용하여 MySQL 서버가 쿼리에 가장 적합한 인덱스를 사용하고 있는지 확인합니다.

 

+ 적은 잠금을 사용합니다. SELECT가 이전 스냅 샷에서 데이터를 리턴하도록 허용 할 수있는 경우 FOR UPDATE 또는 LOCK IN SHARE MODE 절을 추가하면 안됩니다. 동일한 트랜잭션 내에서 각각의 일관된 읽기가 자신의 새 스냅 샷에서 읽기 때문에 READ COMMITTED 격리 수준을 사용하는 것이 좋습니다.

 

+ 위의 격리수준, 혹은 기타 내용들이 현제 작업에 맞지 않고 순서대로 트랜잭션이 처리되어야 한다면 테이블 수준 잠금으로 트랜잭션을 직렬화합니다. InnoDB테이블과 같은 트랜잭션 테이블에 LOCK TABLES를 사용하는 올바른 방법은 SET autocommit=0 (START TRANSACTION이 아님)으로 트랜잭션을 시작한 다음 LOCK TABLES를 사용하고 트랜잭션을 명시적으로 커밋 할 때까지 UNLOCK TABLES를 호출하지 않는 것입니다. 예를 들어, 테이블 t1에 쓰고 테이블 t2에서 읽어야하는 경우 다음을 수행 할 수 있습니다.

mysql> SET autocommit=0;
mysql> LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...
mysql> COMMIT;
mysql> UNLOCK TABLES;

테이블 수준 잠금은 테이블에 대한 동시 업데이트를 방지하여 사용중인 시스템에 대한 응답성이 떨어지는 데드락을 방지합니다.

 

+ 트랜잭션을 직렬화하는 또다른 방법은 단 하나의 행을 포함하는 보조 "세마포어"테이블을 작성하는 것입니다. 다른 테이블에 액세스하기 전에 각 트랜잭션이 해당 행을 업데이트하도록 합니다. 이런 식으로 모든 거래는 일련의 방식으로 발생합니다. 직렬화 잠금은 row-level 잠금이므로 InnoDB 인스턴트 교착 상태 감지 알고리즘도 이 경우 작동합니다. MySQL 테이블 수준 잠금에서는 시간 초과 방법을 사용하여 교착 상태를 해결해야합니다.