MySQL 교착 상태 문제를 해결하기 위한 코드에 대한 자세한 소개

黄舟
풀어 주다: 2017-03-04 14:47:35
원래의
1564명이 탐색했습니다.

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가 반드시 모두 행 수준 잠금을 갖지는 않는 것으로 나타났습니다.

해당 참고자료 단편은 다음과 같습니다.

MySQL InnoDB锁机制之Gap Lock、Next-Key Lock、Record Lock解析
http://www.php.cn/

4、锁选择
1)、如果更新条件没有走索引,例如执行”update from t1 set v2=0 where v2=5;” ,此时会进行全表扫描,扫表的时候,要阻止其他任何的更新操作,所以上升为表锁。
2)、如果更新条件为索引字段,但是并非唯一索引(包括主键索引),例如执行“update from t1 set v2=0 where v1=9;” 
那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因:
a)、首先要保证在符合条件的记录上加上排他锁,会锁定当前非唯一索引和对应的主键索引的值;
b)、还要保证锁定的区间不能插入新的数据。
3)、如果更新条件为唯一索引,则使用Record Lock(记录锁)。
 
InnoDB根据唯一索引,找到相应记录,将主键索引值和唯一索引值加上记录锁。但不使用Gap Lock(间隙锁)。
로그인 후 복사
MySQL InnoDB 锁表与锁行
http://www.php.cn/

由于InnoDB预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL才会执行Row lock (只锁住被选取的资料例) ,否则MySQL将会执行Table Lock (将整个资料表单给锁住)。
로그인 후 복사

분석 결과 tel_id와 task_id가 Where 조건에서 UNIQUE(고유 인덱스)를 설정하지 않은 이유는 무엇인지 추측됩니다. _task_tel 테이블을 업데이트할 때;

5. 해결 방법

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

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

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

위 내용은 MySQL 데드락 문제를 해결하기 위한 코드에 대한 자세한 소개입니다. 더 많은 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!


원천:php.cn
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
최신 이슈
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿