동시에 데이터를 수정해야 하는 쿼리가 여러 개 있을 때마다 동시성 제어 문제가 발생합니다. 여기서는 서버 계층과 스토리지 엔진 계층이라는 두 가지 수준에서 MySQL의 동시성 제어를 논의합니다. 동시성 제어는 거대한 주제이며, 이에 대해 자세히 논의하는 이론적 문헌이 많이 있습니다. 여기서는 MySQL이 동시 읽기 및 쓰기를 제어하는 방법을 간략하게 설명합니다.

Unix 시스템의 이메일 상자를 예로 들면 일반적인 mbox 파일 형식은 매우 간단합니다. mbox 사서함의 모든 이메일은 함께 직렬화되어 끝에서 끝까지 연결됩니다. 이 형식은 Feixi 이메일 정보를 읽는 데 매우 적합하며, 파일 끝에 새 이메일 내용을 추가하기만 하면 이메일 전달도 쉽습니다.

그러나 두 프로세스가 동시에 동일한 사서함으로 메일을 배달하면 어떻게 될까요? 당연히 메일함의 데이터는 삭제되고 두 이메일의 내용은 메일함 파일 끝에 추가됩니다. 잘 설계된 사서함 배달 시스템은 잠금을 사용하여 데이터 손상을 방지합니다. 고객이 메일을 배달하려고 할 때 다른 고객이 사서함을 잠근 경우 배달이 이루어지기 전에 잠금이 해제될 때까지 기다려야 합니다.

이 잠금 방식은 실제 애플리케이션 환경에서는 잘 작동하지만 동시 처리를 지원하지 않습니다. 언제든지 하나의 프로세스만 사서함 데이터를 수정할 수 있으므로 이는 대용량 사서함 시스템에서 문제가 됩니다.

읽기-쓰기 잠금

우편함에서 데이터를 읽는 데는 이러한 문제가 없으며 여러 사용자가 동시에 읽어도 문제가 없습니다. 읽어도 데이터가 수정되지 않으므로 오류가 없습니다. 하지만 한 고객이 메일함을 읽고 있는 동안 다른 사용자가 이메일 번호 25를 삭제하려고 하면 어떻게 될까요? 결론은 불확실합니다. 읽기 클라이언트가 오류로 종료되거나 일관된 사서함 데이터를 읽지 못할 수도 있습니다. 따라서 보안상의 이유로 사서함을 읽는 것에도 특별한 주의가 필요합니다.

위의 사서함을 데이터베이스의 테이블로 처리하고 이메일을 테이블의 행으로 처리하면 동일한 문제가 여전히 존재한다는 것을 쉽게 알 수 있습니다. 여러 면에서 사서함은 단순한 데이터베이스 테이블입니다. 데이터베이스 테이블의 레코드를 수정하는 것은 사서함의 이메일 정보를 삭제하거나 수정하는 것과 매우 유사합니다.

이 고전적인 문제에 대한 해결책은 동시성 제어(읽기 잠금 및 쓰기 잠금)입니다. 실제로 동시 읽기나 쓰기를 처리할 때 두 가지 유형의 잠금으로 구성된 잠금 시스템을 구현하면 문제를 해결할 수 있습니다. 이 두 가지 유형의 잠금을 일반적으로 공유 잠금 및 배타적 잠금이라고 하며, 읽기 잠금 및 쓰기 잠금이라고도 합니다.

여기에서는 잠금의 개념을 설명하지 않겠습니다. 읽기 잠금은 공유되거나 서로를 차단하지 않습니다. 여러 클라이언트가 서로 간섭하지 않고 동시에 동일한 리소스를 읽을 수 있습니다. 쓰기 잠금은 배타적이며, 이는 쓰기 잠금이 다른 쓰기 잠금 및 읽기 잠금을 차단한다는 것을 의미합니다. 이는 보안 정책 고려 사항에 따른 것입니다. 이 방법을 통해서만 주어진 시간에 한 명의 사용자만 쓰기를 수행할 수 있습니다. 다른 사용자가 기록 중인 동일한 리소스를 읽지 못하도록 합니다.

실제 데이터베이스 시스템에서는 매 순간 잠금이 발생합니다. 사용자가 데이터의 특정 부분을 수정하면 MySQL은 잠금을 통해 다른 사용자가 동일한 데이터를 읽지 못하게 합니다. 대부분의 경우 MySQL 잠금의 내부 관리는 투명합니다.

잠금 세분성

공유 리소스에 동시성을 제공하는 한 가지 방법은 잠금 개체를 더 선택적으로 만드는 것입니다. 모든 리소스가 아닌 수정해야 하는 데이터의 일부만 잠그십시오. 보다 이상적인 접근 방식은 수정될 데이터 부분만 정확하게 잠그는 것입니다(특히 수정된 필드를 잠그는 것). 언제든지 특정 리소스에서 잠긴 데이터의 양이 적을수록 서로 충돌이 없는 한 시스템의 동시성이 높아집니다.

문제는 잠금에도 리소스가 소모된다는 점입니다. 잠금 획득, 잠금 해제 여부 확인, 잠금 해제 등 다양한 잠금 작업은 시스템 오버헤드를 증가시킵니다. 시스템이 데이터에 액세스하는 대신 잠금을 관리하는 데 많은 시간을 소비하면 시스템 성능이 저하될 수 있습니다.

소위 잠금 전략은 잠금 오버헤드와 데이터 보안 사이의 균형을 찾는 것입니다. 물론 이러한 균형은 성능에 영향을 미칩니다. 대부분의 상용 데이터베이스 시스템은 더 많은 옵션을 제공하지 않으며 일반적으로 행 수준 잠금을 적용합니다. 잠금이 많을 때 최대한 더 나은 성능을 제공하기 위해 테이블을 구성하고 이를 다양하고 복잡한 방식으로 구현합니다.

MySQL은 다양한 옵션을 제공하며, 각 MySQL 스토리지 엔진은 자체 잠금 전략과 잠금 세분성을 구현할 수 있습니다. 잠금 관리는 스토리지 엔진 설계에 있어 매우 중요한 결정입니다. 특정 수준에서 잠금 세분성을 수정하면 특정 애플리케이션 시나리오에 더 나은 성능을 제공할 수 있습니다. 그러나 동료들은 다른 애플리케이션 시나리오에 대한 좋은 지원을 잃게 됩니다. 다행스럽게도 MySQL은 여러 스토리지 엔진 아키텍처를 지원하므로 단일 범용 솔루션이 필요하지 않습니다. 가장 중요한 두 가지 잠금 전략이 아래에 소개되어 있습니다.

테이블 잠금(table lock)

테이블 잠금은 MySQL에서 가장 기본적인 잠금 전략이자 오버헤드가 가장 적은 전략이다. 테이블 잠금은 앞에서 설명한 메일함 잠금 메커니즘과 매우 유사합니다. 즉, 전체 테이블을 잠급니다. 사용자가 테이블에 대한 쓰기 작업(삽입, 삭제, 업데이트 등)을 수행하려면 쓰기 잠금을 획득해야 합니다. 이렇게 하면 다른 사용자가 테이블에 대한 모든 읽기 및 쓰기 작업을 차단할 수 있습니다. 쓰기 잠금이 없는 경우에만 다른 읽기 사용자가 읽기 잠금을 얻을 수 있으며 읽기 잠금은 서로를 차단하지 않습니다.

테이블 잠금 장치는 특정 시나리오에서 좋은 성능을 발휘할 수도 있습니다. 예를 들어 읽기 로컬 테이블 잠금은 특정 유형의 동시 쓰기 작업을 지원합니다. 또한 쓰기 잠금도 읽기 잠금보다 우선순위가 높으므로 쓰기 잠금 요청이 읽기 잠금 큐 앞에 삽입될 수 있습니다(쓰기 잠금은 잠금 큐의 읽기 잠금 앞에 삽입될 수 있지만 읽기 잠금은 읽기 잠금 앞에 삽입될 수 있음). 잠금은 삽입할 수 없음)을 쓰기 잠금 앞에 삽입할 수 없습니다.

스토리지 엔진이 자체 잠금을 관리할 수 있지만 Mysql 자체는 다양한 목적을 달성하기 위해 다양하고 효과적인 테이블 잠금을 사용합니다. 예를 들어, 서버는 스토리지 엔진의 잠금 메커니즘을 무시하고 alter table과 같은 명령문에 테이블 잠금을 사용합니다.

[참고: 잠금 메커니즘은 스토리지 엔진에 의해 관리되지만 MySQL 자체에서 이 잠금 메커니즘을 강제로 관리하는 경우도 있습니다.]

행 잠금(row lock)

행 수준 잠금은 동시 처리를 최대한 지원할 수 있습니다(그러나 잠금 오버헤드도 가장 큽니다) . InnoDB, XtraDB 및 일부 다른 스토리지 엔진에서 행 수준 잠금이 구현된다는 것은 잘 알려져 있습니다. 행 수준 잠금은 스토리지 엔진 계층에서만 구현되며 MySQL 서버 계층(필요한 경우 이전 기사의 논리 아키텍처 다이어그램을 검토하세요)은 구현되지 않습니다. 서버 계층은 스토리지 엔진의 잠금 구현에 대한 지식이 없습니다. 이 장과 책 전반에 걸쳐 모든 스토리지 엔진은 고유한 방식으로 잠금 메커니즘을 나타냅니다.

읽기-쓰기 잠금

잠금 세분성

테이블 잠금(table lock)

테이블 잠금은 MySQL의 가장 기본적인 잠금 전략이자 오버헤드가 가장 적은 전략이다. 테이블 잠금은 앞에서 설명한 메일함 잠금 메커니즘과 매우 유사합니다. 즉, 전체 테이블을 잠급니다. 사용자가 테이블에 대한 쓰기 작업(삽입, 삭제, 업데이트 등)을 수행하려면 쓰기 잠금을 획득해야 합니다. 이렇게 하면 다른 사용자가 테이블에 대한 모든 읽기 및 쓰기 작업을 차단할 수 있습니다. 쓰기 잠금이 없는 경우에만 다른 읽기 사용자가 읽기 잠금을 얻을 수 있으며 읽기 잠금은 서로를 차단하지 않습니다.

[참고: 잠금 메커니즘은 스토리지 엔진에 의해 관리되지만 MySQL 자체에서 이 잠금 메커니즘을 강제로 관리하는 경우도 있습니다.]