이 기사는 MySQL의 다양한 잠금 유형 및 모드에 대한 관련 지식을 제공합니다. 도움이 되기를 바랍니다.
일상적인 개발 작업에서는 거의 매일 데이터베이스를 처리해야 하는데, CRUD만 아는 SQL BOY로서 매일 DAO 레이어 코드를 자동으로 생성하는 mybatis-generator를 제외하고는 거의 그렇지 않습니다. 동시 요청을 처리하는 방법이 필요하지만 어느 날 갑자기 MYSQL 데이터베이스에 경고가 표시되고 교착 상태가 발생했습니다. 잠겼어?
감독자에게 당황한 표정이 포착되는 것을 피하기 위해서는 데이터베이스에 어떤 잠금 장치가 있는지 미리 알아야 합니다.
MySQL에서 잠금은 실제로 잠금 유형(lock_type)과 잠금 모드(lock_mode)의 두 가지 범주로 나뉩니다.
잠금 유형은 잠금의 세분성, 즉 잠금이 구체적으로 추가되는 위치를 설명하고 잠금 모드는 읽기 잠금이든 쓰기 잠금이든 어떤 종류의 잠금이 추가되는지 설명합니다. 잠금 모드는 잠금 유형과 함께 사용되는 경우가 많습니다.
잠금 모드에 따라 구분됩니다
읽기 잠금
읽기 잠금, 공유 잠금/S 잠금/공유 잠금이라고도 합니다.
읽기 잠금은 읽기 작업(예: 테이블 읽기 또는 특정 행 읽기)을 수행할 때 트랜잭션(예: 트랜잭션 A)에 의해 생성되는 잠금입니다. 수정할 수 없습니다(잠금을 보유하고 있는 사용자가 잠금을 해제하지 않는 한).
트랜잭션 A가 데이터에 읽기 잠금을 추가한 후에도 다른 트랜잭션은 여전히 읽기 잠금(공유)을 추가할 수 있지만 쓰기 잠금은 추가할 수 없습니다.
레코드에 읽기 잠금 추가
InnoDB는 테이블 잠금과 행 잠금을 지원합니다. 행(즉, 레코드)을 잠그면 레코드가 잠기지 않지만 레코드 잠금에 해당하는 인덱스에 잠금이 추가됩니다. . where 조건에 인덱스가 없으면 모든 레코드가 잠깁니다.
명시적 잠금 문은 다음과 같습니다.
참고: 여기에 언급된 읽기는 현재 읽기를 나타내며 스냅샷 읽기에는 잠금이 필요하지 않습니다. 일반적인 선택 읽기는 일반적으로 스냅샷 읽기입니다. 단, 공유 모드의 select...lock과 같은 명시적 잠금 문은 예외입니다. 이는 InnoDB 엔진의 직렬화 가능 수준에서 일반 선택 읽기도 스냅샷 읽기가 됩니다.
또한 행 잠금 잠금 프로세스 분석을 위해서는 트랜잭션 격리 수준, 인덱스 사용 여부(인덱스 유형), 레코드 존재 여부를 기반으로 분석을 결합해야 합니다. 및 잠금이 추가되는 위치를 결정하는 기타 요소입니다.
innodb 엔진에 읽기 잠금을 추가하는 여러 가지 상황
일반적인 쿼리는 격리 수준이 직렬화될 때 레코드에 S 잠금을 추가합니다. 그러나 이는 시나리오에 따라 다릅니다. 비트랜잭션 읽기(자동 커밋)에는 직렬화 가능 격리 수준에서 잠금이 필요하지 않습니다.
직렬화 가능 격리 수준: 쿼리 조건이 고유 인덱스이고 고유한 동일 값 쿼리인 경우: 이 절에서 레코드에 S 잠금을 추가합니다. 고유하지 않은 조건 쿼리(쿼리가 여러 레코드를 스캔하는 경우): 레코드 자체 + 레코드 사이의 간격(간격 범위를 구체적으로 분석해야 함), S를 추가합니다. lock;
select... 공유 모드에서 레코드는 S-잠금 상태가 되지만 잠금 동작은 격리 수준에 따라 다릅니다.
RC 격리 수준: S-잠금이 레코드에 추가됩니다. RR/직렬화 가능 격리 수준: 쿼리 조건이 고유 인덱스이고 고유 동일 값 쿼리인 경우: S 잠금이 레코드에 추가됩니다. 비고유 조건 쿼리(쿼리가 여러 레코드를 스캔하는 경우): 레코드 자체 + 레코드 간격( 간격 범위를 구체적으로 분석해야 함), S 잠금을 추가하세요.
일반적으로 삽입 작업은 잠기지 않지만, 레코드를 삽입하거나 업데이트할 때 중복 키가 감지되면(또는 삭제하도록 표시된 중복 키가 있는 경우) 일반적인 삽입/업데이트에는 S 잠금이 추가되고, 대체 또는 중복 삽입과 같은 SQL 문에는 X 잠금이 추가됩니다.
insert ... select 데이터를 삽입하면 선택 테이블에서 스캔된 데이터에 S 잠금이 추가됩니다.
외래 키 확인: 상위 테이블의 레코드를 삭제할 때 참조 제약 조건이 있는지 확인해야 합니다. 이때 하위 테이블의 해당 레코드를 검색하고 S 잠금을 추가합니다.
테이블에 읽기 잠금 추가
테이블 잠금은 MySQL 서버에 의해 구현되며, 스토리지 엔진이 무엇이든 테이블 잠금을 사용할 수 있습니다. 일반적으로 ALTER TABLE과 같은 DDL 문을 실행하면 테이블 전체가 잠깁니다. SQL 문을 실행할 때 테이블을 명시적으로 잠글 수도 있습니다.
테이블에 대한 명시적 잠금 문은 다음과 같습니다.
MYISAM 엔진을 사용할 때 MYISAM 엔진이 자동으로 SQL 문을 잠그고 전체 프로세스에 사용자가 필요하지 않기 때문에 일반적으로 수동으로 잠글 필요가 없습니다. 개입:
쿼리 문(select): 관련 테이블에 읽기 잠금을 자동으로 추가합니다.
Update 문(업데이트, 삭제, 삽입): 관련 테이블에 쓰기 잠금을 자동으로 추가합니다.
쓰기 잠금
쓰기 잠금, 배타적 잠금/X 잠금/배타적 잠금. 쓰기 잠금의 차단 특성은 읽기 잠금의 차단 특성보다 훨씬 더 엄격합니다. 트랜잭션이 데이터에 쓰기 잠금을 추가한 후에는 다른 트랜잭션이 데이터를 읽거나 변경할 수 없습니다.
읽기 잠금 및 쓰기 잠금 범위와 마찬가지로 쓰기 잠금도 레코드와 테이블 모두에 추가할 수 있습니다.
레코드에 쓰기 잠금 추가
레코드에 쓰기 잠금 추가, 엔진은 InnoDB를 사용해야 합니다.
일반적으로 일반 select 문은 잠기지 않습니다(격리 수준이 직렬화 가능한 경우 제외). 쿼리 중에 배타적 잠금을 추가하려면 다음 문을 사용해야 합니다.
쿼리 중에 쓰기 잠금 추가:
and add read 잠금은 동일하며 쓰기 잠금도 인덱스에 추가됩니다.
업데이트 시 쓰기 잠금 추가:
Add write lock on the table
테이블에 쓰기 잠금을 명시적으로 추가하는 명령문은 다음과 같습니다.
엔진이 myisam을 선택하면, insert/ 업데이트 /delete 문은 자동으로 테이블에 배타적 잠금을 추가합니다.
읽기-쓰기 잠금 호환성:
읽기 잠금은 공유되며 다른 읽기 잠금은 차단하지 않지만 다른 쓰기 잠금은 차단합니다.
쓰기 잠금은 배타적이므로 다른 읽기 잠금을 차단합니다. 그리고 쓰기 잠금
요약: 읽기와 읽기는 상호 배타적이지 않고, 읽기와 쓰기는 상호 배타적입니다.
의도 잠금
의도 잠금은 행 수준 잠금과 충돌하지 않음 수준 잠금은 잠금 유형을 나타냅니다(S 잠금 또는 InnoDB는 행 수준 잠금과 테이블 수준 잠금의 공존을 허용하는 다중 세분성 잠금을 지원합니다.
의도 잠금은 다음과 같이 구분됩니다.
의도 공유 잠금(IS 잠금): IS 잠금은 현재 트랜잭션이 테이블의 행에 공유 잠금을 설정하려고 함을 나타냅니다.
다음 명령문이 실행되면 IS 잠금이 획득됩니다. 첫째, 이 작업은 S Lock을 획득하기 위한 것이므로: Acquire S lock: select ... lock in share mode
의도 배타적 잠금(IX 잠금): IX 잠금은 현재 트랜잭션이 행에 배타적 잠금을 설정하려고 함을 나타냅니다. the table
이 작업은 X 잠금을 획득하기 때문에 IX 잠금을 실행하면 다음 명령문이 먼저 획득됩니다. X 잠금 획득: 업데이트를 위해 선택...
트랜잭션이 특정 테이블에 대해 S 잠금 및 X 잠금을 획득하기 전에 먼저 해당 IS 잠금과 IX 잠금을 각각 획득해야 합니다.
의도 잠금의 기능은 무엇인가요?
다른 트랜잭션이 테이블 수준에서 공유 잠금이나 배타적 잠금을 시도하면 첫 번째 트랜잭션이 제어하는 테이블 수준 의도 잠금에 의해 차단됩니다. 두 번째 트랜잭션은 테이블을 잠그기 전에 개별 페이지 또는 행 잠금을 확인할 필요가 없고 테이블의 의도된 잠금만 확인할 수 있습니다.
예: 테이블 test_user:
트랜잭션 A가 행에 대한 독점 잠금을 획득했지만 이를 커밋하지 않았습니다.
트랜잭션 B가 test_user 테이블에서 테이블 공유 잠금을 획득하려고 합니다. 잠금은 배타적입니다. 잠금은 상호 배타적이므로 트랜잭션 B가 test_user 테이블에 공유 잠금을 추가하려고 할 때 다음을 확인해야 합니다. 잠그다).
트랜잭션 B는 트랜잭션 A가 test_user 테이블의 의도적인 배타적 잠금을 보유하고 있는지 여부만 감지하면 되며, 그러면 트랜잭션 A가 배타적 잠금을 보유해야 한다는 것을 알 수 있습니다. 테이블 잠금의 일부 데이터 행을 잠그면 test_users 테이블에 대한 트랜잭션 B의 잠금 요청이 제외(차단)되므로 테이블의 각 데이터 행에 대해 배타적 잠금이 있는지 여부를 감지할 필요가 없습니다.
트랜잭션 C는 사용자 테이블의 특정 행에 대해 배타적 잠금을 얻으려고 합니다.
트랜잭션 C는 트랜잭션 A가 test_user 테이블에 의도 배타적 잠금을 보유하고 있음을 감지합니다.
의도 잠금은 상호 배타적이지 않습니다. , 따라서 트랜잭션 C는 test_user 테이블의 의도된 배타적 잠금을 획득했습니다. -
의도 잠금 간의 호환성 및 상호 배타성:
의도 잠금과 일반 배타적/공유 잠금 간의 상호 배타성:
자동 증분 잠금
테이블 구조를 설계할 때 일반적으로 기본 키는 자동 증가로 설정되어 있습니다(이유를 생각해 보세요).
InnoDB 스토리지 엔진에서는 각 자체 증가 필드에 대해 자체 증가 카운터가 설정됩니다. 다음 명령문을 실행하여 이 카운터의 현재 값을 얻을 수 있습니다.
삽입 작업을 수행할 때 작업은 이 자체 증가 카운터의 현재 값을 기반으로 +1 작업을 수행하고 이를 다음에 할당합니다. 자체 증가 열의 경우 이 작업을 자동 증가 잠금이라고 합니다. 이러한 종류의 잠금은 실제로 트랜잭션에서 삽입 작업이 발생하면 잠금이 즉시 해제됩니다. 트랜잭션이 커밋될 때까지 기다리는 대신 삽입 작업이 완료됩니다.
잠금 유형에 따라
전역 잠금
전역 잠금이라고 불리는 것은 실제로 전체 데이터베이스 인스턴스를 잠그는 기능입니다.
데이터베이스 인스턴스와 데이터베이스에는 차이점이 있습니다.
데이터베이스는 데이터를 저장하는 창고입니다. 특히 mysql에서 데이터베이스는 실제로 데이터 파일의 모음입니다. 데이터베이스 문 생성) 데이터베이스 생성...).
데이터베이스 인스턴스는 데이터베이스에 액세스하는 애플리케이션을 의미합니다. Mysql에서는 mysqld 프로세스입니다.
간단히 이해하자면 데이터베이스 인스턴스에는 사용자가 생성하는 다양한 데이터베이스가 포함되어 있습니다.
데이터베이스 인스턴스에 전역 잠금을 추가하면 전체 라이브러리가 읽기 전용 상태가 됩니다(매우 위험합니다).
일반적으로 글로벌 잠금의 일반적인 사용 시나리오는 전체 데이터베이스 백업, 즉 데이터베이스의 모든 테이블을 선택하는 것입니다. 하지만 전체 라이브러리를 읽기 전용 상태로 두면 몇 가지 심각한 문제가 발생하므로 주의하세요.
전역 잠금의 잠금 문은 다음과 같습니다.
전역 잠금을 잠금 해제하는 방법은 다음과 같습니다.
데이터베이스 백업이 필요한 경우 공식 논리 백업 도구인 mysqldump를 사용할 수 있습니다.
이미 덤프 도구가 있는데 FTWRL이 필요한 이유는 무엇입니까? 일관된 읽기는 좋지만 엔진이 이 격리 수준을 지원하는 경우에만 가능합니다. 예를 들어 MyISAM과 같은 엔진은 트랜잭션을 지원하지 않습니다. 이때 FTWRL 명령을 사용해야 합니다.
FTWRL 이전에 읽기 또는 쓰기가 있는 경우 FTWRL은 실행하기 전에 읽기 및 쓰기가 완료될 때까지 기다립니다.
FTWRL이 실행되면 더티 페이지 데이터를 디스크에 플러시해야 합니다. 데이터 일관성이 유지되어야 하기 때문에 모든 트랜잭션이 제출될 때 FTWRL이 실행됩니다.
전역 잠금 구현은 여전히 메타데이터 잠금에 의존합니다.
Metadata Lock
MDL 잠금이라고도 하는 MetaData 잠금은 메타데이터 정보를 보호하는 데 사용됩니다. 시스템 수준 잠금은 적극적으로 제어할 수 없습니다. MySQL 버전 5.5에서는 주로 동시 환경에서 DDL과 DML의 동시 작업에서 메타데이터 일관성을 유지하기 위해 MDL 잠금이 도입되었습니다. 예를 들어 다음과 같은 상황이 있습니다.
격리 수준: RR
메타데이터 잠금 보호가 없으면 트랜잭션 2가 DDL 작업을 직접 수행하여 트랜잭션 1에 오류가 발생할 수 있습니다. 이러한 상황이 발생하지 않도록 MYSQL5.5 버전에는 MDL 잠금이 추가되었습니다. 트랜잭션 1이 쿼리를 시작하므로 잠금 모드는 MDL 읽기 잠금입니다. 트랜잭션 2가 DDL을 실행하려면 읽기 잠금과 쓰기 잠금이 상호 배타적이므로 MDL 쓰기 잠금을 획득해야 합니다. 트랜잭션 1이 해제될 때까지 기다려야 합니다. 읽기 잠금이 해제된 경우에만 실행할 수 있습니다.
테이블의 레코드를 추가, 삭제, 수정, 확인할 때(DML 작업) MDL 읽기 잠금이 자동으로 추가됩니다.
테이블 구조를 수정할 때(DDL 작업) MDL 쓰기 잠금이 자동으로 추가됩니다. 추가되었습니다.
MDL 잠금의 세분성
MDL 잠금은 스토리지 엔진 플러그인이 아닌 Mysql 서버 수준에서 구현됩니다. 잠금 범위에 따라 MDL 잠금은 다음 범주로 나눌 수 있습니다.
MDL 잠금 모드
페이지 수준 잠금
행 수준 잠금과 테이블 수준 잠금 간의 잠금 세분성을 갖춘 MySQL의 잠금입니다. 테이블 수준 잠금은 빠르지만 충돌이 많습니다. 행 수준 잠금은 충돌이 거의 없지만 느립니다. 따라서 손상된 페이지 수준이 채택되어 한 번에 인접한 레코드 그룹을 잠급니다. 다양한 스토리지 엔진은 다양한 잠금 메커니즘을 지원합니다. 다양한 스토리지 엔진에 따라 MySQL의 잠금 특성은 대략 다음과 같이 요약됩니다.
페이지 수준 잠금은 BDB 엔진에 적용되는 고유한 잠금 수준입니다. 페이지 수준 잠금의 특징은 다음과 같습니다. 잠금 세분성은 행 수준 잠금과 테이블 수준 잠금 사이에 있으므로 잠금을 획득하는 데 필요한 리소스 오버헤드와 잠금이 제공할 수 있는 동시 처리 기능도 위 둘 사이에 있습니다. 또한 페이지 수준 잠금과 행 수준 잠금은 교착 상태를 유발합니다.
잠금 세분성 비교: 테이블 수준 잠금 > 페이지 수준 잠금 > 행 수준 잠금
테이블 수준 잠금
테이블 잠금은 세분화된 추가와 비교하여 위에 소개되었습니다. 행 잠금 잠금, 테이블 잠금은 전체 테이블을 잠그는 것입니다. 테이블 전체가 잠겨 있으므로 행 잠금만큼 복잡하지 않으므로 행 잠금보다 잠금이 빠르고 교착 상태도 발생하지 않습니다(트랜잭션이 원하는 테이블 잠금을 한 번에 획득하므로). 문제: 잠금 범위가 너무 크고 동시성이 상대적으로 높으면 잠금 충돌 가능성이 높아져 동시성 성능이 크게 저하됩니다.
테이블 잠금 잠금 방법
엔진이 MYISAM을 선택한 경우
MYISAM 엔진은 테이블 잠금만 지원하고 행 잠금은 지원하지 않습니다.
테이블 수준 잠금을 수동으로 추가하는 문은 다음과 같습니다.
MYISAM 엔진을 사용할 때 일반적으로 MYISAM 엔진이 자동으로 SQL 문을 잠그고 전체 프로세스를 잠그기 때문에 수동으로 잠글 필요가 없습니다. 사용자 개입이 필요하지 않습니다.
쿼리 문(select): 관련 테이블에 자동으로 읽기 잠금을 추가합니다.
Update 문(업데이트, 삭제, 삽입): 관련 테이블에 자동으로 쓰기 잠금을 추가합니다.
InnoDB를 엔진으로 선택한 경우 InnoDB 엔진은 행 수준 잠금과 테이블 수준 잠금을 모두 지원하며 기본값은 행 수준 잠금입니다. InnoDB 엔진에서 테이블을 수동으로 잠그고 잠금 테이블 {tableName} 읽기/쓰기 문을 사용하여 읽기/쓰기 잠금을 추가하세요.
또한 innodb는 테이블 수준 잠금인 의도 잠금도 지원합니다(이미 위에 소개됨).
일반적으로 InnoDB 엔진의 테이블 수준 잠금에는 5가지 잠금 모드가 포함됩니다.
LOCK_IS: 읽기 의도 잠금을 나타냅니다.
비즈니스 코드를 작성하는 과정에서 가장 흔히 접하게 되는 것은 행 수준 잠금(테이블 수준 잠금)입니다. 성능 문제로 인해 일반적으로 사용하지 않는 것이 좋습니다. 테이블 수준 잠금과 비교할 때 행 수준 잠금은 다음과 같은 확실한 성능 이점을 갖습니다.
충돌이 적습니다. 여러 스레드에서 서로 다른 레코드에 액세스할 때 잠금 충돌이 거의 없습니다.
기억해야 할 점: 행 잠금은 레코드 잠금이라고도 하며 레코드 잠금은 인덱스에 추가됩니다.
조건이 기본 키 인덱스를 지정하는 경우: 기본 키 인덱스에 잠금이 추가됩니다.
레코드 잠금은 가장 간단한 행 잠금입니다. 예를 들어, RR 격리 수준에서 업데이트 문에 대해 id = 1인 t_user에서 select *를 실행하면 레코드 id = 1(여기서 id는 기본 키)이 실제로 잠겨 있습니다(잠금이 클러스터형 인덱스에 추가됨). . 레코드 잠금은 항상 인덱스에 추가됩니다. 테이블에 인덱스가 없더라도 데이터베이스는 암시적으로 인덱스를 생성합니다. WHERE 조건에 지정된 컬럼이 보조 인덱스인 경우, 보조 인덱스뿐만 아니라 보조 인덱스에 해당하는 클러스터형 인덱스에도 레코드 잠금이 추가됩니다.
SQL 문이 인덱스를 사용할 수 없는 경우 기본 인덱스를 사용하여 전체 테이블 스캔을 구현합니다. 이때 MySQL은 전체 테이블의 모든 데이터 행에 레코드 잠금을 추가합니다.
WHERE 조건을 인덱스로 신속하게 필터링할 수 없는 경우 스토리지 엔진 계층은 모든 레코드를 잠그고 반환한 다음 MySQL 서버 계층에서 이를 필터링합니다. 인덱스가 없으면 잠금 리소스를 많이 소모하고 데이터베이스의 오버헤드를 증가시킬 뿐만 아니라 데이터베이스의 동시성 성능을 크게 저하시키므로 업데이트 작업 중에 인덱스를 사용해야 한다는 점을 기억해야 합니다. 업데이트 작업은 X 잠금을 추가합니다).
행 수준 잠금의 여러 유형:
Gap 잠금: LOCK_GAP(간격만 잠금)
Gap 잠금은 간격 잠금의 한 유형입니다. 잠금은 존재하지 않는 여유 공간이나 두 인덱스 레코드 사이, 첫 번째 인덱스 레코드 또는 마지막 인덱스 뒤의 공간에 추가됩니다. 이는 범위만 잠겨 있음을 나타내는 데 사용됩니다(보통 수행 시 격리됨). 범위 쿼리) RR 또는 직렬화 가능 간격 수준).
GAP 잠금은 일반적으로 RR 격리 수준에서 사용됩니다. GAP 잠금을 사용하는 주요 목적은 GAP 잠금으로 잠긴 간격에서는 팬텀 읽기를 방지하는 것입니다.
갭 잠금 생성 조건: innodb의 격리 수준은 반복 읽기 또는 직렬화 가능입니다.
gap 잠금 범위 설명:
격리 수준: RR
Student 테이블을 샘플 데이터로, id를 기본 키로, stu_code를 학생 번호로 사용하고 일반 인덱스를 추가합니다.
갭 잠금 영역 정의:
검색 조건에 따라 왼쪽 간격으로 가장 가까운 값 A를 찾고, 오른쪽 간격으로 가장 가까운 값 B를 오른쪽에서 찾습니다. B)
왼쪽으로 가장 가까운 값 A를 찾을 수 없으면 오른쪽 간격으로 가장 가까운 값 B를 찾습니다. 간격 잠금은
입니다. 왼쪽 간격으로 가장 가까운 값 A를 찾으면, 오른쪽 간격인 무한대에서는 가장 가까운 값 B를 찾을 수 없습니다. 간격 잠금은 (A, 무한대)
간격(A, B) 예시:
트랜잭션 1:
select * from student where stu_code = 4 for update
로그인 후 복사
트랜잭션 2:
insert into student vaues(2, 2, 'A')；
insert into student values(4, 5, 'B');
로그인 후 복사
트랜잭션 1의 SQL 문 분석에 따르면 갭 잠금 범위는 stu_code = 4개의 레코드가 존재하므로 왼쪽 간격이 가장 최근입니다. 인덱스 값은 stu_code = 3이고 오른쪽 간격은 가장 최근 인덱스 값이 stu_code = 7이므로 간격 범위는 (3, 7)이므로 트랜잭션 2의 두 insert 문 중 하나는 범위 밖에 있고 다른 하나는 범위 내에 삽입될 수 있지만 범위 내의 하나는 차단되므로 (2, 2, 'A')는 성공적으로 삽입될 수 있습니다. 막힌.
간격(무한히 작음, B) 예:
트랜잭션 1:
select * from student where stu_code = 1 for update
로그인 후 복사
트랜잭션 2:
insert into student vaues(2, 0, 'c')；
insert into student vaues(2, 2, 'r')；
insert into student vaues(5, 2, 'o')；
로그인 후 복사
트랜잭션 1의 SQL 문 분석에 따르면 갭 잠금 범위는 stu_code = 1이 존재하며 거기에 있습니다. 왼쪽은 최근 기록이 아닙니다. 따라서 왼쪽은 극미량이고 오른쪽의 가장 가까운 인덱스 값은 stu_code = 3이므로 간격 잠금 범위는 (무한, 3)입니다. 따라서 트랜잭션 2의 첫 번째 및 두 번째 insert sql 문의 실행이 차단되며 gap lock 범위에 속하게 됩니다. 세 번째 insert sql 문은 성공적으로 실행될 수 있으며 gap lock 범위 내에 있지 않습니다.
간격(A, 무한대) 예:
트랜잭션 1:
select * from student where stu_code = 7 for update
로그인 후 복사
트랜잭션 2:
insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；
로그인 후 복사
로그인 후 복사
트랜잭션 1의 SQL 문 분석에 따르면 갭 잠금 범위는 stu_code = 7이 존재하며 가장 가까운 인덱스입니다. 왼쪽 값은 stu_code = 4이고 오른쪽에는 인덱스 값이 없으므로 간격 잠금 범위는 (4, 무한대)입니다. 첫 번째 삽입 문은 성공적으로 실행될 수 있으며 간격 범위 내에 있지 않습니다. 간격 잠금 범위 내에 있는 두 번째 insert 문의 실행이 차단됩니다.
쿼리문이 데이터베이스에 기록되지 않은 경우 잠그는 방법은 무엇인가요?
위 쿼리가 기록됩니다. 쿼리문이 데이터베이스에 기록되지 않은 경우 어떻게 잠글 수 있나요? 계속하자:
트랜잭션 1:
update student set stu_name = '000' where stu_code = 10
로그인 후 복사
트랜잭션 2:
insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；
로그인 후 복사
로그인 후 복사
위 실행문에 따르면 레코드를 찾을 수 없습니다. 왼쪽으로 가장 가까운 레코드(10, 7, 'Xiao Ming')를 가져옵니다. 간격, 즉 간격 잠금 범위는 (7, 무한대)입니다. 첫 번째 삽입 문은 범위 내에 있지 않으며 성공적으로 실행할 수 있습니다. 두 번째 삽입 실행 문은 범위 내에서 차단되어 실행에 실패합니다. 트랜잭션 1의 where 조건이 10보다 큰 경우 가장 가까운 레코드 값도 왼쪽 간격으로 왼쪽에서 찾으므로 갭 잠금 범위도 (7, 무한대)
요약: 갭 조건 잠금 생성
RR/직렬화 가능 격리 수준 다음: 선택 시... 어디서... 업데이트:
고유 인덱스 쿼리만 사용하고 하나의 레코드만 잠그면 InnoDB는 행 잠금을 사용합니다.
고유 인덱스 쿼리만 사용했는데 검색 조건이 범위 검색이거나, 검색 결과가 고유 검색인데 존재하지 않는 경우(존재하지 않는 데이터를 잠그려고 하는 경우) Next-Key Lock이 발생합니다.
일반 인덱스 검색을 사용하는 경우 어떤 쿼리이든 잠겨 있으면 간격 잠금이 생성됩니다.
고유 인덱스와 일반 인덱스를 동시에 사용하는 경우 데이터 행이 먼저 일반 인덱스에 따라 정렬된 다음 고유 인덱스에 따라 정렬되므로 Gap 잠금도 발생합니다.
다음 키 잠금: LOCK_ORDINARY, 다음 키 잠금이라고도 함
다음 키 잠금은 레코드 잠금 + 갭 잠금의 조합입니다. 갭 잠금과 마찬가지로 RC 격리 수준 아래에는 다음 키 잠금이 없으며(구성을 수정하여 강제로 켜지지 않는 한) RR/직렬화 가능 격리 수준에만 있습니다.
MySQL InnoDB는 반복 읽기 격리 수준(RR)에서 작동하며, 가상 읽기 발생을 효과적으로 방지할 수 있는 Next-Key Lock을 사용하여 데이터 행을 잠급니다. Next-Key Lock은 행 잠금과 간격 잠금의 조합입니다. InnoDB는 인덱스 레코드를 스캔할 때 먼저 인덱스 레코드에 행 잠금(Record Lock)을 추가한 다음 간격에 간격 잠금(Gap Lock)을 추가합니다. 색인 기록의 양쪽에. 갭 잠금을 추가한 후에는 다른 트랜잭션이 이 갭의 레코드를 수정하거나 삽입할 수 없습니다.
쿼리된 인덱스에 고유 속성(고유 인덱스, 기본 키 인덱스)이 포함된 경우 Innodb 스토리지 엔진은 다음 키 잠금을 최적화하고 이를 레코드 잠금으로 줄입니다. 즉, 범위가 아닌 인덱스 자체만 잠깁니다. .
삽입 의도 잠금: LOCK_INSERT_INTENSION
레코드를 삽입할 때 사용하는 삽입 의도 잠금입니다. 이 잠금은 삽입 의도를 나타냅니다. 이 잠금은 삽입 문이 실행될 때만 발생합니다.
각각 id = 1 및 id = 5 값을 갖는 인덱스 레코드가 있다고 가정하고(1과 5 사이의 레코드는 없음) 별도의 트랜잭션은 삽입된 레코드의 배타적 잠금을 획득하기 전에 각각 id = 2 및 id = 3을 삽입하려고 시도합니다. row 에서 각 트랜잭션은 삽입 의도 잠금을 사용하여 1과 5 사이의 공간을 잠그지만 서로를 차단하지는 않습니다. 삽입된 의도 잠금 간에 충돌이 없기 때문입니다.
삽입 의도 잠금은 갭 잠금 또는 다음 키 잠금과 충돌합니다. 갭 잠금의 기능은 다른 트랜잭션이 데이터를 삽입하고 팬텀 읽기를 유발하지 않도록 간격을 잠그는 것입니다.
위 시나리오에서 트랜잭션 A가 (1, 5) 간격에서 미리 id로 gap 잠금을 획득했다고 가정하면, 트랜잭션 B가 id = 2를 삽입하려고 하면 먼저 삽입 의도 잠금을 획득하려고 시도합니다. , 그러나 삽입 의도 잠금으로 인해 Gap 잠금과 충돌하여 삽입이 실패하게 되어 팬텀 읽기 발생을 방지합니다.
결론
MYSQL의 잠금 메커니즘은 매우 복잡합니다. 실제 개발 작업에서는 격리 수준 설정에 매우 주의해야 합니다. 예를 들어 RR 수준은 RC 수준보다 간격 잠금이 하나 더 많습니다. 심각한 성능 문제가 발생할 수 있습니다. 이 글에서는 잠금 모드와 잠금 범위의 관점에서 MYSQL 잠금의 분류를 간략하게 소개합니다. 데이터베이스 개발 과정에서 SQL 문이 합리적인지 여부를 주의 깊게 분석하고 연구할 수 있기를 바랍니다. 교착 상태 및 기타 문제가 발생합니다)!
추천 학습: mysql 비디오 튜토리얼
위 내용은 MYSQL의 다양한 모드와 잠금 유형에 대해 이야기해 보겠습니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
