MySQL 잠금 및 분류란 무엇입니까?

1. 데이터베이스 동시성 시나리오

다른 미들웨어를 고려하지 않고 데이터베이스에는 다음과 같은 시나리오가 있습니다.

• Reading: 문제가 없으며 동시성 제어도 필요하지 않습니다.

• 읽기 및 쓰기: 트랜잭션 격리 문제를 일으킬 수 있고 더티 읽기, 팬텀 읽기 및 반복 불가능한 읽기가 발생할 수 있는 스레드 안전 문제가 있습니다.

• Written: 스레드 안전 문제가 있으며 첫 번째 유형의 업데이트가 손실되고 두 번째 유형의 업데이트가 손실되는 등 업데이트 손실 문제가 있을 수 있습니다.

위의 문제를 고려하여 SQL 표준은 서로 다른 격리 수준에서 발생할 수 있는 문제가 다르다고 규정합니다.

MySQL의 4가지 주요 격리 수준:

격리 수준	Dirty read	Non -반복 읽기	팬텀 읽기
커밋되지 않은 읽기: 커밋되지 않은 읽기	발생할 수 있음	발생할 수 있음	발생할 수 있음
커밋된 읽기	해결됨	일어날 수도 있습니다	일어날 수도 있습니다
REPEATABLE READ: 반복 가능한 읽기	Solved	Solved	발생 가능
SERIALIZABLE: 직렬화 가능	Solved	Solved	Solved

MySQL은 실제로 REPEATABLE READ 격리 수준에서 반복 불가능성 문제를 해결하고, 기본적으로는팬텀 읽기 문제를 해결하지만 극단적인 경우 팬텀 읽기가 여전히 존재하는 것을 볼 수 있습니다.

그래서 해결책은 무엇인가요? 일반적으로 두 가지 솔루션이 있습니다.

1️⃣ 읽기 작업을 위한 MVCC, 쓰기 작업을 위한 잠금

읽기의 경우 RR 수준 MVCC에서 트랜잭션이 시작되면 ReadView가 생성되고 ReadView를 통해 검색됩니다. 조건을 만족하는 히스토리 버전이고, 이 버전은 ReadView 생성 시 실제로 스냅샷이 생성되므로 이때 SELECT 쿼리는 snapshot read(또는 Consistency Read)이며, RR에서 우리는 알고 있습니다. , ReadView는 트랜잭션 실행 중 처음으로 SELECT 작업이 수행될 때만 생성됩니다. 후속 SELECT 작업은 이 ReadView를 재사용하여 반복 불가능한 읽기를 방지하고 상당 부분 팬텀 읽기 문제를 방지합니다. . 쓰기의 경우 스냅샷 읽기 또는 일관성 읽기 중에 테이블의 어떤 레코드에도 잠금 작업이 수행되지 않으며 ReadView의 트랜잭션은 기록 버전이지만 최신 버전의 쓰기 작업은 동일하지 않습니다. 충돌이 발생하므로 다른 트랜잭션이 테이블의 레코드를 자유롭게 변경할 수 있습니다.

2️⃣ 읽기 및 쓰기 작업이 잠겨 있습니다

일부 비즈니스 시나리오에서 이전 버전의 레코드 읽기를 허용하지 않지만 은행 예금 거래와 같이 매번 최신 버전의 레코드를 읽어야 하는 경우,

먼저 계정 잔액을 읽어낸 후, 그 금액을 입금액에 추가

하고, 마지막으로

데이터베이스에 기록해야 합니다. 계좌 잔액을 읽은 후에는 다른 거래가 잔액에 접근하는 것을 원하지 않습니다. 입금 거래가 완료될 때까지만 다른 거래가 계좌 잔액에 접근할 수 있습니다. 이런 방식으로 레코드를 읽을 때 레코드를 잠가야 합니다. 즉, 읽기 작업과 쓰기 작업도 쓰기-쓰기 작업처럼 대기열에 추가되고 실행됩니다. 더티 읽기의 경우는 현재 트랜잭션이 커밋되지 않은 다른 트랜잭션이 작성한 레코드

를 읽기 때문인데, 해당 레코드를 쓰는 동안 다른 트랜잭션이

이 레코드를 잠그면, 그러면 현재 트랜잭션은 더 이상 읽을 수 없습니다. 레코드이므로 더티 읽기 문제가 발생하지 않습니다. 비반복 읽기의 경우 현재 트랜잭션이 먼저 레코드를 읽고, 다른 트랜잭션이 레코드를 변경하고 커밋한 후 다시 읽을 때 현재 트랜잭션이 다른 값을 얻기 때문입니다. 현재 트랜잭션에서 레코드를 읽을 때 레코드가 잠기면 다른 트랜잭션이 레코드를 수정할 수 없으며 당연히 반복 불가능한 읽기가 발생하지 않습니다.

팬텀 읽기의 경우 현재 트랜잭션이 범위의 레코드를 읽은 다음 다른 트랜잭션이 범위에 새 레코드를 삽입

하기 때문입니다. 새로운 레코드가 발견되었습니다. 새로 삽입된 레코드를 팬텀 레코드라고 부릅니다.

이 범위를 이해하는 방법은 무엇입니까? 다음과 같습니다. 테이블 user에 id=1인 데이터가 하나만 있다고 가정합니다.

트랜잭션 A가 id = 1의 쿼리 작업을 실행하면 (1,2의 id)와 같은 범위 쿼리인 경우 데이터를 쿼리할 수 있습니다. ) code>의 경우 하나의 데이터만 쿼리됩니다.

• 이때 트랜잭션 B는 id = 2로 새로운 작업을 수행하여 제출합니다.

id=1的数据。

• 当事务 A 执行一个id = 1的查询操作，能查询出来数据，如果是一个范围查询，如 id in(1,2)，必然只会查询出来一条数据。

• 此时事务 B 执行一个id = 2的新增操作，并且提交。

• 此时事务 A 再次执行id in(1,2)的查询，就会读取出 2 条记录，因此产生了幻读。

注：由于 RR 可重复读的原因，其实是查不出 id = 2的记录的，所以如果执行一次 update ... where id = 2

이때 트랜잭션 A는 다시 id in(1,2) 쿼리를 실행하여 2개의 레코드를 읽어오게 되어 팬텀 읽기가 발생하게 됩니다.

Note

: RR의 반복 읽기로 인해 실제로 id = 2인 레코드를 찾을 수 없으므로 update를 실행하면 됩니다. .. id = 2인 경우 범위를 검색하여 찾을 수 있습니다.

잠금으로 팬텀 읽기 문제를 해결하는 것은 쉽지 않습니다. 현재 트랜잭션이 처음으로 레코드를 읽을 때 팬텀 레코드가 존재하지 않기 때문에 읽을 때 잠그는 것이 약간 번거롭습니다. 잠글 사람.

그럼 InnoDB는 어떻게 해결하나요? 먼저 InnoDB 스토리지 엔진에 어떤 잠금 장치가 있는지 살펴보겠습니다.

2. MySQL의 잠금 및 분류

공식 MySQL 문서에서 InnoDB 스토리지 엔진은 다음 유형의 잠금을 도입합니다.

마찬가지로 여전히 혼란스러워 보이지만 JDK 잠금을 배우는 단계를 따를 수 있습니다. 분류하려면:

3. 잠금 세분성 분류

잠금 세분성이란 무엇인가요? 소위 잠금 세분성이란 잠그려는 항목의 범위를 나타냅니다. 🎜🎜예를 들어 집에서 화장실에 갈 경우 가족 구성원이 들어오지 못하도록 집 전체를 잠글 필요는 없습니다. 🎜🎜합리적인 잠금 세분성이란 무엇입니까? 🎜

실제로 화장실은 화장실에 가는 용도뿐만 아니라 샤워하고 손을 씻는 용도로도 사용됩니다. 여기에는 잠금 세분성을 최적화하는 문제가 포함됩니다.

화장실에서 샤워를 할 때 변기, 욕조, 세면대가 모두 분리되어 있고 상대적으로 독립적인 경우(습식 및 건식), 다른 사람들이 실제로 들어가서 동시에 손을 씻을 수 있습니다. 분리되어 있음), 사실 화장실은 3명이 동시에 사용할 수 있지만, 물론 3명이 같은 일을 할 수는 없습니다. 이는 잠금 장치의 세분화를 개선하여 샤워할 때만 욕실 문을 닫으면 되고 다른 사람들은 계속해서 들어가서 손을 씻을 수 있습니다. 욕실을 디자인할 때 서로 다른 기능 영역을 분리하지 않으면 욕실 자원을 극대화할 수 없습니다.

마찬가지로 MySQL에도 잠금 세분성이 있습니다. 일반적으로 행 잠금, 테이블 잠금, 페이지 잠금의 세 가지 유형으로 나뉩니다.

3.1 행 잠금

공유 잠금 및 독점 잠금의 도입에서는 실제로 특정 행에 대해 기록되므로 행 잠금이라고도 할 수 있습니다.

레코드를 잠그면 이 레코드에만 영향을 미치므로 행 잠금의 잠금 세분성은 MySQL에서 가장 좋습니다. InnoDB 스토리지 엔진의 기본 잠금은 행 잠금입니다.

다음과 같은 특징을 가지고 있습니다.

1. 잠금 충돌 가능성이 가장 낮고 동시성 높음

   행 잠금의 세분성이 작기 때문에 잠금 리소스 경합 가능성도 가장 작으므로 잠금 가능성이 높습니다. 갈등은 낮고 동시성은 높을수록 성별이 높습니다.

2. 높은 오버헤드와 느린 잠금

   잠금은 성능을 많이 소모합니다. 데이터베이스의 여러 데이터 조각을 잠그면 필연적으로 많은 리소스를 차지하게 되며 이전 잠금이 완료될 때까지 기다려야 합니다. 잠금을 해제합니다.

3. 교착 상태가 발생합니다

   교착 상태가 무엇인지 아래에서 읽어보세요.

3.2 테이블 잠금

테이블 수준 잠금은 전체 테이블을 잠그는 테이블 수준 잠금입니다. 교착 상태를 매우 효과적으로 방지할 수 있으며 MySQL에서 가장 큰 세부 잠금 메커니즘이기도 합니다.

MyISAM 스토리지 엔진의 기본 잠금은 테이블 잠금입니다.

다음과 같은 특징이 있습니다.

1. 오버헤드가 적고 잠금이 빠릅니다.

   테이블 전체를 잠그기 때문에 단일 데이터를 잠그는 것보다 속도가 빨라야 합니다.

2. 교착 상태가 발생하지 않습니다.

   전체 테이블이 잠겨 있습니다. 다른 트랜잭션은 전혀 잠금을 얻을 수 없으며 당연히 교착 상태가 발생하지 않습니다.

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

3.3 페이지 잠금

페이지 수준 잠금은 MySQL의 고유한 잠금 수준으로, 발견되지 않습니다. 다른 데이터베이스 관리 소프트웨어에서는 일반적입니다.

페이지 수준 잠금의 세분성은 행 수준 잠금과 테이블 수준 잠금 사이에 있으므로 잠금을 획득하는 데 필요한 리소스 오버헤드와 이들이 제공할 수 있는 동시 처리 기능도 위 둘 사이에 있습니다. 또한 행 수준 잠금과 마찬가지로 페이지 수준 잠금도 교착 상태를 일으킬 수 있습니다. ㅋㅋㅋ

잠금 효율성빠름둘 사이의 충돌 확률LowHigh-동시 성능HighLowGeneral성능 머리 위큰작은 사이 둘교착상태인가예아니요예

4. 잠금 호환성 분류

MySQL에서 데이터 읽기는 주로 현재 읽기와 스냅샷 읽기로 구분됩니다.

• 스냅샷 읽기

  스냅샷 읽기, 읽는 것은 스냅샷 데이터입니다. 잠금은 스냅샷 읽기입니다.

  SELECT * FROM table WHERE ...

• 현재 읽기

  현재 읽기는 기록 데이터가 아닌 최신 데이터를 읽는 것을 의미하며, 데이터의 추가, 삭제, 수정이 모두 현재 읽기를 수행합니다.

  SELECT * FROM table LOCK IN SHARE MODE;
  SELECT FROM table FOR UPDATE;
  INSERT INTO table values ...
  DELETE FROM table WHERE ...
  UPDATE table SET ...

대부분의 경우 현재 읽기를 기반으로 데이터베이스를 운영하며 동시 시나리오에서는 읽기-읽기 상황이 영향을 받지 않도록 허용할 뿐만 아니라 쓰기-쓰기, 읽기도 수행해야 합니다. -쓰기 또는 쓰기-읽기 작업이 서로 차단되므로 MySQL에서는 공유 잠금 및 배타적 잠금을 사용해야 합니다.

4.1 공유 잠금 및 배타적 잠금

공유 잠금(공유 잠금)은 읽기 잠금이라고도 하며 S 잠금으로 축약됩니다. 데이터를 동시에 읽을 수 있지만 어떤 트랜잭션도 데이터를 수정할 수 없습니다.

배타적 잠금(배타적 잠금)은 배타적 잠금 또는 쓰기 잠금이라고도 하며 X 잠금이라고도 합니다. 어떤 것이 행에 배타적 잠금을 추가하면 이 트랜잭션만 읽고 쓸 수 있습니다. 이 트랜잭션이 끝나기 전에는 다른 트랜잭션이 해당 행에 잠금을 추가할 수 없습니다. 다른 프로세스는 읽을 수는 있지만 쓰기 작업을 수행할 수는 없습니다. 풀어 주다.

Lock 획득 상황을 분석해 보겠습니다. 트랜잭션 A와 트랜잭션 B가 있다면

• 트랜잭션 A가 레코드의 S 잠금을 획득하고 이때 트랜잭션 B도 해당 레코드의 S 잠금을 획득하려는 경우 트랜잭션 B도 잠금을 획득할 수 있습니다. 이는 트랜잭션 A와 트랜잭션 B가 동시에 레코드의 S 잠금을 보유함을 의미합니다.

• 트랜잭션 B가 레코드의 X 잠금을 획득하려는 경우 트랜잭션 A가 커밋된 후 S 잠금이 해제될 때까지 이 작업이 차단됩니다.

• 트랜잭션 A가 먼저 X 잠금을 획득하면 트랜잭션 B가 레코드의 S 잠금을 획득하든 X 잠금을 획득하든 트랜잭션 A가 커밋될 때까지 차단됩니다.

따라서 S 잠금과 S 잠금은 호환되고, S 잠금과 X 잠금은 호환되지 않으며, X 잠금과 X 잠금도 호환되지 않는다고 말할 수 있습니다.

4.2 의도 잠금

의도 공유 잠금(의도 공유 잠금), IS 잠금이라고도 합니다. 트랜잭션이 레코드에 S 잠금을 추가하려면 먼저 테이블 수준에서 IS 잠금을 추가해야 합니다.

Intention Exclusive Lock(의도 배타적 잠금), IX Lock이라고도 합니다. 트랜잭션이 레코드에 X 잠금을 추가하려면 먼저 테이블 수준에서 IX 잠금을 추가해야 합니다.

의도 잠금은 테이블 수준 잠금입니다. 테이블 수준 S 잠금을 추가할 때 테이블의 레코드가 잠겨 있는지 여부와 테이블에 잠긴 레코드가 없는지 신속하게 판단하기 위해서만 제안됩니다. 즉, IS 잠금은 IS 잠금과 호환되고 IX 잠금은 IX 잠금과 호환됩니다.

의도 잠금이 왜 필요한가요?

InnoDB의 의도 잠금은 주로 여러 세분화된 잠금이 공존할 때 사용됩니다. 예를 들어, 트랜잭션 A는 테이블에 S 잠금을 추가하려고 합니다. 트랜잭션 B가 테이블의 행을 X 잠금에 추가한 경우 잠금 적용도 차단되어야 합니다. 테이블에 데이터가 많으면 행별로 잠금 플래그를 확인하는 오버헤드가 매우 커지고 시스템 성능에 영향을 미치게 됩니다. 예를 들어, 테이블에 1억 개의 레코드가 있고 트랜잭션 A가 여러 레코드의 행을 잠그면 트랜잭션 B는 테이블에 테이블 수준 잠금을 추가해야 합니다. 이 1억 개의 레코드는 테이블에 잠겨 있습니다. 의도 잠금이 있는 경우 트랜잭션 A가 의도 잠금을 추가한 다음 레코드를 업데이트하기 전에 X 잠금을 추가하면 트랜잭션 B는 먼저 테이블에 의도 잠금이 있는지 여부와 기존 의도 잠금이 계획된 잠금과 충돌하는지 여부를 확인합니다. 충돌이 있으면 각 레코드를 확인하지 않고 트랜잭션 A가 해제될 때까지 기다리십시오. 트랜잭션 B는 테이블을 업데이트할 때 어떤 행이 잠겨 있는지 정확히 알 필요가 없으며 적어도 하나의 행이 잠겨 있다는 것만 알면 됩니다.

직접 말하면 의도 잠금의 주요 기능은 행 잠금과 테이블 잠금 사이의 모순을 처리하는 것입니다.

트랜잭션이 특정 행에 잠금을 보유하고 있거나 잠금 보유를 준비하고 있음을 보여줄 수 있습니다

.

테이블 수준

의 다양한 잠금 장치 호환성:

느림

SXSISXIS

4.3 읽기 작업에 대한 잠금

MySQL 읽기 작업에는 두 가지 잠금 방법이 있습니다.

1️⃣ SELECT * FROM table LOCK IN SHARE MODE

현재 트랜잭션이 이 명령문을 실행하면 읽은 레코드에 S 잠금을 추가하여 다른 트랜잭션이 이 레코드에 대해 S 잠금을 계속 획득할 수 있도록 합니다(예: 예를 들어, 다른 트랜잭션도 SELECT ... LOCK IN SHARE MODE 문을 사용하여 이러한 레코드를 읽지만 이러한 레코드의 X 잠금을 얻을 수는 없습니다(예: SELECT .. . FOR UPDATE 문을 사용하여 이러한 레코드를 읽거나 직접 수정할 수 있습니다. SELECT ... LOCK IN SHARE MODE 语句来读取这些记录），但是不能获取这些记录的 X 锁（比方说使用 SELECT ... FOR UPDATE 语句来读取这些记录，或者直接修改这些记录）。

如果别的事务想要获取这些记录的 X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的 S 锁释放掉

2️⃣ SELECT FROM table FOR UPDATE

如果当前事务执行了该语句，那么它会为读取到的记录加 X 锁，这样既不允许别的事务获取这些记录的 S 锁（比方说别的事务使用 SELECT ... LOCK IN SHARE MODE 语句来读取这些记录），也不允许获取这些记录的 X 锁（比如说使用 SELECT ... FOR UPDATE다른 트랜잭션이 이 레코드의 X 잠금을 획득하려는 경우 현재 트랜잭션이 커밋된 후 이 레코드의 S 잠금이 해제될 때까지 차단됩니다.

2️⃣ SELECT FROM table FOR UPDATE

현재 트랜잭션이 실행되는 경우 이 문을 입력하면 읽기 레코드에 X 잠금이 추가됩니다. 이렇게 하면 다른 트랜잭션이 이러한 레코드의 S 잠금을 얻을 수 없습니다. 예를 들어 다른 트랜잭션에서는 SELECT... LOCK IN SHARE MODE 문을 사용하여 이러한 레코드를 읽음), 이러한 레코드의 X 잠금을 얻는 것도 허용되지 않습니다(예를 들어 <code>SELECT ... FOR UPDATE 문을 사용하여 이러한 레코드를 읽거나 직접 수정). 이 기록).

다른 트랜잭션이 이러한 레코드의 S 잠금 또는 X 잠금을 획득하려는 경우 현재 트랜잭션이 커밋된 후 이러한 레코드에 대한 X 잠금이 해제될 때까지 차단됩니다. 4.4 쓰기 작업에 대한 잠금

DELETE, UPDATE 및 INSERT는 MySQL의 일반적인 쓰기 작업입니다. 암시적 잠금, 자동 잠금 및 잠금 해제.

1️⃣ DELETE

레코드에 대한 DELETE 작업을 수행하는 과정은 먼저 B+ 트리에서 해당 레코드를 찾은 다음 해당 레코드의 X 잠금을 얻은 다음 삭제 표시 작업을 수행하는 것입니다. 또한 이 과정은 B+ 트리에서 삭제할 레코드의 위치를 ​​찾기 위해 X 잠금을 획득하는 잠금 읽기 방법을 사용하는 것으로 이해할 수도 있습니다.

2️⃣ INSERT

일반적으로 InnoDB는 트랜잭션 액세스가 커밋되기 전에 새로 삽입된 레코드를 다른 사람이 사용하지 못하도록 일종의 암시적 잠금을 사용합니다.

3️⃣ UPDATE

레코드에 대해 UPDATE 작업을 수행할 때 세 가지 상황이 있습니다.

① 레코드의 키 값이 수정되지 않았고 업데이트된 열이 차지하는 저장 공간이 전후에 변경되지 않은 경우 그런 다음 먼저 B+ 트리에서 이 레코드의 위치를 ​​찾은 다음 레코드의 X 잠금을 획득하고 마지막으로 원본 레코드의 위치에서 수정 작업을 수행합니다. B+ 트리에 수정될 위치를 기록하는 과정을 X 잠금을 획득하기 위한 잠금 읽기 작업으로 생각할 수도 있습니다. ② 레코드의 키 값이 수정되지 않았고 수정 전후에 업데이트된 하나 이상의 열이 차지하는 저장 공간이 변경된 경우 먼저 B+ 트리에서 해당 레코드의 위치를 ​​찾은 후 X를 얻습니다. 레코드 잠금, 레코드를 완전히 삭제(즉, 레코드를 가비지 목록으로 완전히 이동)하고 마지막으로 새 레코드를 삽입합니다. B+ 트리에서 수정할 레코드의 위치를 ​​찾는 과정은 X 잠금을 얻기 위한 잠긴 읽기로 간주되며, 새로 삽입된 레코드는 INSERT 연산에서 제공하는 암시적 잠금으로 보호됩니다.

3 레코드의 키 값이 수정되면 원본 레코드에 대해 DELETE 작업을 수행한 후 INSERT 작업을 수행하는 것과 같습니다. DELETE 및 INSERT 규칙에 따라 잠금 작업을 수행해야 합니다.

PS: 쓰기 잠금이 잠겨 있는데 왜 다른 트랜잭션을 계속 읽을 수 있나요?

InnoDB에는 MVCC 메커니즘(Multi-version Concurrency Control)이 있기 때문에 스냅샷 읽기를 차단하지 않고 사용할 수 있습니다.

4. 잠금 세분성 분류

잠금 세분성

이란 무엇인가요? 소위 잠금 세분성이란 잠그려는 항목의 범위를 나타냅니다.

예를 들어 집에서 화장실에 갈 경우 가족 구성원이 들어오지 못하도록 집 전체를 잠글 필요는 없습니다.

합리적인 잠금 세분성이란 무엇입니까?

사실 화장실은 화장실에 가는 용도뿐만 아니라 샤워하고 손을 씻는 용도로도 사용됩니다. 여기에는 잠금 세분성을 최적화하는 문제가 포함됩니다.

화장실에서 샤워를 할 때 변기, 욕조, 세면대가 모두 분리되어 있고 상대적으로 독립적인 경우(습식 및 건식), 다른 사람들이 실제로 들어가서 동시에 손을 씻을 수 있습니다. 분리되어 있음), 사실 화장실은 3명이 동시에 사용할 수 있지만, 물론 3명이 같은 일을 할 수는 없습니다. 이는 잠금 장치의 세분화를 개선하여 샤워할 때만 욕실 문을 닫으면 되고 다른 사람들은 계속해서 들어가서 손을 씻을 수 있습니다. 욕실을 디자인할 때 서로 다른 기능 영역을 분리하지 않으면 욕실 자원을 극대화할 수 없습니다.

마찬가지로 MySQL에도 잠금 세분성이 있습니다. 일반적으로

행 잠금, 테이블 잠금, 페이지 잠금

의 세 가지 유형으로 나뉩니다.

4.1 행 잠금

공유 잠금과 배타적 잠금의 소개에서는 실제로 특정 행에 대해 기록되므로 행 잠금이라고도 할 수 있습니다.

레코드를 잠그면 이 레코드에만 영향을 미치므로 행 잠금의 잠금 세분성은 MySQL에서 가장 좋습니다. 🎜InnoDB 스토리지 엔진의 기본 잠금은 행 잠금🎜입니다. 🎜🎜다음과 같은 기능이 있습니다:🎜

• 잠금 충돌 확률이 가장 낮고 동시성이 높습니다

  행 잠금의 작은 세분화로 인해 잠금 리소스 경합 확률도 가장 작으므로 잠금 충돌 확률이 낮고 동시성이 더 높습니다.

• 높은 오버헤드와 느린 잠금

  잠금은 성능을 많이 소모합니다. 데이터베이스의 여러 데이터 조각을 잠그면 필연적으로 많은 리소스를 차지하게 되며 이전 잠금이 완료될 때까지 기다려야 합니다. 잠금을 해제합니다.

• 교착 상태가 발생합니다

  교착 상태가 무엇인지 아래에서 읽어보세요.

4.2 테이블 잠금

테이블 수준 잠금은 전체 테이블을 잠그는 테이블 수준 잠금입니다. 교착 상태를 매우 효과적으로 방지할 수 있으며 MySQL에서 가장 큰 세부 잠금 메커니즘이기도 합니다.

MyISAM 스토리지 엔진의 기본 잠금은 테이블 잠금입니다.

다음과 같은 특징이 있습니다.

• 오버헤드가 적고 잠금이 빠릅니다.

  테이블 전체를 잠그기 때문에 단일 데이터를 잠그는 것보다 속도가 빨라야 합니다.

• 교착 상태가 발생하지 않습니다.

  전체 테이블이 잠겨 있습니다. 다른 트랜잭션은 전혀 잠금을 얻을 수 없으며 당연히 교착 상태가 발생하지 않습니다.

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

4.3 페이지 잠금

페이지 수준 잠금은 MySQL의 고유한 잠금 수준으로, 발견되지 않습니다. 다른 데이터베이스 관리 소프트웨어에서는 일반적입니다.

페이지 수준 잠금의 세분성은 행 수준 잠금과 테이블 수준 잠금 사이에 있으므로 잠금을 획득하는 데 필요한 리소스 오버헤드와 이들이 제공할 수 있는 동시 처리 기능도 위 둘 사이에 있습니다. 또한 행 수준 잠금과 마찬가지로 페이지 수준 잠금도 교착 상태를 일으킬 수 있습니다. ㅋㅋㅋ

	IS		IX
호환	호환	호환되지 않음	호환되지 않음
호환됨	호환됨	호환되지 않음	호환되지 않음
호환되지 않음	호환되지 않음	호환되지 않음	호환되지 않음 호환됨
호환됨	호환됨	호환되지 않음	호환되지 않음

잠금 효율성빠름둘 사이의 충돌 확률LowHigh-동시 성능HighLowGeneral성능 머리 위큰작은 사이 둘교착상태인가예아니요예

5. 算法实现分类

对于上面的锁的介绍，我们实际上可以知道，主要区分就是在锁的粒度上面，而 InnoDB 中用的锁就是行锁，也叫记录锁，但是要注意，这个记录指的是通过给索引上的索引项加锁。

InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决 定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁。

不过即使是行锁，InnoDB 里也是分成了各种类型的。换言之，即使对同一条记录加上行锁，不同的锁类型也会产生不同的效果。通常有以下几种常用的行锁类型。

5.1 Record Lock

记录锁，单条索引记录上加锁。

Record Lock 锁住的永远是索引，不包括记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

记录锁是有 S 锁和 X 锁之分的，当一个事务获取了一条记录的 S 型记录锁后，其他事务也可以继续获取该记录的 S 型记录锁，但不可以继续获取 X 型记录锁；当一个事务获取了一条记录的 X 型记录锁后，其他事务既不可以继续获取该记录的 S 型记录锁，也不可以继续获取 X 型记录锁。

5.2 Gap Locks

间隙锁，对索引前后的间隙上锁，不对索引本身上锁。

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚 不存在，我们无法给这些幻影记录加上记录锁。所以我们可以使用间隙锁对其上锁。

如存在这样一张表：

CREATE TABLE test (
    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 test VALUES (1, 1); 
INSERT INTO test VALUES (5, 3); 
INSERT INTO test VALUES (7, 8); 
INSERT INTO test VALUES (11, 12);

如下：

开启一个事务 A：

BEGIN;

SELECT * FROM test WHERE number = 3 FOR UPDATE;

此时，会对((1,1),(5,3))和((5,3),(7,8))之间上锁。

如果此时在开启一个事务 B 进行插入数据，如下：

BEGIN;

# 阻塞
INSERT INTO test (id, number) VALUES (2,2);

结果如下：

为什么不能插入？因为记录(2,2)要 插入的话，在索引 number上，刚好落在((1,1),(5,3))和((5,3),(7,8))之间，是有锁的，所以不允许插入。 如果在范围外，当然是可以插入的，如：

INSERT INTO test (id, number) VALUES (8,8);

5.3 Next-Key Locks

next-key locks 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合，包括记录本身，每个 next-key locks 是前开后闭区间，也就是说间隙锁只是锁的间隙，没有锁住记录行，next-key locks 就是间隙锁基础上锁住右边界行。

InnoDB 默认使用 REPEATABLE READ 隔离级别。在这种情况下，InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，这可以防止幻读的发生。

6. 乐观锁和悲观锁

乐观锁和悲观锁其实不算是具体的锁，而是一种锁的思想，不仅仅是在 MySQL 中体现，常见的 Redis 等中间件都可以应用这种思想。

6.1 乐观锁

所谓乐观锁，就是持有乐观的态度，当我们更新一条记录时，假设这段时间没有其他人来操作这条数据。

实现乐观锁常见的方式

常见的实现方式就是在表中添加 version字段，控制版本号，每次修改数据后+1。

在每次更新数据之前，先查询出该条数据的 version版本号，再执行业务操作，然后在更新数据之前在把查到的版本号和当前数据库中的版本号作对比，若相同，则说明没有其他线程修改过该数据，否则作相应的异常处理。

6.2 悲观锁

所谓悲观锁，就是持有悲观的态度，一开始就假设改数据会被别人修改。

悲观锁的实现方式有两种

共享锁（读锁）和排它锁（写锁），参考上面。

7. 死锁

是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统 处于死锁状态或系统产生了死锁。

产生的条件

• 互斥条件：一个资源每次只能被一个进程使用；

• 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；

• 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺；

• 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系。

MySQL 中其实也是一样的，如下还是这样一张表：

CREATE TABLE `user` (
  `id` bigint NOT NULL COMMENT '主键',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `age` varchar(10) DEFAULT NULL COMMENT '年龄',
  `url` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `suf_index_url` (`name`(3)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

# 数据
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('1', 'a', '1', '18', 'https://javatv.net');
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('2', 'b', '1', '18', 'https://javatv.net');

按照如下顺序执行：

느림

	A	B
①	BEGIN
②		BEGIN
③	SELECT * FROM user WHERE name='a' FOR UPDATE
④		SELECT * FROM user WHERE name='b' FOR UPDATE
⑤	SELECT * FROM user WHERE name='b' FOR UPDATE
⑥		SELECT * FROM user WHERE name='a' FOR UPDATE

1、开启 A、B 两个事务；

2、首先 A 先查询name='a'的数据，然后 B 也查询name='b'的数据；

3、在 B 没释放锁的情况下，A 尝试对 name='b'的数据加锁，此时会阻塞；

4、若此时，事务 B 在没释放锁的情况下尝试对 name='a'的数据加锁，则产生死锁。

此时，MySQL 检测到了死锁，并结束了 B 中事务的执行，此时，切回事务 A，发现原本阻塞的 SQL 语句执行完成了。可通过show engine innodb status \G查看死锁。

如何避免

从上面的案例可以看出，死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致，所以我们在执行 SQL 操作的时候要让加锁顺序一致，尽可能一次性锁定所需的数据行。

위 내용은 MySQL 잠금 및 분류란 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!