MySQL 교착 상태 문제를 해결하는 방법 공유

MySQL 교착상태 문제 해결 방법 공유

1. 환경

• CentOS, MySQL 5.6.21-70, JPA

• 문제 시나리오: 시스템에는 예약된 일괄 업데이트 데이터 상태 작업이 있으며, 매번 수천 개의 레코드를 업데이트하고 테이블의 총 레코드 수는 약 500만 개입니다.

2. 오류 로그

2017-2-25 17:38:41 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Lock wait timeout exceeded; try restarting transaction
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
警告: SQL Error: 1213, SQLState: 40001
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Deadlock found when trying to get lock; try restarting transaction

3. 문제 해결

Check InnoDB status for locks
mysql> SHOW ENGINE InnoDB STATUS;

Check MySQL open tables
mysql> SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions
mysql> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

Check lock dependency - what blocks what
mysql> SELECT * FROM `information_schema`.`innodb_locks`;

문제 해결 후 다음과 같은 문을 실행하면 문제가 발생하는 것으로 확인되었습니다.

update t_task_tel set state='iok', update_date='2017-02-27 11:03:02' where tel_id=66042 and task_id=350199;

4. 분석

해당 정보를 검색한 결과 MySQL InnoDB가 반드시 모든 행을 가지고 있는 것은 아닌 것으로 나타났습니다. -레벨 잠금.

관련 참고자료 단편은 다음과 같습니다.

선택 잠금
1) 업데이트 조건이 인덱스를 거치지 않는 경우, 예를 들어 "update from t1 set v2=0 실행 where v2=5;", 이때 Full Table Scan을 수행하게 되며, Table Scan 시 다른 업데이트 작업은 금지되어야 하므로 Table Lock으로 업그레이드 된다.
2) 업데이트 조건이 인덱스 필드이지만 고유 인덱스(기본 키 인덱스 포함)가 아닌 경우, 예를 들어 "update from t1 set v2=0 where v1=9;"를 실행하는 경우

그러면 업데이트에서 Next-Key Lock을 사용하게 됩니다. Next-Key Lock을 사용하는 이유:
a) 먼저 조건을 충족하는 레코드에 배타적 잠금이 추가되었는지 확인하세요. 그러면 현재 고유하지 않은 인덱스와 해당 기본 키 인덱스의 값이 잠깁니다. 🎜>b) 또한 잠긴 간격에는 새 데이터를 삽입할 수 없다는 것이 보장됩니다.
3) 업데이트 조건이 고유 인덱스인 경우 Record Lock을 사용하세요.

InnoDB는 고유 인덱스를 기준으로 해당 레코드를 찾아 기본 키 인덱스 값과 고유 인덱스 값에 레코드 잠금을 추가합니다. 단, Gap Lock(갭락)은 사용하지 마십시오.
InnoDB는 기본적으로 행 수준 잠금을 사용하므로 MySQL은 기본 키가 "명확하게" 지정된 경우에만 행 잠금(선택한 데이터만 잠그기)을 실행합니다. 그렇지 않으면 MySQL은 테이블 잠금(전체 데이터 잠금)을 실행합니다. 양식이 잠겨 있습니다).

분석 결과에 따르면 _task_tel 테이블 업데이트 시 tel_id와 task_id가 Where 조건에 UNIQUE(고유 인덱스)를 설정하지 않았기 때문인 것으로 추측됩니다. 5. 해결 방법

이러한 분석을 바탕으로 tel_id, task_id 두 필드를 통해 UNIQUE(고유 인덱스)를 설정하여 문제 해결을 시도해 보세요. (또한 테이블에 있는 많은 양의 데이터가 온라인 비즈니스에 영향을 미치지 않도록 먼저 쿼리한 다음 기본 키 ID를 기반으로 업데이트할 수도 있습니다.)

이 문제를 해결한 후에도 문제가 다시 발생하지 않았습니다.

귀하의 문제가 제가 겪은 문제와 유사하다면 그에 따라 해결해 보실 수 있습니다.