mysql 데이터베이스의 잠금 메커니즘에 대한 자세한 소개

이 기사는 MySQL 데이터베이스의 잠금 메커니즘에 대한 자세한 소개를 제공합니다. 이는 특정 참조 가치가 있으므로 도움이 될 수 있습니다.

비관적 잠금과 낙관적 잠금:
비관적 잠금: 이름에서 알 수 있듯이 매우 비관적 잠금입니다. 데이터를 얻으러 갈 때마다 다른 사람이 수정할 것이라고 생각하므로 데이터를 얻을 때마다 잠그게 됩니다. , 원하는 경우 다른 사람이 데이터를 얻을 수 있도록 잠금을 얻을 때까지 차단합니다. 이러한 많은 잠금 메커니즘은 행 잠금, 테이블 잠금, 읽기 잠금, 쓰기 잠금 등과 같은 기존 관계형 데이터베이스에서 사용되며 작업 전에 모두 잠깁니다.

낙관적 잠금: 이름에서 알 수 있듯이 매우 낙관적입니다. 데이터를 얻으러 갈 때마다 다른 사람이 수정하지 않을 것이라고 생각하므로 잠그지 않을 것입니다. 그러나 업데이트할 때 여부를 판단하게 됩니다. 다른 사람들이 이 기간 동안 데이터를 업데이트했다면 버전 번호와 같은 메커니즘을 사용할 수 있습니다. 낙관적 잠금은 처리량을 향상시킬 수 있는 다중 읽기 애플리케이션 유형에 적합합니다. 데이터베이스가 write_condition과 유사한 메커니즘을 제공하는 경우 실제로는 낙관적 잠금을 제공합니다.

테이블 수준: MyISAM 엔진은 잠금 기간 동안 다른 프로세스가 테이블에 쓸 수 없도록 전체 테이블을 직접 잠급니다. 쓰기 잠금이 있는 경우 다른 프로세스는 읽을 수 없습니다. 페이지 수준: 엔진 BDB. 테이블 수준 잠금은 빠르지만 충돌이 많습니다. 따라서 페이지 수준에서 절충안이 채택되어 한 번에 인접한 레코드 그룹을 잠급니다.

행 수준: 엔진 INNODB, 지정된 레코드만 잠그므로 다른 프로세스가 동일한 테이블의 다른 레코드에서 계속 작동할 수 있습니다.

위의 세 가지 잠금 유형의 특징은 대략 다음과 같이 요약할 수 있습니다.

1) 테이블 수준 잠금: 낮은 오버헤드, 빠른 잠금, 큰 잠금 세분성, 가장 높은 잠금 충돌 가능성 및 가장 낮은 잠금 동시성.

2) 페이지 잠금: 비용과 잠금 시간은 테이블 잠금과 행 잠금 사이에 있습니다. 교착 상태는 테이블 잠금과 행 잠금 사이에 있으며 동시성은 평균입니다.
3) 행 수준 잠금: 오버헤드가 높고 잠금이 느릴 수 있습니다. 잠금 세분성은 가장 작고 잠금 충돌 가능성은 가장 낮으며 동시성은 가장 높습니다.


세 가지 유형의 잠금은 각각 고유한 특성을 가지고 있습니다. 잠금 관점에서 보면 테이블 수준 잠금은 웹 애플리케이션과 같이 인덱스 조건에 따라 업데이트되는 데이터 양이 적은 쿼리 기반 애플리케이션에 더 적합합니다. 행 수준 잠금은 일부 온라인 트랜잭션 처리(OLTP) 시스템과 같이 인덱스 조건 및 동시 쿼리를 기반으로 소량의 서로 다른 데이터를 동시에 업데이트하는 대량의 애플리케이션에 적합합니다.

MySQL 테이블 수준 잠금에는 두 가지 모드가 있습니다.

1. 테이블 공유 읽기 잠금(테이블 읽기 잠금). MyISAM 테이블에서 읽기 작업을 수행할 때 동일한 테이블에 대한 다른 사용자의 읽기 요청은 차단되지 않지만 동일한 테이블에 대한 쓰기 작업은 차단됩니다.

2. MyISAM 테이블에 대한 쓰기 작업은 동일한 테이블에 대한 다른 사용자의 읽기 및 쓰기 작업을 차단합니다.


MyISAM 테이블의 읽기 및 쓰기는 직렬입니다. 즉, 읽기 작업 중에는 쓰기 작업을 수행할 수 없으며 그 반대의 경우도 마찬가지입니다. 그러나 특정 조건에서는 MyISAM 테이블이 동시 쿼리 및 삽입 작업도 지원합니다. 해당 메커니즘은 해당 값이 0으로 설정된 경우 동시 삽입이 허용되지 않습니다. MyISAM 테이블에 구멍이 없으면(즉, 테이블에 삭제된 행이 없으면) MyISAM은 한 프로세스가 테이블을 읽을 수 있도록 허용하고 다른 프로세스는 해당 값이 테이블 끝에서 레코드를 삽입하도록 허용합니다. MyISAM 여부에 관계없이 2로 설정됩니다. 테이블에 구멍이 있는지 여부에 관계없이 테이블 끝에 레코드를 동시에 삽입하는 것이 허용됩니다.

MyISAM 잠금 스케줄링이 어떻게 구현되는지 또한 매우 중요한 문제입니다. 예를 들어, 프로세스가 MyISAM 테이블에 대한 읽기 잠금을 요청하고 동시에 다른 프로세스도 동일한 테이블에 대한 쓰기 잠금을 요청하는 경우 mysql은 프로세스의 우선순위를 어떻게 지정합니까? 연구 결과에 따르면 쓰기 프로세스가 먼저 잠금을 획득합니다. 즉, 읽기 요청이 잠금 대기 대기열에 먼저 도착합니다. 그러나 이로 인해 큰 결함도 발생합니다. 즉, 쓰기 작업이 너무 많으면 쿼리 작업이 읽기 잠금을 획득하기 어렵게 되어 영구적인 차단이 발생할 수 있습니다. 다행히도 일부 설정을 통해 MyISAM의 예약 동작을 조정할 수 있습니다. low-priority-updates 매개변수를 지정하여 MyISAM 기본 엔진이 읽기 요청에 우선순위를 부여하도록 하고 해당 값을 1(low_priority_updates=1로 설정)로 설정하여 우선순위를 낮출 수 있습니다.

InnoDB 잠금과 MyISAM 잠금의 가장 큰 차이점은 다음과 같습니다.

1 트랜잭션(TRANCSACTION)을 지원합니다.

2. 행 수준 잠금이 사용됩니다.


트랜잭션은 일련의 SQL 문으로 구성된 논리적 처리 단위라는 것을 알고 있습니다. 여기에는 다음과 같은 4가지 속성(간단히 ACID 속성)이 있습니다.

원자성: 트랜잭션은 데이터에서 작동하는 원자적 연산 단위입니다. 수정 사항은 완전히 실행되거나 전혀 실행되지 않습니다.

일관성: 트랜잭션이 시작되고 완료될 때 데이터는 일관성을 유지해야 합니다.
격리: 데이터베이스 시스템은 트랜잭션이 "독립적"으로 실행되도록 보장하는 특정 격리 메커니즘을 제공합니다.
내구성: 트랜잭션이 완료된 후 데이터 수정 사항이 영구적이며 시스템 오류가 발생하더라도 유지될 수 있습니다.

동시 트랜잭션 처리로 인한 문제
직렬 처리에 비해 동시 트랜잭션 처리는 데이터베이스 리소스의 활용도를 크게 높이고 데이터베이스 시스템의 트랜잭션 처리량을 향상시켜 더 많은 사용자를 지원할 수 있습니다. 그러나 동시 트랜잭션 처리에는 주로 다음과 같은 상황을 포함하여 몇 가지 문제가 발생합니다.
1. 업데이트 손실: 두 개 이상의 트랜잭션이 동일한 행을 선택한 다음 원래 선택한 값을 기반으로 행을 업데이트하는 경우 각 트랜잭션이 다른 트랜잭션의 존재를 인식하지 못하기 때문에 업데이트 손실이 발생합니다. 문제 - 마지막 업데이트가 덮어쓰기되었습니다. 다른 거래로 인한 업데이트. 예를 들어, 두 명의 편집자가 동일한 문서의 전자 사본을 만듭니다. 각 편집자는 독립적으로 사본을 변경한 후 변경된 사본을 저장하고 원본 문서를 덮어씁니다. 자신의 변경 사항 사본을 마지막으로 저장한 편집자가 다른 편집자의 변경 사항을 덮어씁니다. 다른 편집자가 트랜잭션을 완료하고 커밋할 때까지 한 편집자가 동일한 파일에 액세스할 수 없는 경우 이 문제를 피할 수 있습니다.
2. 더티 읽기(Dirty Reads): 트랜잭션이 완료되어 제출되기 전에 이 레코드의 데이터가 일치하지 않는 상태입니다. 다른 트랜잭션도 동일한 레코드를 읽습니다. 두 번째 트랜잭션은 이러한 "더러운" 데이터를 읽고 그에 따라 추가 처리를 수행하여 커밋되지 않은 데이터 종속성을 생성합니다. 이런 현상을 '더티 리딩(dirty reading)'이라고 부르기도 합니다.
3. 반복 불가능한 읽기: 트랜잭션은 특정 데이터를 읽은 후 특정 시간에 이전에 읽은 데이터를 다시 읽지만 읽은 데이터가 변경되었거나 변경된 내용이 있음을 발견합니다. 이러한 현상을 "반복 불가능한 읽기"라고 합니다.
4. 팬텀 읽기(Phantom Reads): 트랜잭션이 이전에 검색한 데이터를 동일한 쿼리 조건에 따라 다시 읽었지만 다른 트랜잭션에서 쿼리 조건을 만족하는 새로운 데이터가 삽입되었음을 발견하는 현상을 "팬텀 읽기"라고 합니다.

트랜잭션 격리 수준
위에서 언급한 동시 트랜잭션 처리로 인해 발생하는 문제 중 "업데이트 손실"은 일반적으로 완전히 피해야 합니다. 그러나 업데이트 손실 방지는 데이터베이스 트랜잭션 컨트롤러만으로는 해결할 수 없습니다. 따라서 업데이트할 데이터에 필요한 잠금을 애플리케이션에서 추가해야 합니다. 따라서 업데이트 손실 방지는 애플리케이션의 책임입니다.
"더티 읽기", "반복 불가능 읽기" 및 "팬텀 읽기"는 실제로 데이터베이스 읽기 일관성 문제이며, 이는 특정 트랜잭션 격리 메커니즘을 제공하는 데이터베이스에서 해결해야 합니다. 데이터베이스가 트랜잭션 격리를 구현하는 방식은 기본적으로 다음 두 가지 유형으로 나눌 수 있습니다.
1. 하나는 다른 트랜잭션이 데이터를 수정하지 못하도록 데이터를 읽기 전에 잠그는 것입니다.
2. 다른 하나는 잠금을 추가하지 않고 특정 메커니즘을 통해 데이터 요청 시점의 일관된 데이터 스냅샷(Snapshot)을 생성하고, 이 스냅샷을 사용하여 일정 수준(명령문 수준 또는 트랜잭션 수준)의 일관된 읽기를 제공하는 것입니다. 사용자의 관점에서 볼 때, 데이터베이스는 동일한 데이터의 여러 버전을 제공할 수 있는 것처럼 보입니다. 따라서 이 기술을 데이터 다중 버전 동시성 제어(MVCC 또는 줄여서 MCC)라고 하며, 흔히 다중 버전 데이터베이스라고도 합니다.

데이터베이스의 트랜잭션 격리가 엄격할수록 동시성 부작용은 작아지지만 지불되는 가격은 더 커집니다. 왜냐하면 트랜잭션 격리는 본질적으로 트랜잭션을 어느 정도 "직렬화"하기 때문입니다. 이는 분명히 "동시성"과 동일합니다. . 동시에, 애플리케이션마다 읽기 일관성 및 트랜잭션 격리에 대한 요구 사항이 다릅니다. 예를 들어, 많은 애플리케이션은 "반복 불가능한 읽기" 및 "팬텀 읽기"에 민감하지 않으며 동시에 데이터에 액세스하는 기능에 더 관심을 가질 수 있습니다.
"격리"와 "동시성" 사이의 모순을 해결하기 위해 ISO/ANSI SQL92는 4가지 트랜잭션 격리 수준을 정의합니다. 각 수준은 서로 다른 격리 수준을 가지며 응용 프로그램은 자체 비즈니스 논리에 따라 이를 전달할 수 있습니다. 요구사항 "격리"와 "동시성" 간의 모순을 균형있게 유지하려면 다양한 격리 수준을 선택하세요. 표 20-5에는 이러한 네 가지 격리 수준의 특성이 잘 요약되어 있습니다.

읽기 데이터 일관성 및 허용되는 동시성 부작용
격리 수준 읽기 데이터 일관성 Dirty read Non-repeatable read Phantom read
Uncommitted read (Read uncommitted) 물리적으로 손상된 데이터를 읽지 않는다는 것만 보장할 수 있는 가장 낮은 수준 예 예 예
커밋된 읽기 명령문 수준 아니요 예 예
반복 읽기(Repeatable read) 트랜잭션 수준 아니요 아니요 예
직렬화 가능(Serialized) 최상위 수준, 트랜잭션 수준 아니요 아니요 아니요

마지막으로 주목해야 할 점은 각 특정 데이터베이스가 반드시 위의 네 가지 격리 수준을 완전히 구현하지는 않는다는 것입니다. 예를 들어 Oracle은 읽기 커밋 및 직렬화 가능이라는 두 가지 표준 격리 수준만 제공하며 자체적으로 정의된 읽기 전용 격리 수준도 제공합니다. 서버 위의 ISO/ANSI SQL92에 정의된 4가지 격리 수준을 지원하는 것 외에도 "스냅샷"이라는 격리 수준도 지원하지만 엄밀히 말하면 MVCC를 사용하여 구현된 직렬화 가능 격리 수준입니다. MySQL은 4가지 격리 수준을 모두 지원하지만 특정 구현에는 몇 가지 특성이 있습니다. 예를 들어 일부 격리 수준에서는 MVCC 일관성 읽기가 사용되지만 경우에 따라 InnoDB에는 두 가지 행 잠금 모드가 있습니다.
1) 공유 잠금(S): 하나의 트랜잭션이 행을 읽을 수 있도록 허용하고 다른 트랜잭션이 동일한 데이터 세트에 대해 배타적 잠금을 획득하는 것을 방지합니다.
    ( 공유 모드에서 ……lock인 table_name에서 * 선택)
2) 배타적 잠금(X): 배타적 잠금을 획득한 트랜잭션이 데이터를 업데이트하도록 허용하고, 다른 트랜잭션이 동일한 데이터에 대해 공유 읽기 잠금 및 배타적 쓰기 잠금을 획득하지 못하도록 방지 세트. (업데이트를 위해 table_name에서 * 선택)
행 잠금과 테이블 잠금이 공존할 수 있도록 다중 세분성 잠금 메커니즘도 구현되었습니다. 내부적으로 사용되는 두 개의 의도 잠금(둘 다 테이블 잠금)이 있습니다. 공유 잠금 및 의도 배타적 잠금.
1) 의도 공유 잠금(IS): 트랜잭션은 데이터 행에 공유 잠금을 추가하기 전에 먼저 테이블의 IS 잠금을 획득해야 합니다.
2) 의도 배타적 잠금(IX): 트랜잭션은 데이터 행에 배타적 잠금을 추가하기 전에 먼저 테이블의 IX 잠금을 획득해야 합니다.
InnoDB 행 잠금 모드 호환성 목록
요청 잠금 모드
호환되나요?
현재 잠금 모드 X IX S IS IS
트랜잭션에서 요청한 잠금 모드가 현재 잠금과 호환되면 InnoDB는 요청된 잠금을 트랜잭션에 부여합니다. ; 그렇지 않고 둘이 호환되지 않으면 트랜잭션은 잠금이 해제될 때까지 기다립니다.
의도 잠금은 InnoDB에 의해 자동으로 추가되며 사용자 개입이 필요하지 않습니다. UPDATE, DELETE 및 INSERT 문의 경우 InnoDB는 관련 데이터 세트에 자동으로 배타적 잠금(X)을 추가합니다. 일반 SELECT 문의 경우 InnoDB는 다음을 통해 공유 잠금이나 배타적 잠금을 레코드 세트에 추가할 수 없습니다. 진술.
1. 공유 잠금(S): SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE.
2. 배타적 잠금(X): SELECT * FROM table_name WHERE ... FOR UPDATE.
InnoDB 행 잠금은 인덱스의 항목을 잠그는 방식으로 구현됩니다. 이는 데이터 블록에서 해당 데이터 행을 잠그는 방식으로 구현되는 MySQL 및 Oracle과 다릅니다. InnoDB의 이 행 잠금 구현 기능은 InnoDB가 인덱스 조건을 통해 데이터를 검색할 때만 행 수준 잠금을 사용한다는 것을 의미합니다. 그렇지 않으면 InnoDB는 테이블 잠금을 사용합니다!
실제 애플리케이션에서는 InnoDB 행 잠금의 이 기능에 특별한 주의를 기울여야 합니다. 그렇지 않으면 많은 잠금 충돌이 발생하여 동시성 성능에 영향을 미칠 수 있습니다.


테이블 수준 잠금 경합 쿼리
테이블 잠금 경합:
table_locks_waited 및 table_locks_immediate 상태 변수를 확인하여 시스템의 테이블 잠금 경합을 분석할 수 있습니다.

mysql> show status like ‘table%’; 
+———————–+——-+ 
| Variable_name         | Value | 
+———————–+——-+ 
| Table_locks_immediate | 2979  | 
| Table_locks_waited    | 0     | 
+———————–+——-+ 
2 rows in set (0.00 sec))

Table_locks_waited 값이 상대적으로 높으면 테이블 잠금 경합이 있음을 나타냅니다. 심각한 문제의 테이블 수준 잠금 경합입니다.
InnoDB 행 잠금 경합:
InnoDB_row_lock 상태 변수를 확인하여 시스템의 행 잠금 경합을 분석할 수 있습니다.

mysql> show status like ‘innodb_row_lock%’; 
+——————————-+——-+ 
| Variable_name                 | Value | 
+——————————-+——-+ 
| InnoDB_row_lock_current_waits | 0     | 
| InnoDB_row_lock_time          | 0     | 
| InnoDB_row_lock_time_avg      | 0     | 
| InnoDB_row_lock_time_max      | 0     | 
| InnoDB_row_lock_waits         | 0     | 
+——————————-+——-+ 
5 rows in set (0.01 sec)

MyISAM 쓰기 잠금 실험:
MyISAM 테이블에 대한 읽기 작업은 다른 사용자가 동일한 테이블에 액세스하는 것을 차단하지 않습니다. 읽기 요청은 동일 테이블에 대한 쓰기 요청을 차단합니다. MyISAM 테이블에 대한 쓰기 작업은 동일한 테이블에 대한 다른 사용자의 읽기 및 쓰기 작업을 차단합니다. ! 표 20-2의 예에 따르면 스레드가 테이블에 대한 쓰기 잠금을 획득하면 잠금을 보유하고 있는 스레드만 테이블을 업데이트할 수 있음을 알 수 있습니다. 다른 스레드의 읽기 및 쓰기 작업은 잠금이 해제될 때까지 기다립니다.

User1: re

mysql> lock table film_text write;

잠금 테이블 조회, 업데이트 및 삽입의 현재 세션을 실행할 수 있습니다.

mysql> select film_id,title from film_text where film_id = 1001;

E
USER2:

mysql> select film_id,title from film_text where film_id = 1001;

대기 중
User1:

잠금 해제:

Rreeeee

user2:

잠금 잠금 , 쿼리는 다음을 반환합니다.
InnoDB 스토리지 엔진의 공유 잠금 실험

mysql> unlock tables;

USER1:

현재 세션은 actor_id=178의 레코드에 공유 모드 공유 잠금을 추가합니다.

USER1： 
mysql> set autocommit = 0; 
USER2： 
mysql> set autocommit = 0;

USER2:

다른 세션은 여전히 ​​레코드를 쿼리할 수 있습니다. , 그리고 또한 레코드에 공유 모드 공유 잠금을 추가할 수 있습니다:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

USER1:

현재 세션이 잠긴 레코드를 업데이트하고 잠금을 기다리고 있습니다:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

Waiting

USER2:

다른 세션도 레코드를 업데이트합니다. , 교착 상태가 종료됩니다:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

ERROR 1213 (40001): 잠금을 시도하는 동안 교착 상태가 발견되었습니다. 트랜잭션을 다시 시작해 보세요.

USER1:
잠금을 획득한 후 성공적으로 업데이트할 수 있습니다:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

다음의 독점 잠금 InnoDB 스토리지 엔진 예시

USER1： 
mysql> set autocommit = 0; 
USER2： 
mysql> set autocommit = 0;

USER1：
当前session对actor_id=178的记录加for update的排它锁：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

USER2：
其他session可以查询该记录，但是不能对该记录加共享锁，会等待获得锁：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178;

USER1：
当前session可以对锁定的记录进行更新操作，更新后释放锁：

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

USER2：
其他session获得锁，得到其他session提交的记录：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

更新性能优化的几个重要参数
bulk_insert_buffer_size
批量插入缓存大小,这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时,提高效率.默认值是8M.可以针对数据量的大小,翻倍增加.
concurrent_insert
并发插入,当表没有空洞(删除过记录),在某进程获取读锁的情况下,其他进程可以在表尾部进行插入.
值可以设0不允许并发插入, 1当表没有空洞时,执行并发插入, 2不管是否有空洞都执行并发插入.
默认是1针对表的删除频率来设置.
delay_key_write
针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘.值为 0不开启, 1开启.默认开启.
delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延迟插入,将数据先交给内存队列,然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持,目前来看,常用的InnoDB不支持, MyISAM支持.根据实际情况调大,一般默认够用了。

위 내용은 mysql 데이터베이스의 잠금 메커니즘에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!