MySQL의 의도 공유 잠금, 의도 배타 잠금 및 교착 상태는 무엇입니까?

1. InnoDB 테이블 수준 잠금

행 수준 잠금은 일반적으로 트랜잭션의 무결성을 보장하기 위해 사용해야 하며, 이는 InnoDB 엔진을 선택하는 일반적인 이유 중 하나이기도 합니다. 그러나 개별적인 경우에는 테이블 수준 잠금이 필요할 수도 있습니다.

트랜잭션은 데이터의 대부분 또는 전부를 업데이트해야 하며 테이블이 상대적으로 큽니다. 기본 행 잠금을 사용하면 트랜잭션 실행 효율성이 낮을 뿐만 아니라 다른 트랜잭션이 트랜잭션에 여러 테이블이 포함되어 있어 교착 상태가 발생하고 다수의 트랜잭션 롤백이 발생할 수 있습니다.

테이블 잠금을 얻으려면 다음 명령을 실행하세요.

테이블 잠금을 사용할 때 관련된 효율성 문제는 다음과 같습니다.

테이블 잠금을 얻으려면 테이블에 공유 잠금 S 또는 배타적 잠금 X를 사용하려면 먼저 이 테이블이 다음에서 획득되지 않았는지 확인해야 합니다. 다른 거래.
이 테이블에 1,000만 개의 데이터가 있다고 가정하면, 이 1,000만 개의 데이터 중 X 잠금이 있는 행을 어떻게 확인할 수 있나요?

테이블의 S 잠금을 얻으려면 테이블의 어느 행에 X 잠금이 있는지 확인해야 합니다. 일부 행에 X 잠금이 있으면 이 테이블의 S 잠금이나 X 잠금을 얻을 수 없습니다. 하나씩 확인하는 것 외에는 더 나은 방법이 없으므로 비효율성이 발생합니다. 비효율성의 문제는 일부 데이터에 행 잠금이 있는지 확인하기 위해 테이블 ​​잠금을 추가하고 데이터를 하나씩 순회해야 하기 때문에 발생합니다. 여기서 배우는 의도 공유 잠금과 의도 독점 잠금은 해결될 수 있습니다.

테이블의 X 잠금을 얻으려면 테이블의 어떤 행 잠금이 (X 또는 S)에 의해 점유되어 있는지 확인할 필요가 없습니다. . IX와 IS만 빠르게 확인하시면 됩니다
.

2. 의도 공유 잠금 및 의도 배타 잠금

의도 공유 잠금(IS 잠금):

트랜잭션 계획은 공유 잠금을 추가하기 전에 먼저 레코드에 행 공유 잠금을 추가해야 합니다. 레코드 행 IS 잠금

• 의도 배타적 잠금(IX 잠금):
트랜잭션 계획은 레코드 행에 배타적 잠금을 추가하기 전에 먼저 트랜잭션을 수행해야 합니다. 테이블의 IX 잠금 획득

행 잠금을 추가하기 전에 InnoDB 스토리지 엔진은 테이블의 IS 또는 IX 잠금을 추가합니다.

• 의도 잠금은 모두

  호환
되며 이는 주로 테이블 잠금 획득 시 효율성을 높이기 위한 것입니다.

• 의도 잠금의 목적은 테이블 잠금을 보다 효율적으로 획득하는 것입니다(표의 X와 S는

  참조). 행 잠금이 아닌 테이블 잠금
!)

• 의도 잠금

  테이블 수준 잠금
이며,
• 테이블 잠금과 행 잠금

  의 공존을 조정합니다. 주요 목적은 트랜잭션이 행을 잠그고 있거나 행을 잠그려고 시도하고 있음을 보여주는 것입니다.

  트랜잭션 1을 분석하여 행 X 잠금을 획득하고 트랜잭션 2를 분석하여 테이블 S 잠금을 획득합니다.

트랜잭션 1에 IX 잠금을 추가해야 하는 경우. 트랜잭션 2가 전체 테이블의 S 잠금을 획득하려고 할 때 다른 트랜잭션이 이 테이블에 대해 IX 잠금을 획득했음을 확인합니다. 이는 이 테이블에 X 잠금에 추가된 일부 데이터가 있어야 함을 의미합니다. Transaction 2에서는 전체 테이블에 S 잠금을 추가할 수 없습니다. 현재 트랜잭션 2는 대기만 할 수 있으며 테이블 S 잠금을 성공적으로 얻을 수 없습니다3. 교착 상태

1. 데이터베이스의 교착 상태

MyISAM 테이블 잠금은 교착 상태가 없기 때문입니다. 테이블 잠금은 항상 필요한 모든 잠금을 한 번에 획득하고 모두 충족하거나 기다리므로 교착 상태가 발생하지 않습니다

. 그러나 InnoDB에서는 단일 SQL로 구성된 트랜잭션을 제외하고 점차적으로 잠금을 획득합니다. 즉, 잠금 세분성이 상대적으로 작아서 InnoDB에서 교착 상태가 가능하다고 판단합니다. 물론 여러 테이블을 처리하는 경우에도 교착 상태가 발생할 수 있습니다.

교착 상태 문제는 일반적으로 다중 스레드 프로그래밍의 교착 상태 상황과 유사하게 여러 스레드가 여러 잠금 리소스를 획득하는 순서가 다르기 때문에 발생합니다. 따라서 서로 다른 코드 세그먼트를 사용하여 데이터베이스의 여러 테이블을 업데이트하는 경우 잠금 충돌로 인해 교착 상태 문제가 발생하지 않도록 이러한 테이블을 동일한 순서로 업데이트해야 합니다.

2. 교착 상태 시나리오 및 솔루션

교착 상태가 발생하는 시나리오는 다음과 같습니다.

트랜잭션 1이 행 잠금 1을 성공적으로 획득했습니다.

트랜잭션 2가 행 잠금 2를 성공적으로 획득했습니다…트랜잭션 1이 행 잠금 2를 획득할 수 없습니다. 차단된 동안에는 커밋/롤백을 실행할 수 없으며 행 잠금 1을 해제할 수 없습니다

트랜잭션 2는 행 잠금 1을 획득할 수 없습니다. 차단된 동안에는 커밋/롤백을 실행할 수 없으며 행 잠금 2를 해제할 수 없습니다

모든 트랜잭션이 차단됨은 프로세스의 모든 스레드가 차단되어 교착 상태 문제가 발생한다는 의미입니다.

교착 상태 해결 방법: 여러 트랜잭션/스레드가 여러 개의 동일한 리소스 잠금을 획득하는 경우 동일한 순서로 리소스 잠금을 획득해야 합니다.

트랜잭션이 차단되었거나 교착 상태에 빠졌습니다. Mysqld(MySQL 서버 데몬)에는 트랜잭션 차단 시간 초과가 설정되어 있습니다. 시간 초과 후에는 트랜잭션 처리가 실패하고 현재 점유 상태입니다. 잠금이 자동으로 해제됩니다.

3. 작업

수동 커밋 및 반복 읽기 격리 수준을 설정하고 트랜잭션을 시작합니다

테이블 데이터를 쿼리합니다. 반복 읽기 격리 수준에서는 MVCC에서 제공하는 스냅샷 읽기가 사용되며 잠금이 없습니다.

트랜잭션 1이 id=7의 독점 잠금을 획득하고, 트랜잭션 2가 id=8의 독점 잠금을 획득

트랜잭션 1이 다시 id=8의 독점 잠금을 획득하고 차단이 발생합니다

트랜잭션 2가 다시 획득 id=7인 배타적 잠금이 차단되었습니다

이때 MySQL 서버는 교착 상태가 발생한 것을 감지하여 트랜잭션 1의 차단을 해제하고 트랜잭션 1을 롤백한 후 행 잠금을 해제합니다. 보유, 그래서 트랜잭션 2는 ID=7로 독점 잠금을 성공적으로 획득했습니다

3. 잠금 최적화 제안

• 비즈니스가 올바르게 완료될 수 있다는 전제 하에 효율성을 보장하기 위해 더 낮은 격리 수준을 사용하려고 합니다. (더티 읽기는 피해야 함)

• 합리적인 인덱스를 설계하고 인덱스를 사용하여 데이터에 액세스하여 잠금을 더 정확하게 만들고 잠금 충돌 가능성을 줄이며 동시성 기능을 향상시키세요

• 합리적인 트랜잭션 선택 작은 트랜잭션에 대한 잠금 충돌 가능성은 작습니다. (트랜잭션이 클수록 더 많은 SQL이 포함되고 더 많은 테이블 리소스 및 행 리소스 잠금이 포함될 수 있으므로 잠금 충돌 가능성이 높아집니다.) 다른 프로그램이 그룹에 액세스할 때 테이블의 경우 동일한 순서로 각 테이블에 액세스하는 데 동의해야 하며, 테이블의 경우 가능한 한 고정된 순서로 테이블의 행에 액세스해야 합니다. 이렇게 하면 교착 상태가 발생할 가능성이 크게 줄어듭니다

• 동일한 삽입에 대한 갭 잠금의 영향을 피할 수 있습니다(실제로 동등한 쿼리도 갭 잠금을 추가합니다).

• 필요하지 않은 경우 쿼리할 때 명시적 잠금을 사용하지 말아야 합니다. 왜냐하면 커밋된 읽기 및 반복 읽기 격리 수준에서 MVCC는 수동 잠금 없이 읽기 메커니즘을 제공하기 때문입니다

위 내용은 MySQL의 의도 공유 잠금, 의도 배타 잠금 및 교착 상태는 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!