mysql 삽입의 잠금 문제 이해...select

mysql tutorial이 칼럼에서는 mysql insert into...select의 잠금 문제를 소개합니다.

인용문:

최근에 데이터베이스 교착 상태 문제가 발생했습니다. 해결 과정에 대한 기록입니다.

문제 발생:

시스템의 mysql에는 통계 데이터와 같은 기능에 대해 몇 분마다 실행되는 여러 이벤트가 있으며, 이 이벤트는 데이터를 테이블에 기록합니다. 일반 내용: b에서 필수 필드를 선택하여 a로 바꾸십시오. 일반적인 구조는 다음과 같습니다. select from b에서 필요한 필드는 실제로 매우 복잡하고 많은 테이블 조인 작업을 포함합니다. 그러면 이 이벤트는 데이터 양이 많을 때 1분에 한 번씩 실행됩니다. 1분 안에 완료되지 않을 수도 있습니다. 그런 다음 b 테이블에서 작동하는 다양한 다른 삽입 및 업데이트 작업을 수행합니다. 이때 백엔드 로그에 교착 상태 및 대기 잠금 시간 초과 오류가 자주 발생하는 것을 확인할 수 있습니다. 최종 테스트 결과 이벤트를 끄면 문제가 해결된 것으로 확인되었으며 기본적으로 이 이벤트에 문제가 있는 것으로 확인되었습니다.

문제 분석:

사실 가장 시간이 많이 걸리는 일은 이벤트 문제라는 것을 알아낸 것이었습니다. 데이터를 쿼리하고 문제를 해결하는 데는 많은 시간이 걸리지 않았습니다. 1. 먼저 백엔드 로그의 오류 정보를 기반으로 교착 상태를 일으킨 테이블과 잠금 시간이 초과될 때까지 기다린 테이블을 찾습니다. 2. 그런 다음 이러한 테이블 이름과 인쇄된 SQL을 기반으로 위치를 알아냈습니다. 문제일 수도 있겠네요
3. 저희의 생각을 다시 확인해보니, 로그에 잠금 문제는 없는 것으로 확인되었습니다
4. 이벤트의 명령문에서 우리는 그것이 아마도 select의 필드로 대체되었음을 발견했습니다.

여기서 주된 이유는 이론적으로 어떤 상황에서 mysql이 잠길지 명확하지 않기 때문입니다. 공유 잠금만 적용되며, b 테이블 삽입 및 업데이트 등의 작업에는 배타적 잠금이 적용됩니다. 이 둘은 호환 가능하며 하나는 읽고 하나는 쓰며 충돌이 없습니다. 하지만 타임아웃 현상으로 미루어 볼 때, b에서 select가 요구하는 필드도 b 테이블을 잠그는 것으로 보입니다. 따라서 삽입과 업데이트 모두 잠금을 기다리고 있습니다.

드디어 Stack Overflow에서 흥미로운 정보와 링크 주소를 찾았습니다. 여기서는 read-committed 수준으로 설정해야 한다고 합니다. 그런 다음 mysql 구성 매개변수 innodb_locks_unsafe_for_binlog도 도입되었습니다.

그래서 우리는 이 정보를 따라 공식 웹사이트에서 확인한 결과 다음 문단을 발견했습니다:

INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.复制代码

즉, INSERT INTO T SELECT ... FROM S WHERE ... 이 경우, 우선 누구인지를 의미합니다. T 테이블에 있는 사람이 갭 잠금이 없는 레코드 잠금(행 수준 잠금)인가요? 테이블 S의 경우 잠금이 잠기지 않는 두 가지 상황이 있습니다.

1. 트랜잭션 격리 수준이 READ COMMITTED인 경우
2. 또는 innodb_locks_unsafe_for_binlog가 활성화되고 트랜잭션 격리 수준이 SERIALIZABLE이 아닌 경우

그렇지 않으면 InnoDB가 공유를 설정합니다. S next-key 행. next-key에 대해 잘 모르신다면 공식 홈페이지에서 이 소개와 링크 주소를 읽어보실 수 있습니다.

그래서 우리가 대기 시간 초과를 해결하려는 방법은 비교적 명확합니다. 즉, S 테이블이 잠기는 것을 방지하는 것입니다. 하지만 잠기는 것을 방지하려면 공식 웹사이트에 언급된 두 가지 방법을 사용할 수 있습니다. 둘 다 가능하지만 innodb_locks_unsafe_for_binlog 매개변수 도입에 따라 방법 1을 사용하고 트랜잭션 격리 수준을 읽기-커밋으로 설정하는 것이 가장 좋습니다.

이유는 다음과 같습니다.

1. innodb_locks_unsafe_for_binlog 매개변수는 정적입니다. my.cnf에 innodb_locks_unsafe_for_binlog = 1 줄을 추가한 다음 데이터베이스를 다시 시작해야 적용됩니다. mysql에 다음 명령어를 입력하세요.

show variables like "%innodb_locks_unsafe_for_binlog%"复制代码

ON 상태인 ​​것으로 확인되면 성공적으로 켜진 것입니다.

2. 트랜잭션의 격리 수준은 상대적으로 세분화되어 있으며 특정 세션에 대해 설정할 수 있습니다. 세션마다 서로 다른 격리 수준을 사용할 수 있으며 이 매개변수는 동적이며 mysql 명령줄에서 직접 수정할 수 있습니다.
3. mysql5.7의 매개변수 소개에는 innodb_locks_unsafe_for_binlog 매개변수가 후속 mysql 버전에서 폐기될 것이라고 나와 있습니다. 이는 사실입니다. mysql8.0의 자세한 매개변수 설명을 확인해보니 이 매개변수가 더 이상 존재하지 않는 것으로 나타났습니다.

따라서 제어를 위해 트랜잭션 격리 수준을 사용하는 것이 좋습니다.

더 많은 관련 무료 학습 권장사항: mysql 튜토리얼(동영상)

위 내용은 mysql 삽입의 잠금 문제 이해...select의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!