MySQL 5.1은 MyISAM 및 MEMORY 테이블에 대한 테이블 수준 잠금, BDB 테이블에 대한 페이지 수준 잠금, InnoDB를 지원합니다. 테이블. 행 수준 잠금. 대부분의 경우 훈련을 기반으로 어떤 잠금 유형이 애플리케이션에 가장 적합한지 추측하는 것이 가능하지만 일반적으로 특정 잠금 유형이 다른 잠금 유형보다 나은지 여부를 말하기는 어렵습니다. 모든 것은 애플리케이션에 따라 다르며, 애플리케이션의 각 부분에는 다른 잠금 유형이 필요할 수 있습니다. 행 수준 잠금 스토리지 엔진을 사용할지 여부를 결정하려면 애플리케이션이 수행하는 작업과 사용하는 선택 및 업데이트 문 조합을 살펴봐야 합니다. 예를 들어 대부분의 웹 애플리케이션은 선택을 많이 수행하고 삭제를 거의 수행하지 않으며 키 값에 대한 업데이트만 수행하고 특정 테이블 삽입을 몇 번만 수행합니다. 기본 MySQL MyISAM 설정은 잘 조정되어 있습니다.
MySQL에서는 테이블 수준 잠금을 사용하는 스토리지 엔진의 경우 테이블이 잠겨도 교착 상태가 발생하지 않습니다. 이는 항상 쿼리 시작 시 즉시 필요한 모든 잠금을 요청하고 항상 동일한 순서로 테이블을 잠그는 방식으로 관리됩니다.
WRITE의 경우 MySQL에서 사용하는 테이블 잠금 방식은 다음과 같다.
◆ 테이블에 잠금이 설정되어 있지 않으면 쓰기 잠금을 설정한다.
◆ 그렇지 않은 경우 잠금 요청을 쓰기 잠금 대기열에 넣습니다.
READ의 경우 MySQL에서 사용하는 잠금 방식은 다음과 같습니다.
◆테이블에 쓰기 잠금이 없으면 읽기 잠금을 설정합니다.
◆ 그렇지 않으면 잠금 요청을 읽기 잠금 대기열에 넣습니다.
잠금이 해제되면 쓰기 잠금 대기열의 스레드가 잠금을 획득한 다음 읽기 잠금 대기열의 스레드가 잠금을 획득할 수 있습니다.
즉, 테이블에 많은 업데이트가 있는 경우 SELECT 문은 더 이상 업데이트가 없을 때까지 기다립니다.
INSERT 문이 충돌하지 않으면 잠금 없이 MyISAM 테이블에 대해 병렬 INSERT 및 SELECT 문을 자유롭게 혼합할 수 있습니다.
InnoDB는 행 잠금을 사용하고 BDB는 페이지 잠금을 사용합니다. 두 스토리지 엔진 모두에서 교착 상태가 발생할 수 있습니다. 이는 InnoDB가 자동으로 행 잠금을 획득하고 BDB가 트랜잭션이 시작될 때가 아닌 SQL 문 처리 중에 페이지 잠금을 획득하기 때문입니다.
행 수준 잠금의 장점:
· 여러 스레드에서 서로 다른 행에 액세스할 때 잠금 충돌이 거의 발생하지 않습니다.
· 적은 수의 변경 사항만 적용하여 롤백합니다.
· 단일 행을 오랫동안 잠글 수 있습니다.
행 수준 잠금의 단점:
· 페이지 수준 또는 테이블 수준 잠금보다 더 많은 메모리를 차지합니다.
· 테이블의 큰 부분에 사용될 경우 더 많은 잠금을 획득해야 하므로 페이지 수준 또는 테이블 수준 잠금보다 느립니다.
· 대부분의 데이터에 대해 GROUP BY 작업을 자주 수행하거나 전체 테이블을 자주 스캔해야 하는 경우 다른 잠금보다 속도가 상당히 느려집니다.
· 높은 수준의 잠금을 사용하면 잠금 비용이 행 수준 잠금보다 낮기 때문에 다양한 유형의 잠금을 지원하여 애플리케이션을 쉽게 확장할 수도 있습니다.
다음과 같은 경우 테이블 잠금은 페이지 수준 또는 행 수준 잠금보다 우선합니다.
· 테이블에 있는 대부분의 문이 읽기에 사용됩니다.
· 엄격한 키를 사용하여 읽고 업데이트하면 단일 읽기 키로 추출할수
있는 행을 업데이트하거나 삭제할 수 있습니다.
• UPDATE tbl_name SET 열 = 값 WHERE Unique_key_col = key_value
• DELETE FROM tbl_name WHERE Unique_key_col = key_value;
병렬 INSERT 문과 매우 적은 UPDATE 또는 DELETE 문.
· 쓰기 작업 없이 전체 테이블에 대해 많은 스캔 또는 GROUP BY 작업이 있습니다.
행 수준 또는 페이지 수준 잠금과 다른 옵션:
· 버전 관리(예: 병렬 삽입을 위해 MySQL에서 사용되는 기술). 여기서는 하나의 쓰기 작업과 여러 읽기 작업이 동시에 있을 수 있습니다. 이는 데이터베이스 또는 테이블이 액세스가 시작되는 시점에 따라 다양한 데이터 보기를 지원함을 의미합니다. 다른 일반적인 용어로는 "시간 추적", "기록 시 복사" 또는 "요청 시 복사"가 있습니다.
· 주문형 복제는 대부분의 경우 페이지 수준 또는 행 수준 잠금보다 우선합니다. 그러나 최악의 경우 일반 잠금을 사용하는 것보다 더 많은 메모리를 사용할 수 있습니다.
· 행 수준 잠금 외에도 MySQL에서는 GET_LOCK() 및 RELEASE_LOCK()과 같은 응용 프로그램 수준 잠금을 사용할 수 있습니다. 이는 권고 잠금이며 제대로 실행되는 응용 프로그램에서만 작동합니다.
최대 잠금 속도를 달성하기 위해 MySQL은 InnoDB 및 BDB를 제외한 모든 스토리지 엔진에 대해 (페이지, 행 또는 열 잠금 대신) 테이블 잠금을 사용합니다. InnoDB 및 BDB 테이블의 경우 MySQL은 LOCK TABLES로 테이블을 명시적으로 잠그는 경우에만 테이블 잠금을 사용합니다. InnoDB는 자동 행 수준 잠금을 사용하고 BDB는 트랜잭션 격리를 보장하기 위해 페이지 수준 잠금을 사용하기 때문입니다.
그러나 대형 테이블의 경우 테이블 잠금은 대부분의 애플리케이션에서 행 잠금보다 우수하지만 몇 가지 단점이 있습니다. 테이블 잠금을 사용하면 많은 스레드가 동시에 테이블에서 읽을 수 있지만 스레드가 테이블에 쓰려면 먼저 독점 액세스 권한을 얻어야 합니다. 업데이트 중에 테이블에 액세스하려는 다른 모든 스레드는 업데이트가 완료될 때까지 기다려야 합니다.
테이블 업데이트는 일반적으로 테이블 검색보다 더 중요한 것으로 간주되므로 더 높은 우선순위가 부여됩니다. 이렇게 하면 테이블에 SELECT 활동이 많더라도 테이블 업데이트 활동이 중단되지 않도록 할 수 있습니다.
테이블 잠금은 하드 디스크가 가득 차서 스레드가 처리할 수 있으려면 여유 공간이 있어야 하기 때문에 스레드가 대기하는 경우와 같은 상황에서 문제를 일으킬 수 있습니다. 이 경우 문제의 테이블에 액세스하려는 모든 스레드도 더 많은 하드 디스크 공간을 사용할 수 있을 때까지 대기 상태가 됩니다.
테이블 잠금은 다음과 같은 상황에서도 문제가 됩니다.
· 고객이 장기 실행 쿼리를 실행합니다.
· 그런 다음 다른 클라이언트가 동일한 테이블을 업데이트합니다. 클라이언트는 SELECT가 완료될 때까지 기다려야 합니다.
· 다른 클라이언트가 동일한 테이블에 대해 또 다른 SELECT 문을 실행합니다. UPDATE는 SELECT보다 우선순위가 높기 때문에 SELECT 문은 UPDATE가 완료될 때까지 기다리고 첫 번째 SELECT가 완료될 때까지 기다립니다.
테이블 잠금으로 인한 경합을 피하거나 줄이기 위한 몇 가지 방법이 아래에 설명되어 있습니다.
· SELECT 문을 더 빠르게 실행해 보십시오. 이를 수행하려면 일부 요약 테이블을 작성해야 할 수도 있습니다.
· --low-priority-updates를 사용하여 mysqld를 시작합니다. 이렇게 하면 테이블을 업데이트(수정)하는 모든 문에 SELECT 문보다 낮은 우선 순위가 부여됩니다. 이 경우 첫 번째 SELECT가 완료될 때까지 기다리지 않고 UPDATE 문 이전에 이전 상황의 두 번째 SELECT 문이 실행됩니다.
· SET LOW_PRIORITY_UPDATES=1 문을 사용하여 특정 연결의 모든 업데이트가 낮은 우선 순위를 사용하도록 지정할 수 있습니다.
· LOW_PRIORITY 속성을 사용하여 특정 INSERT, UPDATE 또는 DELETE 문에 더 낮은 우선 순위를 부여할 수 있습니다.
· HIGH_PRIORITY 속성을 사용하여 특정 SELECT 문에 더 높은 우선 순위를 부여할 수 있습니다.
· mysqld를 시작하려면 max_write_lock_count 시스템 변수에 낮은 값을 지정하여 MySQL이 특정 개수의 삽입이 완료된 후 테이블을 기다리는 모든 SELECT 문의 우선 순위를 일시적으로 높이도록 합니다. 이를 통해 특정 수의 WRITE 잠금 후에 READ 잠금을 부여할 수 있습니다.
· SELECT와 결합된 INSERT에 문제가 있는 경우 동시 SELECT 및 INSERT를 지원하는 새로운 MyISAM 테이블을 사용하도록 전환하세요.
· 동일한 테이블에서 삽입과 삭제를 혼합하는 경우 INSERT DELAYED가 큰 도움이 될 것입니다.
· 동일한 테이블에서 SELECT 문과 DELETE 문을 혼합하는 데 문제가 있는 경우 DELETE의 LIMIT 옵션이 도움이 될 수 있습니다.
· SELECT 문에 SQL_BUFFER_RESULT를 사용하면 테이블 잠금 시간을 단축하는 데 도움이 될 수 있습니다.
· mysys/thr_lock.c의 잠금 코드는 단일 대기열을 사용하도록 변경할 수 있습니다. 이 경우 쓰기 잠금과 읽기 잠금의 우선순위가 같으므로 일부 애플리케이션에 도움이 될 수 있습니다.
다음은 MySQL의 테이블 잠금과 관련된 몇 가지 팁입니다.
· 동일한 테이블에서 많은 행을 확인해야 하는 선택 사항과 업데이트를 혼합하지 않는 경우 병렬 작업을 수행할 수 있습니다.
· 하나의 잠금에 있는 많은 업데이트가 잠금이 없는 업데이트보다 훨씬 빠르기 때문에 LOCK TABLES를 사용하여 속도를 향상시킬 수 있습니다. 테이블의 내용을 여러 테이블로 분할하는 것도 도움이 될 수 있습니다.
· MySQL에서 테이블을 잠글 때 속도 문제가 발생하는 경우 테이블을 InnoDB 또는 BDB 테이블로 변환하여 성능을 향상시킬 수 있습니다.