mysql 갭 잠금 잠금에 대한 규칙은 무엇입니까?

mysql 11 갭 잠금 잠금 규칙

갭 잠금은 반복 읽기 격리 수준에서만 적용됩니다. 다음 키 잠금은 실제로 읽기 커밋 격리 수준(읽기 커밋)으로 전환하면 갭 잠금과 행 잠금으로 구현됩니다. , 그 과정에서 갭 잠금 부분이 제거됩니다. 즉, 행 잠금 부분만 남게 된다는 것을 이해하기 쉽습니다. 읽기-커밋 격리 수준에서는 데이터와 로그 사이에 발생할 수 있는 불일치를 해결하기 위해 binlog 형식을 행으로 설정해야 합니다. 즉, 많은 회사의 구성은 읽기 커밋 격리 수준과 binlog_format=row입니다. 비즈니스에는 반복 읽기 보장이 필요하지 않으므로 읽기 제출 시 작업 데이터의 잠금 범위가 더 작다는 점(갭 잠금 없음)을 고려하면 이 선택이 합리적입니다
.

다음 키 잠금 잠금 규칙

　요약된 잠금 규칙에는 2개의 "원칙", 2개의 "최적화" 및 1개의 "버그"가 포함됩니다.
　원칙 1: 잠금의 기본 단위는 다음 키 잠금입니다. 다음 키 잠금은 열림 및 닫힘 간격입니다.
　원칙 2: 검색 과정에서 접근한 객체만 잠깁니다. 보조 인덱스에 대한 잠금이나 인덱싱되지 않은 열에 대한 잠금은 결국 기본 키로 추적되며 기본 키에도 잠금이 추가됩니다.
　최적화 1: 인덱스에 대한 동등한 쿼리의 경우 고유 인덱스를 잠그면 다음 키 잠금이 행 잠금으로 변질됩니다. 즉, InnoDB가 기본 키 또는 고유 인덱스를 스캔하는 경우 InnoDB는 행 잠금만 사용하여 잠금을 수행합니다. 최적화 2: 인덱스(반드시 고유 인덱스일 필요는 없음)에 대한 동일한 쿼리의 경우 마지막 값이 스캔되면 오른쪽으로 이동합니다. 동등 조건을 만족하지 않으면 next-keylock은 gap lock으로 변질됩니다.
　버그: 고유 인덱스에 대한 범위 쿼리는 조건을 충족하지 않는 첫 번째 값에 액세스합니다.

사례 분석

테이블 테스트를 예로 들어보겠습니다. 테이블 생성 문과 초기화 문은 다음과 같습니다. id는 기본 키 인덱스입니다

CREATE TABLE `test` (
id` int(11) NOT NULL,
col1` int(11) DEFAULT NULL,
col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`col1`)
) ENGINE=InnoDB;
insert into test values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

사례 1: 고유 인덱스 동등 쿼리 간격 잠금

이후 테이블 테스트 =7 레코드에 ID가 없습니다

원칙 1에 따르면 잠금 단위는 다음 키 잠금이고 세션 A의 잠금 범위는 (5,10]입니다.

동시에 최적화 2에 따르면 , 이는 동등한 쿼리(id=7)이고, id=10은 쿼리 조건을 충족하지 않으며, next-key 잠금은 gap 잠금으로 변질되므로 최종 잠금 범위는 (5,10)

Case 2 : 고유하지 않은 인덱스 동등 쿼리 잠금

여기는 세션 A입니다. 인덱스 col1의 col1=5인 행에 읽기 잠금을 추가합니다.

　 원칙 1에 따르면 잠금 단위는 다음 키 잠금이며 열린 상태로 유지됩니다. 오른쪽이 닫히면 5가 닫히므로 (0,5]가 됩니다. 다음 키 잠금 추가

c는 일반 인덱스이므로 c=5 레코드에 액세스하는 것만 즉시 중지할 수 없다는 점에 유의해야 합니다. col1=5)인 다른 레코드,

는 c=10인 경우에만 포기를 찾기 위해 오른쪽으로 순회해야 합니다. 두 번째 원칙에 따르면 모든 액세스를 잠가야 하므로 다음 키 잠금을 추가해야 합니다. 간격(5,10].

　　그러나 동시에 이는 최적화 2: 동등 판단을 따르며 Right 순회 방향으로 마지막 값은 col1=5의 동등 조건을 만족하지 않으므로 gap lock(5 ,10) 원칙 2에 따르면 이 쿼리는 포함 인덱스를 사용하므로 기본 키 인덱스에 액세스할 필요가 없습니다.

따라서 기본 키 인덱스에는 잠금이 추가되지 않습니다. 이는 세션 B의 업데이트 문이 성공적으로 실행될 수 있는 이유를 설명합니다.

　그러나 세션 C는 (7,7,7)의 레코드를 삽입하려고 하며 세션 A의 간격 잠금(5,10)에 의해 잠깁니다. 예는 인덱스에 잠금이 추가되었음을 보여줍니다. 업데이트를 위해 실행하면 시스템은 다음에 데이터를 업데이트하려고 한다고 생각하므로 조건을 충족하는 행에 행 잠금을 추가합니다.

　　데이터가 업데이트되는 것을 방지하기 위해 공유 모드에서 잠금을 사용하여 행에 읽기 잠금을 추가하려면 커버링 인덱스가 기본 키 인덱스에 액세스하지 않기 때문에 커버링 인덱스의 최적화를 우회해야 합니다. 잠금되지 않습니다

사례 3: 기본 키 인덱스 범위 쿼리 잠금

실행 시작 시 id=10인 첫 번째 행을 찾아야 하므로 next-key 잠금(5, 10]이어야 합니다. 최적화 1에 따르면 기본키

id에 대한 등가조건은 행 잠금으로 변질되고, id=10인 행 잠금만 추가됩니다.

　범위 쿼리이고 범위 검색은 계속해서 id=15를 찾습니다. . 이 줄은 멈추고 조건이 충족되지 않으므로

next-key lock(10,15]을 추가해야 합니다. 　이번 세션 A의 잠금 범위는 기본 키 인덱스, 행 잠금 id=10 및 다음 키 잠금(10,15]입니다. 세션 A가 처음으로

id=10인 행을 찾을 때, 동등한 쿼리로 판단되며, id=15까지 오른쪽으로 스캔할 경우 범위 쿼리 판단이 사용됩니다

.

사례 4: 고유하지 않은 인덱스 범위 쿼리 잠금

　 col1=10을 사용하여 처음으로 레코드를 찾을 때 다음 키 잠금(5,10]이 인덱스 c에 추가된 후) 인덱스 col1은 고유하지 않습니다.
한 인덱스에는 최적화 규칙이 없습니다. 즉, 행 잠금으로 전환되지 않습니다. 따라서 세션 A에 추가된 최종 잠금은 두 개의 다음 키 잠금(5,10] 및
(10, 15] on index c
　InnoDB는 검색을 계속할 필요가 없다는 것을 알기 전에 col1=15를 검색해야 하기 때문에 검색을 중지하는 것이 합리적입니다.

사례 5: 고유 인덱스 범위 쿼리 잠금 버그

　Session A는 원칙 1에 따르면 (10,15]의 다음 키 잠금만 인덱스 id에 추가해야 하며 id가 유일한 키이므로 루프가 판단됩니다. 행 id=15 중지되어야 합니다. 그러나 구현 시 InnoDB는 조건을 충족하지 않는 첫 번째 행인 id=20을 스캔합니다. 이는 범위 스캔이므로 인덱스 ID는 (15,20입니다. ).] 이 다음 키 잠금도 잠깁니다. 논리적으로 말하면 여기서 id=20 행을 잠그는 동작은 실제로 불필요합니다. id=15를 스캔한 후에는 찾을 필요가 없기 때문입니다.

사례 6: 고유하지 않은 인덱스에 대한 " "동등" "의 예

여기서 테이블 t에 새 레코드를 삽입합니다. 즉, t 값(30,10,30)에 삽입합니다. 이제 테이블에는 c=10인 행이 두 개 있지만 기본 키 값 id가 다르기 때문에(각각 10과 30) c=10인 두 레코드 사이에 간격이 있습니다.

우리는 삭제 문을 사용하여 확인합니다. 삭제 문의 잠금 논리는 실제로 기사 시작 부분에서 요약한 두 가지 "원칙"과 두 가지 "최적화"인 업데이트를 위한 선택과 유사합니다. 이때 세션 A는 먼저 첫 번째 col1=10 레코드에 액세스합니다. 마찬가지로 원칙 1에 따라 여기에 추가되는 내용은
(col1=5,id=5)입니다. 10) 다음 키 잠금입니다.

c는 일반 인덱스이므로 (col1=15,id=15) 행을 만날 때까지 오른쪽으로 검색이 계속됩니다. 최적화 2에 따르면 이는

등가 쿼리입니다. 조건에 맞지 않는 행은 오른쪽으로 찾아내므로 (col1=10,id=10)에서 (col1=15,id=15)까지의 간격잠김으로 변질됩니다.

　인덱스 c에 대한 이 삭제문의 잠금 범위는 위 그림에서 파란색 영역으로 표시된 부분입니다. 이 파란색 영역의 왼쪽과 오른쪽은 열린 간격, 즉 (col1=5,id=5) 및 (col1=15,id=15)를 나타내는 점선입니다. 이 두 줄에는 잠금 장치가 없습니다

. 사례 7: 제한 문 추가 Lock

　세션 A의 삭제 문은 제한 2를 추가합니다. 실제로 테이블 t에는 c=10인 레코드가 2개만 있으므로 제한 2를 추가하거나 삭제하는 효과는 동일합니다. 하지만 잠금 효과는 다릅니다

이는 Case 7의 delete 문이 제한 2라는 제한을 명확하게 추가했기 때문에 라인(col1=10, id=30)을 순회한 후에 이미 조건 표시줄을 충족하는 두 개의 명령문이 있기 때문입니다. , 사이클이 끝났습니다. 따라서 인덱스 col1의 잠금 범위는 다음 그림과 같이 (col1=5,id=5)
에서 (col1=10,id=30)까지 전면 개방 및 후면 폐쇄 범위가 됩니다.

　이 예제가 우리 실습에서 중요한 의미는 데이터를 삭제할 때 제한을 추가하려고 한다는 것입니다.

　삭제되는 데이터 수를 제어하여 작업을 더욱 안전하게 할 뿐만 아니라 잠금 범위도 줄어듭니다.

사례 8: 교착 상태의 예

　세션 A는 트랜잭션 시작 후 쿼리 문을 실행하고 공유 모드에서 잠금을 추가하고 next-keylock(5,10] 및 gap 잠금(10, 15)을 추가합니다. (오른쪽으로의 인덱스 순회는 간격 잠금으로 변질됩니다)

세션 B의 업데이트 문도 인덱스 c에 next-key 잠금(5,10]을 추가하고 잠금 대기를 입력해야 합니다. 실제로는 두 단계로 나뉩니다.

먼저 추가 (5,10)의 간격 잠금이 성공적으로 잠긴 다음 col1=10의 행 잠금을 추가합니다. 세션 A가 이미 이 행에 읽기 잠금을 추가했으며 이때 교착 상태에 대한 응용 프로그램이 차단됩니다. 그런 다음 세션 A는 세션 B의 간격 잠금으로 잠긴 행(8,8,8)을 삽입해야 합니다. 교착 상태로 인해 InnoDB는 세션 B를 롤백합니다.

사례 9: 인덱스 정렬에 의한 순서의 Gap lock 1

다음과 같은 문장
아래 그림은 이 테이블의 인덱스 id에 대한 개략도입니다.
begin;
select * from test where id>9 and id

우선, 이 쿼리 문의 의미는 order by id desc입니다. 조건을 충족하면 최적화됩니다. 프로세서는 먼저 "
번째 id　이 과정은 인덱스 트리의 검색 과정을 통해 얻은 것입니다. 엔진 내부에서는 실제로 id=12의 값을 찾고 싶었으나 결국 찾지 못했으나 공백(10,15)을 발견했습니다. (id=15는 조건을 만족하지 않으므로 next-key lock은 gap lock(10,
15)으로 변질됩니다.)
그러면 순회 과정에서 더 이상 동등한 쿼리가 아니며 id=입니다. 5를 한 줄씩 스캔하게 되는데, 구간은 왼쪽이 열리고 오른쪽이 닫히므로 next-key lock(0,5]이 추가됩니다. 즉, 실행 과정에서 레코드를 찾을 때
트리 검색을 통해 "Equal query" 방법을 사용합니다.

Case 10: index sorting에 따른 순서의 Gap lock 2

col1 desc순이므로 가장 먼저 찾는 것은 "가장 오른쪽" col1입니다. = 인덱스 col1 20개 행. 이것은 고유하지 않은 인덱스를 사용하는 동일한 쿼리입니다.

먼저 왼쪽 개방 간격에 다음 키 잠금을 추가하여 (15,20] 간격을 형성합니다. 오른쪽으로 이동하면 col1= 25는 조건을 충족하지 않으며 Gap 잠금으로 변질되므로 Gap 잠금(20,25)과 다음 키 잠금(15,20]이 추가됩니다.

col1=10으로 이동할 때 왼쪽으로 인덱스 스캔을 중지합니다. 다음 키 잠금(next-keylock)이 적용됩니다(5,10) 간격으로 오른쪽이 열리고 왼쪽이 닫힙니다

스캔 과정에서 세션 B의 삽입 문이 차단되는 이유입니다. col1=20, col1=15, col1=10의 세 행은 모두 select *이기 때문에 값을 가지므로 기본 키

id에 세 행 잠금이 추가됩니다. 세션 A는

　　　인덱스 col1(5, 25);

　　　ID=15 및 20입니다. 행 잠금

사례 11: 데이터 수정 예 - 먼저 삽입한 후 삭제

참고: col1>5에 따라 발견된 첫 번째 레코드는 col1=10이므로 (0,5는 추가되지 않습니다. ] 이 다음 키 잠금은

세션 A의 잠금 범위는 (5,10], (10,15)입니다. ], (15,20], (20,25] 및 (25,supremum]이 인덱스 col1에 있습니다.

이후 세션 B의 첫 번째 업데이트 문은 col1=5를 col1=1로 변경해야 합니다. 다음과 같이 이해하면 됩니다. 두 단계:
　　기록 삽입(col1=1, id=5)
　　삭제(col1=5, id=5)
　이 작업을 통해 세션 A의 잠금 범위는 그림 7과 같습니다.

　다음 세션 B는 update t set col1 = 5를 실행합니다. 여기서 col1 = 1입니다. 이 명령문은 두 단계로 나눌 수 있습니다: 　　기록 삽입(col1=5, id=5)

　　기록 삭제(col1) =1, ID=5). 첫 번째 단계는 간격 잠금이 추가된 (1,10)에 데이터를 삽입하려고 시도하여 차단되는 것입니다

　　.

위 내용은 mysql 갭 잠금 잠금에 대한 규칙은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!