오늘날 MySQL 잠금 유형 및 잠금 원리에 대한 심층적인 이해

관련 무료 학습 권장사항: mysql 튜토리얼

머리말

• MySQL 인덱스 기본 데이터 구조 및 알고리즘
• MySQL 성능 최적화 원칙 - 1부
• MySQL 성능 최적화 - 연습 1부
• MySQL 성능 최적화 - 연습 2부
• MySQL 잠금 및 트랜잭션 격리 수준

이전에 우리는 MySQL 데이터베이스의 기본 데이터 구조 및 알고리즘과 MySQL 성능 최적화의 일부 내용에 대해 이야기했습니다. 그리고 이전 기사에서는 MySQL의 행 잠금 및 트랜잭션 격리 수준에 대해 설명했습니다. 이 문서에서는 잠금 유형과 잠금 원리에 중점을 둘 것입니다.

먼저 MySQL 잠금을 나눕니다.

1. 잠금의 세분성에 따라 나눕니다: 행 잠금, 테이블 잠금, 페이지 잠금
2. 잠금 사용 방식에 따라 나눕니다: 공유 잠금, 배타적 잠금(비관적 잠금 구현) 잠금)
3. 이념적 잠금에도 두 가지가 있습니다: 비관적 잠금과 낙관적 잠금.
4. InnoDB에는 여러 행 수준 잠금 유형이 있습니다: 레코드 잠금, 갭 잠금, 다음 키 잠금
5. 레코드 잠금: 인덱스 레코드에 대한 잠금
6. Gap 잠금: 갭 잠금
7. 다음 키 잠금: 레코드 잠금+ 갭 Lock

테이블 잠금

테이블 수준 잠금은 MySQL 잠금 중 가장 세부적인 잠금입니다. 즉, 현재 작업이 행 잠금보다 리소스 오버헤드가 적고 교착 상태가 발생하지 않습니다. 하지만 잠금 충돌 가능성은 매우 높습니다. 대부분의 mysql 엔진에서 지원되는 MyISAM과 InnoDB는 모두 테이블 수준 잠금을 지원하지만 InnoDB는 기본적으로 행 수준 잠금을 사용합니다.

테이블 잠금은 MySQL 서버에 의해 구현됩니다. 일반적으로 ALTER TABLE 및 기타 작업과 같은 DDL 문을 실행할 때 전체 테이블이 잠깁니다. SQL 문을 실행할 때 잠길 테이블을 명시적으로 지정할 수도 있습니다.

테이블 잠금은 일회성 잠금 기술을 사용합니다. 즉, 세션 시작 시 잠금 명령을 사용하여 나중에 사용할 모든 테이블을 잠그면 해당 잠긴 테이블에만 액세스할 수 있습니다. 잠금 해제 테이블을 통해 모든 테이블 잠금이 최종적으로 해제될 때까지 다른 테이블에 액세스할 수 없습니다.

잠금 해제를 표시하기 위해 잠금 해제 테이블을 사용하는 것 외에도, 세션이 다른 테이블 잠금을 보유하고 있을 때 잠금 테이블 문을 실행하면 세션이 이전에 보유하고 있던 잠금이 해제되거나 세션이 다른 테이블 잠금을 보유할 때 시작을 실행하면 해제됩니다. 이전에 잠긴 잠금.

공유 잠금 사용법:

LOCK TABLE table_name [ AS alias_name ] READ复制代码

독점 잠금 사용법:

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE复制代码

잠금 해제 사용법:

unlock tables;复制代码

Row lock

행 수준 잠금은 MySQL에서 가장 정밀한 잠금 세분성입니다. 즉, 행만 잠그는 것을 의미합니다. 현재 수술 중입니다.

행 수준 잠금은 데이터베이스 작업의 충돌을 크게 줄일 수 있습니다. 잠금 세분성은 가장 작지만 잠금 오버헤드도 가장 큽니다. 교착상태 상황이 발생할 수 있습니다. 행 수준 잠금은 사용 방법에 따라 공유 잠금과 전용 잠금으로 구분됩니다.

스토리지 엔진마다 행 잠금 구현이 다릅니다. 나중에 특별한 설명이 없으면 행 잠금은 구체적으로 InnoDB에서 구현하는 행 잠금을 나타냅니다.

InnoDB의 잠금 원리를 이해하기 전에 저장 구조에 대한 어느 정도 이해가 필요합니다. InnoDB는 클러스터형 인덱스입니다. 즉, B+ 트리의 리프 노드는 기본 키 인덱스와 데이터 행을 모두 저장합니다. InnoDB 보조 인덱스의 리프 노드에는 기본 키 값이 저장되므로 보조 인덱스를 통해 데이터를 쿼리할 때는 클러스터형 인덱스에서 해당 기본 키를 가져와서 다시 쿼리해야 합니다. MySQL 인덱스에 대한 자세한 내용은 "MySQL 인덱스 기본 데이터 구조 및 알고리즘"을 참조하세요.

다음은 단일 행 데이터에 대한 InnoDB의 잠금 원리를 설명하기 위해 두 개의 SQL을 실행하는 예입니다.

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';复制代码

첫 번째 SQL은 기본 키 인덱스를 사용하여 쿼리한 다음 기본 키 인덱스 ID = 49에 쓰기 잠금을 추가하기만 하면 됩니다.

두 번째 SQL은 보조 인덱스를 사용하여 쿼리한 다음 먼저 쓰기 잠금을 추가합니다. 기본 키 인덱스에 id = Tom 인덱스에 쓰기 잠금을 추가한 다음 InnoDB 보조 인덱스를 사용하여 기본 키 인덱스를 기반으로 다시 쿼리하므로 기본 키 인덱스 id = 49에도 쓰기 잠금을 추가해야 합니다. , 위 그림과 같이.

즉, 기본 키 인덱스를 사용하려면 잠금을 추가해야 하고, 보조 인덱스를 사용하려면 보조 인덱스와 기본 키 인덱스에 잠금을 추가해야 합니다.

이제 인덱스를 기반으로 데이터의 단일 행을 업데이트하는 잠금 원리를 이해했으므로 다음 SQL 실행 시나리오와 같이 업데이트 작업에 여러 행이 포함되는 경우 어떻게 될까요?

update user set age = 10 where id > 49;复制代码

이 시나리오의 잠금 해제는 더 복잡하고 최적화 방법이 많이 있습니다. 아직 이해가 되지 않는 경우 설명을 위해 아래 메시지를 남겨주세요.

페이지 잠금

페이지 수준 잠금은 행 수준 잠금과 테이블 수준 잠금 사이에 잠금 세분성이 있는 MySQL의 잠금입니다. 테이블 수준 잠금은 빠르지만 충돌이 많습니다. 행 수준 잠금은 충돌이 거의 없지만 느립니다. 그래서 우리는 손상된 페이지 수준을 취하고 한 번에 인접한 레코드 그룹을 잠갔습니다. BDB는 페이지 수준 잠금을 지원합니다.

공유 잠금/독점 잠금

공유 잠금(공유 잠금)

읽기 잠금이라고도 하는 공유 잠금은 읽기 작업에 의해 생성되는 잠금입니다. 다른 사용자는 동시에 데이터를 읽을 수 있지만 모든 공유 잠금이 해제될 때까지 어떤 트랜잭션도 데이터를 수정할 수 없습니다(데이터에 대한 배타적 잠금 획득).

트랜잭션 T가 데이터 A에 공유 잠금을 추가하면 다른 트랜잭션은 A에 공유 잠금만 추가할 수 있고 배타적 잠금은 추가할 수 없습니다. 공유 잠금이 부여된 트랜잭션은 데이터를 읽을 수만 있고 데이터를 수정할 수는 없습니다.

Usage

SELECT ... LOCK IN SHARE MODE;SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁（eXclusive Lock）

排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法

SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE

쿼리 문 뒤에 LOCK IN SHARE MODE를 추가하면 MySQL에서 공유 잠금을 추가합니다. 다른 스레드는 쿼리 결과 집합의 모든 행에 배타적 잠금을 사용하지 않으며 공유 잠금을 성공적으로 적용할 수 있습니다. 그렇지 않으면 차단됩니다. 다른 스레드도 공유 잠금을 사용하는 테이블을 읽을 수 있으며 이러한 스레드는 동일한 버전의 데이터를 읽습니다.

배타적 잠금(eXclusive Lock)

배타적 잠금은 쓰기 잠금이라고도 합니다. 트랜잭션 T가 데이터 A에 배타적 잠금을 추가하면 다른 트랜잭션은 A에 더 이상 추가할 수 없습니다. 봉쇄의. 배타적 잠금이 부여된 트랜잭션은 데이터를 읽고 수정할 수 있습니다.

Usage

SELECT ... FOR UPDATE;

쿼리 문 뒤에 FOR UPDATE를 추가하면 Mysql은 쿼리 결과 집합의 각 행에 배타적 잠금이 추가됩니다. 쿼리 결과 집합의 모든 행에 대해 다른 스레드가 배타적 잠금을 사용하지 않으면 배타적 잠금을 성공적으로 적용할 수 있으며, 그렇지 않으면 차단됩니다.

낙관적 잠금과 비관적 잠금

데이터베이스의 잠금 메커니즘에 도입된 것처럼 데이터베이스 관리 시스템(DBMS)에서 동시성 제어 작업은 여러 트랜잭션이 동일한 데이터에 액세스할 때 트랜잭션의 격리가 파괴되지 않도록 하는 것입니다. 데이터베이스의 통일성과 데이터베이스의 통일성을 동시에 유지합니다.

낙관적 동시성 제어(낙관적 잠금)와 비관적 동시성 제어(비관적 잠금)는 동시성 제어에 사용되는 주요 기술 수단입니다.

비관적 잠금이든 낙관적 잠금이든 모두 사람들이 정의한 개념이고 일종의 생각이라고 볼 수 있습니다. 실제로 관계형 데이터베이스 시스템에는 낙관적 잠금(Optimistic Locking)과 비관적 잠금(Pessimistic Locking)이라는 개념이 존재할 뿐만 아니라, Memcache, Hibernate, tair 등도 비슷한 개념을 갖고 있다.

다양한 비즈니스 시나리오에서는 다양한 동시성 제어 방법을 선택해야 합니다. 그러므로 낙관적 동시성 제어와 비관적 동시성 제어를 좁은 의미의 DBMS 개념으로 이해하지 마시고, 데이터에서 제공하는 잠금 메커니즘(행 잠금, 테이블 잠금, 배타적 잠금, 공유 잠금)과 혼동하지 마십시오. 실제로 DBMS에서는 데이터베이스 자체에서 제공하는 잠금 메커니즘을 사용하여 비관적 잠금을 구현합니다.

비관적 잠금

• 관계형 데이터베이스 관리 시스템에서 비관적 동시성 제어("비관적 잠금"이라고도 함, 비관적 동시성 제어, 약어로 "PCC")는 동시성 제어 방법입니다. 이는 트랜잭션이 다른 사용자에게 영향을 미치는 방식으로 데이터를 수정하는 것을 방지합니다. 트랜잭션에 의해 수행된 작업이 데이터 행에 잠금을 적용하는 경우 트랜잭션이 잠금을 해제한 경우에만 다른 트랜잭션이 잠금과 충돌하는 작업을 수행할 수 있습니다. 비관적 동시성 제어는 주로 데이터 경합이 심한 환경과 동시성 충돌이 발생할 때 데이터를 보호하기 위해 잠금을 사용하는 비용이 트랜잭션 롤백 비용보다 낮은 환경에서 사용됩니다.
• 비관적 잠금은 이름에서 알 수 있듯이 외부 세계에 의해 수정되는 데이터(이 시스템의 다른 현재 트랜잭션 및 외부 시스템의 트랜잭션 처리 포함)에 대해 보수적인 태도(비관적)를 나타냅니다. 처리 과정에서 데이터가 잠겨 있습니다. 비관적 잠금의 구현은 데이터베이스에서 제공하는 잠금 메커니즘에 의존하는 경우가 많습니다(데이터베이스 계층에서 제공하는 잠금 메커니즘만이 데이터 액세스의 배타성을 실제로 보장할 수 있습니다. 그렇지 않으면 이 시스템에 잠금 메커니즘이 구현되어 있어도 잠금 메커니즘이 없습니다). 데이터를 수정하지 않도록 보장)
• 비관적 잠금의 특정 프로세스

모든 레코드를 수정하기 전에 레코드에 배타적 잠금을 추가해 보세요.

잠금에 실패하면 레코드가 잠금 해제되었음을 의미합니다. 수정 중인 경우 현재 쿼리가 기다리거나 예외가 발생할 수 있습니다. 구체적인 응답 방법은 실제 필요에 따라 개발자가 결정합니다.

🎜잠금에 성공하면 기록이 수정될 수 있으며 거래가 완료된 후 잠금이 해제됩니다. 🎜🎜레코드를 수정하거나 단독 잠금을 추가하는 다른 작업이 있는 경우 해당 작업은 우리가 잠금을 해제할 때까지 기다리거나 직접 예외를 발생시킵니다. 🎜🎜🎜비관적 잠금의 장점과 단점🎜🎜비관적 잠금은 실제로 "액세스하기 전에 먼저 잠금을 확보"하는 전략을 채택하여 데이터 처리의 보안을 보장합니다. 그러나 효율성 측면에서 추가 잠금 메커니즘으로 인해. , 추가 오버헤드가 발생하고 교착 상태 가능성이 높아집니다. 그리고 이는 동시성을 감소시킵니다. 한 항목이 데이터 행을 획득하면 다른 항목은 이 데이터 행에 대해 작업을 수행하기 전에 트랜잭션이 제출될 때까지 기다려야 합니다. 🎜

Optimistic Lock

관계형 데이터베이스 관리 시스템에서 낙관적 동시성 제어("낙관적 잠금", 낙관적 동시성 제어, 약어 "OCC"라고도 함)는 동시성 제어 방법입니다. 다중 사용자 동시 트랜잭션은 처리 중에 서로 영향을 미치지 않으며 각 트랜잭션은 잠금을 생성하지 않고도 영향을 받는 데이터 부분을 처리할 수 있다고 가정합니다. 데이터 업데이트를 커밋하기 전에 각 트랜잭션은 먼저 트랜잭션이 데이터를 읽은 후 다른 트랜잭션이 데이터를 수정했는지 여부를 확인합니다. 다른 트랜잭션에 업데이트가 있으면 제출 중인 트랜잭션이 롤백됩니다.

낙관적 잠금 비관적 잠금과 비교하여 낙관적 잠금은 일반적으로 데이터가 충돌을 일으키지 않는다고 가정하므로 업데이트를 위해 데이터를 제출할 때 데이터의 충돌이 공식적으로 감지됩니다. 충돌이 발견되면 오류 정보가 표시됩니다. 사용자에게 반환되며 사용자는 무엇을 할지 결정할 수 있습니다.

비관적 잠금과 비교하여 낙관적 잠금은 데이터베이스를 처리할 때 데이터베이스가 제공하는 잠금 메커니즘을 사용하지 않습니다. 낙관적 잠금을 구현하는 일반적인 방법은 데이터 버전을 기록하는 것입니다.

데이터 버전, 데이터에 추가된 버전 식별자입니다. 데이터를 읽을 때 버전 식별자의 값을 함께 읽습니다. 데이터가 업데이트될 때마다 버전 식별자도 동시에 업데이트됩니다. 업데이트를 제출할 때 데이터베이스 테이블의 해당 레코드의 현재 버전 정보를 처음 가져온 버전 식별 값과 비교합니다. 처음으로 업데이트하십시오. 그렇지 않으면 만료된 데이터로 간주됩니다.

낙관적 잠금의 장점과 단점

낙관적 동시성 제어는 트랜잭션 간의 데이터 경합 가능성이 상대적으로 적다고 생각하므로 최대한 직접적으로 수행하고, 제출 시점까지 잠금을 설정하지 않으므로 잠금이 발생하지 않습니다. 모든 잠금 및 교착 상태가 발생합니다. 그러나 이렇게 간단하게 수행하면 여전히 예상치 못한 결과가 발생할 수 있습니다. 예를 들어 두 트랜잭션이 데이터베이스의 특정 행을 읽은 다음 수정 후 데이터베이스에 다시 쓰면 문제가 발생합니다.

의도 공유 잠금/의도 배타 잠금

테이블 잠금과 행 잠금은 잠금 범위가 다르기 때문에 서로 충돌합니다. 따라서 테이블 잠금을 추가하려면 먼저 테이블의 모든 레코드를 탐색하여 배타적 잠금이 추가되었는지 확인해야 합니다. 이 순회 확인 방법은 분명히 비효율적인 방법입니다. MySQL은 테이블 잠금과 행 잠금 간의 충돌을 감지하기 위해 의도 잠금을 도입합니다.

의도 잠금도 테이블 수준 잠금이며 읽기 의도 잠금(IS 잠금)과 쓰기 의도 잠금(IX 잠금)으로 나눌 수도 있습니다. 트랜잭션이 레코드에 읽기 잠금 또는 쓰기 잠금을 추가하려면 먼저 테이블에 의도 잠금을 추가해야 합니다. 이런 식으로 테이블에 잠긴 레코드가 있는지 판단하는 것은 매우 간단합니다. 테이블에 의도적인 잠금이 있는지 확인하면 됩니다.

의도 잠금은 서로 충돌하지 않으며 AUTO_INC 테이블 잠금과도 충돌하지 않습니다. 테이블 수준 읽기 잠금이나 테이블 수준 쓰기 잠금만 차단합니다. 또한 의도 잠금은 행 잠금과만 충돌하지 않습니다. 블록 테이블 수준 읽기 잠금 또는 테이블 수준 쓰기 잠금은 행 잠금과 충돌합니다.

의도 잠금은 InnoDB에 의해 자동으로 추가되며 사용자 개입이 필요하지 않습니다.

삽입, 업데이트 및 삭제의 경우 InnoDB는 관련 데이터에 자동으로 배타적 잠금(X)을 추가합니다.

일반 Select 문의 경우 InnoDB는 공유 잠금을 추가하여 트랜잭션을 표시할 수 없습니다. 다음 명령문 또는 배타적 잠금.

의도 공유 잠금(Intention Shared Lock)

의도 공유 잠금(IS): 트랜잭션이 데이터 행에 공유 잠금을 추가할 준비를 하고 있음을 나타냅니다. 즉, 데이터 행에 공유 잠금을 추가하기 전에 IS가 테이블의 잠금을 먼저 획득해야 합니다

Intention Exclusive Lock(Exclusive Lock)

Intention Exclusive Lock(IX): 위와 유사하게 트랜잭션이 데이터 행에 배타적 잠금을 추가할 준비를 하고 있음을 의미합니다. 트랜잭션은 데이터 행에 배타적 잠금을 추가하기 전에 먼저 테이블의 IX 잠금을 획득해야 합니다.

레코드 잠금

레코드 잠금은 가장 간단한 행 잠금이며 이에 대해 말할 것도 없습니다. 위에서 설명한 InnoDB 잠금 원리의 잠금은 레코드 잠금으로, ID = 49 또는 이름 = 'Tom'인 레코드만 잠급니다.

SQL 문이 인덱스를 사용할 수 없는 경우 전체 테이블 스캔이 수행됩니다. 이때 MySQL은 전체 테이블의 모든 데이터 행에 레코드 잠금을 추가한 다음 MySQL 서버 계층에서 이를 필터링합니다. 그러나 MySQL Server 계층에서 필터링을 할 때 WHERE 조건을 만족하지 않는 것으로 확인되면 해당 레코드에 대한 잠금이 해제된다. 이렇게 하면 조건을 만족하는 레코드에 대해서만 잠금이 유지되지만, 각 레코드에 대한 잠금 작업은 생략할 수 없습니다.

그래서 업데이트 작업은 인덱스를 기반으로 수행되어야 합니다. 인덱스가 없으면 많은 잠금 리소스를 소비하고 데이터베이스의 오버헤드가 증가할 뿐만 아니라 데이터베이스의 동시성 성능도 크게 저하됩니다.

Gap Lock

동일 조건 대신 범위 조건을 사용하여 데이터를 검색하고 공유 또는 배타적 잠금을 요청하면 InnoDB는 다음과 같은 키 값에 대한 조건을 충족하는 기존 데이터 레코드의 인덱스 항목을 잠급니다. 조건 범위 내에 있지만 존재하지 않는 경우 InnoDB는 "간격"도 잠급니다. 이 잠금 메커니즘을 소위 간극 잠금이라고 합니다.

间隙锁是锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

间隙锁在 InnoDB 的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排他锁。

要禁止间隙锁，可以把隔离级别降为读已提交，或者开启参数 innodb_locks_unsafe_for_binlog

 show variables like 'innodb_locks_unsafe_for_binlog';复制代码

innodb_locks_unsafe_for_binlog：默认

值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini） 重新启动才行。

# 在 my.cnf 里面的[mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1复制代码

案例1：唯一索引的间隙锁

测试环境：

MySQL5.7，InnoDB，默认的隔离级别（RR）

示例表：

CREATE TABLE `my_gap` (  `id` int(1) NOT NULL AUTO_INCREMENT,  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `my_gap` VALUES ('1', '张三');INSERT INTO `my_gap` VALUES ('5', '李四');INSERT INTO `my_gap` VALUES ('7', '王五');INSERT INTO `my_gap` VALUES ('11', '赵六');复制代码

在进行测试之前，我们先看看 my_gap 表中存在的隐藏间隙：

1. (-infinity, 1]
2. (1, 5]
3. (5, 7]
4. (7, 11]
5. (11, +infinity]

只使用记录锁（行锁），不会产生间隙锁

/* 开启事务1 */BEGIN;/* 查询 id = 5 的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 5 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 name = '杰伦' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '杰伦'); # 正常执行/* 事务3插入一条 name = '学友' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '学友'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

上述案例，由于主键是唯一索引，而且只使用一个索引查询，并且只锁定了一条记录，所以只会对 id = 5 的数据加上记录锁（行锁），而不会产生间隙锁。

产生间隙锁

恢复初始化的4条记录，继续在 id 唯一索引列上做以下测试：

/* 开启事务1 */BEGIN;/* 查询 id 在 7 - 11 范围的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '思聪3' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (3, '思聪3'); # 正常执行/* 事务3插入一条 id = 4，name = '思聪4' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '思聪4'); # 正常执行/* 事务4插入一条 id = 6，name = '思聪6' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '思聪6'); # 阻塞/* 事务5插入一条 id = 8， name = '思聪8' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '思聪8'); # 阻塞/* 事务6插入一条 id = 9， name = '思聪9' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (9, '思聪9'); # 阻塞/* 事务7插入一条 id = 11， name = '思聪11' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (11, '思聪11'); # 阻塞/* 事务8插入一条 id = 12， name = '思聪12' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (12, '思聪12'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

从上面可以看到，(5,7]、（7,11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以可以得出结论：当我们给(5,7] 这个区间加锁的时候，会锁住(5,7]、(7,11] 这两个区间。

恢复初始化的4条记录，我们再来测试如果锁住不存在的数据时，会如何？

/* 开启事务1 */BEGIN;/* 查询 id = 3 这一条不存在的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '小张' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (2, '小张'); # 阻塞/* 事务3插入一条 id = 4，name = '小白' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '小白'); # 阻塞/* 事务4插入一条 id = 6，name = '小东' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '小东'); # 正常执行/* 事务5插入一条 id = 8， name = '大罗' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '大罗'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

从上面可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。

结论

1. 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁（行锁）和间隙锁，如果记录存在，则只会产生记录锁（行锁）；
2. 对于查找某一范围内的查询语句，会产生间隙锁。

案例2：普通索引的间隙锁

示例表：id 是主键，在 number 上，建立了一个普通索引。

# 注意：number 不是唯一值CREATE TABLE `my_gap1` (  `id` int(1) NOT NULL AUTO_INCREMENT,  `number` int(1) NOT NULL COMMENT '数字',
  PRIMARY KEY (`id`),  KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `my_gap1` VALUES (1, 1);INSERT INTO `my_gap1` VALUES (5, 3);INSERT INTO `my_gap1` VALUES (7, 8);INSERT INTO `my_gap1` VALUES (11, 12);复制代码

在进行测试之前，我们先来看看 my_gap1 表中 number 索引存在的隐藏间隙：

1. (-infinity, 1]
2. (1, 3]
3. (3, 8]
4. (8, 12]
5. (12, +infinity]

测试1

我们执行以下的事务（事务1最后提交），分别执行下面的语句：

/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 number = 0 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (0); # 正常执行/* 事务3插入一条 number = 1 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (1); # 被阻塞/* 事务4插入一条 number = 2 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (2); # 被阻塞/* 事务5插入一条 number = 4 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (4); # 被阻塞/* 事务6插入一条 number = 8 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (8); # 正常执行/* 事务7插入一条 number = 9 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (9); # 正常执行/* 事务8插入一条 number = 10 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (10); # 正常执行/* 提交事务1 */COMMIT;复制代码

我们会发现有些语句可以正常执行，有些语句被阻塞来。查看表中的数据：

这里可以看到，number(1,8) 的间隙中，插入语句都被阻塞来，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。

测试2

我们再进行以下测试，这里将数据还原成初始化那样

/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);/* 事务1插入一条 id = 2， number = 1 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (2, 1); # 阻塞/* 事务2插入一条 id = 3， number = 2 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (3, 2); # 阻塞/* 事务3插入一条 id = 6， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (6, 8); # 阻塞/* 事务4插入一条 id = 8， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (8, 8); # 正常执行/* 事务5插入一条 id = 9， number = 9 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (9, 9); # 正常执行/* 事务6插入一条 id = 10， number = 12 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (10, 12); # 正常执行/* 事务7修改 id = 11， number = 12 的数据 */UPDATE `my_gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞/* 提交事务1 */COMMIT;复制代码

查看表中的数据；

这里有一个奇怪的现象：

• 事务3 添加 id = 6，number = 8 的数据，阻塞了；
• 事务4 添加 id = 8，number = 8 的数据，正常执行了；
• 事务7 将 id = 11，number = 12 的数据修改为 id = 11， number = 5 的操作，给阻塞了。

这是为什么？我们来看看下面的图：

从图中库看出，当 number 相同时，会根据主键 id 来排序

1. 事务 3 添加的 id = 6，number = 8，这条数据是在 (3,8) 的区间里边，所以会阻塞；
2. 事务 4 添加的 id = 8，number = 8，这条数据实在 (8,12) 区间里边，所以不会阻塞；
3. 事务 7 的修改语句相当于 在 (3,8) 的区间里边插入一条数据，所以也被阻塞了。

结论

1. 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
2. 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通普通索引排序，再根据唯一索引排序。

临键锁（Next-key Locks）

临键锁，是记录锁（行锁）与间隙锁的组合，它的锁范围，即包含索引记录，又包含索引区间。它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下：

(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)复制代码

通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。还是之前的例子，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50)

此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录。

注意：临键锁的主要目的，也是为了避免幻读（Phantom Read）。如果把事务隔离级别降级为 RC，临键锁则也会失效。

插入意向锁（Insert Intention Locks）

插入意向锁是一种特殊的间隙锁（简称II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。

插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。

插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

插入意向锁的作用：

1. 为来唤起等待。由于该间隙已经有锁，插入时必须阻塞，插入意向锁的作用具有阻塞功能；
2. 插入意向锁是一种特殊的间隙锁，既然是一种间隙锁，为什么不直接使用间隙锁？间隙锁直接不相互排斥。不可以阻塞即唤起等待，会造成幻读。
3. 为什么不实用记录锁（行锁）或 临键锁？申请了记录锁或临键锁，临键锁之间可能相互排斥，即影响 insert 的并发性。

自增锁（Auto-inc Locks）

AUTO_INC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO_INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTO_INC 锁具有如下特点：

• AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
• 自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

自增操作

使用AUTO_INCREMENT 函数实现自增操作，自增幅度通过 auto_increment_offset和auto_increment_increment这2个参数进行控制：

• auto_increment_offset 表示起始数字
• auto_increment_increment 表示调动幅度（即每次增加n个数字，2就代表每次+2）

通过使用last_insert_id()函数可以获得最后一个插入的数字

select last_insert_id();复制代码

自增锁

首先insert大致上可以分成三类：

1. simple insert 如insert into t(name) values('test')
2. bulk insert 如load data | insert into ... select .... from ....
3. mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');

如果存在自增字段，MySQL 会维护一个自增锁，和自增锁相关的一个参数为（5.1.22 版本后加入） innodb_autoinc_lock_mode ，可以设定 3 值：

• 0 ：traditonal (每次都会产生表锁)
• 1 ：consecutive（会产生一个轻量锁，simple insert 会获得批量的锁，保证连续插入）
• 2 ：interleaved （不会锁表，来一个处理一个，并发最高）

  MyISam引擎均为 traditonal，每次均会进行表锁。但是InnoDB引擎会视参数不同产生不同的锁，默认为 1：consecutive。

 show variables like 'innodb_autoinc_lock_mode';复制代码

traditonal

innodb_autoinc_lock_mode 为 0 时，也就是 traditional 级别。该自增锁时表锁级别，且必须等待当前 SQL 执行完毕后或者回滚才会释放，在高并发的情况下可想而知自增锁竞争时比较大的。

• 它提供来一个向后兼容的能力
• 在这一模式下，所有的 insert 语句（“insert like”）都要在语句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才释放这把锁。注意，这里说的是语句级而不是事务级的，一个事务可能包含有一个或多个语句；
• 它能保证值分配的可预见性、可连续性、可重复性，这个也就是保证了 insert 语句在复制到 slave 的时候还能生成和 master 那边一样的值（它保证了基于语句复制的安全）；
• 由于在这种模式下 auto_inc 锁一直要保持到语句的结束，所以这个就影响了并发的插入。

consecutive

innodb_autoinc_lock_mode 为 1 时，也就是 consecutive 级别。这是如果是单一的 insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其它事务中已经有 session 获取了自增锁）。另外当SQL是一些批量 insert SQL 时，比如 insert into ... select ... ， load data , replace ... select ... 时，这时还是表级锁，可以理解为退化为必须等待当前 SQL 执行完才释放。可以认为，该值为 1 时相对比较轻量级的锁，也不会对复制产生影响，唯一的缺陷是产生自增值不一定是完全连续的。

• 这一模式下对 simple insert 做了优化，由于 simple insert 一次性插入的值的个数可以立马得到确定，所以 MyQL 可以一次生成几个连续的值，用于这个 insert 语句。总得来说这个对复制也是安全的（它保证了基于语句复制的安全）；
• 这一模式也是MySQL的默认模式，这个模式的好处是 auto_inc 锁不要一直保持到语句的结束，只要语句得到了相应的值就可以提前释放锁。

interleaved

innodb_autoinc_lock_mode 为 2 时，也就是 interleaved 级别。所有 insert 种类的 SQL 都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当 binlog_format 为 statement 时，这是复制没法保证安全，因为批量的 insert，比如 insert ... select ... 语句在这个情况下，也可以立马获取到一大批的自增 id 值，不必锁整个表， slave 在回放这个 SQL 时必然会产生错乱。

• 由于这个模式下已经没有了 auto_inc 锁，所以这个模式下的性能是最好的，但是也有一个问题，就是对于同一个语句来说它所得到的 auto_incremant 值可能不是连续的。

如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。

由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。

요약

InnoDB 잠금의 특징

1. 인덱스 조건 없이 쿼리할 때 InnoDB는 테이블 잠금을 사용합니다!
2. MySQL의 행 잠금은 레코드가 아닌 인덱스에 대한 잠금이기 때문에 서로 다른 행의 레코드에 접근하더라도 동일한 인덱스 키를 사용하면 잠금 충돌이 발생합니다.
3. 테이블에 여러 개의 인덱스가 있는 경우 서로 다른 트랜잭션은 서로 다른 인덱스를 사용하여 서로 다른 행을 잠글 수 있습니다. 또한 기본 키 인덱스, 고유 인덱스 또는 일반 인덱스를 사용하든 InnoDB는 행 잠금을 사용하여 데이터를 잠급니다.
4. 조건에 인덱스 필드가 사용되더라도 데이터 검색에 인덱스를 사용할지 여부는 MySQL이 일부 경우와 같이 전체 테이블 스캔이 더 효율적이라고 판단하는 경우 다양한 실행 계획의 비용을 판단하여 MySQL에 의해 결정됩니다. 작은 테이블에서는 인덱스를 사용하지 않습니다. 이 경우 InnoDB는 행 잠금 대신 테이블 잠금을 사용합니다. 따라서 잠금 충돌을 분석할 때 실제로 인덱스가 사용되는지 확인하기 위해 SQL 실행 계획(Explain 뷰)을 확인하는 것을 잊지 마세요.

잠금 모드

잠금 모드에는 읽기 의도 잠금, 쓰기 의도 잠금, 읽기 잠금, 쓰기 잠금 및 자동 증가 잠금(auto_inc)이 있습니다.

다양한 모드 잠금에 대한 호환성 매트릭스

호환행 잠금으로 세분화됩니다. 행 잠금은 Next-Key Lock

	IS	IX	S	X	AI
IS	호환	호환	호환		호환
IX	호환	호환 가능			호환 가능
S	호환		호환
		AI			호환
		요약하자면 다음과 같습니다.	의도 잠금은 서로 충돌하지 않습니다.	S 잠금 S/IS 잠금과만 호환됩니다.	테이블 잠금 과

,

Gap Lock gap lock

,

Record Lock을 포함하여 다양한 시나리오에 따라 더 세분화될 수 있습니다. 기록 잠금
• 및
삽입 의도 GAP 잠금
• .
• 다른 잠금은 다른 위치를 잠급니다. 예를 들어, 레코드 잠금은 해당 레코드만 잠그고, 갭 잠금은 레코드 사이의 공백을 잠그고, Next-Key 잠금은 레코드와 이전 레코드 사이의 공백을 잠급니다. 다양한 유형의 잠금 장치의 잠금 범위는 대략 아래 그림과 같습니다.

다양한 유형의 자물쇠에 대한 호환성 매트릭스RECORD

GAP

NEXT-KEY

II GAP

호환 가능Compatible

그 중 첫 번째 행은 기존 잠금을 ​​나타내고, 첫 번째 열은 추가할 잠금을 나타냅니다. 의도 잠금 삽입은 특별하므로 먼저 의도 잠금 삽입에 대해 다음과 같이 요약합니다.

• 의도 잠금 삽입은 다른 트랜잭션 및 다른 잠금에 영향을 미치지 않습니다. 즉, 트랜잭션이 삽입 의도 잠금을 획득했으며 다른 트랜잭션에 영향을 주지 않습니다.
• 삽입 의도 잠금은 간격 잠금 및 Next-key 잠금과 충돌합니다. 즉, 삽입 의도 잠금을 획득하려는 트랜잭션이 이미 갭 잠금이나 Next-key 잠금을 추가한 다른 트랜잭션이 있으면 차단됩니다.

다른 유형의 잠금에 대한 규칙은 비교적 간단합니다.

• Gap 잠금은 다른 잠금과 충돌하지 않습니다(삽입 의도 잠금 제외).

• 레코드 잠금은 레코드 잠금과 충돌하고, Next-key 잠금은 Next-와 충돌합니다. 키 잠금, 레코드 잠금 및 다음 키 잠금 충돌

	기록			호환 가능
GAP	호환 가능	호환	호환	호환
NEXT -KEY		호환 가능

위 내용은 오늘날 MySQL 잠금 유형 및 잠금 원리에 대한 심층적인 이해의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!