mysql의 innoDB 잠금 소개

InnoDB 잠금 전에 트랜잭션을 시작해야 하는 이유는 무엇입니까?

트랜잭션이 제출/롤백된 후에 innodb의 잠금이 해제됩니다. 트랜잭션이 제출/롤백되면 트랜잭션의 잠금이 자동으로 해제됩니다. to autocommit= 1. 자동 제출이 활성화됩니다.

검색 조건에 인덱스를 사용하는 잠금과 인덱스를 사용하지 않는 잠금의 차이점:

　검색 조건에 인덱스가 있는 경우 특정 행이 잠깁니다.

검색 조건을 사용하지 않으면 전체 테이블 스캔을 수행하여 모든 행(존재하지 않는 레코드 포함)을 잠급니다.

읽기 잠금:

읽기 잠금은 공유되거나 서로 차단되지 않습니다. 여러 사용자가 서로 간섭하지 않고 동시에 동일한 리소스를 읽을 수 있습니다.

쓰기 잠금:

　쓰기 잠금은 배타적입니다. 즉, 쓰기 잠금은 다른 쓰기 잠금과 읽기 잠금을 차단합니다. 또한, 쓰기 잠금은 읽기 잠금보다 우선순위가 높기 때문에 쓰기 잠금 요청이 읽기 잠금 대기열 앞에 삽입될 수 있지만 읽기 잠금은 쓰기 잠금 앞에 삽입될 수 없습니다

테이블 잠금:

InnoDB에는 두 가지 테이블 잠금도 있습니다: 의도 공유 잠금(IS), 의도 배타적 잠금(IX)

행 잠금:

　 InnoDB는 두 가지 유형의 행 수준 잠금, 공유 잠금 및 배타적 잠금을 구현합니다.

낙관적 잠금:

낙관적 동시성 제어라고도 하는 낙관적 잠금은 다중 사용자 동시 트랜잭션이 처리 중에 서로 영향을 미치지 않으며 각 트랜잭션이 잠금을 생성하지 않고 자체적으로 처리할 수 있다고 가정합니다. 영향을 받는 데이터의. 데이터 업데이트를 커밋하기 전에 각 트랜잭션은 먼저 트랜잭션이 데이터를 읽은 후 다른 트랜잭션이 데이터를 수정했는지 여부를 확인합니다. 다른 트랜잭션의 업데이트가 있는 경우 현재 커밋 중인 트랜잭션이 롤백됩니다.

비관적 잠금:

비관적 동시성 제어라고도 하는 비관적 잠금은 트랜잭션 A가 특정 데이터 행에 잠금을 적용하고 이 트랜잭션이 잠금을 해제하면 다른 트랜잭션이 잠금과 충돌하는 작업을 수행할 수 있습니다. 트랜잭션 A가 적용한 잠금을 비관적 잠금이라고 합니다. 공유 잠금과 배타적 잠금(행 잠금, 갭 잠금, 다음 키 잠금)은 모두 비관적 잠금과 낙관적 잠금의 구현입니다.

비관적 잠금의 구현은 다음과 같이 데이터베이스에서 제공하는 잠금 메커니즘에 의존합니다. 업데이트를 위해 id=12인 뉴스에서 *를 선택하고 낙관적 잠금은 데이터 버전 기록에 의존합니다. 즉, 테이블에 버전 번호 필드를 추가하여 성공적으로 제출할 수 있는지 여부를 결정하는 핵심 요소입니다.

공유 잠금(S):

공유 잠금은 읽기 잠금이라고도 합니다. 트랜잭션은 데이터 행에 대한 공유 잠금을 획득할 수 있지만 다른 트랜잭션은 해당 행에 해당하는 공유 잠금을 획득할 수 없습니다. 즉, 트랜잭션이 데이터 행을 읽을 때 다른 트랜잭션도 이를 읽을 수 있지만 데이터 행을 추가, 삭제 또는 수정할 수는 없습니다.

공유 잠금 설정: SELECT .... LOCK IN SHARE MODE;

독점 잠금(X):

　 배타적 잠금은 쓰기 잠금이라고도 합니다. 트랜잭션이 데이터 행에 대한 배타적 잠금을 획득하면 다른 트랜잭션은 더 이상 해당 행에 대한 다른 잠금(배타적 잠금 또는 공유 잠금)을 획득할 수 없습니다. 트랜잭션이 데이터 행을 읽습니다. 이때 다른 트랜잭션은 데이터 행을 추가, 삭제 또는 수정할 수 없습니다.

배타적 잠금 설정: SELECT .... FOR UPDATE

　

참고:

Select 문의 경우, innodb는 잠금을 추가하지 않습니다. 즉, 잠금이 전혀 없기 때문에 잠금 충돌 없이 여러 선택 작업을 동시에 수행할 수 있습니다.
삽입, 업데이트 및 삭제 작업의 경우 innodb는 관련 데이터에 자동으로 배타적 잠금을 추가합니다. 쿼리 선택에서만 배타적 잠금을 수동으로 설정해야 합니다.
의도 공유 잠금(IS):

　 다음에 적용해야 할 잠금을 데이터베이스에 알리고 테이블을 잠급니다. 레코드 A에 공유 잠금을 추가해야 하는 경우 innodb는 먼저 이 테이블을 찾아 테이블에 의도 공유 잠금을 추가한 다음 레코드 A에 공유 잠금을 추가합니다. 즉, 데이터 행에 공유 잠금을 추가하기 전에 먼저 테이블의 IS 잠금을 획득해야 합니다

의도 배타적 잠금(IX):

　다음에 어떤 잠금을 적용해야 하는지 데이터베이스에 알리고 테이블을 잠급니다. . 레코드 A에 배타적 잠금을 추가해야 하는 경우 innodb는 먼저 이 테이블을 찾아 의도적인 배타적 잠금을 테이블에 추가한 다음 레코드 A에 공유 잠금을 추가합니다. 즉, 데이터 행에 배타적 잠금을 추가하기 전에 먼저 테이블의 IX 잠금을 획득해야 합니다. 공유 잠금과 의도 배타적 잠금, 배타적 잠금과 의도 배타적 잠금의 차이점:

공유 잠금 및 배타적 잠금, 시스템이 특정 상태에 있는 경우 공유 잠금 또는 배타적 잠금은 조건에 따라 자동으로 추가되며, 공유 잠금 또는 배타적 잠금은 수동으로 추가할 수도 있습니다.

• 의도 공유 잠금 및 의도 배타 잠금은 시스템에 의해 자동으로 추가 및 해제되며 전체 프로세스에는 수동 개입이 필요하지 않습니다.

• 공유 잠금 및 배타적 잠금은 모두 잠금의 행 레코드이며 의도 공유 잠금 및 의도 배타적 잠금 잠금 테이블입니다.

잠금 구현 방법:

MySQL에서 행 수준 잠금은 레코드를 직접 잠그는 것이 아니라 인덱스를 잠급니다. 인덱스는 기본 키 인덱스와 기본 키가 아닌 인덱스로 구분됩니다. SQL 문이 기본 키 인덱스에서 작동하는 경우 MySQL은 기본 키 인덱스가 아닌 명령문에서 작동하는 경우 먼저 잠급니다. 기본 키가 아닌 인덱스를 확인한 다음 관련 기본 키 인덱스를 잠급니다 .

　InnoDB 행 잠금은 인덱스 항목을 잠그는 방식으로 구현됩니다. 인덱스가 없으면 InnoDB는 숨겨진 클러스터형 인덱스를 통해 레코드를 잠급니다. 즉, 인덱스 조건을 통해 데이터가 검색되지 않으면 InnoDB는 테이블의 모든 데이터를 잠급니다. 실제 효과는 테이블 잠금과 동일합니다.

레코드 잠금: 즉, 레코드를 잠그는 것입니다.

　Gap Lock: 인덱스 항목 사이의 'gap', 첫 번째 레코드 전의 간격 또는 마지막 레코드 이후의 간격을 잠급니다. 즉, 레코드 자체를 제외한 레코드 범위를 잠급니다.

　Next-key Lock: a를 잠급니다. 레코드 범위 및 레코드 자체 포함(위 두 가지의 조합)

참고:

InnoDB의 기본 수준은 반복 가능-읽기(반복 읽기)

수준입니다. ANSI/IOS SQL 표준은 커밋되지 않은 읽기, 커밋된 읽기, 반복 가능한 읽기, 직렬화 등 4가지 트랜잭션 격리 수준을 정의합니다. Gap Lock 및 Next-key Lock 차이점:

　

Next-Key Lock은 행 잠금과 Gap 잠금의 조합입니다. 이런 방식으로 InnoDB는 인덱스 레코드를 스캔할 때 먼저 선택한 인덱스 레코드에 행 잠금(Record Lock)을 추가한 다음 인덱스 레코드의 양쪽에 Gap과 함께 행 잠금(Record Lock)을 추가합니다. 잠그다. 트랜잭션 T1에 의해 간격이 잠긴 경우 다른 트랜잭션은 이 간격에 레코드를 삽입할 수 없습니다.

행 잠금은 다른 트랜잭션이 수정되거나 삭제되는 것을 방지하고, Gap 잠금은 다른 트랜잭션이 추가되는 것을 방지하며, 행 잠금과 GAP 잠금의 조합으로 형성된 Next-Key 잠금은 RR의 팬텀 읽기 문제를 공동으로 해결합니다. 데이터를 쓸 때 섹터.

InnoDB에서 테이블 잠금을 사용해야 하는 경우:

InnoDB는 대부분의 경우 행 수준 잠금을 사용합니다. 트랜잭션과 행 잠금이 우리가 InnoDB를 선택하는 이유인 경우가 많기 때문입니다. 그러나 어떤 경우에는 테이블 사용도 고려합니다. -level lock

• 트랜잭션이 대부분의 데이터를 업데이트해야 하는 경우 테이블이 상대적으로 큽니다. 기본 행 잠금을 사용하면 비효율적일 뿐만 아니라 다른 트랜잭션이 일정 시간 동안 대기하게 되기 쉽습니다. 오랜 시간과 잠금 충돌.

• 트랜잭션은 비교적 복잡하며 교착 상태 및 롤백이 발생할 수 있습니다.

InnoDB에서는 테이블 잠금을 사용할 때 다음 두 가지 사항에 주의해야 합니다.

(1) LOCK TALBES를 사용하여 InnoDB에 테이블 수준 잠금을 추가할 수 있지만

테이블 잠금은 InnoDB 스토리지 엔진 계층

에 의해 관리되지 않고 상위 계층 MySQL 서버에 의해 관리된다는 점에 유의해야 합니다. autocommit=0, innodb_table_lock=1(기본 설정), InnoDB 계층은 MySQL이 추가한 테이블 잠금을 알 수 있고 MySQL 서버는 InnoDB가 추가한 행 잠금을 감지할 수 있습니다. 이 경우 InnoDB는 테이블 수준과 관련된 종료를 자동으로 식별할 수 있습니다. 그렇지 않으면 InnoDB가 이러한 교착 상태를 자동으로 감지하고 처리할 수 없습니다. (2) LOCAK TABLES를 사용하여 InnoDB를 잠글 때 AUTOCOMMIT를 0으로 설정하도록 주의하십시오. 그렇지 않으면 MySQL은 트랜잭션이 끝나기 전에 UNLOCAK TABLES를 사용하여 테이블 잠금을 해제하지 않습니다. 암시적으로 트랜잭션을 로컬로 제출합니다. COMMIT 또는 ROLLBACK은 LOCAK TABLES로 추가된 테이블 수준 잠금을 해제할 수 없습니다. 테이블 잠금을 해제하려면 UNLOCK TABLES를 사용해야 합니다.

　예: 테이블에 써야 하는 경우 t1 및 테이블 t

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;[do something with tables t1 and here];COMMIT;
UNLOCK TABLES;

에서 읽습니다. 교착 상태:

MyISAM은 항상 필요한 모든 잠금을 한 번에 획득하거나 모두 만족하거나 모두 기다리고 있기 때문에 교착 상태가 발생하지 않는다고 말했습니다. InnoDB에서는 잠금이 점진적으로 획득되므로 교착 상태가 발생할 가능성이 있습니다.

교착 상태가 발생한 후 InnoDB는 일반적으로 이를 감지하여 한 트랜잭션이 잠금을 해제하고 롤백하고 다른 트랜잭션이 잠금을 획득하여 트랜잭션을 완료하도록 할 수 있습니다. 그러나 InnoDB는 외부 잠금이나 잠금이 관련된 경우 교착 상태를 완전히 자동으로 감지할 수 없습니다. 이 문제는 잠금 대기 시간 초과 매개변수 innodb_lock_wait_timeout을 설정하여 해결해야 합니다. 이 매개변수는 교착 상태 문제를 해결하는 데만 사용되는 것이 아닙니다. 동시 액세스가 상대적으로 높을 때 필요한 잠금을 즉시 얻을 수 없어 많은 수의 트랜잭션이 일시 중지되면 많은 양의 컴퓨터 리소스를 차지하게 됩니다. 문제로 인해 데이터베이스가 다운될 수도 있습니다. 적절한 잠금 대기 시간 초과 임계값을 설정하면 이러한 상황을 방지할 수 있습니다.

　교착 상태를 방지하는 방법에는 여러 가지가 있습니다. 다음은 세 가지 일반적인 방법입니다.

1. 다른 프로그램이 여러 테이블에 동시에 액세스하는 경우 동일한 순서로 테이블에 액세스하는 데 동의하면 교착 상태가 발생할 가능성이 크게 줄어듭니다. 두 세션이 서로 다른 순서로 두 테이블에 액세스하는 경우 교착 상태가 발생할 가능성이 매우 높습니다! 그러나 동일한 순서로 접근을 수행한다면 교착상태를 피할 수 있다.

2. 동일한 트랜잭션에서 교착 상태 가능성을 줄이기 위해 필요한 모든 리소스를 한 번에 잠그십시오.

3. 교착 상태가 발생하기 쉬운 비즈니스 부분의 경우 업그레이드된 잠금 세분성을 사용하여 테이블 수준 잠금을 통해 교착 상태 가능성을 줄일 수 있습니다.

4. 프로그램이 데이터를 일괄 처리할 때 각 스레드가 고정된 순서로 레코드를 처리하도록 데이터를 미리 정렬해 두면 교착 상태가 발생할 가능성도 크게 줄일 수 있습니다.

5. REPEATEABLE-READ 격리 수준에서 두 스레드가 SELECT...ROR UPDATE를 사용하여 동시에 동일한 조건 레코드에 배타적 잠금을 추가하는 경우 레코드가 레코드와 일치하지 않으면 두 스레드 모두 성공적으로 잠겼습니다. 프로그램은 레코드가 아직 존재하지 않음을 발견하고 새 레코드를 삽입하려고 시도합니다. 두 스레드가 모두 이를 수행하면 교착 상태가 발생합니다. 이 경우 격리 수준을 READ COMMITTED로 변경하면 문제를 피할 수 있습니다.

6. Isolation Level이 READ COMMITED일 때 두 스레드 모두 SELECT...FOR UPDATE를 먼저 실행하면 조건에 맞는 레코드가 있는지 확인하고, 없으면 해당 레코드를 삽입합니다. 이때 한 스레드만 성공적으로 삽입할 수 있고 다른 스레드는 잠금을 기다리게 됩니다. 첫 번째 스레드가 제출되면 두 번째 스레드는 다시 기본 키로 인해 오류가 발생하지만 이 스레드는 오류가 발생하지만 독점 잠금 장치를 획득하게 됩니다! 이때, 세 번째 쓰레드가 배타적 잠금을 신청하게 되면 교착상태도 발생하게 된다. 이 경우 삽입 작업을 직접 수행한 후 기본 키 중복 예외를 잡을 수도 있고, 기본 키 중복 오류가 발생하면 항상 ROLLBACK을 실행하여 획득한 배타적 잠금을 해제할 수 있습니다

　　ps: 교착 상태가 발생하면, SHOW INNODB STATUS 명령을 사용하여 마지막 교착 상태의 원인을 파악하고 개선 조치를 취할 수 있습니다.

요약:

InnoDB 테이블의 경우 주로 다음과 같은 사항이 있습니다.

(1) InnoDB의 마케팅은 인덱스를 통해 데이터에 액세스하지 않는 경우 테이블 잠금을 사용합니다.

(2) InnoDB gap lock 메커니즘과 InnoDB가 gap lock을 사용하는 이유.

(3) 서로 다른 격리 수준에서는 InnoDB의 잠금 메커니즘과 일관된 읽기 전략이 다릅니다.

(4) MySQL 복구 및 복제는 InnoDB 잠금 메커니즘과 일관된 읽기 전략에도 큰 영향을 미칩니다.

(5) 잠금 충돌과 심지어 교착 상태도 완전히 피하기는 어렵습니다.

​ InnoDB의 잠금 특성을 이해한 후 사용자는 다음을 포함한 설계, SQL 조정 및 기타 조치를 통해 잠금 충돌 및 교착 상태를 줄일 수 있습니다.

• 더 낮은 격리 수준을 사용해 보세요.

• 인덱스를 신중하게 설계하고 시도해 보세요. 가능한 한 많이 사용하려면 데이터에 대한 인덱스 액세스를 통해 잠금이 더 정확해지고 잠금 충돌 가능성이 줄어듭니다.

• 합리적인 트랜잭션 크기를 선택하면 소규모 트랜잭션에 대한 잠금 충돌 가능성이 줄어듭니다.

• 기록 세트 표시를 잠글 때는 한번에 충분한 수준의 잠금을 요청하는 것이 가장 좋습니다. 예를 들어, 데이터를 수정하려는 경우 먼저 공유 잠금을 적용한 후 수정 시 배타적 잠금을 요청하는 것보다 직접 배타적 잠금을 적용하는 것이 최선이며, 이로 인해 교착 상태가 발생하기 쉽습니다.

• 다른 프로그램이 일련의 테이블에 액세스할 때 각 테이블에 동일한 순서로 액세스하는 데 동의해야 합니다. 테이블의 경우 고정된 순서로 테이블의 행에 액세스해야 합니다. 이렇게 하면 교착 상태가 발생할 가능성이 크게 줄어들 수 있습니다.

• 동시 삽입 시 간격 잠금의 영향을 피하기 위해 동등 조건을 사용하여 데이터에 액세스해 보세요.

• 필요한 경우를 제외하고는 실제 요구 사항을 초과하는 잠금 수준을 적용하지 말고 쿼리할 때 잠금을 표시하지 마세요.

• 일부 특정 트랜잭션의 경우 테이블 잠금을 사용하여 처리 속도를 높이거나 교착 상태 가능성을 줄일 수 있습니다.

위 내용은 mysql의 innoDB 잠금 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!