사례는 다음과 같습니다.
엔진 상태를 확인하기 위해 Show innodb status를 사용하는 동안 교착 상태 문제가 발견되었습니다.
*** (1) TRANSACTION:
TRANSACTION 0 677833455, ACTIVE 0 sec, 프로세스 번호 11393, OS 스레드 ID 278546 시작 인덱스 읽기
사용 중인 mysql 테이블 1, 잠김 1
LOCK WAIT 3 잠금 구조체, 힙 크기 320
MySQL 스레드 ID 83, 쿼리 ID 162348740 dcnet03 dcnet 업데이트에 대한 행 검색
업데이트 TSK_TASK 세트 STATUS_ID=1064,UPDATE_TIME=now () 여기서 STATUS_ID=1061 및 MON_TIME
*** (1) 이 잠금이 부여되기를 기다리는 중:
RECORD LOCKS 공간 ID 0 페이지 번호 849384 n 비트 208 테이블 `dcnet_db/TSK_TASK` trx id 0 677833455의 인덱스 `PRIMARY` 인덱스 `PRIMARY` 0 677833455 lock_mode X 잠금은 기록되지만 갭 대기는 아님
레코드 잠금 , 힙 번호 92 물리적 레코드: n_fields 11; 정보 비트 0
0: len 800000000097629c; asc b ;; len 6; len 7; @ ;; 3: 16진수 8000000000050b2; 16진수 80000000000502a; 16진수 800000000005426; 7: 길이 23; 16진수 75706c6f6164666972652e636f6d2f6
8616e642e706870; len 8;
asc 4; asc 8000000004e; 만원;;
* (2) 트랜잭션:
TRANSACTION 0 677833454, ACTIVE 0초, 프로세스 번호 11397, OS 스레드 ID 344086 업데이트 또는 삭제, InnoDB 내부에 선언된 스레드 499
mysql 테이블 사용 중 1, 잠김 1
3 잠금 구조체, 힙 크기 320, 실행 취소 로그 항목 1
MySQL 스레드 ID 84, 쿼리 ID 162348739 dcnet03 dcnet 업데이트 업데이트
TSK_TASK 세트 STATUS_ID=1067,UPDATE_TIME=now () 여기서 ID는 (9921180)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS 공간 ID 0 페이지 번호 849384 n 비트 208 테이블 `dcnet_db/TSK_TASK`의 인덱스 `PRIMARY` trx ID 0 677833454 lock_mode X 잠금은 기록이지만 간격은 아님
레코드 잠금, 힙 번호 92 물리적 레코드: n_fields 11 ; 압축 형식, 정보 비트
0 8; 16진수 800000000097629c; asc b ;; 1: 16진수 00002866eaee; asc (f ;; 2: 16진수 00000d40040110; asc @ ;; 3: 16진수 80000000000050b2 ;; 4: 렌 8 ; 16진수 800000000000502a; 16진수 800000000005426; asc uploadfire.com/hand.php;; 8: 16진수 800000000000042b; 8; hex 8000000000004e24; asc N$ ;;
*** (2) 이 잠금이 부여되기를 기다리는 중:
RECORD LOCKS 공간 ID 0 페이지 번호 843102 n 비트 600 테이블 `dcnet_db/TSK_TASK` trx ID 0 677833454 lock_mode X는 기록을 잠그지만 갭 대기는 없음
395 PHYSICAL RECORD: n_fields 3; 정보 비트 0
0: len 800000000000425; asc %;; 29c; ;;
*** WE ROLL BACK TRANSACTION (1)
이 교착 상태 문제는 시스템 모니터링 작업을 저장하는 데 사용되는 TSK_TASK 테이블과 관련됩니다.
ID: 기본 키
:
STATUS_ID
;작업 상태,
인덱스: KEY_TSKTASK_MONTIME2(STATUS_ID, MON_TIME).
분석에 따르면 관련된 두 문이 동일한 TSK_TASK 레코드를 포함해서는 안 되는데 왜 교착 상태가 발생합니까?
MySQL 공식 웹사이트 문서를 조회한 후 이것이 MySQL의 인덱싱 메커니즘과 관련되어 있음을 발견했습니다. MySQL의 InnoDB 엔진은 행 수준 잠금을 사용합니다. 원래는 레코드가 직접 잠긴다고 알고 있었지만 실제로는 그렇지 않습니다.
핵심 사항은 다음과 같습니다.
레코드를 잠그는 대신
UPDATE 및 DELETE 작업 중에 인덱스가 잠깁니다. MySQL은 WHERE 조건에 의해 스캔된 모든 인덱스 레코드를 잠글 뿐만 아니라 소위 next-key라는 인접한 키 값도 잠급니다.
예를 들어 UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000 문은 기본 키가 1000보다 크거나 같은 모든 레코드를 잠급니다. 문이 완료되기 전에는 기본 키가 동일한 레코드에 대해 작업을 수행할 수 없습니다
.
비클러스터 인덱스인
경우(비클러스터 인덱스 레코드가 잠긴 경우 해당 작업을 완료하려면 관련 클러스터 인덱스 레코드도 잠가야 함)
문제가 발생한 두 SQL 문을 분석해 보면 문제를 찾는 것은 어렵지 않습니다:
"update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME
"update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)"이 거의 동시에 실행된다고 가정하면, 이 명령문은 먼저 STATUS_ID 값을 업데이트해야 하므로 클러스터 인덱스(기본 키)를 잠급니다. KEY_TSKTASK_MONTIME2의 일부 인덱스 레코드를 잠그는 데도 필요합니다.
이와 같이 첫 번째 문은 KEY_TSKTASK_MONTIME2의 레코드를 잠그고 기본 키 인덱스를 기다리는 반면, 두 번째 문은 기본 키 인덱스의 레코드를 잠그고 KEY_TSKTASK_MONTIME2의 레코드를 기다리는 경우 교착 상태가 발생합니다.
작성자는 첫 번째 명령문을 분할하여 교착 상태 문제를 해결했습니다.
먼저 정규화된 ID를 찾습니다. TSK_TASK에서 ID를 선택합니다(여기서 STATUS_ID=1061 및 MON_TIME < date_sub(now(), INTERVAL 30분)). 그런 다음 상태를 업데이트합니다. TSK_TASK set STATUS_ID=를 업데이트합니다. 1064 where ID in (….)
이 시점에서 교착상태 문제는 완전히 해결되었습니다.