MySQL의 테이블 수준 잠금, 행 수준 잠금, 배타적 잠금 및 공유 잠금이란 무엇입니까?

머리말

비즈니스의 복잡성으로 인해 트랜잭션을 구성하는 여러 관련 SQL 문이 필요합니다. 그럼 먼저 거래가 무엇인지부터 설명하겠습니다. 트랜잭션은 함께 실행되는 SQL 문 그룹을 의미하며 모두 성공적으로 실행되어야 하며 부분적으로 성공하거나 부분적으로 실패하는 것은 허용되지 않습니다. 거래에는 ACID 특성이 있습니다:

• 원자성: 거래의 일관성을 보장하기 위해 모두 성공하거나 모두 실패합니다.

• 일관성: 예를 들어, 은행 송금은 한 사람의 돈을 공제합니다. 다른 사람에게 돈을 추가해야 하면 추가하지 않고 공제할 수 없습니다. 그렇지 않으면 비즈니스에 문제가 발생하고 데이터의 일관성이 파괴됩니다.

• 지속성: 데이터를 커밋한 후에는 데이터가 삭제됩니다. 캐시에 처음 기록됩니다. 그중 캐시에 있는 데이터를 디스크에 기록하는 데는 여전히 시간이 걸립니다. 정전, 가동 중지 시간 또는 재시작이 발생하는 경우 데이터베이스의 내구성을 보장하기 위해 다시 실행 로그가 있습니다.

격리:
• 이 섹션에서는 트랜잭션이 동시에 실행될 수 있어야 하기 때문에 트랜잭션을 격리해야 하는 이유를 설명할 수 있습니다. 하나의 비즈니스에는 많은 트랜잭션이 포함되며 종종 백그라운드에서 많은 비즈니스를 실행할 수 있어야 합니다. 모든 트랜잭션이 직렬인 경우 실행이 정상이면 다중 스레드 프로그램을 작성하면 단 하나의 스레드만 작업을 수행하므로 매우 비효율적입니다. 따라서 트랜잭션은 동시에 실행되어야 하지만 동시 실행에는

  트랜잭션의 보안 및 일관성 및 동시성 효율성 문제와 같은 몇 가지 문제가 수반됩니다. 트랜잭션이 동시에 실행될 때 전혀 격리되지 않음, 더티 읽기가 발생할 수 있음(트랜잭션 B는 트랜잭션 A의 커밋되지 않은 데이터를 읽은 다음 트랜잭션 A의 커밋되지 않은 데이터를 사용하여 계산을 수행하고 다른 결과를 많이 얻은 다음 트랜잭션 A 해당 데이터를 롤백하면 트랜잭션 B에서 계산한 데이터는 모두 문제가 있는 데이터이므로 더티 읽기(dirty reading)는 확실히 문제를 일으킬 것임), 비반복 읽기(동일 조건에서 데이터 조각을 제거한 후 다시 쿼리에 갔을 때) 물론, 반복 불가능한 읽기가 반드시 문제를 일으키는 것은 아닙니다. 이는 비즈니스 데이터의 보안 및 일관성이 엄격한지 여부와 관련이 있습니다. phantom Read (트랜잭션에서 동일한 조건 전후의 두 쿼리 결과의 데이터 양이 다릅니다).

그런 다음 트랜잭션 동시 실행에서 발생하는 문제를 해결하기 위해 트랜잭션 격리 수준을 제공합니다.

직렬화, 직렬화는 잠금을 사용하여 완전히 구현되며 모든 트랜잭션은 잠금을 통해 정렬됩니다. 데이터 보안은 높지만 동시성 효율성은 낮습니다. 일반적으로 우리는 이렇게 하지 않습니다.

• 우리가 작성한 다중 스레드 프로그램의 경우 동시성은 높지만 데이터 보안은 매우 낮습니다. . 이는 문제가 있으므로 커밋되지 않은 읽기를 사용해서는 안 됩니다.
직렬화 및 커밋되지 않은 읽기는 실제 프로젝트에서 사용되지 않습니다. 일반적으로 데이터베이스 엔진은 기본적으로 커밋된 읽기 및 반복 가능한 읽기에서 작동합니다. 이 두 가지 격리 수준은 데이터 보안 및 일관성을 데이터 동시성 효율성과 결합하며, 이 두 가지는 MVCC 다중 읽기에 의해 실현됩니다. 버전 동시성 제어 메커니즘
• .
  읽기 제출됨, Oracle의 기본 작업 수준입니다. 커밋되지 않은 데이터 읽기는 허용되지 않습니다. 이 수준에서는 반복 불가능한 읽기 및 가상 읽기가 여전히 허용됩니다.

• 반복 읽기, MySQL 기본 작업 수준. 트랜잭션을 다시 읽을 때 동일한 데이터를 얻을 수 있다는 것이 보장되어 가상 읽기가 부분적으로 해결되지만 충돌을 피하기 위해 더 많은 성능이 소비됩니다. 즉 효율성이 낮습니다.

• "반복 읽기" 수준에서는 실제로 일부 가상 읽기 문제를 해결할 수 있지만 업데이트 업데이트로 인해 발생하는 가상 읽기 문제를 방지할 수는 없습니다. 직렬화 격리 수준.

트랜잭션 격리 수준 구현 원칙:

Lock + MVCC

. 직렬화의 기본 구현 원칙은 잠금입니다. 잠금에는 공유 잠금, 배타적 잠금, 의도 공유 잠금, 의도 배타적 잠금, 간격 잠금 및 교착 상태가 포함됩니다. InnoDB의 커밋된 읽기 및 반복 가능 읽기의 기본 구현 원칙은 MVCC(다중 버전 동시성 제어)입니다. MVCC는 스냅샷 읽기(동일한 데이터에 여러 버전이 있음), 현재 읽기, 실행 취소 로그 및 다시 실행 로그를 포함한 동시 읽기 방법을 제공합니다. MVCC는 Commited Read와 Repeatable Read의 원칙이고, Lock은 Serialization의 원칙

트랜잭션 로그는 ACID 기능을 구현하는 데 사용되며 공유 잠금, 배타적 잠금 및 MVCC는 일관성(I) 기능을 구현하는 데 사용됩니다. 트랜잭션 로그는 undo 로그(rollback 로그)와 redo 로그(redo 로그)로 구분됩니다.

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

• 테이블 수준 잠금: 테이블 전체를 잠급니다. 오버헤드가 적고(테이블에서 특정 행의 레코드를 찾아 잠글 필요가 없기 때문이다. 이 테이블을 수정하고 싶다면 이 테이블의 잠금을 직접 신청하면 된다) 잠금이 빠르며,

  잠금 세분성이 크고 잠금 충돌 가능성이 높으며 동시성은 낮습니다

• 행 수준 잠금:

  행 레코드를 잠급니다. 비용이 많이 들고(테이블에서 해당 레코드를 찾아야 하며 테이블과 인덱스를 검색하는 프로세스가 있음) 잠금이 느리고 교착 상태가 발생하며 잠금 세분성이 가장 작고 잠금 충돌 가능성이 높습니다. MyISAM 스토리지 엔진은 테이블 수준 잠금만 지원하는 반면 InnoDB는 트랜잭션 처리, 행 수준 잠금 및 더 나은 동시성 기능을 지원합니다

• 2. 독점 잠금 및 공유 잠금

독점 잠금:

X Lock이라고도 함, 쓰기 잠금

공유 잠금:

S 잠금이라고도 함, 읽기 잠금

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

• 1. 서로 다른 트랜잭션 간의 배타적 잠금과 공유 잠금의 호환성을 테스트합니다

  먼저 테이블 SQL과 내용을 확인합니다

격리 수준 보기:

먼저 트랜잭션 A를 열고 ID=7인 데이터에 배타적 잠금을 추가합니다.

다른 클라이언트에서 트랜잭션 B 열기:

id=7에 배타적 잠금을 추가하든 공유 잠금을 추가하든 관계없이 차단되어 쿼리할 수 없습니다. 트랜잭션 A가 id=7 행의 데이터에 배타적 잠금을 추가하기 때문입니다. 쓰기 잠금이 설정되어 있고 다른 사용자는 읽거나 쓸 수 없습니다.

요약: 서로 다른 트랜잭션 간의 데이터 잠금의 경우 SS 잠금만 공존할 수 있으며 XX, SX 및 인덱스 트리에는 있습니다.

테스트를 마칠 때마다 방금 수행한 작업을 롤백하세요.

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

이제 트랜잭션 2는 chenwei

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

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

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

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

그런 다음 방금 수행한 작업을 수행합니다. 작전:

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

이제 이름이 인덱스에 있으므로 zhangsan을 사용하여 보조 인덱스 트리에 있는 행 레코드의 ID인 7을 찾은 다음 기본 키 인덱스 트리로 이동하여 해당 행 레코드의 배타적 잠금 (개인 추측으로는 보조 키 인덱스 트리와 기본 키 인덱스 트리의 해당 레코드가 잠겨 있음)

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

모든 직렬화 트랜잭션은 공유 잠금 또는 배타적 잠금을 사용합니다. , 수동으로 추가할 필요가 없습니다. 선택은 공유 잠금을 획득하고, 삽입, 삭제, 업데이트는 배타적 잠금을 획득합니다.

직렬화 격리 수준 설정:

두 트랜잭션이 동시에 공유 잠금을 획득할 수 있습니다(SS 공존:

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

마감 삽입하려면 배타적 잠금을 추가해야 하지만 트랜잭션 1이 전체 테이블에 공유 잠금을 추가했기 때문에 트랜잭션 2는 더 이상 테이블을 성공적으로 잠글 수 없습니다(sx는 공존하지 않습니다).

모든 잠금 획득 상태를 롤백하고 롤백합니다. 삭제됨 :

두 개의 트랜잭션 열기:

name에 인덱스를 추가했기 때문에 위의 선택은 zhangsan이라는 데이터에 행 공유 잠금을 추가하는 것과 동일합니다.

transaction 2update;

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

트랜잭션 2는 보조 인덱스 트리에서 zhangsan을 찾아 해당 기본 키 값을 찾은 후 기본 키로 이동합니다. 인덱스 트리에서 해당 레코드를 찾았으나 이 레코드 행이 공유 잠금에 의해 잠겨 있음을 발견했습니다. 트랜잭션 2는 공유 잠금을 얻을 수 있지만 배타적 잠금을 얻을 수 없습니다

기본 키 인덱스를 사용하여 확인해 보겠습니다. id는 업데이트될 수 있습니다

는 여전히 차단되어 있지만 name 대신 id를 사용하는 필드도 보조 인덱스 트리를 통해 해당 기본 키를 찾은 다음 기본 키에서 해당 레코드를 찾습니다. 인덱스 트리 및 기본 키 인덱스 트리 레코드가 잠겨 있습니다

id=8로 데이터를 업데이트했는데 성공했습니다. 왜냐하면 선택했을 때 id=7인 데이터에만 행 잠금을 추가했기 때문입니다. id=8

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

테이블 수준 잠금 또는 행 수준 잠금 여부. 잠금의 세분성을 의미하며, 공유 잠금과 배타적 잠금은 잠금의 특성을 나타냅니다. 테이블 잠금이든 행 잠금이든 공유 잠금과 배타적 잠금은 구분됩니다.

직렬화는 배타적 잠금을 사용합니다. 반복 가능한 읽기 수준에서는 수동으로 잠그지 않으면 MVCC가 실제로 사용되지 않습니다. InnoDB가 인덱스를 생성하지 않으면 테이블 잠금을 사용할 수도 있습니다. 인덱스 항목은 쿼리에 사용되며 행 잠금을 사용합니다. 행 잠금은 단순히 데이터 행을 잠그는 대신 인덱스를 잠그는 것입니다.

위 내용은 MySQL의 테이블 수준 잠금, 행 수준 잠금, 배타적 잠금 및 공유 잠금이란 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!