• MySQL매뉴얼
    • MySQL 5.6 매뉴얼
    • MySQL 5.1 매뉴얼
    • MySQL 5.0 매뉴얼
    • MySQL HA 매뉴얼
  • 기술문서
    • Xtrabackup 구성
    • 메모리 사용량 모니터링
  • 라이선스
  • 온라인문의
  • 회사소개
  • → 목 록 (MySQL5.6 한글메뉴얼) [close]
  • 1. MySQL 5.6 새로운 기능
  • 2. MySQL 설치 및 업그레이드
  • 3. MySQL Tutorial
  • 4. MySQL 프로그램
  • 5. MySQL 서버관리
  • 6. 보안
  • 7. 백업 및 복구
  • 8. 최적화
  • 9. Language Structure(언어구조)
  • 10. Character Sets(Globalization)
  • 11. 데이터형(Data Types)
  • 12. 함수와 연산자
  • 13. SQL 문법
  • 14. InnoDB 스토리지 엔진
  • 1. InnoDB 소개
    2. InnoDB의 개념과 아키텍처
    1. MySQL 과 ACID 모델
    2. InnoDB 트랜잭션 모델 및 잠금
    3. InnoDB 잠금 모드
    4. 일관성 nonlock 읽기
    5. 잠금 읽기 (SELECT ... FOR UPDATE 및 SELECT ... LOCK IN SHARE MODE)
    6. InnoDB 레코드 격차 및 넥스트 키 잠금
    7. 넥스트키 로크에 의한 팬텀 문제 해결
    8. InnoDB의 다양한 SQL문에서 설정된 잠금
    9. 암시적 트랜잭션 커밋과 롤백
    10. 교착 상태 감지 및 롤백
    11. 교착 상태 해결 방법
    12. InnoDB 멀티 버전
    13. InnoDB 테이블 및 인덱스 구조
    3. InnoDB 구성
    4. InnoDB 관리
    5. InnoDB 테이블 스페이스 관리
    6. InnoDB 테이블 관리
    7. InnoDB 압축 테이블
    8. InnoDB 파일 형식 관리
    9. InnoDB Row Storage and Row Formats
    10. InnoDB 디스크 I/O 및 파일 영역 관리
    11. InnoDB와 온라인 DDL
    12. InnoDB 부팅 옵션 및 시스템 변수
    13. InnoDB의 성능
    14. InnoDB INFORMATION_SCHEMA 테이블
    15. InnoDB 모니터
    16. InnoDB 백업 및 복구
    17. InnoDB와 MySQL 복제
    18. InnoDB 및 memcached의 통합
    19. InnoDB 문제 해결
  • 15. 기타 스토리지 엔진
  • 16. 고가용성 및 확장성
  • 17. 리플리케이션
  • 18. MySQL Cluster
  • 19. 파티셔닝
  • 20. Stored Programs and Views
  • 21. INFORMATION_SCHEMA
  • 22. PERFORMANCE SCHEMA
  • 23. 컨넥터 및 API
  • 24. MySQL 확장
  • 25. MySQL Enterprise Edition
  • 26. MySQL Workbench
  • 27. 제약 및 제한
  • 28. MySQL 5.7 새로운 기능

14.2.8 InnoDB의 다양한 SQL문에서 설정된 잠금

일반적으로 잠금 읽기 , UPDATE 또는 DELETE 는 SQL 문 처리시 검색되는 모든 인덱스 레코드에 레코드 잠금이 설정됩니다. 행을 제외 WHERE 조건이 문 내에 존재하는지 여부는 관계 없습니다. InnoDB 는 정확한 WHERE 조건이 기억되지 않지만, 스캔 된 인덱스의 범위는 인식됩니다. 일반적으로 잠금 레코드의 직전에있는 '갭'에 삽입도 차단 넥스트 키 잠금 입니다. 그러나 갭 잠금 은 명시 적으로 해제 할 수 있습니다. 따라서 넥스트 키 잠금을 사용하지 않습니다. 자세한 내용은 섹션 14.2.6 "InnoDB 레코드 격차 및 넥스트 키 잠금" 을 참조하십시오. 트랜잭션 격리 수준에 따라 어떤 잠금이 설정되는지도 영향을받습니다. 섹션 13.3.6 "SET TRANSACTION 구문" 을 참조하십시오.

검색에서 보조 인덱스가 사용되어 설정되는 인덱스 레코드 잠금이 독점 인 경우, InnoDB 는 해당 클러스터 된 인덱스 레코드를 가져와 잠금을 설정할 수도합니다.

공유 잠금과 배타 락의 차이에 대해서는 섹션 14.2.3 "InnoDB 잠금 모드" 를 참조하십시오.

문에 적합한 인덱스가없고, MySQL가 문을 처리하기 위해 전체 테이블을 스캔해야하는 경우, 테이블의 모든 행이 잠겨 있습니다. 그 결과 해당 테이블에 다른 사용자가 모든 삽입이 차단됩니다. 쿼리에서 불필요하게 여러 행이 검색되지 않도록 적절한 인덱스를 만드는 것이 중요합니다.

SELECT ... FOR UPDATE 또는 SELECT ... LOCK IN SHARE MODE 에서는 스캔 된 행에 대해 잠금을 획득하고 WHERE 절에 지정된 조건을 만족하지 않는 등의 이유로 결과 세트에 포함 대상에서 제외됩니다 타행 내용은 잠금이 해제 될 것으로 예상됩니다. 그러나 경우에 따라서는 쿼리의 실행 결과 행과 원본과의 관계가 상실 되었기 때문에, 행 잠금이 즉시 해제되지 않을 수도 있습니다. 예를 들어 UNION 에서는 스캔 (및 잠금) 된 테이블의 행이 결과 집합에 포함 대상 여부의 평가 전에 임시 테이블에 삽입 될 수 있습니다. 이 상황에서는 임시 테​​이블의 행과 원래 테이블의 행과의 관계가 손실되어 있기 때문에 쿼리 실행이 끝날 때까지 후자의 행 잠금 상태가 해제되지 않습니다.

InnoDB 는 다음과 같이 특정 잠금 유형을 설정합니다.

  • SELECT ... FROM 일관성 읽기이며, 데이터베이스의 스냅 샷을 읽기 트랜잭션 격리 수준이 SERIALIZABLE 로 설정되어야 잠금을 설정하지 않습니다. SERIALIZABLE 수준의 경우, 검색에서 발견 된 인덱스 레코드에 공유 넥스트 키 잠금이 설정됩니다.

  • SELECT ... FROM ... LOCK IN SHARE MODE 는 검색에서 발견 된 모든 인덱스 레코드에 공유 넥스트 키 잠금이 설정됩니다.

  • SELECT ... FROM ... FOR UPDATE 는 검색에서 발견 된 인덱스 레코드에 대해 다른 세션이 SELECT ... FROM ... LOCK IN SHARE MODE 를 수행하거나 특정 트랜잭션 격리 수준에서 읽어 하는 것을 차단합니다. 일관성 독해는 읽은 뷰에있는 레코드에 설정된 잠금은 무시됩니다.

  • UPDATE ... WHERE ... 검색에서 발견 된 모든 레코드에 배타 넥스트 키 잠금을 설정합니다.

  • DELETE FROM ... WHERE ... 검색에서 발견 된 모든 레코드에 배타 넥스트 키 잠금을 설정합니다.

  • INSERT 은 삽입 된 행에 배타적 잠금을 설정합니다. 이 락은 넥스트 키 잠금 대신 인덱스 레코드 잠금이다 (즉, 갭 잠금이 존재하지 않는) 때문에 다른 세션이 삽입 된 행 앞에있는 갭에 삽입하는 것은 피할 수 없습니다.

    행 삽입 전에 삽입 인텐션 갭 잠금라는 일종의 갭 잠금이 설정됩니다. 이 잠금은 같은 인덱스 갭에 삽입하는 여러 트랜잭션은 그 차이의 동일한 위치에 삽입해야 서로 기다릴 필요가 없도록 의도적으로 삽입하는 것을 보여줍니다. 값이 4와 7의 인덱스 레코드가 존재한다고 가정합니다. 각 값 5와 6의 삽입을 시도 별도의 트랜잭션은 삽입 된 행의 배타 락을 취득하기 전에 삽입 인텐션 잠금을 사용하여 4와 7 사이의 갭을 ​​잠그는하지만 행 충돌이 발생하지 않기 때문에 서로 차단되지 않습니다.

    중복 키 오류가 발생하면 중복 인덱스 레코드에 공유 잠금이 설정됩니다. 여러 세션이 동일한 행을 삽입하려고 할 때, 다른 세션이 이미 단독 잠금을하지 않으면 이렇게 공유 잠금을 사용하여 교착 상태가 발생할 수 있습니다. 이것은 다른 세션이 그 행을 삭제 한 경우에 발생할 수 있습니다. InnoDB 테이블 t1 의 구조가 다음과 같이되어 있다고합니다.

     CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
    

    다음 세 가지 세션이 다음 작업을 순차적으로 실행하는 것으로합니다.

    세션 1 :

     START TRANSACTION;
     INSERT INTO t1 VALUES (1);
    

    세션 2 :

     START TRANSACTION;
     INSERT INTO t1 VALUES (1);
    

    세션 3 :

     START TRANSACTION;
     INSERT INTO t1 VALUES (1);
    

    세션 1 :

     ROLLBACK;
    

    세션 1에 의한 최초의 처리는 행의 배타적 잠금이됩니다. 세션 2와 3의 처리는 모두 중복 키 오류가 발생하고 어떤 세션도 행의 공유 잠금을 요청합니다. 세션 1은 롤백시 행에 대한 배타적 잠금을 해제하고 대기중인 세션 2와 3의 공유 잠금 요청이 부여됩니다. 이 시점에서 세션 2와 3에서 교착 상태가 발생합니다. 모두 다른이 보유하고있는 공유 잠금을 위해 행의 단독 잠금을 얻을 수 없습니다.

    키 값이 1 행이 테이블에 포함되어있는 경우도 비슷한 상황이 발생하고 3 개의 세션이 다음 작업을 순차적으로 실행합니다.

    세션 1 :

     START TRANSACTION;
     DELETE FROM t1 WHERE i = 1;
    

    세션 2 :

     START TRANSACTION;
     INSERT INTO t1 VALUES (1);
    

    세션 3 :

     START TRANSACTION;
     INSERT INTO t1 VALUES (1);
    

    세션 1 :

     COMMIT;
    

    세션 1에 의한 최초의 처리는 행의 배타적 잠금이됩니다. 세션 2와 3의 처리는 모두 중복 키 오류가 발생하고 어떤 세션도 행의 공유 잠금을 요청합니다. 세션 1은 커밋시 행에 대한 배타적 잠금을 해제하고 큐 세션 2와 3의 공유 잠금 요청이 부여됩니다. 이 시점에서 세션 2와 3에서 교착 상태가 발생합니다. 모두 다른이 보유하고있는 공유 잠금을 위해 행의 단독 잠금을 얻을 수 없습니다.

  • INSERT ... ON DUPLICATE KEY UPDATE 중복 키 오류가 발생했을 때 업데이트되는 행에 공유 잠금 대신 단독 넥스트 키 잠금이 배치된다는 점에서 간단한 INSERT 와 다릅니다.

  • REPLACE 는 고유 키가 충돌하지 않으면 INSERT 와 마찬가지로 작동합니다. 그렇지 않으면 대체되는 행에 독점적 넥스트 키 잠금이 배치됩니다.

  • INSERT INTO T SELECT ... FROM S WHERE ... 는 T 에 삽입 된 각 행에 격차 잠금없이 단독 인덱스 레코드 잠금을 설정합니다. 트랜잭션 격리 수준이 READ COMMITTED 인 경우, 또는 innodb_locks_unsafe_for_binlog 가 활성화되어 있고, 트랜잭션 격리 수준이 SERIALIZABLE 아닌 경우, InnoDB 는 일관성 읽기 (잠금 없음)로 S 에서 검색을 실행합니다. 그렇지 않으면, InnoDB 는 S 에서 인출 된 행에 공유 넥스트 키 잠금을 설정합니다. InnoDB 는 후자의 경우에 잠금을 설정해야합니다. 백업에서 롤 포워드 복구시에는 모든 SQL 문을 원래와 완전히 같은 방법으로 실행해야합니다.

    CREATE TABLE ... SELECT ... 는 INSERT ... SELECT 의 경우와 마찬가지로 SELECT 를 공유 넥스트 키 잠금을 사용하여 실행하거나 일관성 독해로서 실행합니다.

    구조 문 REPLACE INTO t SELECT ... FROM s WHERE ... 또는 UPDATE t ... WHERE col IN (SELECT ... FROM s ...) 에서 SELECT 가 사용되면, InnoDB 는 테이블 s 의 행 공유 넥스트 키 잠금을 설정합니다.

  • InnoDB 는 테이블에 사전에 지정된 AUTO_INCREMENT 컬럼의 초기화 동안 AUTO_INCREMENT 컬럼과 연관된 인덱스의 마지막에 배타적 잠금을 설정합니다. InnoDB 는 자동 증가 카운터에 액세스 할 때 잠금이 전체 트랜잭션의 끝까지가 아니라 현재의 SQL 문이 끝날 때까지 계속 특별한 AUTO-INC 테이블 잠금 모드가 사용됩니다. AUTO-INC 테이블 잠금이 유지되는 동안 다른 세션은 테이블에 삽입 할 수 없습니다. 섹션 14.2.2 "InnoDB 트랜잭션 모델 및 잠금" 을 참조하십시오.

    InnoDB 는 잠금을 설정하지 않고 사전에 초기화 된 AUTO_INCREMENT 컬럼의 값을 가져옵니다.

  • FOREIGN KEY 제약이 테이블에 정의되어있는 경우에는 제약 조건을 확인해야 삽입, 업데이트 또는 삭제가 이루어지면 제약을 체크하기 위해 참조되는 레코드에 공유 레코드 수준 잠금 이 설정됩니다. InnoDB 는 제약이 실패 할 경우에 대비하여 이러한 잠금 설정도 실시합니다.

  • LOCK TABLES 테이블 잠금을 설정하지만 이러한 잠금을 설정하는 InnoDB 계층보다 상위의 MySQL 레이어입니다. InnoDB 는 innodb_table_locks = 1 (기본값)하고 autocommit = 0 의 경우 테이블 잠금을 인식하고 InnoDB 보다 상위의 MySQL 레이어는 행 레벨 락을 식별합니다.

    그렇지 않으면, InnoDB 의 자동 데드 록 검출에서는 이러한 테이블 잠금이 참여하는 교착 상태를 감지 할 수 없습니다. 또한이 경우에는 상위의 MySQL 레이어는 행 레벨 락을 식별하지 않기 때문에 현재 다른 ​​세션이 행 수준 잠금을 보유하고있는 테이블에서 테이블 잠금을 얻을 수 있습니다. 그러나 섹션 14.2.10 "교착 상태 감지 및 롤백" 에서 설명했듯이, 이는 트랜잭션의 무결성이 손상 될 수는 없습니다. 섹션 14.6.7 "InnoDB 테이블에서의 제한" 을 참조하십시오.


서울시 강남구 영동대로 602 6층
TEL: 02-6061-0006  /  E: csr@mysqlkorea.com
주식회사 이노클러스터  등록번호 : 727-86-02261
Copyright © innocluster Co. ltd. all rights reserved