MySQL Tutorial 칼럼에서는 잠금이 팬텀 읽기 문제를 해결하는 방법을 소개합니다.

잠금이 무엇인지 이해하고 MySQL에서 팬텀 읽기 문제를 해결하는 방법

머리말

오늘은 MySQL의 Lock에 관련된 지식을 소개해드리겠습니다.

별도의 언급이 없는 한 이 글에서는 기본 InnoDB 엔진을 사용합니다. 다른 엔진이나 데이터베이스가 관련되어 있는 경우 구체적으로 언급됩니다.

잠금이란 무엇입니까

잠금은 트랜잭션이 특정 데이터 부분을 잠그면 각 트랜잭션이 여전히 일관된 방식으로 데이터를 읽고 수정할 수 있도록 보장하는 방법입니다. 잠금이 해제될 때까지 차단하고 기다릴 수만 있으므로 잠금의 세분성은 데이터베이스 액세스 성능에 어느 정도 영향을 미칠 수 있습니다.

잠금 세분성 측면에서 잠금을 테이블 잠금과 행 잠금으로 나눌 수 있습니다.

테이블 잠금

이름에서 알 수 있듯이 테이블 잠금은 테이블을 직접 잠그는 것입니다. MyISAM 엔진에는 테이블 잠금만 있습니다.

테이블 잠금의 잠금 방법은 다음과 같습니다.

LOCK TABLE 表名 READ;--锁定后表只读
UNLOCK TABLE; --解锁复制代码

로그인 후 복사

Row lock

Row 잠금은 이름에서 알 수 있듯이 데이터 행을 잠그는 것입니다. 그러나 행 잠금의 실제 구현 알고리즘은 비교적 복잡하며 때로는 그렇지 않습니다. 단순한 잠금이 아닌 특정 데이터를 저장하고 나중에 확장하세요.

일반적인 아이디어는 데이터 행을 잠근 후 다른 트랜잭션이 이 데이터에 액세스할 수 없다는 것입니다. 그런 다음 트랜잭션 A가 데이터 조각에 액세스하면 해당 데이터를 읽기 위해 꺼내고 수정하지 않을 것이라고 상상합니다. 저 역시 이 데이터에 접근하고 싶은데 그냥 꺼내서 읽어보고 싶은데, 이때 차단하면 좀 아깝겠죠. 성능의. 따라서 이 데이터 읽기 시나리오를 최적화하기 위해 행 잠금을 공유 잠금과 배타적 잠금이라는 두 가지 주요 유형으로 나눕니다.

공유 잠금

읽기 잠금, S 잠금이라고도 알려진 공유 잠금은 S 잠금으로 데이터 조각이 추가된 후 다른 트랜잭션도 데이터를 읽고 잠금을 공유할 수 있음을 의미합니다.
다음 명령문을 통해 공유 잠금을 추가할 수 있습니다:

select * from test where id=1 LOCK IN SHARE MODE;复制代码

로그인 후 복사

잠금 후 잠긴 트랜잭션이 종료될 때까지(커밋 또는 롤백) 잠금이 해제됩니다.

배타적 잠금

배타적 잠금, 배타적 잠금, 쓰기 잠금이라고도 함, X 잠금. 즉, X 잠금이 데이터 조각에 추가된 후 이 데이터에 액세스하려는 다른 트랜잭션은 차단하고 잠금이 해제될 때까지만 기다릴 수 있습니다. 이는 배타적입니다.

MySQL은 삽입, 업데이트, 삭제 등 데이터를 수정할 때 자동으로 배타적 잠금을 추가합니다. 마찬가지로 다음 SQL 문을 통해 배타적 잠금을 수동으로 추가할 수 있습니다.

select * from test where id=1 for update;复制代码

로그인 후 복사

InnoDB 엔진에서는 Row 잠금과 테이블 잠금은 공존이 허용됩니다.

하지만 문제가 발생합니다. 트랜잭션 A가 테이블 t의 데이터 한 행을 잠그고 트랜잭션 B가 테이블 t를 잠그려고 한다면 이때 어떻게 해야 할까요? 트랜잭션 B는 테이블 t에 행 잠금이 있는지 어떻게 알 수 있나요? 전체 테이블 순회를 사용하면 테이블의 데이터가 크면 잠그는 데 반나절이 걸리므로 MySQL은 의도 잠금을 도입했습니다.

의도 잠금

의도 잠금은 의도 공유 잠금과 의도 배타적 잠금의 두 가지 유형으로 구분되는 테이블 잠금입니다. 이 두 잠금은 각각 IS 잠금 및 IX 잠금이라고 할 수 있습니다.

의도 잠금은 MySQL 자체에서 유지 관리되며 사용자는 수동으로 의도를 추가할 수 없습니다.

의도 잠금에는 두 가지 주요 잠금 규칙이 있습니다.

데이터 행에 S 잠금을 추가해야 하는 경우 MySQL은 먼저 테이블에 IS 잠금을 추가합니다.
데이터 행에 X 잠금을 추가해야 하는 경우 MySQL은 먼저 테이블에 IX 잠금을 추가합니다.

이 경우 위의 문제는 테이블 전체를 순회하지 않고 테이블에 해당 의도 잠금이 있는지 여부만 확인하면 쉽게 해결됩니다.

다양한 잠금장치의 호환성

아래 그림은 공식 웹사이트에서 참조한 다양한 잠금장치의 호환성입니다.

Sshare공유

상호 배타

상호 질책

锁到底锁的是什么

建立以下两张表，并初始化5条数据，注意test表有2个索引而test2没有索引：

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

INSERT INTO test VALUE(1,'张1');
INSERT INTO test VALUE(5,'张5');
INSERT INTO test VALUE(8,'张8');
INSERT INTO test VALUE(10,'张10');
INSERT INTO test VALUE(20,'张20');

CREATE TABLE `test2` (
  `id` varchar(32) NOT NULL,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test2 VALUE(1,'张1');
INSERT INTO test2 VALUE(5,'张5');
INSERT INTO test2 VALUE(8,'张8');
INSERT INTO test2 VALUE(10,'张10');
INSERT INTO test2 VALUE(20,'张20');复制代码

로그인 후 복사

举例猜测

在行锁中，假如我们对一行记录加锁，那么到底是把什么东西锁住了，我们来看下面两个例子：
举例1（操作test表）：

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=1 FOR UPDATE;
	SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞
	SELECT * FROM test WHERE id=5 FOR UPDATE; 加锁成功
COMMIT; （释放锁）
	SELECT * FROM test WHERE id=1 FOR UPDATE; 加锁成功

举例2（操作test2表）：

事务A	事务B
BEGIN;
SELECT * FROM test2 WHERE id=1 FOR UPDATE;
	SELECT * FROM test2 WHERE id=1 FOR UPDATE; 阻塞
	SELECT * FROM test2 WHERE id=5 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT * FROM test2 WHERE id=1 FOR UPDATE; 加锁成功

从上面两个例子我们可以发现，test表好像确实是锁住了id=1这一行的记录，而test2表好像不仅仅是锁住了id=1这一行记录，实际上经过尝试我们就知道，test2表是被锁表了，所以其实MySQL中InnoDB锁住的是索引，当没有索引的时候就会锁表。

接下来再看一个场景：

事务A	事务B
BEGIN;
SELECT * FROM test WHERE name=‘张1’ FOR UPDATE;
	SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞
	SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功

这个例子中我们是把name索引锁住了，然后我们在事务B中通过主键索引只查id，这样就用到name索引了，但是最后发现也被阻塞了。所以我们又可以得出下面的结论，MySQL索引不但锁住了辅助索引，还会把辅助索引对应的主键索引一起锁住。

到这里，可能有人会有怀疑，那就是我把辅助索引锁住了，但是假如加锁的时候，只用到了覆盖索引，然后我再去查主键会怎么样呢？

接下来让我们再验证一下：

事务A	事务B
BEGIN;
SELECT name FROM test WHERE name=‘张1’ FOR UPDATE;
	SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞
	SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞
	SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功

보조 인덱스 잠금만 사용하더라도 MySQL은 기본 키 인덱스를 계속 잠그고 기본 키 인덱스의 B+ 트리 리프 노드에 전체 데이터가 저장되므로 쿼리되는 모든 필드가 잠긴다는 것을 알 수 있습니다.

이 시점에서 잠금이 무엇인지 명확하게 결론을 내릴 수 있습니다.

결론

InnoDB 엔진에서 잠긴 것은 인덱스입니다.

테이블에 인덱스가 없으면 MySQL은 잠깁니다. (실제로 잠긴 것은 숨겨진 컬럼 ROWID의 기본 키 인덱스입니다.)
보조 인덱스를 잠그면 보조 인덱스에 해당하는 기본 키 인덱스도 잠깁니다
기본 키 인덱스는 모든 레코드가 잠겨 있습니다(기본 키 인덱스 리프 노드가 전체 데이터를 저장합니다)

행 잠금 알고리즘

이전 기사에서 트랜잭션을 소개할 때 MySQL이 팬텀 읽기를 방지한다고 언급했습니다. 하지만 행 잠금이 레코드 행만 잠그고 팬텀 읽기를 방지하지 못하는 경우에는 레코드를 잠그는 행 잠금은 실제로 행 잠금에 대한 세 가지 알고리즘이 있습니다. Gap Lock과 Next-Key Lock(Next-Key Lock), 그리고 이것이 유령 판독을 방지할 수 있는 이유가 바로 Next-Key Lock의 역할입니다.

Record Lock

Record Lock은 위에서 소개되었습니다. 쿼리가 레코드에 도달하면 InnoDB는 레코드 잠금을 사용하여 적중된 레코드 행을 잠급니다.

Gap Lock

우리의 쿼리가 기록에 도달하지 못하면 InnoDB는 이때 gap lock을 추가합니다.

거래 A	거래 B
BEGIN;
SELECT * FROM test WHERE id=1 FOR UPDATE;
	테스트에 삽입 VALUE (2,'Zhang 2'); Blocking
	INSERT INTO test VALUE (3,'Zhang 3'); Blocking
	SELECT * FROM test WHERE id= 2 FOR UPDATE; Lock 성공적으로
COMMIT; (release lock)

위의 예에서 다음과 같은 결론을 내릴 수 있습니다.

Gap lock 및 gap lock이 있습니다. 즉, 트랜잭션 A는 갭 잠금을 추가하고 트랜잭션 B는 동일한 갭에 갭 잠금을 추가할 수 있습니다. (Gap 잠금을 사용하는 이유는 데이터 적중이 없을 때 읽기를 차단할 필요가 없고, 동일한 Gap을 잠그는 다른 트랜잭션을 차단할 필요도 없기 때문입니다.)
Gap 잠금은 주로 삽입 작업을 차단합니다.

갭은 어떻게 결정되나요?

테스트 테이블에는 5개의 레코드가 있고 기본 키 값은 1,5,8,10,20입니다. 그러면 다음과 같은 6개의 공백이 있게 됩니다:
(-무한대,1),(1,5),(5,8),(8,10),(10,20),(20,+무한)

그리고 기본 키가 int 유형이 아닌 경우 ASCII 코드로 변환된 다음 간격이 결정됩니다.

Next-Key Lock

Next-Key Lock은 레코드 잠금과 갭 잠금의 조합입니다. 범위 쿼리를 수행하고 하나 이상의 레코드에 도달할 뿐만 아니라 간격도 포함하면 임시 키 잠금이 사용됩니다. 키 없는 잠금은 InnoDB의 행 잠금에 대한 기본 알고리즘입니다.

참고로 이는 RR 격리 수준에만 해당됩니다. 외래 키 제약 조건 및 고유성 제약 조건 외에도 간격 잠금이 추가되므로 당연히 임시 키 잠금이 없습니다. RC 레벨에 추가된 라인 잠금은 모두 레코드 잠금입니다. 레코드에 적중되지 않으면 잠금이 잠기지 않습니다. 따라서 RC 레벨은 팬텀 읽기 문제를 해결하지 않습니다.

임시 키 잠금은 다음 두 가지 조건에 따라 갭 잠금 또는 기록 잠금으로 다운그레이드됩니다.

쿼리가 작업 기록을 놓친 경우 갭 잠금으로 다운그레이드됩니다.
기본 키 또는 고유 인덱스를 사용하여 레코드에 도달하면 레코드 잠금으로 다운그레이드됩니다.

INSERT INTO 테스트 VALUE(9,'Zhang 9');COMMIT;

上面这个例子，事务A加的锁跨越了(1,5)和(5,8)两个间隙，且同时命中了5，然后我们发现我们对id=8这条数据进行操作也阻塞了，但是9这条记录插入成功了。

临键锁加锁规则

临键锁的划分是按照左开右闭的区间来划分的，也就是我们可以把test表中的记录划分出如下区间：(-∞,1],(1,5],(5,8],(8,10],(10,20],(20,+∞)。

那么临键锁到底锁住了哪些范围呢？

**临键锁中锁住的是最后一个命中记录的 key 和其下一个左开右闭的区间**

那么上面的例子中其实锁住了(1,5]和(5,8]这两个区间。

临键锁为何能解决幻读问题

临键锁为什么要锁住命中记录的下一个左开右闭的区间？答案就是为了解决幻读。

我们想一想上面的查询范围id>=2且id

当然，其实如果我们执行的查询刚好是id>=2且id

在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥的特性。一个事务持有锁的时候，会阻止其他的事务获取锁，这个时候会造成阻塞等待，那么假如事务一直等待下去，就会一直占用CPU资源，所以，锁等待会有一个超时时间，在InnoDB引擎中，可以通过参数：innodb_lock_wait_timeout查询：

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';复制代码

로그인 후 복사

默认超时时间是50s，超时后会自动释放锁回滚事务。但是我们想一下，假如事务A在等待事务B释放锁，而事务B又在等待事务A释放锁，这时候就会产生一个等待环路了，而这种情况是无论等待多久都不可能会获取锁成功的，所以是没有必要去等50s的，这种形成等待环路的现象又叫做死锁。

死锁(Dead Lock)

什么是死锁

死锁是指的两个或者两个以上的事务在执行过程中，因为争夺锁资源而造成的一种互相等待的现象。

트랜잭션 A	트랜잭션 B
BEGIN;
SELECT * FROM test WHERE id>=2 AND id

	INSERT INTO 테스트 VALUE(2,'Zhang 2'); blocking
	INSERT INTO 테스트 VALUE(6,'Zhang 6' ); 차단
	INSERT INTO test VALUE (8,'张8'); blocking
	SELECT * FROM test WHERE id=8 FOR UPDATED
	삽입 성공
(릴리스 잠금)

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=10 FOR UPDATE;
	BEGIN;
	SELECT * FROM test WHERE id=20 FOR UPDATE;
SELECT * FROM test WHERE id=20 FOR UPDATE;
	SELECT * FROM test WHERE id=10 FOR UPDATE;
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
查询出结果

교착 상태가 발생한 후 트랜잭션이 롤백되기 전에 목적 없이 50초 동안 기다리지 않고 즉시 롤백되는 것을 볼 수 있습니다. 그러면 MySQL은 교착 상태가 발생했음을 어떻게 알 수 있으며 어떻게 이를 수행합니까? 교착상태를 감지했나요? 자물쇠에 무슨 일이 일어났나요?

교착 상태 감지

현재 대부분의 데이터베이스는 교착 상태를 감지하기 위해 대기 그래프(wait graph) 방법을 사용합니다. 데이터베이스에는 두 가지 유형의 정보가 기록됩니다.

잠금 정보 목록
거래 대기 목록
대기 그래프 알고리즘은 이 두 가지 정보를 기반으로 그래프를 작성합니다. 그래프에 루프가 있으면 교착 상태가 있음을 증명:
아래 그림과 같이 t1과 t2 사이에 루프가 있습니다. 이는 t1과 t2의 트랜잭션 사이에 교착 상태가 있음을 증명합니다

교착 상태 방지

긴 트랜잭션을 여러 개의 작은 트랜잭션으로 분할
쿼리할 때 where 조건문이 없는 쿼리는 피하고 인덱스 쿼리를 최대한 사용하세요
가능하다면 동등한 쿼리를 사용해 보세요

잠금 정보 쿼리

InnoDB는 아래에 3개의 테이블을 제공합니다. information_schema 라이브러리를 사용하여 트랜잭션 및 잠금 관련 질문을 쿼리하고 문제를 해결할 수 있습니다.

INNODB_TRX

는 트랜잭션이 잠금을 기다리고 있는지 여부, 트랜잭션이 시작된 시간, 트랜잭션이 실행 중인 SQL 문(있는 경우) 등을 포함하여 현재 InnoDB에서 실행되는 각 트랜잭션에 대한 정보를 기록합니다.

열 이름	의미
trx_id	InnoDD 엔진의 트랜잭션 고유 ID
trx_state	트랜잭션 상태: RUNNING, LOCK WAIT, ROLLING , COMMIT TING
trx_started	트랜잭션 시작 시간
trx_requested_lock_id	대기 중인 트랜잭션의 잠금 ID, trx_state가 LOCK WAIT가 아닌 경우 null입니다.
trx_wait_started	트랜잭션이 시작을 기다리고 있는 시간
trx_weight	transactional 가중치는 트랜잭션에 의해 수정되고 잠긴 행 수를 반영합니다. 교착 상태가 발생하면 InnoDB는 롤백할 가장 작은 값을 가진 트랜잭션을 선택합니다.
trx_mysql_thread_id	MySQL의 스레드 ID는 SHOW PROCESSLIST
trx_query	트랜잭션에 의해 실행되는 SQL 문을 통해 쿼리됩니다
trx_eration_state	트랜잭션의 현재 작업 상태(NULL이 아닌 경우)
trx_tables_in_use	에서 사용하는 테이블 수 현재 트랜잭션에서 실행된 SQL 문
trx_tables_locked	잠긴 테이블 수(행 잠금을 사용하기 때문에 테이블이 잠겨 있는 것으로 표시되지만 한 개 또는 몇 개의 행만 잠길 수 있으므로 다른 행은 잠길 수 있음) 여전히 다른 트랜잭션에서 액세스 가능)
trx_lock_structs	현재 트랜잭션이 보유한 잠금 수
trx_lock_memory_bytes	메모리에 있는 현재 트랜잭션의 인덱스 구조 크기
trx_rows_locked	대략적인 숫자 현재 트랜잭션의 잠금 수(행 수) 삭제 표시 및 물리적으로 존재하지만 현재 트랜잭션에는 보이지 않는 기타 데이터를 표시한 행 수
trx_rows_modified	현재 트랜잭션에 의해 수정되거나 삽입된 행 수
trx_concurrency_tickets	현재 트랜잭션이 종료되기 전에 수정하거나 삽입할 수 있는 행 수를 나타내는 동시 실행 수 동시 실행 수는 시스템 변수 innodb_concurrency_tickets
trx_isolation_level	을 통해 설정할 수 있습니다. 트랜잭션 격리 수준
trx_unique_checks	현재 트랜잭션에 대한 고유 제약 조건을 열거나 닫을지 여부: 0-no 1-yes
trx_foreign_key_checks	현재 트랜잭션에 대해 외래 키 제약 조건을 설정할지 여부: 0-아니요 1-예
trx_last_foreign_key_error	마지막 외래 키 오류 메시지, 그렇지 않으면 비어 있습니다
trx_adaptive_hash_latched	적응형 해시 인덱스가 현재 트랜잭션에 의해 잠겨 있는지 여부. 분할된 적응형 해시 인덱스 검색 시스템을 사용하는 경우 단일 트랜잭션으로 전체 적응형 해시 인덱스가 잠기지 않습니다. 적응형 해시 인덱스 파티셔닝은 기본적으로 8로 설정되는 innodb_adaptive_hash_index_parts에 의해 제어됩니다.
trx_adaptive_hash_timeout	적응형 해시 인덱스에 대한 검색 래치를 즉시 삭제할지 아니면 MySQL의 호출 전반에 걸쳐 이를 유지할지 여부입니다. 적응형 해시 인덱스 경합이 없으면 이 값은 0으로 유지되고 문은 완료될 때까지 래치를 유지합니다. 경합 중에 해당 개수는 0으로 줄어들고 문은 각 행 조회 후 즉시 래치를 해제합니다. 적응형 해시 인덱스 검색 시스템이 분할되면(innodb_adaptive_hash_index_parts에 의해 제어됨) 이 값은 0으로 유지됩니다.
trx_is_read_only	현재 트랜잭션이 읽기 전용인지 여부: 0-no 1-yes
trx_autocommit_non_locking	값이 1이면 공유 모델 업데이트 및 잠금을 포함하지 않는 명령문임을 의미합니다. , autocommit이 켜진 경우 이 문만 실행됩니다. 이 열과 TRX_IS_READ_ONLY가 모두 1이면 InnoDB는 테이블 데이터를 변경하는 트랜잭션과 관련된 오버헤드를 줄이기 위해 트랜잭션을 최적화합니다.

INNODB_LOCKS

은 트랜잭션이 잠금을 요청했지만 획득하지 못한 각 잠금에 대한 정보와 한 트랜잭션이 보유했지만 다른 트랜잭션을 차단하고 있던 각 잠금에 대한 정보를 기록합니다.

열 이름

의미

lock_id

잠금의 ID(LOCK_ID에 현재 TRX_ID가 포함되어 있지만 LOCK_ID의 데이터 형식은 언제든지 변경될 수 있으므로 LOCK_ID 값을 구문 분석하는 애플리케이션을 작성하지 마세요) )

lock_trx_id

이전 테이블의 트랜잭션 ID

lock_mode

잠금 모드: S, Row lock

lock_table

잠긴 테이블

lock_index

잠긴 인덱스, 테이블 잠금이 NULL

lock_space

잠금 레코드의 공간 ID, 테이블 잠금이 NULL

lock_page

트랜잭션 잠긴 페이지 수, 테이블 잠금이 NULL

lock_rec

트랜잭션이 잠긴 행 수, 테이블 잠금은 NULL

lock_data

트랜잭션 잠금의 기본 키 값, 테이블 잠금은 NULL

INNODB_LOCK_WAITS

는 잠금 대기 정보를 기록합니다. 차단된 각 InnoDB 트랜잭션에는 요청한 잠금과 요청을 차단하는 잠금을 나타내는 하나 이상의 행이 포함되어 있습니다.

열 이름	의미
lock_id	잠금의 ID(LOCK_ID에 현재 TRX_ID가 포함되어 있지만 LOCK_ID의 데이터 형식은 언제든지 변경될 수 있으므로 LOCK_ID 값을 구문 분석하는 애플리케이션을 작성하지 마세요) )
requesting_trx_id	요청된 잠금 리소스의 트랜잭션 ID
requested_lock_id	요청된 잠금의 ID
blocking_trx_id	차단된 트랜잭션 ID
blocking_lock_id	ID 차단된 자물쇠