집 > 기사 > 데이터 베이스 > MySQL의 잠금(전역 잠금, 테이블 수준 잠금, 행 잠금)에 대한 심층적인 이해

MySQL의 잠금(전역 잠금, 테이블 수준 잠금, 행 잠금)에 대한 심층적인 이해

青灯夜游앞으로: 2021-08-31 10:43:551990검색

이 글은 MySQL의 잠금을 이해하고 MySQL의 전역 잠금, 테이블 수준 잠금 및 행 잠금을 소개하는 데 도움이 되기를 바랍니다.

MySQL의 잠금은 잠금 범위에 따라 크게 글로벌 잠금, 테이블 수준 잠금, 행 잠금의 세 가지 범주로 나눌 수 있습니다.

1 글로벌 잠금

글로벌 잠금은 전체 데이터베이스를 잠급니다. 사례. MySQL은 전역 읽기 잠금을 추가하는 방법을 제공합니다. 명령은 읽기 잠금으로 테이블 플러시입니다. 전체 라이브러리를 읽기 전용 상태로 만들어야 하는 경우 이 명령을 사용할 수 있습니다. 그 후에는 다른 스레드의 다음 명령문이 차단됩니다: 데이터 업데이트 명령문(데이터 추가, 삭제 및 수정), 데이터 정의 명령문. (테이블 생성, 테이블 구조 수정 등 포함) 및 트랜잭션 커밋 문 업데이트. [관련 권장 사항: mysql 튜토리얼(동영상)] Flush tables with read lock。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。【相关推荐：mysql教程(视频)】

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本

但是让整个库都只读，可能出现以下问题：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

在可重复读隔离级别下开启一个事务能够拿到一致性视图

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。single-transaction只适用于所有的表使用事务引擎的库

1.既然要全库只读，为什么不使用set global readonly=true的方式？

在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改global变量的方式影响面更大
在异常处理机制上有差异。如果执行Flush tables with read lock命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高

二、表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）

表锁的语法是lock tables … read/write。可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象

如果在某个线程A中执行lock tables t1 read,t2 wirte;

전역 잠금의 일반적인 사용 시나리오는 전체 데이터베이스의 논리적 백업을 만드는 것입니다. 즉, 전체 데이터베이스의 모든 테이블을 선택하여 텍스트로 저장합니다

그러나 전체 데이터베이스를 읽기 전용으로 만들 경우 다음과 같은 문제가 발생할 수 있습니다.

메인 데이터베이스에 백업하면 업데이트가 불가능합니다. 기본적으로 슬레이브 데이터베이스에 백업을 하면 백업 기간 동안 마스터 데이터베이스에서 동기화된 binlog를 슬레이브 데이터베이스가 실행할 수 없어 마스터-슬레이브 열기가 지연됩니다. 반복 읽기 격리 수준의 트랜잭션은 일관성을 얻을 수 있습니다. SexView

1. 전체 라이브러리가 읽기 전용이므로 set global readonly=true를 사용하면 어떨까요?
일부 시스템에서는 라이브러리가 기본 라이브러리인지 대기 라이브러리인지 결정하는 등의 다른 논리에 읽기 전용 값이 사용됩니다. 따라서 전역 변수를 수정하는 방법이 예외 처리 메커니즘에 더 큰 영향을 미칩니다. Flush tables with read lock 명령을 수행한 후 클라이언트의 연결이 비정상적으로 끊어지면 MySQL은 자동으로 전역 잠금을 해제하고 전체 라이브러리가 정상적으로 업데이트 가능한 상태로 돌아갑니다. 전체 라이브러리를 읽기 전용으로 설정한 후 클라이언트에서 예외가 발생하면 데이터베이스가 읽기 전용 상태로 유지되므로 전체 라이브러리가 오랫동안 쓸 수 없는 상태가 되며 위험이 더 커집니다

2. 테이블 수준 잠금

MySQL의 잠금(전역 잠금, 테이블 수준 잠금, 행 잠금)에 대한 심층적인 이해 MySQL에는 두 가지 유형의 테이블 수준 잠금이 있습니다. 하나는 테이블 잠금이고 다른 하나는 메타 데이터 잠금(meta data lock, MDL)입니다.

🎜테이블 잠금의 구문은 다음과 같습니다. 테이블 잠금... 읽기/쓰기. 잠금 해제 테이블을 사용하여 잠금을 적극적으로 해제하거나 클라이언트 연결이 끊어질 때 자동으로 해제할 수 있습니다. 잠금 테이블 구문은 다른 스레드의 읽기 및 쓰기를 제한하는 것 외에도 특정 스레드 A에서 lock tables t1 read, t2 wirte;를 실행하면 이 스레드의 다음 작업 개체도 제한합니다. 이 명령문을 실행하면 다른 스레드의 명령문인 t1 쓰기와 t2 읽기 및 쓰기 명령이 차단됩니다. 동시에 스레드 A는 잠금 해제 테이블을 실행하기 전에 t1을 읽고 t2를 읽고 쓰는 작업만 수행할 수 있습니다. t1에 쓰는 것조차 허용되지 않습니다🎜🎜테이블 수준 잠금의 또 다른 유형은 MDL입니다. MDL은 명시적으로 사용할 필요가 없으며 테이블에 액세스할 때 자동으로 추가됩니다. MDL의 기능은 읽기 및 쓰기의 정확성을 보장하는 것입니다. 쿼리가 테이블의 데이터를 순회하고 다른 스레드가 실행 중에 테이블 구조를 변경하고 열을 삭제하는 경우 쿼리 스레드에서 얻은 결과는 테이블 구조와 일치하지 않으므로 확실히 작동하지 않습니다🎜🎜🎜 MySQL5 버전 .5에서는 테이블을 추가, 삭제, 수정 및 쿼리할 때 MDL 읽기 잠금을 추가합니다. 테이블에 구조를 변경할 때는 MDL 쓰기 잠금을 추가하세요🎜🎜🎜🎜🎜읽기 잠금은 상호 배타적이지 않습니다. 따라서 여러 스레드가 동시에 테이블을 추가, 삭제, 수정 및 확인할 수 있습니다.🎜🎜🎜🎜읽기-쓰기 잠금과 쓰기 잠금은 상호 배타적이므로 테이블 구조 변경의 보안을 보장합니다. 따라서 동시에 테이블에 필드를 추가하려는 두 개의 스레드가 있는 경우 테이블에 필드를 추가하거나 필드를 수정하려면 그 중 하나가 다른 스레드의 실행이 완료될 때까지 기다려야 합니다. 인덱스를 추가하려면 전체 테이블의 데이터를 스캔해야 합니다. 대규모 테이블을 운영할 때는 온라인 서비스에 영향을 주지 않도록 특히 주의해야 합니다🎜🎜🎜🎜 세션 A가 먼저 시작됩니다. 이때 테이블 t에 MDL 읽기 잠금이 추가됩니다. 세션 B에도 MDL 읽기 잠금이 필요하므로 정상적으로 실행될 수 있습니다. 나중에 세션 A의 MDL 읽기 잠금이 해제되지 않았기 때문에 세션 C가 차단되며, 세션 C에는 MDL 쓰기 잠금이 필요하므로 차단만 가능합니다. 세션 C 자체만 차단되는 경우에는 중요하지 않지만 테이블 t에 대한 MDL 읽기 잠금을 적용하려는 모든 향후 요청도 세션 C에 의해 차단됩니다. 테이블에 대한 모든 추가, 삭제, 수정 및 쿼리 작업은 먼저 MDL 읽기 잠금을 적용한 다음 모두 잠기므로 이제 테이블을 완전히 읽거나 쓸 수 없습니다🎜

트랜잭션의 MDL 잠금은 명령문 실행 초기에 적용되지만 명령문 종료 직후에 해제되지 않고 전체 트랜잭션이 제출된 후에 해제됩니다.

1 안전하게 필드를 추가하는 방법. 작은 테이블로?

먼저 긴 트랜잭션을 해결해야 합니다. 트랜잭션이 제출되지 않으면 DML 잠금이 항상 점유됩니다. MySQL의 information_schema 라이브러리의 innodb_trx 테이블에서 현재 실행 중인 트랜잭션을 확인할 수 있습니다. DDL로 변경할 테이블에 긴 트랜잭션이 실행되는 경우 먼저 DDL을 일시 중지하거나 긴 트랜잭션을 종료하는 것을 고려하세요

2. 변경하려는 테이블이 데이터 양은 아니지만 핫스팟 테이블인 경우. 위의 요청이 너무 자주 발생하는데 필드를 추가해야 하는데 어떻게 해야 하나요?

Alter Table 문에 대기 시간을 설정하세요. 지정된 대기 시간 내에 MDL 쓰기 잠금을 얻을 수 없다면 후속 비즈니스 문을 차단하지 말고 먼저 포기하세요. 그런 다음

명령을 다시 시도하여 프로세스를 반복합니다. 3. 행 잠금

MySQL의 행 잠금은 엔진 계층의 각 엔진에 의해 구현됩니다. 그러나 모든 엔진이 행 잠금을 지원하는 것은 아닙니다. 예를 들어 MyISAM 엔진은 행 잠금을 지원하지 않습니다. 행 잠금은 데이터 테이블의 행 레코드에 대한 잠금입니다. 예를 들어 트랜잭션 A는 행을 업데이트하고 트랜잭션 B도 이때 동일한 행을 업데이트해야 합니다. 업데이트하기 전에 트랜잭션 A의 작업이 완료될 때까지 기다려야 합니다. 1. 2단계 잠금 프로토콜

두 가지. 트랜잭션 A가 보유한 트랜잭션은 커밋 시에만 해제됩니다. 트랜잭션 A가 커밋을 실행할 때까지 트랜잭션 B는 계속해서 실행될 수 있습니다. 시점에 추가되지만 더 이상 필요하지 않을 때 즉시 해제되지 않고 거래가 끝날 때까지 해제됩니다. 이것이 2단계 잠금 프로토콜입니다

MySQL의 잠금(전역 잠금, 테이블 수준 잠금, 행 잠금)에 대한 심층적인 이해 트랜잭션에서 여러 행을 잠가야 하는 경우 잠금 충돌을 일으킬 가능성이 가장 높고 동시성에 영향을 미칠 가능성이 가장 높은 잠금은 가능한 한 멀리 배치해야 합니다

당신이 영화표 온라인 거래 사업을 구현하고 싶다고 가정하자. 고객 A는 영화관 B에서 영화표를 사고 싶어한다. 비즈니스에는 다음 작업이 포함되어야 합니다. 1. 고객 A의 계정 잔액에서 영화 티켓 가격을 공제합니다.

2. 극장 B의 계정 잔액에 영화 티켓 가격을 추가합니다. 3.

트랜잭션을 보장하려면 원자성을 달성하려면 이 세 가지 작업을 하나의 트랜잭션에 배치해야 합니다. 거래에서 이 세 가지 명세서의 순서를 어떻게 정렬합니까?

B극장에서 동시에 티켓을 구매하려는 또 다른 고객 C가 있는 경우 두 거래 사이에 충돌하는 부분은 진술 2입니다. 동일한 극장 계정의 잔액을 업데이트하려고 하므로 동일한 데이터 행을 수정해야 합니다. 2단계 잠금 프로토콜에 따르면 트랜잭션이 커밋되면 작업에 필요한 모든 행 잠금이 해제됩니다. 따라서 명세서 2를 마지막에 배열하면(예를 들어 3, 1, 2의 순서로) 극장 계좌 잔고 라인의 잠금 시간이 가장 짧아집니다. 이는 트랜잭션 간의 잠금 대기를 최소화하고 동시성을 향상시킵니다.

2. 교착 상태 및 교착 상태 감지

동시 시스템에서는 순환 리소스 종속성이 서로 다른 스레드에서 발생하고 관련된 모든 스레드가 대기 중입니다. 다른 스레드가 리소스를 해제하면 이러한 스레드는 무한 대기 상태에 진입합니다. 이를 교착 상태

트랜잭션 A는 트랜잭션 B가 ID=2인 행 잠금을 해제하기를 기다리고 있는 반면, 트랜잭션 B는 트랜잭션 A가 ID=1인 행 잠금을 해제하기를 기다리고 있습니다. 트랜잭션 A와 트랜잭션 B는 서로의 리소스가 해제되기를 기다리고 있습니다. 이는 교착 상태에 진입했음을 의미합니다. 교착 상태가 발생하면 두 가지 전략이 있습니다.

한 가지 전략은 타임아웃될 때까지 직접 기다리는 것입니다. 이 시간 초과는 innodb_lock_wait_timeout 매개변수를 통해 설정할 수 있습니다. 또 다른 전략은 교착 상태 감지를 시작하는 것입니다. 교착 상태가 발견된 후 다른 트랜잭션이 계속 실행될 수 있도록 교착 상태 체인에서 트랜잭션을 적극적으로 롤백합니다. innodb_deadlock_Detect 매개변수를 on으로 설정하세요. 이는 이 논리를 켜는 것을 의미합니다

InnoDB에서 innodb_lock_wait_timeout의 기본값은 50초입니다. 이는 첫 번째 전략이 채택되면 교착 상태가 발생할 때 첫 번째 잠긴 스레드가 대기해야 함을 의미합니다. 이는 시간 초과되어 50초 후에 종료되며, 그런 다음 다른 스레드가 계속 실행될 수 있습니다. 온라인 서비스의 경우 이러한 대기 시간은 허용되지 않는 경우가 많습니다. 정상적인 상황에서는 활성 교착 상태 검사 전략을 채택해야 하며 innodb_deadlock_Detect 자체의 기본값은 켜져 있습니다. 활성 교착 상태 모니터링은 교착 상태 발생 시 신속하게 감지하고 처리할 수 있지만 추가적인 부담이 있습니다. 트랜잭션이 잠길 때마다 해당 스레드가 다른 스레드에 의해 잠겼는지 등을 확인하고 마지막으로 모든 트랜잭션이 동일한 행을 업데이트해야 하는 경우 교착 상태가 발생하는지 확인해야 합니다. 이 시나리오에서 새로 차단된 각 스레드는 자체 추가로 인해 교착 상태가 발생할지 여부를 결정해야 합니다. 이는 O(n)의 시간 복잡도를 갖는 작업입니다.

MySQL의 잠금(전역 잠금, 테이블 수준 잠금, 행 잠금)에 대한 심층적인 이해 이 핫 행 업데이트 성능 문제를 해결하는 방법은 무엇입니까?

1. 이 비즈니스가 교착 상태에 빠지지 않을 것이라고 확신한다면 일시적으로 교착 상태 감지 기능을 끌 수 있습니다

2.
3. 잠금 충돌을 줄이려면 한 줄을 여러 논리 줄로 변경하세요. 극장 계정을 예로 들면, 10개 레코드 등 여러 레코드에 배치하는 것을 고려할 수 있습니다. 극장의 총 계정 가치는 이 10개 레코드의 가치를 합한 것과 같습니다. 이런 식으로 극장 계좌에 돈을 추가하고 싶을 때마다 추가할 레코드 중 하나를 무작위로 선택하면 됩니다. 이렇게 하면 각 충돌 확률이 원래 멤버의 1/10이 되어 잠금 대기 횟수를 줄이고 교착 상태 감지의 CPU 소비를 줄일 수 있습니다

4. 명령문 실행이 이렇게 느린 이유는 무엇입니까? 한 줄만 확인하시겠습니까?

두 개의 필드 id와 c로 테이블을 구성하고 100,000행의 레코드를 삽입합니다.
```
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
	declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
END
```
1. 카테고리 1: 오랫동안 쿼리가 반환되지 않습니다
```
select * from t3 where id=1;
```
쿼리 결과가 반환되지 않습니다. 오랜 시간이 지난 후 show processlist 명령을 사용하여 현재 문의 상태를 확인합니다

1), Waiting for MDL lock

아래 그림과 같이 show processlist; 명령을 사용하여 Waiting for table의 다이어그램을 봅니다. 메타데이터 잠금

이 상태는 현재를 나타냅니다. 테이블 t에 MDL 쓰기 잠금을 요청하거나 보류하는 스레드가 있어 select 문을 차단합니다

장면 반복:

sessionA가 MDL 쓰기를 보유합니다. lock table 명령을 통한 테이블 t의 잠금 및 sessionB 쿼리는 MDL 읽기 잠금을 획득해야 합니다. 따라서 sessionB는 대기 상태에 들어갑니다

이러한 문제를 해결하는 방법은 MDL 쓰기 잠금을 보유한 사람이 누구인지 찾아내어 죽이는 것입니다. 하지만 show processlist 결과에서 sessionA의 Command 컬럼이 Sleep이므로 검색이 불편합니다. sys.schema_table_lock_waits 테이블을 쿼리하면 차단을 유발한 프로세스 ID를 직접 찾아내고 kill로 연결을 끊을 수 있습니다. 명령(MySQL을 시작할 때performance_schema=on을 설정해야 합니다. off로 설정하는 것과 비교하면 약 10% 성능 손실이 있습니다.)
```
select blocking_pid from sys.schema_table_lock_waits;
```
2), 플러시

를 기다린 후 테이블에서 다음 SQL 문을 실행합니다. t:
```
select * from information_schema.processlist where id=1;
```
확인 특정 스레드의 상태는 테이블 플러시 대기

입니다. 이 상태는 이제 테이블 t에서 플러시 작업을 수행하는 스레드가 있음을 의미합니다. MySQL의 테이블에는 일반적으로 두 가지 플러시 작업 사용법이 있습니다.
```
flush tables t with read lock;flush tables with read lock;
```
이 두 플러시 문은 테이블 t가 지정되면 테이블 t만 닫히는 것을 의미합니다. 특정 테이블 이름이 지정되지 않으면 열려 있는 모든 테이블을 닫는다는 의미입니다. MySQL

그러나 일반적인 상황에서는 이 두 문이 다른 스레드에 의해 차단되지 않는 한 매우 빠르게 실행됩니다

따라서 Waiting for table Flush 상태가 나타날 수 있는 상황은 다음과 같습니다. 플러시 테이블이 있습니다. 명령이 다른 스레드에 의해 차단되었습니다.

Scene recurrence:

sessionA에서 sleep(1)은 한 줄에 한 번 호출되므로 이 문은 기본적으로 100,000초 동안 실행됩니다. sessionA에 의해 열렸습니다. 그런 다음 sessionB가 테이블 t를 플러시한 다음 테이블 t를 닫을 때 sessionA의 쿼리가 끝날 때까지 기다려야 합니다. 이런 식으로 sessionC가 다시 쿼리하려고 하면 플러시 명령

3)에 의해 차단되고, 행 잠금을 기다리는 중
```
select * from t where id=1 lock in share mode;
```
id=1인 레코드에 접근할 때 읽기 잠금이 필요하므로, 이때 이미 트랜잭션이 있는 경우 이 레코드 행에 쓰기 잠금이 유지되면 select 문이 차단됩니다

시나리오 재발:

sessionA가 트랜잭션을 시작하고 쓰기 잠금을 점유했으며 제출하지 않아 sessionB가 차단되었습니다. 이유

2. 두 번째 범주: 느린 쿼리

sessionA는 먼저 일관된 스냅샷 명령과 함께 시작 트랜잭션을 사용하여 트랜잭션을 열고 트랜잭션(스냅샷 읽기라고도 합니다. MVCC 메커니즘은 실행 취소 로그에서 제출된 데이터를 가져오는 데 사용됩니다. 따라서 읽기는 비차단입니다.) 그런 다음 sessionB는 업데이트 문을 실행합니다.

sessionB가 100만 개의 업데이트 문을 실행한 후 1백만 개의 롤백 로그 생성

带lock in share mode的语句是当前读，因此会直接读到1000001这个结果，速度很快；而select * from t where id=1这个语句是一致性读，因此需要从1000001开始，依次执行undo log，执行了100万次以后，才将1这个结果返回

五、间隙锁

建表和初始化语句如下：
```
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
```
这个表除了主键id外，还有一个索引c

为了解决幻读问题，InnoDB引入了间隙锁，锁的就是两个值之间的空隙

当执行select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录

行锁分成读锁和写锁

跟间隙锁存在冲突关系的是往这个间隙中插入一个记录这个操作。间隙锁之间不存在冲突关系

这里sessionB并不会被堵住。因为表t里面并没有c=7会这个记录，因此sessionA加的是间隙锁(5,10)。而sessionB也是在这个间隙加的间隙锁。它们用共同的目标，保护这个间隙，不允许插入值。但它们之间是不冲突的

间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。表t初始化以后，如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。因为+∞是开区间，在实现上，InnoDB给每个索引加了一个不存在的最大值supremum，这样才符合都是前开后闭区间

间隙锁和next-key lock的引入，解决了幻读的问题，但同时也带来了一些困扰

间隙锁导致的死锁：

1.sessionA执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10)

2.sessionB执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突

3.sessionB试图插入一行(9,9,9)，被sessionA的间隙锁挡住了，只好进入等待

4.sessionA试图插入一行(9,9,9)，被sessionB的间隙锁挡住了

两个session进入互相等待状态，形成了死锁

间隙锁的引入可能会导致同样的语句锁住更大的范围，这其实是影响并发度的

在读提交隔离级别下，不存在间隙锁

六、next-key lock

表t的建表语句和初始化语句如下：
```
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
```
1、next-key lock加锁规则
- 原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间
- 原则2：查找过程中访问到的对象才会加锁
- 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
- 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
- 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止
这个规则只限于MySQL5.x系列

2、案例一：等值查询间隙锁

1.由于表t中没有id=7的记录，根据原则1，加锁单位是next-key lock，sessionA加锁范围就是(5,10]

2.根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5,10)

所以，sessionB要往这个间隙里面插入id=8的记录会被锁住，但是sessionC修改id=10这行是可以的

3、案例二：非唯一索引等值锁

1.根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock

2.c是普通索引，因此访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock

3.根据优化2，等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)

4.根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有任何锁，这就是为什么sessionB的update语句可以执行完成

锁是加在索引上的，在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁，这样的话sessionB的update语句会被阻塞住。如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段

4、案例三：主键索引范围锁

1.开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock(5,10]。根据优化1，主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁

2.范围查询就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]

所以，sessionA这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10,15]

5、案例四：非唯一索引范围锁

这次sessionA用字段c来判断，加锁规则跟案例三唯一的不同是：在第一次用c=10定位记录的时候，索引c上加上(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，因此最终sessionA加的锁是索引c上的(5,10]和(10,15]这两个next-key lock

6、案例五：唯一索引范围锁bug

sessionA是一个范围查询，按照原则1的话，应该是索引id上只加(10,15]这个next-key lock，并且因为id是唯一键，所以循环判断到id=15这一行就应该停止了

但是实现上，InnoDB会扫描到第一个不满足条件的行为止，也就是id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上

所以，sessionB要更新id=20这一行是会被锁住的。同样地，sessionC要插入id=16的一行，也会被锁住

7、案例六：非唯一索引上存在等值的例子
```
insert into t values(30,10,30);
```
新插入的这一行c=10，现在表里有两个c=10的行。虽然有两个c=10，但是它们的主键值id是不同的，因此这两个c=10的记录之间也是有间隙的

sessionA在遍历的时候，先访问第一个c=10的记录。根据原则1，这里加的是(c=5,id=5)到(c=10,id=10)这个next-key lock。然后sessionA向右查找，直到碰到(c=15,id=15)这一行，循环才结束。根据优化2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10)到(c=15,id=15)的间隙锁

也就是说，这个delete语句在索引c上的加锁范围，就是下图中蓝色区域覆盖的部分，这个蓝色区域左右两边都是虚线，表示开区间

8、案例七：limit语句加锁

加了limit 2的限制，因此在遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引c上的加锁范围就变成了从(c=5,id=5)到(c=10,id=30)这个前开后闭区间，如下图所示：

再删除数据的时候尽量加limit，这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围

9、案例八：一个死锁的例子

1.sessionA启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock(5,10]和间隙锁(10,15)

2.sessionB的update语句也要在索引c上加next-key lock(5,10]，进入锁等待

3.然后sessionA要再插入(8,8,8)这一行，被sessionB的间隙锁锁住。由于出现了死锁，InnoDB让sessionB回滚

sessionB的加next-key lock(5,10]操作，实际上分成了两步，先是加(5,10)间隙锁，加锁成功；然后加c=10的行锁，这时候才被锁住的

七、用动态的观点看加锁

表t的建表语句和初始化语句如下：
```
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
```
1、不等号条件里的等值查询
```
begin;
select * from t where id>9 and id<12 order by id desc for update;
```
利用上面的加锁规则，这个语句的加锁范围是主键索引上的(0,5]、(5,10]和(10,15)。加锁单位是next-key lock，这里用到了优化2，即索引上的等值查询，向右遍历的时候id=15不满足条件，所以next-key lock退化为了间隙锁(10,15)

1.首先这个查询语句的语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到第一个id

2.这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10,15)这个间隙

3.然后根据order by id desc，再向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock (0,5]

在执行过程中，通过树搜索的方式定位记录的时候，用的是等值查询的方法

2、等值查询的过程
```
begin;
select id from t where c in(5,20,10) lock in share mode;
```
这条in语句使用了索引c并且rows=3，说明这三个值都是通过B+树搜索定位的

在查找c=5的时候，先锁住了(0,5]。但是因为c不是唯一索引，为了确认还有没有别的记录c=5，就要向右遍历，找到c=10确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)。执行c=10会这个逻辑的时候，加锁的范围是(5,10]和(10,15)，执行c=20这个逻辑的时候，加锁的范围是(15,20]和(20,25)

这条语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁
```
select id from t where c in(5,20,10) order by c desc for update;
```
由于语句里面是order by c desc，这三个记录锁的加锁顺序是先锁c=20，然后c=10，最后是c=5。这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁

八、insert语句的锁为什么这么多？

1、insert … select语句

表t和t2的表结构、初始化数据语句如下：
```
CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t;
```
在可重复读隔离级别下，binlog_format=statement时执行下面这个语句时，需要对表t的所有行和间隙加锁
```
insert into t2(c,d) select c,d from t;
```
2、insert循环写入

要往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1，SQL语句如下：
```
insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);
```
这个语句的加锁范围，就是表t索引c上的(3,4]和(4,supermum]这两个next-key lock，以及主键索引上id=4这一行

执行流程是从表t中按照索引c倒序吗，扫描第一行，拿到结果写入到表t2中，因此整条语句的扫描行数是1

但如果要把这一行的数据插入到表t中的话：
```
insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);
```
explain结果中的Extra字段中Using temporary字段，表示这个语句用到了临时表

执行流程如下：

1.创建临时表，表里有两个字段c和d

2.按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表

3.由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中

这个语句会导致在表t上做全表扫描，并且会给索引c上的所有间隙都加上共享的next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据

需要临时表是因为这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符

3、insert唯一键冲突

sessionA执行的insert语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁，sessionA持有索引c上的(5,10]共享next-key lock（读锁）

在sessionA执行rollback语句回滚的时候，sessionC几乎同时发现死锁并返回

1.在T1时刻，启动sessionA，并执行insert语句，此时在索引c的c=5上加了记录锁。这个索引是唯一索引，因此退化为记录锁

2.在T2时刻，sessionA回滚。这时候，sessionB和sessionC都试图继续执行插入操作，都要加上写锁。两个session都要等待对方的行锁，所以就出现了死锁

4、insert into … on duplicate key update

上面这个例子是主键冲突后直接报错，如果改写成
```
insert into t values(11,10,10) on duplicate key update d=100;
```
就会给索引c上(5,10]加一个排他的next-key lock（写锁）

insert into … on duplicate key update的语义逻辑是，插入一行数据，如果碰到唯一键约束，就继续执行后面的更新语句。如果有多个列违反了唯一性索引，就会按照索引的顺序，修改跟第一个索引冲突的行

表t里面已经有了(1,1,1)和(2,2,2)这两行，执行这个语句效果如下：

主键id是先判断的，MySQL认为这个语句跟id=2这一行冲突，所以修改的是id=2的行

思考题：

1、如果要删除一个表里面的前10000行数据，有以下三种方法可以做到：
- 第一种，直接执行delete from T limit 10000;
- 第二种，在一个连接中循环执行20次delete from T limit 500；
- 第三种，在20个连接中同时执行delete from T limit 500；
选择哪一种方式比较好？

参考答案：

第一种方式，单个语句占用时间长，锁的时间也比较长，而且大事务还会导致主从延迟

第三种方式，会人为造成锁冲突

第二种方式相对较好

更多编程相关知识，请访问：编程入门！！