동시 데이터 액세스의 일관성과 효율성을 보장하는 방법은 모든 데이터베이스가 해결해야 하는 문제입니다. 잠금 충돌도 데이터베이스 동시 액세스 성능에 영향을 미치는 중요한 요소입니다. 이러한 관점에서 잠금은 데이터베이스에 특히 중요하고 복잡합니다.
MySQL 잠금 개요
다른 데이터베이스와 비교하여 MySQL의 잠금 메커니즘은 상대적으로 간단합니다. 가장 중요한 특징은 다양한 스토리지 엔진이 다양한 잠금 메커니즘을 지원한다는 것입니다.
예를 들어
MyISAM 및 MEMORY 스토리지 엔진은 테이블 수준 잠금을 사용합니다.
InnoDB 스토리지 엔진은 행 수준 잠금과 테이블 수준 잠금을 모두 지원하지만 기본적으로 행 수준 잠금이 사용됩니다.
MySQL의 세 가지 잠금 특성은 대략 다음과 같이 요약할 수 있습니다.
테이블 수준 잠금: 낮은 오버헤드, 빠른 잠금, 큰 잠금 세분성, 가장 높은 잠금 충돌 가능성 및 가장 낮은 동시성.
행 수준 잠금: 높은 오버헤드, 느린 잠금, 교착 상태가 발생할 수 있습니다. 잠금 세분성은 가장 작고 잠금 충돌 가능성은 가장 낮으며 동시성은 가장 높습니다.
페이지 잠금: 비용과 잠금 시간은 테이블 잠금과 행 잠금 사이에 있습니다. 교착 상태는 테이블 잠금과 행 잠금 사이에 있으며 동시성은 평균입니다.
잠금의 관점에서 볼 때, 테이블 수준 잠금은 주로 쿼리 기반이고 웹 애플리케이션과 같이 인덱스 조건에 따라 소량의 데이터만 업데이트되는 애플리케이션에 더 적합하지만 행 수준 잠금은 더 적합합니다. 일부 온라인 트랜잭션 처리(OLTP) 시스템과 같이 소량의 서로 다른 데이터 및 동시 쿼리 애플리케이션을 기반으로 하는 다수의 동시 업데이트가 있는 애플리케이션에 적합합니다. 다음 섹션에서는 MySQL 테이블 잠금 및 InnoDB 행 잠금 문제에 중점을 둡니다.
MyISAM 테이블 잠금
MyISAM 스토리지 엔진은 MySQL의 처음 몇 버전에서 지원되는 유일한 잠금 유형이기도 한 테이블 잠금만 지원합니다. 트랜잭션 무결성 및 동시성에 대한 애플리케이션 요구 사항이 지속적으로 개선됨에 따라 MySQL은 트랜잭션 기반 스토리지 엔진을 개발하기 시작했습니다. 이후 페이지 잠금을 지원하는 BDB 스토리지 엔진과 행 잠금을 지원하는 InnoDB 스토리지 엔진이 서서히 등장했습니다. 현재 Oracle에 인수된 회사). 그러나 MyISAM의 테이블 잠금은 여전히 가장 널리 사용되는 잠금 유형입니다. 이 섹션에서는 MyISAM 테이블 잠금의 사용을 자세히 소개합니다.
테이블 수준 잠금 경합 쿼리
table_locks_waited 및 table_locks_immediate 상태 변수를 확인하여 시스템의 테이블 잠금 경합을 분석할 수 있습니다.
mysql> show status like 'table%';
| | 값 |
| Table_locks_immediate | 2979 |
| Table_locks_waited | 0 |
2행
Table_locks_waited 값이 상대적으로 높은 경우 , 그러면 이는 심각한 테이블 수준 잠금 경합이 있음을 나타냅니다. MySQL 테이블 수준 잠금 잠금 모드MySQL 테이블 수준 잠금에는 테이블 공유 읽기 잠금(Table Read Lock)과 테이블 독점 쓰기 잠금(Table Write Lock)의 두 가지 모드가 있습니다. MyISAM 테이블의 읽기 작업은 동일한 테이블에 대한 다른 사용자의 읽기 요청을 차단하지 않지만 동일한 테이블의 쓰기 요청은 차단합니다. MyISAM 테이블의 쓰기 작업은 다른 사용자의 읽기 및 쓰기를 차단합니다.
쓰기 작업뿐만 아니라 MyISAM 테이블의 읽기 및 쓰기 작업도 연속적입니다.
스레드가 테이블에 대한 쓰기 잠금을 획득하면 잠금을 보유한 스레드만 테이블을 업데이트할 수 있습니다. . 다른 스레드의 읽기 및 쓰기 작업은 잠금이 해제될 때까지 기다립니다.
)
Session2가 잠금을 가져오면 쿼리는 다음을 반환합니다. mysql> film_id = 1001;1행(57.59초)
테이블 잠금을 추가하는 방법은 무엇인가요?
MyISAM은 쿼리 문(SELECT)을 실행하기 전에 관련된 모든 테이블에 자동으로 읽기 잠금을 추가합니다. 업데이트 작업(UPDATE, DELETE, INSERT 등)을 실행하기 전에 관련된 테이블에 자동으로 쓰기 잠금을 추가합니다. not 사용자 개입이 필요하지 않습니다. 따라서 일반적으로 사용자는 MyISAM 테이블을 명시적으로 잠그기 위해 LOCK TABLE 명령을 직접 사용할 필요가 없습니다. 예제에서는 편의를 위해 기본적으로 명시적 잠금을 수행했으며 필수는 아닙니다.
MyISAM 테이블의 디스플레이 잠금은 일반적으로 트랜잭션 작업을 어느 정도 시뮬레이션하고 특정 시점에 여러 테이블을 일관되게 읽기 위해 수행됩니다. 예를 들어, 각 주문의 총액을 기록하는 주문 테이블이 있고, 각 주문의 각 제품의 소계 금액을 기록하는 주문 세부 정보 테이블인 order_detail도 있다고 가정해 보겠습니다. 총액이 일치하는지 확인하려면 다음 두 SQL을 실행해야 할 수 있습니다.
주문에서 합계(총액)를 선택합니다.
order_detail에서 합계(소계)를 선택합니다. 두 개의 테이블을 먼저 실행하는 동안 order_detail 테이블이 변경되었을 수 있으므로 잘못된 결과가 생성될 수 있습니다. 따라서 올바른 방법은 다음과 같습니다.
테이블 주문 잠금은 로컬 읽기, order_detail은 로컬 읽기;
주문에서 합계(총액) 선택;
order_detail에서 합계(소계) 선택;
테이블 잠금 해제;
특별 지침 다음 두 가지 점.
위의 예에서는 LOCK TABLES 시 "local" 옵션을 추가합니다. 해당 기능은 MyISAM 테이블의 동시 삽입 조건이 충족될 때 다른 사용자가 테이블 끝에 레코드를 동시에 삽입할 수 있도록 하는 것입니다. MyISAM 테이블에 대해서는 나중에 자세히 설명하겠습니다.
LOCK TABLES를 사용하여 테이블에 테이블 잠금을 명시적으로 추가하는 경우 테이블에 관련된 모든 잠금을 동시에 획득해야 하며 MySQL은 잠금 업그레이드를 지원하지 않습니다. 즉, LOCK TABLES를 실행한 후에는 명시적으로 잠긴 테이블에만 액세스할 수 있지만 동시에 잠금 해제된 테이블에는 액세스할 수 없습니다. 읽기 잠금을 추가하면 쿼리 작업만 수행할 수 있고 업데이트 작업은 수행할 수 없습니다. 실제로 자동 잠금의 경우 기본적으로 MyISAM은 항상 SQL 문에서 요구하는 모든 잠금을 한 번에 획득합니다. 이것이 MyISAM 테이블이 교착 상태에 빠지지 않는 이유입니다(Deadlock Free).
A 세션은 LOCK TABLE 명령을 사용하여 film_text 테이블에 읽기 잠금을 추가합니다. 이 세션은 잠긴 테이블의 레코드를 쿼리할 수 있지만 동시에 다른 테이블을 업데이트하거나 액세스하면 오류가 표시됩니다. 테이블의 레코드를 쿼리할 수 있지만 업데이트할 때 잠금 대기가 발생합니다.
LOCK TABLES를 사용할 경우 사용되는 모든 테이블을 한번에 잠궈야 할 뿐만 아니라, SQL 문에 같은 테이블이 몇 번 나타나더라도 SQL 문과 동일한 별칭을 통해 잠궈야 합니다. , 그렇지 않으면 오류가 발생합니다!
예제는 다음과 같습니다.
(1) 행위자 테이블에 대한 읽기 잠금 획득:
mysql> lock table actor read;
Query OK, 0 행이 영향을 받음(0.00초)
(2) 그러나 별칭을 통해 액세스하면 오류가 발생합니다.
mysql> a.first_name,a.last_name,b.first_name,b.last_name을 액터 a,actor b에서 선택합니다. 여기서 a.first_name = b.first_name 및 a.first_name = 'Lisa' 및 a.last_name = 'Tom' 및 a .last_name b.last_name;
ERROR 1100 (HY000): 테이블 'a'가 LOCK TABLES
(3) 별칭을 별도로 잠가야 합니다.
mysql> ,actor as b read;
Query OK, 0 행 영향을 받음(0.00초)
(4) 별칭에 따른 쿼리는 올바르게 실행될 수 있습니다:
mysql> select a.first_name,a.last_name,b.first_name ,b.last_name 배우 a,배우 b 여기서 a.first_name = b.first_name 및 a.first_name = 'Lisa' 및 a.last_name = 'Tom' 및 a.last_name b.last_name;
| noDB_row_lock_current_waits | 0 |
5개 행 세트(0.01초)
InnoDB_row_lock_waits 및 InnoDB_row_lock_time_avg 값이 상대적으로 높은 등 잠금 경합이 심각하다고 판단되면 InnoDB 모니터를 설정하여 테이블, 데이터를 추가로 관찰할 수도 있습니다. 잠금 충돌이 발생한 행 등을 분석하고 잠금 경합 원인을 분석합니다.
구체적인 방법은 다음과 같습니다:
mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
Query OK, 0 행이 영향을 받았습니다. (0.14초)
그런 다음 다음 문을 사용하여 볼 수 있습니다.
mysql> innodb 상태 표시 콘텐츠에는 테이블 이름, 잠금 유형, 잠금 기록 상태 등을 포함하여 현재 잠금 대기에 대한 자세한 정보가 있어 추가 분석 및 문제 파악이 용이합니다. 모니터를 연 후에는 기본적으로 15초마다 모니터링된 내용이 로그에 기록됩니다. 장시간 열어두면 .err 파일의 용량이 매우 커지므로 사용자는 문제의 원인을 확인한 후에 조치를 취해야 합니다. 모니터를 닫으려면 모니터링 테이블을 삭제하거나 "--console" 옵션을 사용하여 서버를 시작하여 로그 파일 쓰기를 해제해야 합니다.
5. InnoDB의 행 잠금 모드 및 잠금 방법
InnoDB는 다음 두 가지 유형의 행 잠금을 구현합니다.
공유 잠금(S): 하나의 트랜잭션이 행을 읽을 수 있도록 허용하고 다른 트랜잭션이 동일한 데이터 세트에 대해 배타적 잠금을 획득하는 것을 방지합니다.
배타적 잠금(X): 배타적 잠금을 획득한 트랜잭션이 데이터를 업데이트하도록 허용하고, 다른 트랜잭션이 동일한 데이터 세트에 대해 공유 읽기 잠금 및 배타적 쓰기 잠금을 획득하지 못하도록 합니다.
또한 행 잠금과 테이블 잠금이 공존할 수 있도록 하고 다중 입도 잠금 메커니즘을 구현하기 위해 InnoDB에는 내부적으로 사용되는 두 가지 의도 잠금(Intention Lock)도 있습니다.
두 의도 잠금은 모두 테이블 잠금입니다.
의도 공유 잠금(IS): 트랜잭션은 데이터 행에 공유 잠금을 추가하려고 합니다. 트랜잭션은 데이터 행에 공유 잠금을 추가하기 전에 먼저 테이블의 IS 잠금을 획득해야 합니다.
의도 배타적 잠금(IX): 트랜잭션은 데이터 행에 배타적 잠금을 추가하기 전에 먼저 테이블의 IX 잠금을 획득해야 합니다.
트랜잭션에서 요청한 잠금 모드가 현재 잠금과 호환되면 InnoDB는 요청된 잠금을 트랜잭션에 부여합니다. 그렇지 않고 둘이 호환되지 않으면 트랜잭션은 잠금이 해제될 때까지 기다립니다. . 의도 잠금은 InnoDB에 의해 자동으로 추가되며 사용자 개입이 필요하지 않습니다.요약은 다음과 같습니다. 
2 일반 SELECT 문의 경우에는 잠금을 추가하지 않습니다.
3 . 트랜잭션은 다음 명령문을 통해 공유 잠금 또는 독점 잠금을 추가할 수 있습니다.
공유 잠금(S): SELECT * FROM table_name WHERE ... 공유 모드에서 잠금.
독점 잠금(X): SELECT * FROM table_name WHERE ... FOR UPDATE.
공유 잠금을 얻으려면 SELECT ... IN을 사용하십시오. 이는 주로 데이터 종속성이 필요할 때 특정 레코드 행이 존재하는지 확인하고 이 레코드에 대해 누구도 UPDATE 또는 DELETE 작업을 수행하지 않도록 하는 데 사용됩니다.
그러나 현재 트랜잭션도 레코드를 업데이트해야 하는 경우 교착 상태가 발생할 가능성이 높습니다. 행 레코드를 잠근 후 업데이트해야 하는 애플리케이션의 경우 SELECT... FOR UPDATE 메서드를 사용하여 전용 자물쇠.
6. InnoDB 행 잠금 구현 방법
InnoDB 행 잠금은 인덱스 항목을 잠그는 방식으로 구현됩니다. 이는 MySQL 및 Oracle과 다릅니다. 후자는 데이터 블록에서 해당 데이터 행을 잠그는 방식으로 구현됩니다.
InnoDB의 행 잠금 구현 기능은 InnoDB가 인덱스 조건을 통해 데이터를 검색할 때만 행 수준 잠금을 사용한다는 것을 의미합니다. 그렇지 않으면 InnoDB는 테이블 잠금을 사용합니다!
실제 애플리케이션에서는 InnoDB 행 잠금의 이 기능에 특별한 주의를 기울여야 합니다. 그렇지 않으면 많은 잠금 충돌이 발생하여 동시성 성능에 영향을 미칠 수 있습니다.
(1) 인덱스 조건 없이 쿼리할 때 InnoDB는 행 잠금 대신 테이블 잠금을 사용합니다.
(2) MySQL의 행 잠금은 레코드에 대한 잠금이 아닌 인덱스에 대한 잠금이므로 서로 다른 행의 레코드에 액세스하더라도
동일한 인덱스 키를 사용하면 잠금 충돌이 발생합니다. 애플리케이션을 디자인할 때 이 점에 주의하시기 바랍니다.
(3) 테이블에 여러 개의 인덱스가 있는 경우 서로 다른 트랜잭션은 서로 다른 인덱스를 사용하여 서로 다른 행을 잠글 수 있습니다.
또한 InnoDB는 기본 키 인덱스, 고유 인덱스 또는 일반 데이터 잠금을 사용하든 행 잠금을 사용합니다.
(4) 조건에 인덱스 필드가 사용되더라도 데이터 검색에 인덱스를 사용할지 여부는 MySQL이 다양한 실행 계획의 비용을 판단하여 결정합니다. 일부 작은 테이블의 경우 인덱스를 사용하지 않습니다. 이 경우 InnoDB는 행 잠금 대신 테이블 잠금을 사용합니다. 따라서 잠금 충돌을 분석할 때는 SQL 실행 계획을 확인하여 해당 인덱스가 실제로 사용되는지 확인하는 것을 잊지 마세요. MySQL이 인덱스를 사용하지 않는 상황에 대한 자세한 설명은 이 장의 "인덱스 문제" 섹션 소개를 참조하세요.
7. 갭 잠금(Next-Key 잠금)
동등 조건 대신 범위 조건을 사용하여 데이터를 검색하고 공유 또는 배타적 잠금을 요청하면 InnoDB는 키 값이 조건 범위 내에 있지만 조건을 충족하지 않는 레코드에 대해 조건을 충족하는 기존 데이터 레코드의 인덱스 항목을 잠급니다. "GAP"라고 불리는 존재하는 경우 InnoDB는 이 "gap"도 잠급니다. 이 잠금 메커니즘은 소위 gap 잠금(Next-Key 잠금)입니다. emp 테이블에 101개의 레코드만 있는 경우 empid 값은 1,2,...,100,101입니다. 다음 SQL은 다음과 같습니다.
Select * from emp 여기서 empid >
는 범위입니다. 조건 검색을 통해 InnoDB는 조건을 충족하는 empid 값이 101인 레코드를 잠글 뿐만 아니라 empid가 101보다 큰 "간격"도 잠급니다(이러한 레코드는 존재하지 않습니다). 갭 잠금을 사용하는 InnoDB의 목적은 한편으로는 팬텀 읽기를 방지하고 관련 격리 수준의 요구 사항을 충족하는 것입니다. 위의 예에서 갭 잠금을 사용하지 않는 경우 다른 트랜잭션에서 emid가 100보다 큰 레코드를 삽입하는 경우입니다. , 이 트랜잭션이 위 명령문을 다시 실행하면 반면에 팬텀 읽기가 발생합니다. 이는 복구 및 복제 요구를 충족하기 위한 것입니다. 잠금 메커니즘에 대한 복구 및 복제의 영향과 다양한 격리 수준에서 InnoDB의 갭 잠금 사용은 후속 장에서 자세히 소개됩니다.
분명히 범위 조건을 사용하여 레코드를 검색하고 잠그는 경우 InnoDB의 잠금 메커니즘은 자격을 갖춘 범위 내에서 키 값의 동시 삽입을 차단하므로 심각한 잠금 대기가 발생하는 경우가 많습니다. 따라서 실제 애플리케이션 개발, 특히 동시 삽입이 많은 애플리케이션에서는 비즈니스 로직을 최적화하고, 업데이트 데이터에 액세스하기 위해 동일한 조건을 사용하고, 범위 조건을 사용하지 않도록 노력해야 합니다.
특별 참고 사항은 InnoDB가 범위 조건을 통해 잠글 때 갭 잠금을 사용하는 것 외에도 존재하지 않는 레코드에 대한 잠금을 요청하기 위해 동일한 조건을 사용하는 경우에도 갭 잠금을 사용한다는 것입니다!
복구 및 복제의 필요성, InnoDB 잠금 메커니즘에 미치는 영향
MySQL은 BINLOG를 사용하여 데이터를 업데이트하는 INSERT, UPDATE, DELETE 및 기타 SQL 문의 성공적인 실행을 기록하고 이를 통해 복구 및 마스터-슬레이브 복제를 실현합니다. MySQL 데이터베이스의. MySQL의 복구 메커니즘(복제는 실제로 Slave Mysql에서 지속적인 BINLOG 기반 복구임)은 다음과 같은 특징을 가지고 있습니다.
첫 번째, MySQL 복구는 SQL 문 수준, 즉 BINLOG에서 SQL 문을 다시 실행하는 것입니다. 이는 데이터베이스 파일 블록을 기반으로 하는 Oracle 데이터베이스와 다릅니다.
둘째, MySQL의 BINLOG는 트랜잭션이 제출된 순서대로 기록되며, 복구도 이 순서대로 수행됩니다. 이는 Oracle과도 다릅니다. Oracle은 SCN(시스템 변경 번호)에 따라 데이터를 복원합니다. Oracle은 SCN의 순서와 트랜잭션 시작 시간 순서가 일관되게 할당됩니다.
위의 두 가지 점에서 우리는 MySQL의 복구 메커니즘에 다음이 필요하다는 것을 알 수 있습니다. 트랜잭션이 제출되기 전에 다른 동시 트랜잭션은 잠금 조건을 충족하는 레코드를 삽입할 수 없습니다. 즉, ISO/를 초과하는 팬텀 읽기는 허용되지 않습니다. ANSI SQL92 "반복 읽기" 격리 수준에 대한 요구 사항에서는 실제로 트랜잭션을 직렬화해야 합니다.
또한 "insert into target_tab select * from source_tab where ..." 및 "create table new_tab ...select ... From source_tab where ...(CTAS)"와 같은 SQL 문에 대해 사용자는 이를 수행하지 않습니다. source_tab 업데이트 작업을 수행하지만 MySQL은 이러한 종류의 SQL 문에 대해 특별한 처리를 수행합니다.
(여기서 InnoDB는 source_tab에 공유 잠금을 추가하고 다중 버전 데이터 일관성 읽기 기술을 사용하지 않습니다!) 
위 예에서 단순히 source_tab 테이블의 데이터를 읽는 것은 일반적인 For SELECT 문을 실행하는 것과 같습니다. , 일관된 독서를 사용하십시오. ORACLE은 MVCC 기술로 구현된 다중 버전 데이터를 사용하여 source_tab에 잠금을 추가하지 않고도 일관된 읽기를 수행합니다. 우리는 InnoDB도 다중 버전 데이터를 구현하고 일반 SELECT 일관성 읽기에 잠금이 필요하지 않다는 것을 알고 있습니다. 그러나 여기서 InnoDB는 source_tab에 공유 잠금을 추가하고 다중 버전 데이터 일관성 읽기 기술을 사용하지 않습니다.
MySQL이 왜 이런 일을 합니까? 그 이유는 복구와 복제의 정확성을 보장하기 위해서입니다. 왜냐하면 잠금을 하지 않은 상태에서 위 명령문 실행 중에 다른 트랜잭션이 source_tab을 업데이트하면 잘못된 데이터 복구 결과가 나올 수 있기 때문입니다. 이를 설명하기 위해 이전 예제를 반복해 보겠습니다. session_1이 트랜잭션을 실행하기 전에 시스템 변수 innodb_locks_unsafe_for_binlog의 값이 "on"(기본값은 off)으로 설정된다는 것입니다.
위에서 볼 수 있듯이 시스템 변수 innodb_locks_unsafe_for_binlog의 값을 "on"으로 설정한 후 InnoDB는 더 이상 source_tab을 잠그지 않으며 결과는 애플리케이션 로직과 일치합니다. 그러나 BINLOG의 내용을 분석하면. :
SET TIMESTAMP=1169175130;
BEGIN;
# at 274
#070119 10:51:57 서버 ID 1 end_log_pos 105 쿼리 thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP =1169175117;
update source_tab set name = '8 ' where name = '1';
# at 379
#070119 10:52:10 server id 1 end_log_pos 406 474 Query thread_id= 2 exec_time=0 error_code=0
SET TIMESTAMP= 1169175134;
BEGIN;
# at 474
#070119 10:51:29 서버 ID 1 end_log_pos 119 쿼리 thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1169175089;
target_tab에 삽입하고 d1을 선택하세요. name from source_tab where name = '1';
# at 593
#070119 10:52:14 server id 1 end_log_pos 620 Xid = 7
COMMIT;
BINLOG에서 찾을 수 있는 위치는 업데이트 작업은 INSERT...SELECT 이전입니다. 이 BINLOG가 데이터베이스 복구에 사용되는 경우 복제가 수행되면 복구 결과가 실제 애플리케이션 논리와 일치하지 않으며 마스터-슬레이브 데이터베이스가 일치하지 않습니다.
따라서 INSERT...SELECT... 및 CREATE TABLE...SELECT... 문은 소스 테이블에 대한 동시 업데이트를 방지하여 소스 테이블 잠금을 기다리게 할 수 있습니다. 쿼리가 복잡하면 심각한 성능 문제가 발생하므로 애플리케이션에서 쿼리를 사용하지 않도록 노력해야 합니다. 실제로 MySQL은 이런 종류의 SQL을 비결정적 SQL이라고 부르며 권장되지 않습니다.
이런 종류의 SQL을 사용하여 애플리케이션에서 비즈니스 로직을 구현해야 하고 소스 테이블의 동시 업데이트에 영향을 주지 않으려면 다음 두 가지 조치를 취할 수 있습니다.
먼저 위의 예에서 innodb_locks_unsafe_for_binlog 값을 변경하여 MySQL이 다중 버전 데이터 일관성 읽기를 사용하도록 하려면 "on"으로 설정합니다. 그러나 binlog를 사용하면 데이터를 올바르게 복원하거나 복사할 수 없다는 단점이 있으므로 이 방법은 권장되지 않습니다.
두 번째는 "select * from source_tab ... Into outfile"과 "load data infile ..." 문을 조합하여 간접적으로 달성하는 것입니다. 이런 식으로 MySQL은 source_tab을 잠그지 않습니다.
8. 다양한 격리 수준에서 InnoDB의 일관된 읽기 및 잠금의 차이점
앞서 언급했듯이 잠금 및 다중 버전 데이터는 InnoDB가 일관된 읽기 및 ISO/ANSI SQL92 격리 수준을 달성하기 위한 수단입니다. 격리 수준, 일관된 읽기 전략 및 SQL 처리 시 InnoDB에서 사용하는 필수 잠금이 다릅니다. 동시에 데이터 복구 및 복제 메커니즘의 특성은 일부 SQL 일관성 읽기 전략 및 잠금 전략에도 큰 영향을 미칩니다. 이러한 특징을 독자의 편의를 위해 표 20-16에 정리하였다.
1: 격리 수준이 RC인 경우 공식 문서에서는 다음과 같이 설명합니다.
동일한 트랜잭션 내에서도 각 일관된 읽기는 자체적인 새로운 스냅샷을 설정하고 읽습니다. 섹션 14.8.2.3, “일관적인 비잠금 읽기”를 참조하세요.
잠금 읽기(SELECT with FOR UPDATE 또는 LOCK IN SHARE MODE), UPDATE 문 및 DELETE 문의 경우 InnoDB는 인덱스 레코드만 잠그고 그 앞의 간격은 잠그지 않습니다. 잠긴 레코드 옆에 새 레코드를 자유롭게 삽입할 수 있습니다. 갭 잠금은 외래 키 제약 조건 검사 및 중복 키 검사에만 사용됩니다.
공식 문서 주소는 https://dev.mysql.com/doc/refman입니다. /5.5/en/innodb-transaction-isolation-levels.html
2: 반복 읽기 격리 수준에서 인덱스가 고유하고 검색도 고유하면 갭 잠금이 사용되지 않고, 그렇지 않으면 갭 잠금이 사용됩니다. 공식 설명은 다음과 같습니다:
REPEATABLE READ
이것은 InnoDB의 기본 격리 수준입니다. 동일한 트랜잭션 내의 일관된 읽기는 첫 번째 읽기에 의해 설정된 스냅샷을 읽습니다. 동일한 트랜잭션인 경우 이러한 SELECT 문은 섹션 14.8.2.3, “일관적인 비잠금 읽기”를 참조하세요.
잠금 읽기(FOR UPDATE 또는 LOCK IN SHARE MODE를 사용하는 SELECT), UPDATE 및 DELETE 문의 경우 잠금은 해당 명령문이 고유한 검색 조건을 갖는 고유 인덱스를 사용하는지 아니면 범위형 검색 조건을 사용하는지에 따라 달라집니다.
고유한 검색 조건을 갖는 고유 인덱스의 경우 InnoDB는 발견된 인덱스 레코드만 잠그고 그 앞의 간격은 잠그지 않습니다.
다른 검색 조건의 경우 InnoDB는 범위에 포함된 간격에 다른 세션이 삽입되는 것을 차단하기 위해 간격 잠금 또는 다음 키 잠금을 사용하여 스캔된 인덱스 범위를 잠급니다. . 1, “InnoDB 잠금”.
공식 문서 설명 주소는 https://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html
9 테이블 잠금을 언제 사용해야 할까요?
InnoDB 테이블의 경우 , 대부분의 경우 행 수준 잠금을 사용해야 합니다. 왜냐하면 트랜잭션과 행 잠금이 우리가 InnoDB 테이블을 선택하는 이유인 경우가 많기 때문입니다. 그러나 개별 특수 트랜잭션에서는 테이블 수준 잠금도 고려할 수 있습니다.
첫 번째 상황은 트랜잭션이 데이터의 대부분 또는 전체를 업데이트해야 하며 테이블이 상대적으로 크다는 것입니다. 기본 행 잠금을 사용하면 트랜잭션 실행 효율성이 낮을 뿐만 아니라 다른 문제가 발생할 수도 있습니다. 트랜잭션이 오랫동안 대기하고 잠금 충돌이 발생할 수 있습니다. 이 경우 트랜잭션 실행 속도를 향상시키기 위해 테이블 잠금 사용을 고려할 수 있습니다.
두 번째 상황은 트랜잭션에 여러 테이블이 포함되어 있어 상대적으로 복잡하고 교착 상태가 발생하고 많은 수의 트랜잭션 롤백이 발생할 가능성이 있다는 것입니다. 이 경우 교착 상태를 방지하고 트랜잭션 롤백으로 인한 데이터베이스 오버헤드를 줄이기 위해 트랜잭션에 관련된 테이블을 한 번에 잠그는 것도 고려할 수 있습니다.
물론 애플리케이션에 이 두 가지 유형의 트랜잭션이 너무 많아서는 안 됩니다. 그렇지 않으면 MyISAM 테이블 사용을 고려해야 합니다.
InnoDB에서는 테이블 잠금을 사용할 때 다음 두 가지 사항에 주의해야 합니다.
(1) LOCK TABLES를 사용하여 InnoDB에 테이블 수준 잠금을 추가할 수 있지만 테이블 잠금은 InnoDB 스토리지 엔진 계층에서 관리되지 않고 상위 계층인 MySQL 서버에서만 관리된다는 점에 유의해야 합니다. autocommit =0, innodb_table_locks=1(기본 설정), InnoDB 계층은 MySQL이 추가한 테이블 잠금을 알 수 있고 MySQL 서버도 InnoDB가 추가한 행 잠금을 인식할 수 있습니다. 이 경우 InnoDB는 테이블 수준과 관련된 교착 상태를 자동으로 식별할 수 있습니다. 그렇지 않으면 InnoDB는 이러한 교착 상태를 자동으로 감지하고 처리할 수 없습니다. 교착상태에 대해서는 다음 섹션에서 계속 논의하겠습니다.
(2) LOCK TABLES를 사용하여 InnoDB 테이블을 잠글 때 AUTOCOMMIT를 0으로 설정하도록 주의하십시오. 그렇지 않으면 MySQL은 트랜잭션이 끝나기 전에 테이블을 잠그지 않습니다. UNLOCK TABLES를 사용하여 테이블 잠금을 해제하지 마십시오. 트랜잭션을 암시적으로 커밋합니다. COMMIT 또는 ROLLBACK은 LOCK TABLES로 추가된 테이블 수준 잠금을 해제할 수 없으며 테이블 잠금은 UNLOCK TABLES로 해제해야 합니다. 올바른 방법은 다음과 같습니다.
예를 들어 테이블 t1에 쓰고 테이블 t에서 읽어야 하는 경우 다음과 같이 할 수 있습니다.
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, . ..;
[여기서 테이블 t1 및 t2로 작업 수행];
COMMIT;
UNLOCK TABLES;
10. 교착 상태에 관해
위에서 언급했듯이 MyISAM 테이블 잠금은 교착 상태가 없습니다. 왜냐하면 MyISAM은 항상 교착 상태를 확보하기 때문입니다. 한 번 필요한 모든 잠금이 충족되거나 대기 중이므로 교착 상태가 발생하지 않습니다. 하지만 InnoDB에서는 단일 SQL로 구성된 트랜잭션을 제외하고 점진적으로 잠금을 획득하므로 InnoDB에서는 교착 상태가 발생할 수 있다고 판단됩니다. 표 20-17은 교착 상태의 예를 보여줍니다.
위의 예에서 두 트랜잭션 모두 트랜잭션을 계속 완료하려면 상대방이 보유한 독점 잠금을 획득해야 합니다. 이러한 종류의 순환 잠금 대기는 일반적인 교착 상태입니다.
교착 상태가 발생한 후 InnoDB는 일반적으로 이를 자동으로 감지하여 한 트랜잭션이 잠금을 해제하고 롤백하도록 하고, 다른 트랜잭션은 잠금을 획득하고 계속해서 트랜잭션을 완료합니다. 그러나 외부 잠금 또는 테이블 잠금이 포함된 경우 InnoDB는 교착 상태를 완전히 자동으로 감지할 수 없습니다. 이 문제는 잠금 대기 시간 제한 매개변수 innodb_lock_wait_timeout을 설정하여 해결해야 합니다. 이 매개 변수는 교착 상태 문제를 해결하는 데만 사용되는 것이 아니라 동시 액세스가 상대적으로 높을 때 필요한 잠금을 즉시 얻을 수 없어 많은 수의 트랜잭션이 일시 중지되면 많은 양의 컴퓨터 리소스를 차지하게 됩니다. 심각한 성능 문제가 발생합니다. 문제는 데이터베이스 전체에도 영향을 미칩니다. 적절한 잠금 대기 시간 초과 임계값을 설정하면 이러한 상황을 방지할 수 있습니다.
일반적으로 교착 상태는 애플리케이션 설계의 문제입니다. 비즈니스 프로세스, 데이터베이스 개체 설계, 트랜잭션 크기 및 데이터베이스에 액세스하는 SQL 문을 조정하면 대부분의 교착 상태를 피할 수 있습니다.
다음은 교착 상태를 피하기 위한 몇 가지 일반적인 방법을 예제를 통해 소개합니다.
(1) 애플리케이션에서 서로 다른 프로그램이 동시에 여러 테이블에 액세스하는 경우 동일한 순서로 테이블에 액세스하는 데 동의하도록 노력하십시오. 그러면 교착 상태가 발생할 가능성이 크게 줄어들 수 있습니다. 다음 예에서는 두 세션이 서로 다른 순서로 두 테이블에 액세스하기 때문에 교착 상태가 발생할 가능성이 매우 높습니다! 그러나 동일한 순서로 접근을 하면 교착상태를 피할 수 있다.
(2) 프로그램이 데이터를 일괄 처리할 때 각 스레드가 정해진 순서대로 레코드를 처리하도록 미리 데이터를 정렬해 놓으면 교착 상태가 발생할 가능성도 크게 줄일 수 있습니다.
(3) 트랜잭션에서 레코드를 업데이트하려면 충분한 수준의 잠금, 즉 독점 잠금을 먼저 신청한 다음 공유 잠금을 신청하면 안 됩니다. 사용자가 배타적 잠금을 적용할 때 다른 트랜잭션이 동일한 레코드의 공유 잠금을 획득하여 잠금 충돌이나 심지어 교착 상태가 발생할 수 있으므로 업데이트 시 배타적 잠금을 적용하십시오. 구체적인 데모를 보려면 섹션 20.3.3의 예를 참조하십시오.
(4) 앞에서 언급한 것처럼 REPEATABLE-READ 격리 수준에서 두 스레드가 SELECT...FOR UPDATE를 사용하여 동시에 동일한 조건의 레코드에 배타적 잠금을 추가하는 경우 조건을 충족하는 레코드가 없는 경우 , 두 스레드 모두 잠금 성공을 추가합니다. 프로그램은 레코드가 아직 존재하지 않음을 발견하고 새 레코드를 삽입하려고 시도합니다. 두 스레드가 모두 이를 수행하면 교착 상태가 발생합니다. 이 경우 격리 수준을 READ COMMITTED로 변경하면 문제를 피할 수 있습니다.
(5) 격리 수준이 READ COMMITTED일 때 두 스레드 모두 SELECT...FOR UPDATE를 먼저 실행하면 조건에 맞는 레코드가 있는지 확인하고, 없으면 해당 레코드를 삽입합니다. 이때 한 스레드만 성공적으로 삽입할 수 있고 다른 스레드는 잠금을 기다리게 됩니다. 첫 번째 스레드가 제출되면 두 번째 스레드는 다시 기본 키로 인해 오류가 발생하지만 이 스레드는 오류가 발생하지만 독점 잠금 장치를 획득하게 됩니다! 이때, 세 번째 쓰레드가 배타적 잠금을 적용하면 교착상태도 발생하게 된다.
이 경우 삽입 작업을 직접 수행한 후 기본 키 중복 예외를 포착하거나, 기본 키 중복 오류 발생 시 항상 ROLLBACK을 실행하여 획득한 배타적 잠금을 해제할 수 있습니다.
위에 소개된 설계와 SQL 최적화 및 기타 조치를 통해 교착 상태를 크게 줄일 수 있지만 교착 상태를 완전히 피하기는 어렵습니다. 따라서 프로그래밍 시 교착상태 예외를 항상 포착하고 처리하는 것은 좋은 프로그래밍 습관입니다.
교착 상태가 발생하면 SHOW INNODB STATUS 명령을 사용하여 마지막 교착 상태의 원인을 확인할 수 있습니다. 반환된 결과에는 교착 상태를 발생시킨 SQL 문, 트랜잭션이 획득한 잠금, 대기 중인 잠금, 롤백된 트랜잭션 등 교착 상태 관련 트랜잭션에 대한 자세한 정보가 포함됩니다. 이를 바탕으로 교착상태의 원인과 개선방안을 분석할 수 있다.
다음은 SHOW INNODB STATUS의 샘플 출력입니다.
mysql> show innodb status G
InnoDB 요약
이 장에서는 MySQL의 MyISAM 테이블 수준 잠금 및 InnoDB 행 수준 잠금의 구현 기능에 중점을 둡니다. 스토리지 엔진에서 자주 발생하는 두 가지 잠금 문제와 솔루션에 대해 논의합니다.
MyISAM 테이블 잠금의 경우 다음 사항이 주로 논의됩니다.
(1) 공유 읽기 잠금(S)은 호환되지만 공유 읽기 잠금(S)과 배타적 쓰기 잠금(X) 사이에는 배타적 쓰기 잠금(X) )은 상호 배타적입니다. 이는 읽기와 쓰기가 연속적이라는 것을 의미합니다.
(2) 특정 조건에서 MyISAM은 쿼리와 삽입이 동시에 실행되도록 허용합니다. 이를 사용하여 애플리케이션의 동일한 테이블에 대한 쿼리 및 삽입에 대한 잠금 경합 문제를 해결할 수 있습니다.
(3) MyISAM의 기본 잠금 스케줄링 메커니즘은 쓰기 우선순위입니다. 이는 모든 애플리케이션에 반드시 적합한 것은 아닙니다. 사용자는 LOW_PRIORITY_UPDATES 매개변수를 설정하거나 INSERT, UPDATE 및 DELETE에서 LOW_PRIORITY 옵션을 지정하여 읽기-쓰기 잠금 경합을 조정할 수 있습니다. 진술.
(4) 테이블 잠금의 잠금 세분성 및 직렬 읽기 및 쓰기 작업으로 인해 업데이트 작업이 많은 경우 MyISAM 테이블에서 잠금 충돌을 줄이기 위해 InnoDB 테이블 사용을 고려할 수 있습니다.
InnoDB 테이블에 대해 이 장에서는 주로 다음 내용을 논의합니다.
InnoDB의 행 잠금은 잠금 인덱스를 기반으로 합니다. 인덱스를 통해 데이터에 액세스하지 않으면 InnoDB는 테이블 잠금을 사용합니다.
InnoDB 갭 잠금(Next-key) 메커니즘과 InnoDB가 갭 잠금을 사용하는 이유를 소개했습니다.
다양한 격리 수준에서는 InnoDB의 잠금 메커니즘과 일관된 읽기 전략이 다릅니다.
MySQL 복구 및 복제는 InnoDB 잠금 메커니즘과 일관된 읽기 전략에도 큰 영향을 미칩니다.
잠금 충돌과 심지어 교착 상태도 완전히 피하기는 어렵습니다.
InnoDB의 잠금 특성을 이해한 후 사용자는 다음을 포함한 설계 및 SQL 조정을 통해 잠금 충돌 및 교착 상태를 줄일 수 있습니다.
가능한 한 낮은 격리 수준을 사용합니다.
인덱스를 신중하게 설계하고 인덱스를 사용하여 액세스합니다. 데이터 잠금을 더욱 정밀하게 만들어 잠금 충돌 가능성을 줄입니다.
합리적인 거래 규모를 선택하면 소규모 거래에 대한 잠금 충돌 가능성이 줄어듭니다.
기록 세트를 잠금 표시할 때는 한 번에 충분한 수준의 잠금을 요청하는 것이 가장 좋습니다. 예를 들어, 데이터를 수정하려는 경우 먼저 공유 잠금을 적용한 후 수정 시 배타적 잠금을 요청하는 것보다 직접 배타적 잠금을 적용하는 것이 최선이며, 이로 인해 교착 상태가 발생하기 쉽습니다.
다른 프로그램이 테이블 그룹에 액세스하는 경우 각 테이블에 동일한 순서로 액세스하는 데 동의해야 합니다. 테이블의 경우 고정된 순서로 테이블의 행에 액세스해야 합니다. 이렇게 하면 교착 상태가 발생할 가능성이 크게 줄어듭니다.
동시 삽입 시 간격 잠금의 영향을 피하기 위해 동등 조건을 사용하여 데이터에 액세스해 보세요.
필요한 경우를 제외하고는 실제 요구 사항을 초과하는 잠금 수준을 적용하지 말고 쿼리 시 잠금을 표시하지 마세요.
일부 특정 트랜잭션의 경우 테이블 잠금을 사용하여 처리 속도를 높이거나 교착 상태 가능성을 줄일 수 있습니다.
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
