MySQL 교착 상태 문제 해결 사례 분석

문제가 나타납니다

어느 날 오후 갑자기 시스템에 경보가 울리고 예외가 발생했습니다.

자세히 보니 트랜잭션 롤백 예외인 것 같았는데 교착상태로 인해 롤백이 되었다고 나오네요.. 그래도 아직 Mysql 잠금에 대해 어느 정도 이해가 있어서 이 문제를 적극적으로 조사하기 시작했습니다. .

먼저 데이터베이스에서 Innodb Status를 검색하면 마지막 교착 상태 정보가 Innodb Status에 기록됩니다.

엔진 INNODB 상태 표시

교착 상태 정보는 다음과 같으며, sql 정보는 간단하게 처리하였습니다.

​------------

최근에 감지된 교착 상태

​------------

2019-02-22 15:10:56 0x7eec2f468700

*** (1) 거래:

TRANSACTION 2660206487, ACTIVE 0초 인덱스 읽기 시작

　mysql 테이블은 사용 중 1, 잠김 1

LOCK WAIT 잠금 구조체 2개, 힙 크기 1136, 행 잠금 1개

MySQL 스레드 ID 31261312, OS 스레드 핸들 139554322093824, 쿼리 ID 11624975750 10.23.134.92 erp_crm__6f73 업데이트 중

/*id:3637ba36*/UPDATE 테넌트_config SET

​open_card_point = 0

​tenant_id = 123

*** (1) 이 잠금이 부여되기를 기다리는 중:

RECORD LOCKS 공간 ID 1322 페이지 번호 534 n 비트 960 인덱스 uidx_tenant of table ——erp_crm_member_plan——． ——tenant_config—— trx id 2660206487 lock_mode X는 기록을 잠그지만 간격 대기는 하지 않습니다

*** (2) 거래:

TRANSACTION 2660206486, ACTIVE 0초 인덱스 읽기 시작

　mysql 테이블은 사용 중 1, 잠김 1

​3개의 잠금 구조체, 힙 크기 1136, 2개의 행 잠금

MySQL 스레드 ID 31261311, OS 스레드 핸들 139552870532864, 쿼리 ID 11624975758 10.23.134.92 erp_crm__6f73 업데이트 중

/*id:3637ba36*/UPDATE 테넌트_config SET

​open_card_point = 0

​tenant_id = 123

*** (2) 잠금 장치 보유(S):

RECORD LOCKS 공간 ID 1322 페이지 번호 534 n 비트 960 인덱스 uidx_tenant of table ——erp_crm_member_plan——． ——tenant_config—— trx ID 2660206486 잠금 모드 S

*** (2) 이 잠금이 부여되기를 기다리는 중:

RECORD LOCKS 공간 ID 1322 페이지 번호 534 n 비트 960 인덱스 uidx_tenant of table ——erp_crm_member_plan——． ——tenant_config—— trx id 2660206486 lock_mode X는 기록을 잠그지만 간격 대기는 하지 않습니다

*** 거래를 롤백합니다 (1)

​----------------

이 교착 상태 로그를 간단히 분석하고 설명하겠습니다. 트랜잭션 1이 Update 문을 실행할 때 uidx_tenant 인덱스를 얻은 다음 트랜잭션 2가 동일한 Update 문을 실행하고 X 잠금(행 잠금)을 수행해야 합니다. uidx_tenant.X 잠금(행 잠금)을 획득하기 위해 교착 상태가 발생하고 트랜잭션 1이 롤백되었습니다. 나는 그 당시 매우 혼란스러웠고, 교착 상태가 발생하는 데 필요한 조건을 떠올렸습니다.

상호 배타적.

요청 및 보류 조건.

조건 박탈이 없습니다.

대기주기. 로그를 보면 트랜잭션 1과 트랜잭션 2가 모두 같은 행의 행 잠금을 위해 경쟁하고 있음을 알 수 있는데, 이는 이전의 잠금 경쟁과 조금 다른 점은 어떻게 보더라도 만족할 수 없다는 것입니다. 순환 대기 조건. 동료의 주의를 받은 후 교착 상태 로그는 조사할 수 없으므로 비즈니스 코드와 비즈니스 로그를 통해서만 문제를 조사할 수 있습니다. 이 코드의 논리는 다음과 같습니다:

공개 int saveTenantConfig(PoiContext poiContext, TenantConfigDO 테넌트Config) {

​시도해 보세요 {

​returntenantConfigMapper.saveTenantConfig(poiContext.getTenantId(), poiContext.getPoiId(),tenantConfig);

} catch (DuplicateKeyException e) {

​LOGGER.warn("[saveTenantConfig] 기본 키 충돌, 레코드 업데이트. context:{}, config:{}", poiContext,tenantConfig);

​returntenantConfigMapper.updateTenantConfig(poiContext.getTenantId(),tenantConfig);

}

}

이 코드의 의미는 구성 파일을 저장한다는 것입니다. 고유 인덱스 충돌이 발생하면 업데이트됩니다. 물론 여기에 작성된 내용은 매우 표준화되어 있지 않을 수 있지만 실제로 사용할 수 있습니다

…

에 삽입 중복 키 업데이트

동일한 효과를 얻을 수 있지만, 이를 사용하더라도 실제로 교착상태가 발생하게 됩니다. 코드를 읽은 후 동료가 당시 업무일지를 보내줬는데요,

동시에 3개의 로그가 발생한 것을 볼 수 있는데, 이는 고유 인덱스 충돌이 발생하여 업데이트된 문을 입력한 후 교착 상태가 발생했음을 나타냅니다. 이 시점에서 마침내 대답이 조금 더 명확해 보입니다.

이때 테이블 구조를 다음과 같이 살펴보겠습니다(간략화):

테이블 만들기 ——tenant_config——

​——id—— bigint(21) NOT NULL AUTO_INCREMENT,

——tenant_id—— int (11) NULL이 아닙니다.

​——open_card_point—— int (11) 기본 NULL,

기본 키(——id——),

​고유 키 ——uidx_tenant—— (——tenant_id——)

) 엔진=InnoDB 기본 문자셋=utf8mb4 ROW_FORMAT=COMPACT

Tenant_id는 고유 인덱스로 사용되며 조건이 모두 고유 인덱스를 기반으로 하는 삽입 및 업데이트입니다.

테넌트_구성 설정 업데이트

​open_card_point = 0

​tenant_id = 123

이 시점에서 삽입 시 고유 인덱스를 잠그는 것이 관련이 있다고 생각합니다. 다음 단계에서 심층 분석을 수행하겠습니다.

심층 분석

위에서는 세 개의 트랜잭션이 업데이트 문을 입력한다고 말했습니다. 설명을 단순화하기 위해 동시에 업데이트 문을 입력하려면 두 개의 트랜잭션만 필요합니다.

팁: S 잠금은 공유 잠금이고, X 잠금은 상호 배제 잠금입니다. 일반적으로 X 잠금, S 잠금 및 X 잠금은 상호 배타적이지만 S 잠금 및 S 잠금은 상호 배타적이지 않습니다.

위의 프로세스에서 이 교착 상태의 핵심은 S 잠금을 획득하는 것임을 알 수 있습니다. 다시 삽입할 때 S 잠금을 획득해야 하는 이유는 무엇입니까? 고유 인덱스를 감지해야 하기 때문에? RR 격리 수준에서 읽으려는 경우 현재 읽기이므로 실제로 S 잠금을 추가해야 합니다. 이때 고유 키가 이미 존재하는 것으로 확인되었으며, 이때 두 트랜잭션의 S 잠금에 의해 업데이트 실행이 차단되어 위의 루프 대기 조건이 형성됩니다.

팁: MVCC에서 현재 읽기와 스냅샷 읽기의 차이점은 최신 데이터를 얻으려면 현재 읽기를 매번 잠가야 하는 반면(공유 잠금 또는 뮤텍스 잠금 사용 가능) 스냅샷 읽기는 해당 시점에 시작된 트랜잭션을 읽어야 한다는 것입니다. 스냅샷은 실행 취소 로그를 통해 구현되었습니다.

이것이 전체 교착상태의 원인이다.이런 종류의 교착상태가 발생할 수 있는 또 다른 상황은 동시에 3개의 삽입 작업이 있는 경우이다. 거래. 교착 상태.

솔루션

여기서 핵심 문제는 S 잠금을 제거하는 것입니다. 참고할 수 있는 세 가지 해결 방법은 다음과 같습니다.

RR 격리 수준을 RC 격리 수준으로 줄입니다. 여기서 RC 격리 수준은 스냅샷 읽기를 사용하므로 S 잠금이 추가되지 않습니다.

다시 삽입 시 업데이트를 위해 *를 선택하여 X 잠금을 추가하면 S 잠금이 추가되지 않습니다.

분산 잠금을 미리 추가할 수도 있고, Redis나 ZK 등을 사용할 수도 있습니다. 분산 잠금에 대해서는 제 글을 참고하세요. 분산 잠금에 대해 이야기해 봅시다

첫 번째 방법은 그다지 현실적이지 않습니다. 결국 격리 수준을 쉽게 수정할 수 없기 때문입니다. 세 번째 방법은 더 번거롭습니다. 그래서 두 번째 방법이 우리가 최종적으로 결정한 방법입니다.

위 내용은 MySQL 교착 상태 문제 해결 사례 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!