MySQL 데이터베이스 InnoDB 엔진의 행 수준 잠금 잠금 범위에 대한 자세한 설명

Mysql 데이터베이스 InnoDB 엔진은 행 수준 잠금을 지원합니다. 즉, 테이블의 특정 데이터 행에 대해 잠금 작업을 수행할 수 있다는 의미입니다. 잠금 작업의 영향은 다음과 같습니다. 하나의 트랜잭션이 테이블의 특정 행에 대해 잠금 작업을 수행하는 경우. , 다른 트랜잭션도 동일한 행에 잠금 작업을 수행해야 하므로 두 번째 트랜잭션의 잠금 작업이 차단될 수 있습니다. 일단 차단되면 두 번째 트랜잭션은 첫 번째 트랜잭션이 완료(커밋 또는 롤백)될 때까지만 대기할 수 있습니다. 또는 시간 초과.

이 기사에서는 주로 행 잠금의 잠금 범위에 초점을 맞춰 InnoDB의 행 잠금과 관련된 개념을 소개합니다.

• 어떤 종류의 SQL 문이 잠길까요?

• 어떤 종류의 자물쇠를 추가해야 하나요?

• 잠금 문으로 잠길 행은 무엇인가요?

배경 지식

위에서 InnoDB의 행 수준 잠금에 대해 간략하게 소개했습니다. 후속 검증 부분을 이해하려면 몇 가지 배경 지식을 추가해야 합니다. 해당 지식을 아주 잘 알고 계시다면 바로 검증 부분으로 넘어갈 수 있습니다.

1. InnoDB 잠금 유형

InnoDB 엔진은 다음과 같은 7가지 유형의 잠금을 사용합니다.

• 공유 및 배타적 잠금(공유 및 배타적 잠금)

• 의도 잠금(의도 잠금)

• 기록 잠금

• 간격 잠금

• 다음 키 잠금

• 삽입 의도 잠금

• 자동 입력 C Locks

이 문서는 주로 공유 및 Exclusive Locks, Record Locks, Gap Locks 및 Next-Key Locks. 다른 유형의 잠금 장치에 관심이 있다면 여기서는 자세히 설명하지 않겠습니다.

1.1 공유 및 배타적 잠금

공유 잠금(S 잠금)과 배타적 잠금(X 잠금)의 개념은 많은 프로그래밍 언어에 등장했습니다. 먼저 MySQL에서 이 두 가지 잠금의 영향을 설명하겠습니다.

• 트랜잭션이 특정 데이터 행에 S 잠금을 추가하면 다른 트랜잭션도 해당 행에 S 잠금을 추가할 수 있지만 해당 행에는 추가할 수 없습니다. X 잠금을 추가합니다.

• 트랜잭션이 특정 데이터 행에 X 잠금을 추가하면 다른 트랜잭션은 해당 행에 S 잠금이나 X 잠금을 추가할 수 없습니다.

공유 잠금과 배타적 잠금 사이의 상호 배타적 관계를 계속 설명하려면 기본 매트릭스 테이블을 사용하십시오.

111

X

그림에서 S는 공유 잠금을 나타내고 X는 독점 잠금을 나타냅니다. 0은 잠금 호환성을 나타내고 1은 잠금 충돌이 차단되지 않음을 나타냅니다. 테이블에서 볼 수 있듯이 트랜잭션이 배타적 잠금을 추가하면 다른 트랜잭션은 잠금을 기다려야 합니다. 여러 공유 잠금은 서로를 차단하지 않습니다.

1.2 레코드 잠금, 갭 잠금, 다음 키 잠금

이 세 가지 유형의 잠금은 모두 잠금 범위를 설명하므로 함께 설명합니다.

다음 정의는 공식 MySQL 문서

• 에서 가져온 것입니다. 레코드 잠금: 레코드 잠금은 인덱스의 레코드를 잠급니다.

• Gap 잠금: Gap 잠금은 인덱스 레코드 중간의 값을 잠그거나 첫 번째 인덱스 레코드 앞의 값 또는 마지막 인덱스 레코드 뒤의 값을 잠급니다.

• Next-Key 잠금: Next-Key 잠금은 인덱스 레코드의 레코드 잠금과 인덱스 레코드 앞의 간격 잠금을 조합한 것입니다.

정의는 모두 색인 레코드를 언급합니다. 왜? 행 잠금과 인덱스의 관계는 무엇입니까? 실제로 InnoDB는 테이블의 인덱스를 검색하거나 스캔하여 잠금 작업을 완료합니다. InnoDB는 만나는 모든 인덱스 데이터에 공유 잠금 또는 배타적 잠금을 추가합니다. 따라서 행 수준 잠금(행 수준 잠금)을 인덱스 레코드 잠금(인덱스 레코드 잠금)이라고 부를 수 있습니다. 행 수준 잠금은 행에 해당하는 인덱스에 추가되기 때문입니다.

세 가지 유형의 자물쇠의 잠금 범위는 서로 다르며 점차 확장됩니다. 다양한 잠금의 잠금 범위를 간략하게 설명하기 위해 예를 들어 보겠습니다. 테이블 t의 인덱스 열에 3, 5, 8, 9의 4가지 숫자 값이 있다고 가정합니다. 공식 문서에 따르면 잠금 범위는 다음과 같습니다. 세 개의 잠금은 다음과 같이 결정됩니다.

• 레코드 잠금의 잠금 범위는 별도의 인덱스 레코드로, 데이터 3, 5, 8, 9의 4개 행입니다.

• 간격 잠금은 행의 간격을 잠급니다. 이는 (-무한대,3), (3,5), (5,8), (8,9), (9,+)으로 집합으로 표시됩니다. ∨).

• Next-Key 잠금은 인덱스 레코드 잠금과 인덱스 레코드 잠금 이전의 간격 잠금을 조합한 것으로 (-무한대,3], (3,5], (5,8)과 같은 집합으로 표현됩니다. ] , (8,9], (9,+무한)

마지막으로 간격 잠금에 대해 세 가지 사항을 추가해야 합니다:

1. 간격 잠금은 다른 트랜잭션이 간격 데이터를 동시에 삽입하는 것을 방지합니다. 효과적인 솔루션 Phantom 문제 이로 인해 모든 트랜잭션 격리 수준이 간격 잠금을 사용하는 것은 아닙니다. MySQL InnoDB 엔진은 반복 읽기(기본) 격리 수준에서만 간격 잠금을 사용합니다. 다른 트랜잭션이 갭에 데이터를 삽입하는 것을 방지합니다. 이는 다른 트랜잭션이 동일한 갭 잠금을 보유하는 것을 방지하지 않습니다. 이는 삽입 문을 제외하고 다른 SQL 문이 문제 없이 동일한 행에 갭 잠금을 추가할 수 있음을 의미합니다. .Blocked.

2. 고유 인덱스 잠금 동작의 경우, 현재로서는 레코드 잠금만 작동합니다

2.InnoDB는 이미 소개했습니다. 잠금 동작은 SQL 문 실행 중에 인덱스 레코드를 검색하여 수행됩니다. 어떤 문이 잠길까요? 다음으로 하나씩 설명하겠습니다.

3. select ... from 문: InnoDB 엔진은 다중을 사용합니다. -비차단 읽기를 구현하기 위한 버전 동시성 제어(MVCC), 따라서 일반 select 읽기 문의 경우 InnoDB는 잠그지 않습니다. [참고 1]

select ... from lock in. 공유 모드 문: 이 문의 차이점 일반적인 select 문은 공유 모드의 잠금이 마지막에 추가된다는 것입니다. 문자 그대로의 의미로 볼 때 이는 잠긴 읽기 문이고 잠금 유형은 공유 잠금(읽기 잠금)입니다. 검색된 모든 인덱스 레코드에 추가되지만 고유 인덱스의 유일한 행을 검색하면 next-key가 인덱스 레코드 잠금으로 다운그레이드됩니다.

select ... from for update 문: 위 명령문과 동일 . , 이 명령문은 배타적 잠금(쓰기 잠금)을 추가합니다. InnoDB는 검색된 모든 인덱스 레코드에 next-key 잠금을 추가하지만 고유 인덱스의 고유 행을 스캔하면 next-key가 인덱스 레코드 업데이트로 다운그레이드됩니다. ... where ... 문: InnoDB는 검색된 모든 인덱스 레코드에 next-key 잠금을 추가하지만, 고유 인덱스의 행만 스캔하면 next-key가 인덱스 레코드 잠금으로 다운그레이드됩니다.
delete ... where ... 명령문: InnoDB는 검색된 모든 인덱스 레코드에 next-key 잠금을 추가하지만, 고유 인덱스의 고유 행을 스캔하면 next-key가 인덱스 레코드 잠금으로 다운그레이드됩니다.
insert 문: InnoDB는 삽입할 행에만 배타적 인덱스 레코드 잠금을 설정합니다.
두 가지 최종 사항:
쿼리가 보조 인덱스를 사용하고 인덱스 레코드에 배타적 잠금을 추가하면 InnoDB는 해당 집계 인덱스 레코드를 잠급니다.
SQL 문이 인덱스를 사용할 수 없는 경우 MySQL은 문을 처리하기 위해 전체 테이블을 스캔해야 합니다. 결과적으로 테이블의 각 행이 잠기고 다른 사용자가 테이블에 삽입하는 모든 것이 차단됩니다.
SQL 문 확인

더 이상 고민하지 말고 이 문서의 SQL 문 확인 부분을 살펴보겠습니다.

1. 테스트 환경

데이터베이스: MySQL 5.6.35
트랜잭션 격리 수준: 반복 읽기
데이터베이스 액세스 터미널: mysql 클라이언트

2. 검증 시나리오

2.1 시나리오 1

테이블 생성:

CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(8) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

데이터 삽입:

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

먼저 SQL 문의 템플릿을 실행합니다:

Steps	client 1	client 2
1	begin;	--
2	선택 * FROM user where name='e' 업데이트용;	--
3	--	begin;
4	--	INSERT INTO `user`(`id`,` 이름`) VALUES (10, #{name});
5	rollback;	--
6	--	rollback;

Re 단계의 지명 5개 값, 관찰 결과:

이름 값	실행 결과
a	notblocking
b	차단 금지
d	차단
e	차단
f	차단
h	차단 금지
i	차단 금지

결과를 관찰한 결과, SQL 문
SELECT * FROM user where name='e' for updateSELECT * FROM user where name='e' for update
一共锁住索引name中三行记录，(c,e]区间应该是next-key锁而(e,h)区间是索引记录e后面的间隙。

接下来我们确定next-key锁中哪部分是索引记录锁哪部分是间隙锁。

执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name='e' for update;	--
3	--	SELECT * FROM user where name=#{name} for update;
5	rollback;	--
6	--	rollback;

替换步骤5中name的值，观察结果：

name的值	执行结果
d	不阻塞
e	阻塞
f	不阻塞

因为间隙锁只会阻止insert语句，所以同样的索引数据，insert语句阻塞而select for update语句不阻塞的就是间隙锁，如果两条语句都阻塞就是索引记录锁。

观察执行结果可知，d和f为间隙锁，e为索引记录锁。

结论：通过两条SQL，我们确定了对于辅助索引name在查询条件为 where name='e'  가 총 3개를 잠긴 것으로 나타났습니다. 인덱스 이름의 레코드 행, (c, e] 간격은 다음 키 잠금이어야 하며 (e, h) 간격은 인덱스 레코드 e 이후의 간격입니다.

다음으로 다음 키의 어느 부분을 결정합니다. 키 잠금은 인덱스 레코드 잠금이며 SQL 문장을 실행하기위한 갭 잠금 장치입니다. --
2

• SELECT * FROM user where name ='e' 업데이트용;

--
3

• --

SELECT * FROM user where name=#{name} 업데이트용 ;

5

rollback;--6--rollback; 이름 값실행 결과d차단 안함실행 결과를 관찰하면 d와 f가 갭 잠금이고 e가 인덱스 레코드 잠금임을 알 수 있습니다.

	5단계에서 name 값을 바꾸고 결과를 관찰합니다.
	Notblocking
e		Blocking
	f
		간격 때문에 잠금은 insert 문만 차단하므로 동일한 인덱스 데이터가 insert 문은 차단되지만 select for update 문은 차단되지 않는 경우 , 두 문이 모두 차단되면 인덱스 레코드 잠금입니다.

결론: 두 개의 SQL을 통해 쿼리 조건이 이고 name='e' 가 (c, e], (e, g)일 때 보조 인덱스 이름에 대한 잠금 범위를 확인했습니다. 그 중: 🎜🎜🎜🎜 SQL 문으로 스캔한 인덱스 레코드 e에 인덱스 레코드 잠금 [e]를 추가합니다. 🎜🎜🎜🎜 e 앞의 간격을 잠그고 c 사이의 데이터(c, e)에 간격 잠금을 추가합니다. and e. 🎜🎜🎜🎜처음 두 개는 다음 키 잠금(c, e]를 구성합니다. 🎜🎜🎜🎜e 뒤의 간격(e, g)도 잠겨 있다는 점에 주목할 가치가 있습니다. 🎜🎜🎜🎜세심함 여기 독자들은 테스트 데이터에 간격 경계 데이터 c와 g가 없다는 것을 발견했을 수도 있습니다. 다음으로 간격 경계 값을 테스트하겠습니다. 🎜🎜🎜🎜🎜Step 🎜🎜client 1🎜🎜client 2. 🎜🎜 🎜🎜🎜🎜1🎜🎜begin;🎜🎜--🎜🎜🎜🎜2🎜🎜SELECT * FROM user where name='e' for update;🎜🎜--🎜🎜🎜🎜3🎜🎜- 시작하다; 🎜🎜🎜🎜4🎜🎜--🎜🎜INSERT INTO `user` (`id`, `name`) VALUES (#{id}, #{name});🎜🎜🎜🎜5🎜🎜rollback;🎜 🎜- -🎜🎜🎜🎜6🎜🎜--🎜🎜롤백;🎜🎜🎜🎜

5단계에서 id 및 name 값을 바꾸고 결과를 관찰합니다.

------g----gc-1c 1c2c3c4c5c6 c7c8c9 c10c --12차단-위의 실행 결과를 관찰한 결과, name이 c와 e와 같을 때 insert문의 결과가 잠시 잠겨 있고, ID 값에 따라 잠시 잠기지 않습니다. 이러한 결과가 발생하려면 ID 열이 잠겨 있어야 합니다. id=5 데이터 행의 결과를 살펴보지 않으면 다음과 같은 패턴을 발견합니다. name=e일 때 name=e에 해당하는 id=7의 id 집계 인덱스 데이터 레코드 이전 간격 (5,7), (3,5), (1,3) 및 (-무한대,1)은 모두 잠겨 있습니다. select * from user where id = x for update; 문을 사용하여 위의 간격에 추가된 잠금이 모두 간격 잠금인지 확인할 수 있습니다. 다음으로 id=5의 잠금 상황을 설명하겠습니다. SQL 문 실행을 위한 템플릿:

The value of id	name=c				-3
그룹 플러그			--		-2
차단			-1		차단하지 않음
g	차단함		1		차단하지 않음
g	차단 금지		2		차단 금지
g	차단		3		차단하지 않음
g	차단하지 않음		4		차단
g	차단		5		차단
g	차단		6		차단
g	차단		7		차단하지 않음
g	차단하지 않음		8		차단
g	차단 안함		9		차단하지 않음
g	차단하지 않음		10		차단
g	차단하지 않음		11		차단
-		c		-	-
		When name=c, name=c에 해당하는 <code>id=3의 ID 집계 색인 데이터 레코드 뒤의 간격 (3,5), (5,7) , (7,9), (9,무한)은 모두 잠겨 있습니다.

insert语句的结果随着id值得不同一会儿锁定，一会儿不锁定。那一定是id列加了锁才会造成这样的结果。

如果先不看id=5这一行数据的结果，我们发现一个规律：

• 当name=c时，name=c对应的id=3的id聚合索引数据记录之后的间隙(3,5)，(5,7)，(7,9)，(9,∞)都被加上了锁。

• 当name=e时，name=e对应的id=7的id聚合索引数据记录之前的间隙(5,7)，(3,5)，(1,3)，(-∞,1)都被加上了锁。

• 我们可用select * from user where id = x for update;语句判断出以上间隙上加的锁都为间隙锁。

接下来我们解释一下id=5Steps

client 1

client 2 1begin;--2SELECT * FROM user where name='e' for update;--3- SELECT * FROM user where id=#{id} for update;5rollback;--6--rollback;id 값

	단계에서 ID를 바꿉니다. 5 값, 관찰 결과:

실행 결과

Notblocking차단 금지차단차단 안함차단 안함

通过观察执行结果可知，id=5的聚合索引记录上添加了索引记录锁。根据MySQL官方文档描述，InnoDB引擎在对辅助索引加锁的时候，也会对辅助索引所在行所对应的聚合索引（主键）加锁。而主键是唯一索引，在对唯一索引加锁时，间隙锁失效，只使用索引记录锁。所以SELECT * FROM user where name='e' for update;不仅对辅助索引name=e列加上了next-key锁，还对对应的聚合索引id=5列加上了索引记录锁。

最终结论：  
对于SELECT * FROM user where name='e' for update;一共有三种锁定行为:

1. 对SQL语句扫描过的辅助索引记录行加上next-key锁（注意也锁住记录行之后的间隙）。

2. 对辅助索引对应的聚合索引加上索引记录锁。

3. 当辅助索引为间隙锁“最小”和“最大”值时，对聚合索引相应的行加间隙锁。“最小”锁定对应聚合索引之后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。

上面我们将对辅助索引加锁的情况介绍完了，接下来我们测试一下对聚合索引和唯一索引加锁。

2.2 场景二

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意与场景一表user不同的是name列为唯一索引。

插入数据：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

首先我们执行SQL语句的模板：

	3
	4
	5
	6
	7

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name='e' for update;
3	--	begin;
4	--	INSERT INTO `user` (`id`, `name`) VALUES (10, #{name});
5	rollback;	--
6	--	rollback;

替换步骤5中name的值，观察结果：

name的值	执行结果
a	不阻塞
b	不阻塞
c	不阻塞
d	不阻塞
e	阻塞
f	不阻塞
g	不阻塞
h	不阻塞
i	不阻塞

由测试结果可知，只有name='e'这行数据被锁定。

通过SQL语句我们验证了，对于唯一索引列加锁，间隙锁失效，

2.3 场景三

场景一和场景二都是在查询条件等于的情况下做出的范围判断，现在我们尝试一下其他查询条件，看看结论是否一致。

借用场景一的表和数据。

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	begin;
4	--	INSERT INTO `user` (`id`, `name`) VALUES ('10', #{name});
5	rollback;	--
6	--	rollback;

替换步骤5中name的值，观察结果：

name的值	执行结果
a	阻塞
b	阻塞
c	阻塞
d	阻塞
e	阻塞
f	阻塞
g	阻塞
h	阻塞
i	阻塞

这个结果是不是和你想象的不太一样，这个结果表明where name>'e'这个查询条件并不是锁住'e'列之后的数据，而锁住了所有name列中所有数据和间隙。这是为什么呢？

我们执行以下的SQL语句执行计划：

 explain select * from user where name>'e' for update;

执行结果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | index_name    | index_name | 26      | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

如果你的结果与上面不同先执行一下OPTIMIZE TABLE user;再执行以上语句。

通过观察SQL语句的执行计划我们发现，语句使用了name列索引，且rows参数等于5，user表中一共也只有5行数据。SQL语句的执行过程中一共扫描了name索引记录5行数据且对这5行数据都加上了next-key锁，符合我们上面的执行结果。

接下来我们再制造一组数据。  
建表：

CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(8) NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

这张表和前表的区别是多了一列非索引列age。

我们再执行一下同样的SQL语句执行计划：

 explain select * from user where name>'e' for update;

执行结果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | index_name    | index_name | 26      | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

是不是和第一次执行结果不同了，rows参数等于2，说明扫描了两行记录，结合SQL语句select * from user where name>'e' for update;执行后返回结果我们判断这两行记录应该为g和i。

因为select * from user where name>'e' for update;语句扫描了两行索引记录分别是g和i，所以我们将g和i的锁定范围叠就可以得到where name>'e'的锁定范围：

1. 索引记录g在name列锁定范围为(e,g],(g,i)。索引记录i的在name列锁定范围为(g,i],(i,+∞)。两者叠加后锁定范围为(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

2. g和i对应id列中的7和9加索引记录锁。

3. 当name列的值为锁定范围上边界e时，还会在e所对应的id列值为5之后的所有值之间加上间隙锁，范围为(5,7),(7,9),(9,+∞)。下边界为+∞无需考虑。

接下来我们逐一测试：

首先测试验证了next-key锁范围，执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	begin;
4	--	INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
5	rollback;	--
6	--	rollback;

替换步骤5中name的值，观察结果：

name的值	执行结果
a	不阻塞
b	不阻塞
c	不阻塞
d	不阻塞
f	阻塞
g	阻塞
h	阻塞
i	阻塞
j	阻塞
k	阻塞

下面验证next-key锁中哪部分是间隙锁，哪部分是索引记录锁，执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	SELECT * FROM user where name=#{name} for update;
5	rollback;	--
6	--	rollback;

替换步骤5中name的值，观察结果：

name的值	执行结果
e	不阻塞
f	不阻塞
g	阻塞
h	不阻塞
i	阻塞
j	不阻塞

接下来验证对id列加索引记录锁，执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	SELECT * FROM user where id=#{id} for update;
5	rollback;	--
6	--	rollback;

替换步骤5中id的值，观察结果：

id的值	执行结果
5	不阻塞
6	不阻塞
7	阻塞
8	不阻塞
9	阻塞
10	不阻塞

最后我们验证name列的值为边界数据e时，id列间隙锁的范围，执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	begin;
4	--	INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18');
5	rollback;	--
6	--	rollback;

替换步骤5中id的值，观察结果：

id的值	执行结果
-1	不阻塞
1	不阻塞
2	不阻塞
3	不阻塞
4	不阻塞
5	不阻塞
6	阻塞
7	阻塞
8	阻塞
9	阻塞
10	阻塞
11	阻塞
12	阻塞

注意7和9是索引记录锁记录锁。

观察上面的所有SQL语句执行结果，可以验证select * from user where name>'e' for update的锁定范围为此语句扫描name列索引记录g和i的锁定范围的叠加组合。

2.4 场景四

我们通过场景三验证了普通索引的范围查询语句加锁范围，现在我们来验证一下唯一索引的范围查询情况下的加锁范围。有了场景三的铺垫我们直接跳过扫描全部索引的情况，创建可以扫描范围记录的表结构并插入相应数据测试。

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

和场景三表唯一不同是name列为唯一索引。

SQL语句select * from user where name>'e'扫描name列两条索引记录g和i。如果需要只对g和i这两条记录加上记录锁无法避免幻读的发生，索引锁定范围应该还是两条数据next-key锁锁的组合：(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

我们通过SQL验证我们的结论，执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	begin;
4	--	INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
5	rollback;	--
6	--	rollback;

5단계에서 name 값을 바꾸고 결과를 관찰합니다.

name 값	Execution result
a	Noblocking
b	차단하지 않음
c	차단하지 않음
d	차단하지 않음
f	차단
g	차단
h	차단
i	차단
j	차단
k	Blocking

다음 키 잠금 중 어느 부분이 간격 잠금이고, 어떤 부분이 인덱스 레코드 잠금인지 확인해 보겠습니다. SQL 문 실행을 위한 템플릿:

56 5단계에서 name 값을 바꾸고 결과를 관찰합니다. 실행 결과

Steps	client 1	클라이언트 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;
rollback;	--
--	rollback;
		name. 가치

enotblocking다음으로 보조 인덱스를 잠근 후 집계 인덱스로의 잠금 전송을 확인하고 SQL 문 템플릿을 실행해 보겠습니다. Steps

f	Noblocking
g	차단
h	차단 안함
i	차단
j	차단 안함
위 두 가지를 통해 SQL 문장의 검증 결과를 통해 우리는 g와 i의 잠금 범위 추세는 둘의 다음 키 중첩 조합입니다.

client 1

client 2

23565단계에서 id 값을 바꾸고 결과를 관찰합니다. 실행 결과

1	begin ;	--
SELECT * FROM user where name>'e' for update;	--
--	SELECT * FROM user where id=#{id } 업데이트용;
rollback;	--
--	rollback;
		id 값

5차단 안함지금까지 모든 실험 결과는 시나리오 3과 완전히 동일하므로, 역시 이해하기 쉽습니다. 결국 시나리오 4와 시나리오 3은 보조 인덱스 이름의 인덱스 유형만 다릅니다. 하나는 고유 인덱스이고 다른 하나는 일반 인덱스입니다. 마지막으로 의도를 검증하고 next-key가 경계 데이터 e를 잠그고 결론이 시나리오 3과 동일한지 확인합니다. client 1begin;--

6	차단 안함
7	차단
8	에 해당하는 Aggregate Index의 id 컬럼에 7과 9에 보조 인덱스 인덱스 레코드 잠금이 추가된 결과를 확인할 수 있습니다. 이름의 g 및 i 열.
SQL 문 실행을 위한 템플릿:	Steps
client 2	1
--	2 SELECT * FROM user where name> ;업데이트의 경우 'e';

name中的g和i列对应的聚合索引id列中的7和9加上了索引记录锁。

到目前为止所有实验结果和场景三完全一样，这也很好理解，毕竟场景四和场景三只是辅助索引name3

--

begin;

4--INSERT INTO `user`(`id`, `name`,` age`) VALUES (#{id}, 'e','18');5rollback;--6--rollback;

5단계에서 id 값을 바꾸고 결과를 관찰합니다.

id 값	실행 결과
-1	차단 없음
1	차단하지 않음
2	차단하지 않음
3	차단하지 않음
4	차단 금지
5	차단 없음
6	차단 없음
7	차단
8	차단 금지
9	차단
10	차단 안함
11	차단 안함
12	차단 금지

7과 9는 인덱스 레코드 잠금 레코드 잠금입니다 .

결과를 보면 name 컬럼이 인덱스 레코드의 상한 e인 경우 id에 대한 잠금 동작이 없어 시나리오 3과 다른 점을 알 수 있습니다.

고유 인덱스에 대한 범위 쿼리는 일반 인덱스에 대한 범위 쿼리와 유사하지만 유일한 차이점은 보조 인덱스가 상위 및 하위 범위의 경계 값과 같을 때 기본 키에 간격 잠금이 추가되지 않는다는 것입니다.

고유 인덱스 범위 쿼리 잠금 범위:

• 스캔된 보조 인덱스 레코드의 잠금 범위는 여러 인덱스 레코드의 다음 키 범위를 겹쳐 놓은 조합입니다.

• 집계 인덱스(기본 키) 잠금 범위의 경우 여러 보조 인덱스에 해당하는 집계 인덱스 열에 인덱스 레코드 잠금이 추가됩니다.

결론

InnoDB 엔진은 스캔한 인덱스 레코드에 해당 잠금을 추가합니다. "시나리오 1"을 통해 일반 인덱스 레코드 스캔의 잠금 범위를 명확히 했습니다. 일반 인덱스 레코드에 대한 잠금 범위 수에는 제한이 없습니다. "시나리오 2"를 통해 고유 인덱스 레코드(또는 기본 키)를 검색하기 위한 잠금 범위를 결정했습니다. "시나리오 4"를 통해 스캔 인덱스 레코드(또는 기본 키) 수에 관계없이 잠금 범위를 유추할 수 있습니다. 두 SQL 문이 서로 잠겨 있는지 여부를 확인하기 위해 실제 응용 프로그램에서 유연하게 사용할 수 있습니다. 여기서도 인덱스의 쿼리 조건은 당연하게 받아들일 수 없으며, 실행 계획을 기반으로 인덱스가 최종적으로 스캔하는 레코드 수를 판단해야 하는 경우가 많습니다. 잠금 범위를 이해하는 데 편차가 발생합니다.

---

---

---

Remarks

참고 1: 트랜잭션 격리 수준이 SERIALIZABLE인 경우 일반 select 문은 문 실행 중에 검색된 인덱스에 다음 키 잠금도 추가합니다. 명령문이 고유 인덱스를 스캔하는 경우 다음 키 잠금이 인덱스 레코드 잠금으로 다운그레이드됩니다.
참고 2: 업데이트 문이 집계 인덱스(기본 키) 레코드를 수정하면 영향을 받은 보조 인덱스에 대해 암시적 잠금 작업이 수행됩니다. 새로운 보조 인덱스 레코드가 삽입되기 전에 중복 검사 스캔이 수행되고 새로운 보조 인덱스 레코드가 삽입되면 업데이트 작업은 영향을 받는 보조 인덱스 레코드에 공유 잠금도 추가합니다.

관련 권장 사항:

sql 파일 실행 시 mysql 오류 오류: 알 수 없는 저장소 엔진'InnoDB 해결 방법

MySQL 시작 시 InnoDB 엔진이 비활성화된 경우 수행할 작업

MySQL 저장소 엔진 MyISAM 및 InnoDB 비교

위 내용은 MySQL 데이터베이스 InnoDB 엔진의 행 수준 잠금 잠금 범위에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!