MySQL 테이블 잠금, 행 잠금, 배타적 잠금 및 공유 잠금을 사용하는 방법

1. 트랜잭션 격리 메커니즘 선택

• 전혀 신경 쓰지 않고 uncommitted read 트랜잭션 격리 메커니즘을 사용하고 이러한 스레드가 데이터베이스를 동시에 작동하도록 허용하면 더티 읽기(Uncommitted 읽기) 데이터), 비반복 읽기(두 개의 쿼리 값이 다름), 팬텀 읽기(두 개의 쿼리 데이터 볼륨이 다름) 등의 문제, 데이터 보안이 가장 낮고 장점은 동시성 효율성이 매우 높다는 것입니다. 일반적으로 사용되지 않습니다

• 우리가 직렬화(잠금으로 구현)하고 모든 트랜잭션을 잠금을 통해 주문하면 데이터 보안이 향상되지만 동시성 효율성이 너무 낮아 일반적으로 사용되지 않습니다

• 그래서 우리는 일반적으로 데이터의 보안, 일관성, 동시성 효율성의 균형을 맞추는 Committed Read와 Repeatable Read의 두 가지 격리 수준을 사용하며, 이는 MVCC 다중 버전 동시성 제어(MVCC)로 구현되는 Committed Read 및 Repeatable Read의 원칙입니다. 반복 읽기, 잠금은 직렬화 원칙)

2. 테이블 수준 잠금 & 행 수준 잠금

테이블 수준 잠금: 테이블 전체를 잠급니다. 오버헤드가 적고(테이블에서 특정 행의 레코드를 찾아 잠글 필요가 없기 때문이다. 이 테이블을 수정하고 싶다면 이 테이블의 잠금을 직접 신청하면 된다) 잠금이 빠르며, 교착 상태가 발생하지 않으며 잠금 세분성이 크고 잠금 충돌이 발생합니다. 확률은 높고 동시성은 낮습니다.

행 수준 잠금: 레코드 행을 잠급니다. 비용이 많이 들고(테이블에서 해당 레코드를 찾아야 하며 테이블과 인덱스를 검색하는 프로세스가 있음) 잠금이 느리고 교착 상태가 발생하며 잠금 세분성이 가장 낮고 잠금 충돌 가능성이 높습니다. 가장 낮고 동시성이 높습니다

InnoDB 스토리지 엔진은 트랜잭션 처리를 지원하고 테이블은 행 수준 잠금을 지원하며 동시성 기능이 더 좋습니다

1. InnoDB 행 잠금은 인덱스 항목을 잠그면 달성됩니다. 즉, InnoDB는 인덱스 조건을 통해 데이터를 검색할 때만 행 수준 잠금을 사용합니다. 그렇지 않으면 InnoDB는 테이블 잠금을 사용합니다

2. InnoDB의 행 잠금 구현은 다음과 같습니다. 행 레코드에 대한 잠금이 아닌 인덱스 필드에 잠금이 추가됩니다. 따라서 InnoDB 엔진에서 테이블의 다른 행에 액세스하더라도 동일한 인덱스 필드를 필터 조건으로 사용하면 잠금 충돌이 계속 발생합니다. 동시에는 안되고 순차적으로만 가능합니다

3. SQL에서 인덱스를 사용하더라도, 그러나 MySQL의 옵티마이저 이후에는

   인덱스를 사용하는 것보다 전체 테이블 스캔이 더 효율적이라고 판단되면 포기하게 됩니다. 이때 인덱스를 사용하므로
   행 잠금을 사용하지 않고 테이블 잠금을 사용합니다. 예를 들어 일부 작은 테이블의 경우 MySQL은 인덱스를 사용하지 않습니다

3. 배타적 잠금(Exclusive) 및 공유 잠금(공유)

• X 잠금이라고도 하는 독점 잠금, 쓰기 잠금

• S 잠금이라고도 하는 공유 잠금, 읽기 잠금

읽기 및 읽기(SS)는 호환되지만 읽기 및 읽기 잠금 쓰기(SX, SX)와 쓰기 및 쓰기(XX)는 상호 배타적입니다

쌍 X 및 S 잠금을 추가하는 트랜잭션 간에는 다음과 같은 관계가 있습니다.

• A 트랜잭션은 데이터 개체 A에 S 잠금을 추가합니다. 읽기를 수행할 수 있습니다. A에 대한 작업은 수행할 수 있지만 잠금 기간 동안 다른 트랜잭션은 A에 S 잠금을 추가할 수 있습니다. S 잠금은 추가할 수 없지만 X 잠금은 추가할 수 없습니다

• A 트랜잭션은 공유 잠금을 강제하기 위해 공유 모드에서 ：select … 획득, 독점 잠금 획득을 위한 업데이트를 위해 … 선택

먼저 트랜잭션을 열고 ID=7인 데이터에 독점 잠금을 추가합니다.

그런 다음 다른 클라이언트를 사용하여 트랜잭션을 엽니다.

우리는 다른 트랜잭션의 서비스 스레드에 독점 잠금을 추가했습니다. id=7인 데이터에 차단되었습니다

id=7인 데이터에 공유 잠금을 추가하려고 했지만 여전히 차단되었습니다

요약: 서로 다른 트랜잭션 간 데이터 잠금의 경우 SS 잠금만 가능합니다. XX, SX, 그리고

테이블의 인덱싱되지 않은 필드를 필터링 조건으로 사용

이제 트랜잭션 2도 이 레코드의 독점 잠금을 얻으려고 하지만 상상할 수 있듯이 실패하고 이제 트랜잭션 2가 획득합니다. chenwei의 기록 독점 잠금, 성공할 수 있는지 확인해보세요

InnoDB는 행 잠금을 지원합니다. 방금 기본 키 ID가 필터 조건으로 사용되었을 때 트랜잭션 1과 트랜잭션 2는 서로 다른 행에 대한 잠금을 성공적으로 획득할 수 있습니다. 그러나 이제 우리는 chenwei라는 독점 잠금 장치를 얻을 수 없다는 것을 알게 되었습니다. 설명하자면:

InnoDB의 행 잠금은 테이블의 행 레코드를 잠그는 것이 아니라 인덱스 항목을 잠그는 방식으로 구현됩니다.

그리고 인덱스를 사용하지 않고 이름을 필터 조건으로 사용하므로 당연히 행 잠금은 사용되지 않지만 테이블 자물쇠가 사용됩니다. 즉, InnoDB는 인덱스를 통해 데이터를 검색할 때 행 수준 잠금만 사용합니다. 그렇지 않으면 InnoDB는 테이블 잠금을 사용합니다!!!

이름 필드에 인덱스를 추가합니다

다음에 인덱스를 추가하는 것을 발견했습니다. 이름 필드 인덱스를 추가한 후 두 트랜잭션은 서로 다른 행에 대해 배타적 잠금(업데이트용)을 얻을 수 있습니다. 이는 이제 이름이 인덱스에 있으므로 InnoDB의 행 잠금이 인덱스 항목에 추가되었음을 다시 한 번 증명합니다

, zhangsan을 통해 보조 인덱스 트리에 위치한 행 레코드의 ID가 7이라는 것을 발견한 후 기본 키 인덱스 트리로 이동하여 해당 행 레코드의 배타적 잠금을 얻었습니다 (내 개인적인 추측은 해당 보조 인덱스 트리와 기본 키 인덱스 트리의 레코드가 추가되었습니다. Lock)

4. 직렬화 격리 수준 테스트

(모든 트랜잭션은 배타적 잠금 또는 공유 잠금을 사용하므로 사용자가 수동으로 잠금을 추가할 필요가 없습니다)

설정 직렬화 격리 수준

트랜잭션 2개 공유 잠금을 동시에 획득 가능(SS 공존)

이제 트랜잭션 2에 데이터를 삽입하도록 합니다

이때 독점 잠금을 설정해야 합니다. 삽입으로 인해 추가되었지만 트랜잭션 1이 이미 전체 테이블에 공유 잠금을 추가했기 때문에 트랜잭션 2는 더 이상 테이블을 성공적으로 잠글 수 없습니다(SX는 공존하지 않습니다)

rollback

name에 인덱스를 추가했기 때문입니다. , 위의 선택은 zhangsan

이라는 데이터에 행 공유 잠금을 추가하는 것과 같습니다.

트랜잭션 2 업데이트

트랜잭션 2는 트랜잭션 1의 공유 잠금으로 전체 테이블이 잠겨 있기 때문에 업데이트할 수 없습니다

트랜잭션 2는 다음과 같습니다. 보조 색인 트리에서 zhangsan에 대해 해당 기본 키 값을 찾은 다음 진행합니다. 기본 키 색인 트리에서 해당 레코드를 찾았지만 이 레코드 행이 공유 잠금에 의해 잠겨 있음을 발견했습니다. 트랜잭션 2는 공유 잠금을 얻을 수 있습니다. , 그러나 배타적 잠금을 얻을 수 없습니다

기본 키 인덱스 ID를 사용하여 업데이트할 수 있는지 확인해 보겠습니다. 또한 보조 인덱스 트리를 통해 해당 기본 키를 찾은 후 기본 키 인덱스 트리에서 해당 레코드를 찾습니다.

그리고 기본 키 인덱스 트리의 레코드는 잠겨 있습니다

(개인적인 추측은 보조 인덱스에 해당하는 데이터가 트리와 기본 키 인덱스 트리가 잠겨 있습니다)

id=8로 데이터를 업데이트했는데 성공했습니다. ID가 7인 행 데이터에는 행 잠금만 추가되므로 ID가 8인 데이터를 성공적으로 조작할 수 있습니다

인덱스가 있으면 행 잠금을 사용하고, 인덱스가 없으면 테이블 잠금을 사용합니다.

테이블 수준 잠금 또는 행 수준 잠금은 잠금의 세분성을 나타냅니다. 공유 잠금과 배타적 잠금은 잠금의 특성을 나타냅니다. 테이블 잠금인지 행 잠금인지에 따라 공유 잠금과 배타적 잠금이 구분됩니다. 독점 자물쇠

위 내용은 MySQL 테이블 잠금, 행 잠금, 배타적 잠금 및 공유 잠금을 사용하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!