충격! 하나의 SQL 문에 너무 많은 잠금이 있습니다 ...

권장(무료): SQL 튜토리얼

Gap 잠금과 행 잠금을 함께 사용하면 잠금 대기가 발생할지 여부를 판단하는 데 실수하기 쉽습니다.

간격 잠금은 반복 읽기 격리 수준에서만 효과적이므로 이 문서에서는 기본적으로 반복 읽기를 사용합니다.

잠금 규칙

• 원칙 1
  잠금의 기본 단위는 열림과 닫힘 간격인 넥스트 키 잠금입니다.
• 원칙 2
  검색 과정에서 접근한 객체만 잠깁니다.
• 최적화 1
  인덱스에 대한 동등한 쿼리의 경우 고유 인덱스를 잠그면 다음 키 잠금이 행 잠금으로 변질됩니다.
• 최적화 2
  인덱스에 대한 동일 쿼리의 경우 오른쪽으로 순회하고 마지막 값이 동일 조건을 충족하지 않으면 다음 키 잠금이 간격 잠금으로 퇴화됩니다.
• 버그
  고유 인덱스에 대한 범위 쿼리는 조건을 충족하지 않는 첫 번째 값에 액세스합니다.

데이터 준비

테이블 이름: t
새 데이터: (0,0,0),(5,5,5),(10,10,10),(15,15,15), ( 20,20,20),(25,25,25)
다음 예는 기본적으로 그림 설명을 동반하므로 원고와 대조하여 읽어 보시기 바랍니다. 일부 예는 "세 가지 견해를 망칠" 수 있으므로 다음을 권장합니다. 기사를 읽은 후 직접 연습해 보세요.

Case

동일값 쿼리 간격 잠금

• 동일값 쿼리 간격 잠금

• 테이블 t에는 id=7이 없으므로 원칙 1에 따라 잠금 단위는 다음- 키 잠금, 따라서 세션 A의 잠금 범위 (5,10]

• 동시에 최적화 2에 따르면 동등한 쿼리(id=7)이지만 id=10이 만족되지 않으면 다음 키 lock은 gap lock으로 변질되므로 최종 잠금 범위는 (5,10)

따라서 세션 B가 id=8인 레코드를 이 gap에 삽입하면 잠기지만 세션 C는 다음과 같이 행을 수정할 수 있습니다. id=10.

비고유 인덱스에 해당합니다. Lock

• 비고유 인덱스에만 추가되는 잠금
  

• 세션 A는 c=5에 읽기 잠금을 추가해야 합니다. 인덱스 c의 행
  원칙 1에 따르면 잠금 단위는 next-key 잠금이므로 (0,5]에 next-key 잠금을 추가합니다.
  c는 일반 인덱스 이므로 레코드 c=5 에만 액세스할 수 없습니다. 즉시 중지, 오른쪽으로 이동하고 c=10을 찾은 경우에만 포기해야 합니다. 원칙 2에 따르면 액세스된 모든 항목은 잠겨 있어야 하므로 (5,10]에 다음 키 잠금을 추가합니다. 동시에 최적화 2: 동등성 판단을 충족하고 오른쪽으로 이동하며 마지막 값은 c=5의 동등성 조건을 충족하지 않으므로 간격 잠금(5,10)
  원칙 2에 따르면, 액세스된 객체는 잠깁니다. 이 쿼리는 커버링 인덱스를 사용하고 기본 키 인덱스에 액세스할 필요가 없으므로 기본 키 인덱스에 잠금이 추가되지 않으므로 세션 B의 업데이트 문이 실행될 수 있습니다. C가 (7,7,7)을 삽입하려고 하면 세션 A의 간격 잠금(5,10)에 의해 잠깁니다.
  
  

이 예에서 공유 모드의 잠금은 포함 인덱스만 잠그지만 업데이트를 위해 실행하면 시스템은 다음에 데이터를 업데이트하려고 한다고 생각하므로 기본 키 인덱스의 조건을 충족하는 행에 행 잠금을 추가합니다.

이 예는 다음과 같습니다. 동시에 인덱스에 잠금이 추가된다는 점을 설명합니다. 공유 모드에서 잠금을 사용하여 행에 읽기 잠금을 추가하여 데이터가 업데이트되는 것을 방지하려면 우회해야 한다는 지침이 제공됩니다. 커버링 인덱스의 최적화. 인덱스에 존재하지 않는 필드를 추가합니다. 예를 들어 공유 모드에서 c=5 잠금에서 d를 선택하도록 세션 A의 쿼리 문을 변경하면 효과를 직접 확인할 수 있습니다. 3 기본 키 인덱스 범위 잠금

Range 쿼리.

다음 두 쿼리 문은 동일한 잠금 범위를 갖나요? mysql> select * from t where id=10

mysql> * from t where id>=10 and id<11 for update;

id가 int 유형으로 정의되므로 이 두 문장이 동일하다고 생각할 수도 있습니다. 사실, 그들은 완전히 동일하지 않습니다.

논리적으로 이 두 검색 문은 확실히 동일하지만 잠금 규칙은 다릅니다. 이제 세션 A가 두 번째 쿼리 문을 실행하여 잠금 효과를 살펴보겠습니다.

그림 3 기본 키 인덱스의 범위 쿼리에 대한 잠금
이제 앞서 언급한 잠금 규칙을 사용하여 세션 A에 어떤 잠금이 추가될지 분석해 보겠습니다.

실행을 시작할 때 id=10인 첫 번째 행을 찾아야 하므로 next-key lock(5,10]이어야 합니다. 최적화 1에 따르면 기본 키 id에 대한 등가 조건이 행 잠금으로 변질됩니다. , 그리고 id=10인 행의 행 잠금만 추가합니다.

id=15인 행을 찾을 때까지 범위 검색이 계속되므로 next-key lock(10,15]을 추가해야 합니다.

따라서 이때 세션 A의 잠금 범위는 기본 키 인덱스, 행 잠금 id=10 및 다음 키 잠금(10,15]입니다. 이를 통해 세션 B와 세션 C의 결과를 이해할 수 있습니다.

여기서 주목해야 할 점은 첫 번째 세션 A에서 id=10인 행을 찾으면 등가 쿼리로 판단하고, id=15까지 오른쪽으로 스캔하면 범위로 판단한다는 것입니다. 쿼리

.

범위 쿼리 잠금을 다시 보면 사례 3과 비교할 수 있습니다.

고유하지 않은 인덱스 범위 잠금

고유하지 않은 인덱스으로 변질되지 않습니다. 따라서 session1은 최종적으로 c( 5,10] 및 (10,15] next-key에 잠깁니다. 고유하지 않은 인덱스는 c=15로 스캔되어야 합니다. 계속해서 뒤로 이동할 필요는 없습니다. 고유 인덱스 범위 잠금 버그처음 네 가지 경우에는 두 가지 원칙과 두 가지 최적화가 사용됩니다.

session_1	session_2	session_3
begin; t에서 *를 선택하세요 여기서 업데이트의 경우 c>=10 및 c<11입니다.		update t set d=d+1 여기서 c=15;(차단됨)
session1 c=10</을 사용하는 경우 code>를 사용하여 처음으로 레코드를 찾으려면 인덱스 c에 <code>(5,10] next-key lock이 추가됩니다.	c는	입니다. 최적화 규칙은 없습니다. 즉, 행 잠금
따라서 결과 관점에서 sesson2가 (8,8,8)의 삽입 문을 삽입하려고 하면 차단됩니다.

• c=10定位记录时，索引c加了(5,10] next-key lock
• c是非唯一索引，无优化规则，即不会退变为行锁
• 因此最终sesion1加锁为c的(5,10] 和(10,15] next-key lock。

所以从结果上来看，sesson2要插入（8,8,8)的这个insert语句时就被阻塞。

非唯一索引要扫到c=15，才知道无需继续往后遍历。

唯一索引范围锁bug

前四案例用到两个原则和两个优化，再看加锁规则bug案例。

session_1	session_2	session_3
begin; select * from t where id>10 and id<=15 for update;
	update t set d=d+1 where id=20;(阻塞)
		insert into t values(16,16,16);(阻塞)

session1是范围查询

• 按原则1，索引id只加(10,15] next-key lock，因为id是唯一键，所以循环判断到id=15这行就该停止遍历。

但实现上，InnoDB会继续扫描到第一个不满足条件的行，即id=20，且由于这是范围扫描，因此id上的(15,20] next-key lock也会被锁。

所以session2要更新id=20这行会被阻塞。
session3要插入id=16，也会被阻塞。

按理说锁住id=20这行没必要，因为唯一索引扫描到id=15即可确定不用继续遍历。但实现上还是这么做了，可能是个bug。

非唯一索引上存在"等值"的例子

为更好地说明“间隙”概念。
插入记录7

新插入的这一行c=10，即现在表里有两个c=10。那么，这时索引c上的间隙是什么状态了呢？
由于非唯一索引上包含主键的值，所以不可能存在“相同”两行。

但现在虽然有两个c=10，它们的主键值id却不同，因此这两个c=10记录之间也有间隙。

看如下案例。

6

delete加锁逻辑类似select ... for updatesession_1

session_2session_3begin; 업데이트하려면 t id>10 및 id=15에서 *를 선택하세요. 세트 d =d+1insert into t value(16,16,16); (Blocking)session1은 범위입니다. query원칙 1에 따라 인덱스 ID에 (10,15] next-key lock만 추가합니다. id가 유일한 키이므로 루프는 id=15를 결정합니다. code> 이 행은 탐색을 중지해야 합니다 <td align="left"><br></td>그러나 구현 시 InnoDB는 조건을 충족하지 않는 첫 번째 행, 즉 <code>id=20을 계속 스캔합니다. 이는 범위 스캔이기 때문입니다. , ID의 (15,20] next-key lock도 잠깁니다. 따라서 session2가 id=20 행을 업데이트하려는 경우 차단됩니다. Session3에는 id=16도 차단됩니다. 순회를 계속할 필요가 없는지 확인하기 위해 고유 인덱스가 id=15를 검색하므로 id=20 행을 잠글 필요가 없다는 것은 당연합니다. 그러나 이는 여전히 구현 단계에서 수행되며 이는 버그일 수 있습니다.

고유하지 않은 인덱스의 "동등성" 예
• "간격" 개념을 더 잘 설명합니다.
레코드 7 삽입

새로 삽입된 줄 c = 10, 즉 테이블에 c=10이 2개 있습니다. 그렇다면 현재 인덱스 c의 갭 상태는 어떤가요? 고유하지 않은 인덱스에는 기본 키 값이 포함되어 있으므로 두 개의 "동일한" 행을 가질 수 없습니다.

업데이트 t	여기서 id=20;(blocking)

하지만 이제 두 개의 c=10이 있더라도 기본 키 값 ID가 다르기 때문에 두 c=10 레코드 사이에 간격이 있습니다. 다음 사례를 보세요. 삭제 잠금 논리는 업데이트를 위해 ... 선택과 유사하며 초기 규칙도 준수합니다. session_1begin에서 * 삭제

6
session_2 session_3
c=10

🎜🎜🎜🎜🎜t 🎜값(13)에 삽입 ,13,13);(차단)🎜🎜🎜🎜🎜🎜🎜🎜🎜🎜🎜🎜🎜update t set d=d+1 여기서 c=15;🎜🎜🎜🎜🎜session1은 먼저 10을 탐색할 때 첫 번째 c=에 액세스합니다. : 🎜🎜🎜원칙 1에 따라 (c=10,id=10) next-key lock에 (c=5,id=5)를 추가합니다🎜🎜그런 다음 session1은 (c=15, id=15) 이 줄에서 루프가 종료됩니다. 최적화 2, 등가 쿼리에 따르면, 조건에 맞지 않는 행을 오른쪽으로 찾아내므로 gap lock(오픈 간격, (c=The two Rows 5, id=5) 및 (c=15, id=15)에는 잠금이 없습니다). 🎜🎜🎜🎜7 제한 문 잠금🎜🎜🎜🎜🎜🎜session_1🎜🎜session_2🎜🎜🎜🎜🎜🎜begin; 🎜 delete * from t 🎜c=10 제한 2🎜🎜 🎜🎜🎜🎜🎜🎜🎜🎜삽입 t 🎜값(13,13,13);(차단)🎜🎜🎜🎜

session1의 삭제 문에 제한 2가 추가되었습니다. 실제로 테이블 t에는 c=10인 레코드가 2개만 있으므로 제한 2를 추가하든 안 하든 삭제 효과는 동일하지만 잠금 효과는 다릅니다. Case 6의 결과와는 다르게 세션 B의 insert 문이 통과된 것을 확인할 수 있습니다.

이것은 Case 7의 삭제 문이 명확하게 제한 2의 제한을 추가했기 때문에 라인(c=10, id=30)으로 이동한 후 이미 조건을 충족하는 두 개의 명령문이 있고 루프가 종료되기 때문입니다.

따라서 인덱스 c의 잠금 범위는 아래 그림과 같이 (c=5, id=5)에서 (c=10, id=30)까지 전면 열림 및 후면 닫힘 간격이 됩니다.

제한 2

의 잠금 효과를 보면 (c=10, id=30) 이후의 간격이 잠금 범위에 있지 않으므로 c=12를 삽입하는 insert 문이 성공적으로 실행될 수 있습니다.

우리 실습에서 이 예의 중요한 의미는 데이터를 삭제할 때 제한을 추가하려고 시도하는 것입니다. 이는 삭제된 데이터의 수를 제어하여 작업을 보다 안전하게 할 뿐만 아니라 잠금 범위를 줄여줍니다.

교착상태 예시

이전 예시에서는 분석할 때 next-key lock의 로직에 따라 분석했는데, 이렇게 분석하는 것이 더 편리하기 때문입니다. 마지막으로 설명하기 위해 또 다른 사례를 살펴보겠습니다. 다음 키 잠금은 실제로 간격 잠금과 행 잠금의 합계 결과입니다.

이런 컨셉이 처음에도 언급되지 않았는지 궁금하시죠? 걱정하지 마세요. 다음 예를 먼저 살펴보겠습니다.

케이스 8

session A의 작업 순서 A. 트랜잭션 시작 후 쿼리 문을 실행하고 공유 모드에서 잠금을 추가한 후 next-key를 추가합니다. lock(인덱스 c의 5,10] 및 갭 잠금(10,15);

세션 B의 업데이트 문은 또한 인덱스 c에 다음 키 잠금(5,10]을 추가하고 잠금 대기를 입력해야 합니다.

그런 다음 세션 A는 (8,8,8)을 삽입해야 합니다. 이 줄은 세션 B의 갭 잠금으로 인해 잠겨 있습니다. 교착 상태로 인해 InnoDB는 세션 B를 롤백합니다.

세션의 다음 키 잠금이 아닌가? B가 성공적으로 적용되었나요?

실제로 세션 B의 "다음 키 잠금 추가(5,10]" 작업은 실제로 두 단계로 나누어집니다. 먼저 (5,10)의 간격 잠금을 추가합니다. 잠금이 성공하면 c=10을 추가합니다. 이때 행 잠금이 잠깁니다.

즉, 다음 키 잠금을 사용하여 잠금 규칙을 분석할 수 있지만 특정 실행 중에는 갭 잠금으로 나누어야 하며 행 잠금은 2단계로 실행됩니다.

요약

모든 경우는 반복 읽기에서 확인됩니다. 모든 잠긴 리소스는 2단계 잠금 프로토콜을 따릅니다.

마지막 경우에는 실제로 next-key 잠금이 gap 잠금과 row 잠금으로 구현된다는 것을 확실히 알 수 있는데, 읽기 커밋 격리 수준으로 전환하면 gap 잠금 부분이 제거되어 이해하기 쉽습니다. 그 과정에서 행 잠금 부분만 남습니다.

읽기 커밋 격리 수준 아래에는 또 다른 최적화가 있습니다. 즉, 문 실행 중에 행 잠금이 추가되는 것입니다. "조건을 충족하지 않는 행"에서는 트랜잭션이 커밋될 때까지 기다리지 않고 직접 해제해야 합니다.

읽기-커밋 격리 수준에서는 잠금 범위가 작고 잠금 시간이 짧아서 많은 기업에서도 사용합니다.

비즈니스에서 반복 읽기를 사용해야 하는 경우 팬텀 읽기 문제를 해결하고 트랜잭션을 병렬로 처리하는 시스템 기능을 최대화할 수 있습니다.

Gap 잠금과 행 잠금을 사용하면 잠금 대기 여부를 쉽게 확인할 수 있습니다.

Gap 잠금은 반복 읽기 격리 수준에서만 유효하므로 이 문서에서는 기본적으로 반복 읽기로 설정되어 있으므로

SQL

무료 칼럼~~

을 참조하세요.

위 내용은 충격! 하나의 SQL 문에 너무 많은 잠금이 있습니다 ...의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!