MySQL5.6 테이블 잠금 문제

8.10.2 테이블 잠금 문제

InnoDB 테이블에서는 여러 세션 및 응용 프로그램이 서로 대기하거나 불일치의 결과를 생성 할 필요없이 동일한 테이블에 동시에 읽고 쓸 수 있도록 행 수준 잠금을 사용합니다. 이 스토리지 엔진은 LOCK TABLES 문은 특별한 보호를 제공하지 않고 대신 병렬성이 낮아지기 때문에이 사용을 피하십시오. 자동 행 레벨 잠금은 이러한 테이블이 가장 중요한 데이터를 저장하는 가장 바쁜 데이터베이스에 적합하고 동시에 테이블 잠금 및 잠금 해제가 필요 없기 때문에 응용 프로그램 논리가 쉬워집니다. 그 결과, InnoDB 스토리지 엔진은 MySQL 5.6의 기본입니다.

MySQL은 InnoDB 를 제외한 모든 스토리지 엔진에 대해 테이블 잠금 (페이지, 행 또는 열 잠금 대신)를 사용합니다. 잠금 작업 자체는 그다지 오버 헤드가 없습니다. 그러나 한 번에 하나의 세션 만 테이블에 쓸 수 없기 때문에 이러한 다른 스토리지 엔진에서 최고의 성능을 위해 자주 쿼리되는 거의 삽입 또는 업데이트되지 않는 테이블에 주로 그들을 사용 합니다.

InnoDB를 우선 성능 고려 사항

테이블을 작성하기 위해 InnoDB 를 사용하거나 다른 스토리지 엔진을 사용할 것인지를 선택하는 경우 테이블 잠금 다음과 같은 단점을 고려하십시오.

테이블 잠금으로 많은 세션을 동시에 테이블에서 읽을 수 있지만, 세션 테이블을 작성해야하는 경우, 먼저 단독 액세스를 취득 할 필요가 있습니다 만, 이것은 먼저 다른 세션이 테이블을 처리 을 마치고 기다릴 필요가있을 수 있다는 것을 의미합니다. 업데이트 중이 특정 테이블에 액세스하려고하는 다른 모든 세션은 업데이트가 완료 될 때까지 대기해야합니다.
디스크가 꽉 세션을 계속하려면 빈 공간을 사용할 수있게 될 필요가 있기 때문에 세션이 대기하고있는 경우 테이블 잠금으로 인해 문제가 발생합니다. 이 경우 문제의 테이블에 액세스하려고하는 모든 세션이 더 많은 디스크 공간을 사용할 수있게 될 때까지 대기 상태가됩니다.
실행 시간이 오래 걸릴 SELECT 문은 그동안 다른 세션 테이블의 갱신이 막히고 다른 세션이 느려지고 응답하지 않는 것처럼 보입니다. 세션이 업데이트를 위해 테이블에 대한 단독 액세스를 얻을 때까지 기다리는 동안 SELECT 문을 실행하는 다른 세션은 그 뒤에 줄을 읽기 세션에서 병렬 처리가 낮아집니다.

잠금 성능 문제 해결

다음 항목에서는 테이블 잠금에 의해 발생하는 충돌을 피하거나 줄일 수있는 몇 가지 방법에 대해 설명합니다.

설치시에 CREATE TABLE ... ENGINE=INNODB 을 사용하거나 기존의 테이블에 ALTER TABLE ... ENGINE=INNODB 을 사용하여 테이블을 InnoDB 스토리지 엔진으로 전환하는 것을 고려하고 있습니다. 이 스토리지 엔진에 대한 자세한 내용은 14 장 "InnoDB 저장 엔진" 을 참조하십시오.
테이블을 잠글 시간이 단축되도록 SELECT 문을 최적화하여 실행 속도합니다. 이렇게하려면 몇 가지 요약 테이블을 만들 필요가있을 수 있습니다.
--low-priority-updates 에 mysqld를 시작합니다. 테이블 수준 잠금만을 사용하는 스토리지 엔진 ( MyISAM , MEMORY 및 MERGE 등)의 경우 이렇게하면 테이블을 갱신 (변경)하는 모든 문에 SELECT 문보다 낮은 우선 순위를 부여합니다. 이 경우 앞서 언급 한 시나리오의 두 번째 SELECT 문은 UPDATE 문 앞에 실행 된 첫 번째 SELECT 의 종료를 기다리지 않습니다.
특정 연결에 발행 된 모든 업데이트를 낮은 우선 순위로 실행하도록 지정하려면 low_priority_updates 서버 시스템 변수를 1로 동일하게 설정합니다.
특정 INSERT , UPDATE 또는 DELETE 문에 낮은 우선 순위를 부여하려면 LOW_PRIORITY 속성을 사용합니다.
특정 SELECT 문에 높은 우선 순위를 부여하려면 HIGH_PRIORITY 속성을 사용합니다. 섹션 13.2.9 "SELECT 구문" 을 참조하십시오.
max_write_lock_count 시스템 변수의 값을 낮게 mysqld를 시작하고 테이블에 대한 특정 수의 삽입이 이루어진 후에 테이블을 기다리는 모든 SELECT 문에 우선 순위를 일시적으로 강제로 높일 수 있습니다. 이는 특정 수의 WRITE 잠금 후의 READ 잠금이 허용됩니다.
SELECT 와 함께 INSERT 에 문제가있는 경우는 동시 SELECT 문과 INSERT 문을 지원하는 MyISAM 테이블로 전환을 고려합니다. ( 섹션 8.10.3 "동시 삽입" 을 참조하십시오.)
같은 비 트랜잭션 테이블에 삽입과 삭제를 결합하면 INSERT DELAYED 이 도움이 될 수 있습니다. 섹션 13.2.5.2 "INSERT DELAYED 구문" 을 참조하십시오.
참고
MySQL 5.6.6 현재 INSERT DELAYED 는 사용되지 않으며 이후 릴리스에서 제거됩니다. 대신 INSERT ( DELAYED 를 붙이지 않는)을 사용하십시오.
결합 된 SELECT 및 DELETE 문에 문제가있는 경우, DELETE 에 LIMIT 옵션이 도움이 될 수 있습니다. 섹션 13.2.2 "DELETE 구문" 을 참조하십시오.
SELECT 문에서 SQL_BUFFER_RESULT 를 사용하면 테이블 잠금 시간 단축에 도움이 될 수 있습니다. 섹션 13.2.9 "SELECT 구문" 을 참조하십시오.
테이블의 내용을 별도의 테이블로 분할하면 (쿼리를 하나의 테이블 컬럼에 대해서 실행하여 업데이트를 다른 테이블의 컬럼에 제한함으로써) 도움이 될 수 있습니다.
단일 큐를 사용하도록 mysys/thr_lock.c 잠금 코드를 변경할 수 있습니다. 이 경우, 쓰기 잠금 및 읽기 잠금은 동일한 우선 순위가 일부 응용 프로그램에 유용 할 수 있습니다.