여러 DB 결과를 사용하여 MySQL replace를 선택 항목으로 실행하여 교착 상태 발생

여러 DB 결과를 사용하여 MySQL replace를 선택 항목으로 실행하여 교착 상태 발생

스택 오버플로우에서 "Deadlock in MySQL"과 같은 다른 유사한 문제를 확인했지만 해결로 이어지는 것은 없습니다.

REPLACE INTO db2.table2 (id, some_identifier_id, name, created_at, updated_at) (SELECT id, some_identifier_id, name, created_at, updated_at FROM db1.table1 WHERE some_identifier_id IS NOT NULL AND some_identifier_id NOT IN (SELECT some_identifier_id FROM db2.table1 WHERE some_other_identifier_id IS NOT NULL));

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

상황:

1. 모든 테이블은 InnoDB; db1.table1 => 대조: latin1_ci 및 db2 => 대조: utf8_ci
2. 쿼리는 Server 버전 10.0.15-Maria인 개발 서버에서 정상적으로 동작합니다.DB
3. Galera 클러스터를 사용하여 멀티 마스터 복제를 공유하는 DB 서버가 5개 있다고 가정합니다.
4. 이 5개의 서버 중 하나에서 수동으로 쿼리를 실행하고 있는데 오류가 발생합니다.
5. 이 서버의 버전은 쿼리 실행이 성공한 개발 서버와 동일합니다(10.0.15-Maria).DB

시행된 것:

1. LOCK IN SHARE MODE를 포함했습니다. 예를 들어 REPLACE IN SHARE MODE... (첫 번째 쿼리(하위 쿼리) LOCK IN SHARE MODE 선택); 그러나 동일한 메시지와 함께 실패했습니다.
2. Insert / REPLACE ... (첫 번째 쿼리 LOCK IN SARE MODE) LOCK IN SARE MODE (하위 쿼리 LOCK IN SARE MODE)를 선택합니다.그것도 실패해, 같은 메세지가 표시됩니다.
3. select 쿼리 / sub select 쿼리에서 id별로 순서를 지정하려고 했습니다.같은 메시지로 다시 실패했습니다.
4. db1.table1과 db2.table1은 둘 다 50k 레코드밖에 없기 때문에 문제가 발생하지 않을 것입니다.
5. 모든 테이블에는 프라이머리 키로서 id가 있으며 자동으로 증가합니다.하지만 난 그걸 어떻게 해서든 분명히 사용하고 있어. 질문을 잘 봐줘.
6. SHOW Engine INNODB STATUS; 나에게 유용한 힌트는 전혀 없습니다.

가장 가능성이 높은 원인은 Galera 클러스터 뒤에 있는 멀티 마스터 리플리케이션의 최적 잠금(http://www.severalnines.com/blog/avoiding-deadlocks-galera-set-haproxy-single-node-writes-and-multi-node-reads)) 때문일 수 있습니다.그러나 개별 노드에서 쿼리를 실행할 때는 실패하지 않아야 합니다.성공 시 멀티마스터 레플리케이션에서도 같은 작업을 수행해야 하지만 기본적인 문제가 해결되면 복제된 서버에서 문제가 발생하지 않을 것으로 예상됩니다.

참고:

임시 테이블이나 하위 쿼리 결과를 코드로 저장하지 않고 이 작업을 수행해야 합니다.단일 쿼리를 실행하는 것이 지금까지 가장 바람직한 방법인 다른 종속성이 있습니다.

좋아, 이걸 해결할 방법을 찾았어.저의 조사와 테스트 결과, 이 실패의 배경에는 두 가지 문제가 있다고 생각합니다.

1. replace to 쿼리는 db1.table1에서db2.table2로 id를 다른 필수 필드와 동기화합니다.자동 증분 기본 키 삽입/ 교체가 갤러라에서 교착 상태가 발생할 가능성이 가장 높고 명백한 이유입니다.이 쿼리에서 id를 삭제하고 some_identifier_id를 동일한 치환 쿼리를 지원하는 고유 키로서 유지했습니다.그리고 그것은 교착 오류를 거의 막았다.

자동 증분 값은 순차적으로 증가하지 마십시오.Galera는 자동 증분 인크리먼트에 기초한 메커니즘을 사용하여 고유한 충돌 없는 시퀀스를 생성하므로 모든 노드에서 시퀀스에 공백이 생깁니다. https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/

2. 그러나 여전히 같은 교착상태 메시지가 1/10번 오고 그것은 Galera의 알려진 동작이다.Galera는 낙관적인 잠금을 사용합니다. 드물게 교착 상태가 발생합니다. 이 경우 트랜잭션을 다시 시도하는 것이 가장 좋습니다.

Galera 클러스터는 클러스터 수준의 최적 동시성 제어를 사용합니다.이것에 의해, 그 단계에서 COMMIT 를 발행하는 트랜잭션이 중단되는 일이 있습니다. http://galeracluster.com/documentation-webpages/limitations.html

즉, 쿼리는 개별 서버에서 정상적으로 실행되고 있었지만 갤러라일 경우 오류가 발생합니다.이 쿼리에서 자동 증분 프라이머리 키를 삭제하고 같은 트랜잭션을 처리하여 교착 상태에서 재시작하면 문제가 해결되었습니다.

[편집]

1. 저는 스키마, 환경, 문제, 그리고 어떻게 작업했는지를 설명하는 글을 썼습니다.같은 문제에 직면한 사람에게 유용할 수 있습니다.

2. 문제가 커뮤니티에 보고되어 있다.

언급URL : https://stackoverflow.com/questions/30094558/executing-mysql-replace-into-select-using-multiple-db-results-to-deadlock