Lock은 컴퓨터가 리소스에 동시에 액세스하기 위해 여러 프로세스나 스레드를 조정하는 메커니즘입니다. 데이터베이스에서는 컴퓨팅 리소스(예: CPU, RAM, I/O 등)에 대한 전통적인 경쟁 외에도 데이터도 많은 사용자가 공유하는 리소스입니다. 데이터에 대한 동시 접근의 일관성과 효율성을 어떻게 보장하는가는 모든 데이터베이스가 해결해야 할 문제이다. 잠금 충돌 역시 데이터베이스에 대한 동시 접근 성능에 영향을 미치는 중요한 요소이다. 이러한 관점에서 잠금은 데이터베이스에 특히 중요하고 복잡합니다. 이 장에서는 MySQL 잠금 메커니즘의 특성, 일반적인 잠금 문제, MySQL 잠금 문제를 해결하기 위한 몇 가지 방법이나 제안에 중점을 둡니다.
Mysql은 행 잠금, 테이블 잠금, 읽기 잠금, 쓰기 잠금 등과 같은 많은 잠금 메커니즘을 사용하며 모두 작업 전에 잠깁니다. 이러한 잠금을 집합적으로 비관적 잠금이라고 합니다.
MySQL 잠금 개요
다른 데이터베이스와 비교하여 MySQL의 잠금 메커니즘은 상대적으로 간단합니다. 가장 중요한 특징은 다양한 스토리지 엔진이 다양한 잠금 메커니즘을 지원한다는 것입니다. 예를 들어 MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking)도 테이블 수준 잠금을 지원하지만 기본적으로 행 수준 잠금이 사용됩니다.
테이블 수준 잠금: 낮은 오버헤드, 빠른 잠금, 큰 잠금 세분성, 가장 높은 잠금 충돌 가능성 및 가장 낮은 동시성.
행 수준 잠금: 높은 오버헤드, 느린 잠금이 발생할 수 있습니다. 잠금 세분성은 가장 작고 잠금 충돌 가능성은 가장 낮으며 동시성은 가장 높습니다.
페이지 잠금: 오버헤드와 잠금 시간은 테이블 잠금과 행 잠금 사이에 있으며 교착 상태는 테이블 잠금과 행 잠금 사이에 발생하며 동시성은 평균입니다.
위의 특성에서 알 수 있습니다. 일반화하기 어렵습니다. 어떤 잠금 장치가 더 나은지 말하기 위해서는 특정 애플리케이션의 특성에 따라 어떤 잠금 장치가 더 적합한지 말할 수 있습니다! 잠금의 관점에서 볼 때, 테이블 수준 잠금은 주로 쿼리 기반 애플리케이션에 더 적합하고 웹 애플리케이션과 같이 인덱스 조건에 따라 소량의 데이터만 업데이트하는 반면 행 수준 잠금은 애플리케이션에 더 적합합니다. 인덱스 조건을 기반으로 하는 다수의 동시 업데이트와 소수의 서로 다른 데이터 및 일부 온라인 트랜잭션 처리(OLTP) 시스템과 같은 동시 쿼리 애플리케이션이 있습니다.
MyISAM 테이블 잠금
MySQL 테이블 수준 잠금에는 테이블 공유 읽기 잠금(Table Read Lock)과 테이블 독점 쓰기 잠금(Table Write Lock)의 두 가지 모드가 있습니다.
MyISAM 테이블에 대한 읽기 작업은 동일한 테이블에 대한 다른 사용자의 읽기 요청을 차단하지 않지만 동일한 테이블에 대한 쓰기 요청은 차단합니다. MyISAM 테이블에 대한 쓰기 작업은 동일한 테이블에 대한 다른 사용자의 읽기 및 쓰기 작업을 차단합니다. ;MyISAM 테이블의 읽기 작업과 쓰기 작업은 물론 쓰기 작업도 직렬입니다! 표 20-2의 예에 따르면 스레드가 테이블에 대한 쓰기 잠금을 획득하면 잠금을 보유하고 있는 스레드만 테이블을 업데이트할 수 있음을 알 수 있습니다. 다른 스레드의 읽기 및 쓰기 작업은 잠금이 해제될 때까지 기다립니다.
MyISAM 스토리지 엔진 쓰기 잠금 차단 읽기 예:
스레드가 테이블에 대한 쓰기 잠금을 획득하면 잠금을 보유한 스레드만 테이블을 업데이트할 수 있습니다. 다른 스레드의 읽기 및 쓰기 작업은 잠금이 해제될 때까지 기다립니다.
MyISAM 스토리지 엔진의 읽기 잠금 차단 쓰기 예:
A 세션은 LOCK TABLE 명령을 사용하여 film_text 테이블에 읽기 잠금을 추가합니다. 이 세션은 잠긴 테이블의 레코드를 쿼리할 수 있지만 다른 테이블을 업데이트하거나 액세스하면 오류 메시지를 표시합니다. 동시에 다른 세션이 테이블의 레코드를 쿼리할 수 있지만 업데이트할 때 잠금 대기가 발생합니다.
시계 잠금 추가 방법
MyISAM은 업데이트 작업(UPDATE, DELETE, INSERT 등)을 실행하기 전에 관련된 모든 테이블에 자동으로 읽기 잠금을 추가합니다. 이 프로세스에는 사용자 개입이 필요하지 않습니다. 따라서 일반적으로 사용자는 MyISAM 테이블을 명시적으로 잠그기 위해 LOCK TABLE 명령을 직접 사용할 필요가 없습니다. 예제에서 명시적 잠금은 대부분 데모용이므로 필요하지 않습니다.
MyISAM 테이블의 디스플레이 잠금은 일반적으로 트랜잭션 작업을 어느 정도 시뮬레이션하고 특정 시점에 여러 테이블을 일관되게 읽기 위한 것입니다. 예를 들어, 각 주문의 총액을 기록하는 주문 테이블이 있고, 각 주문의 각 제품의 소계 금액을 기록하는 주문 세부 정보 테이블인 order_detail도 있다고 가정해 보겠습니다. 총액이 일치하는지 확인하려면 다음 두 SQL을 실행해야 할 수도 있습니다.
Select sum(total) from orders; Select sum(subtotal) from order_detail;
이때 두 테이블을 먼저 잠그지 않으면 첫 번째 문이 이기 때문에 잘못된 결과가 발생할 수 있습니다. 실행되면 order_detail 테이블이 변경되었을 수 있습니다. 따라서 올바른 방법은 다음과 같습니다.
Lock tables orders read local, order_detail read local; Select sum(total) from orders; Select sum(subtotal) from order_detail; Unlock tables;
다음 두 가지 사항에 특히 주의해야 합니다.
1 위의 예에서는 LOCK TABLES 시 "local" 옵션을 추가하며 해당 기능은 다음과 같습니다. MyISAM 테이블에 동시 삽입하는 조건에서 다른 사용자는 테이블 끝에 레코드를 동시에 삽입할 수 있습니다. MyISAM 테이블에 동시 삽입 문제는 나중에 자세히 소개하겠습니다.
2. LOCK TABLES를 사용하여 테이블에 테이블 잠금을 명시적으로 추가하는 경우 테이블에 관련된 모든 잠금을 동시에 획득해야 하며 MySQL은 잠금 업그레이드를 지원하지 않습니다. 즉, LOCK TABLES를 실행한 후에는 명시적으로 잠긴 테이블에만 액세스할 수 있지만 동시에 잠금 해제된 테이블에는 액세스할 수 없습니다. 읽기 잠금을 추가하면 쿼리 작업만 수행할 수 있고 업데이트 작업은 수행할 수 없습니다. 실제로 자동 잠금의 경우 기본적으로 MyISAM은 항상 SQL 문에서 요구하는 모든 잠금을 한 번에 획득합니다. 이것이 MyISAM 테이블이 교착 상태에 빠지지 않는 이유입니다(Deadlock Free).
LOCK TABLES를 사용할 경우 사용되는 모든 테이블을 한번에 잠궈야 할 뿐만 아니라, SQL 문에 동일한 테이블이 몇 번이나 등장하는지에도 동일한 별칭을 통해 잠궈야 합니다. 그렇지 않으면 뭔가 잘못될 것입니다! 아래에 예가 나와 있습니다.
(1) Actor 테이블에 대한 읽기 잠금을 얻습니다:
mysql> lock table actor read; Query OK, 0 rows affected (0.00 sec)
(2) 그러나 별칭을 통해 액세스하면 오류가 발생합니다:
mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name; ERROR 1100 (HY000): Table ‘a’ was not locked with LOCK TABLES
mysql> lock table actor as a read,actor as b read; Query OK, 0 rows affected (0.00 sec)
mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name; +————+———–+————+———–+ | first_name | last_name | first_name | last_name | +————+———–+————+———–+ | Lisa | Tom | LISA | MONROE | +————+———–+————+———–+ 1 row in set (0.00 sec)
# 🎜🎜#테이블 수준 잠금 경합 쿼리 시스템의 테이블 잠금 경합은 table_locks_waited 및 table_locks_immediate 상태 변수를 확인하여 분석할 수 있습니다.
mysql> show status like 'table%'; 1Variable_name | Value Table_locks_immediate | 2979 Table_locks_waited | 0 2 rows in set (0.00 sec))
위에서 언급했듯이 MyISAM 테이블의 읽기 및 쓰기는 직렬입니다. 일반적으로. 특정 조건에서 MyISAM 테이블은 동시 쿼리 및 삽입 작업도 지원합니다. MyISAM 스토리지 엔진에는 동시 삽입 동작을 제어하는 데 특별히 사용되는 시스템 변수 Concurrent_insert가 있으며 그 값은 각각 0, 1 또는 2일 수 있습니다.
1. Concurrent_insert가 0으로 설정되면 동시 삽입이 허용되지 않습니다.
MyISAM 스토리지 엔진에는 동시 삽입 동작을 제어하는 데 특별히 사용되는 시스템 변수 Concurrent_insert가 있으며 그 값은 각각 0, 1 또는 2일 수 있습니다.
concurrent_insert가 0으로 설정되면 동시 삽입이 허용되지 않습니다. Concurrent_insert가 1로 설정된 경우 MyISAM 테이블에 구멍이 없으면(즉, 테이블 중간에 삭제된 행이 없는 경우) MyISAM은 한 프로세스가 테이블을 읽을 수 있도록 허용하고 다른 프로세스는 테이블 끝에서 레코드를 삽입합니다. 테이블. 이는 MySQL의 기본 설정이기도 합니다. Concurrent_insert를 2로 설정하면 MyISAM 테이블에 홀이 있는지 여부에 관계없이 테이블 끝에 레코드를 동시에 삽입할 수 있습니다.
다음 예에서 session_1은 테이블의 READ LOCAL 잠금을 획득할 수 있지만 삭제 및 업데이트 작업이 수행되지 않더라도 다른 스레드(session_2)를 업데이트할 수는 없습니다. 하지만 테이블 중앙에는 구멍이 없는 것으로 가정합니다.
MyISAM 스토리지 엔진 읽기 및 쓰기(INSERT) 동시성 예:
다음의 동시 삽입 기능을 활용할 수 있습니다. MyISAM 스토리지 엔진은 쿼리에 대한 잠금 경합을 해결하고 애플리케이션의 동일한 테이블에 삽입합니다. 예를 들어, Concurrent_insert 시스템 변수를 2로 설정하면 항상 동시 삽입이 허용되며, 동시에 시스템의 유휴 기간 동안 OPTIMIZE TABLE 문이 정기적으로 실행되어 공간 조각 모음을 수행하고 레코드 삭제로 인해 발생한 중간 구멍을 복구합니다.
MyISAM 잠금 예약
앞서 언급했듯이 MyISAM 스토리지 엔진의 읽기 잠금 및 쓰기 잠금은 상호 배타적이며 읽기 및 쓰기 작업은 직렬입니다. 따라서 한 프로세스가 MyISAM 테이블에 대한 읽기 잠금을 요청하고 동시에 다른 프로세스도 동일한 테이블에 대한 쓰기 잠금을 요청하는 경우 MySQL은 이를 어떻게 처리합니까? 대답은 쓰기 프로세스가 먼저 잠금을 획득한다는 것입니다. 뿐만 아니라 잠금 대기 큐에 읽기 요청이 먼저 도착하고 쓰기 요청이 나중에 도착하더라도 읽기 잠금 요청보다 먼저 쓰기 잠금이 삽입됩니다! 이는 MySQL이 쓰기 요청을 일반적으로 읽기 요청보다 더 중요하게 간주하기 때문입니다. 이것이 MyISAM 테이블이 업데이트 작업과 쿼리 작업이 많은 애플리케이션에 적합하지 않은 이유입니다. 업데이트 작업이 많으면 쿼리 작업이 영원히 차단될 수 있는 읽기 잠금을 획득하기 어렵게 되기 때문입니다. 이 상황은 때때로 정말 악화될 수 있습니다! 다행히도 일부 설정을 통해 MyISAM의 예약 동작을 조정할 수 있습니다.
1. 시작 매개변수 low-priority-updates를 지정하면 MyISAM 엔진은 기본적으로 읽기 요청에 우선순위를 부여합니다.
2. SET LOW_PRIORITY_UPDATES=1 명령을 실행하면 연결에서 발행된 업데이트 요청의 우선순위가 낮아집니다.
3. INSERT, UPDATE 및 DELETE 문의 LOW_PRIORITY 속성을 지정하여 문의 우선순위를 낮춥니다.
위의 세 가지 방법은 업데이트 우선 또는 쿼리 우선이지만 쿼리가 비교적 중요한 애플리케이션(예: 사용자 로그인 시스템)에서 심각한 읽기 잠금 대기 문제를 해결하는 데 여전히 사용할 수 있습니다. 질문.
또한 MySQL은 읽기 및 쓰기 충돌을 조정하는 절충 방법도 제공합니다. 즉, 테이블의 읽기 잠금이 이 값에 도달하면 MySQL은 일시적으로 쓰기를 차단합니다. 요청의 우선순위가 낮아져 읽기 프로세스에 잠금을 얻을 수 있는 특정 기회가 제공됩니다.
쓰기 우선순위 스케줄링 메커니즘으로 인해 발생하는 문제와 해결책은 위에서 논의되었습니다. 여기서 또 다른 점을 강조해야 합니다. 긴 실행 시간이 필요한 일부 쿼리 작업은 쓰기 프로세스도 "고갈"시킵니다! 따라서 응용 프로그램에서 장시간 실행되는 쿼리 작업을 피해야 합니다. 겉보기에 영리해 보이는 이 SQL 문은 종종 더 복잡하고 실행하는 데 시간이 더 오래 걸리기 때문에 문제를 해결하기 위해 항상 SELECT 문을 사용하려고 하지 마십시오. SQL 문은 중간 테이블과 기타 측정값을 사용하여 어느 정도 "분해"할 수 있으므로 쿼리의 각 단계를 더 짧은 시간에 완료하여 잠금 충돌을 줄일 수 있습니다. 복잡한 쿼리가 불가피한 경우 데이터베이스의 유휴 기간 동안 실행되도록 예약해야 합니다. 예를 들어 일부 일반 통계는 밤에 실행되도록 예약할 수 있습니다.
InnoDB 잠금에 대해서는 나중에 설명하겠습니다.
관련 질문이 더 필요하시면 PHP 중국어 웹사이트를 방문하세요: Mysql 비디오 튜토리얼
위 내용은 mysql 잠금 메커니즘의 원리에 대한 자세한 설명(1)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
