[MySQL] 스토리지 엔진 수준의 잠금(LOCK)의 종류

https://jinsang-2.tistory.com/120

[MySQL] 트랜잭션 격리 수준(Isolation Level)

트랜잭션 격리 수준을 공부하기 위해 필요한 배경지식들이 있어서 정리해 놓습니다.

MySQL 락 

• MySQL에서 사용되는 락은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나뉘어 진다.
• 스토리지 엔진 레벨의 잠금은 테이블의 데이터를 다루기 위한 락이다.
• MySQL 엔진 레벨의 잠금은 테이블이나 데이터베이스 등과 같은 부분을 위한 락에 해당한다.

 

레코드 락(Record Lock)

레코드(record)의 의미 = 테이블에서의 하나의 행(row)을 의미 (하나의 레코드는 테이블의 모든 컬럼 값을 포함하는 것을 의미)

• 일반적으로 레코드 락은 테이블 레코드(row) 자체를 잠그는 락을 의미(MySQL은 작동 방식이 좀 다름)
• 레코드 수준의 잠금은 상당히 작은 공간으로 관리되기에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업 되는 경우(락 에스컬레이션)는 없다.

핵심💡) but!! MySQL에서의 레코드 락은 테이블의 레코드가 아닌 인덱스의 레코드를 잠근다는 점에서 중요한 차이가 있다.

# member 테이블에서 last_name이'J'로 시작하는 구성원은 300명이다.
SELECT COUNT(*) FROM member WHERE last_name LIKE 'J%';

# 그 중에서 first_name이 MangKyu인 사원은 1명만 있다.
SELECT COUNT(*) FROM member WHERE last_name LIKE 'J%' AND first_name = 'MangKyu';

예를 들어 위에와 같이 성이 J로 시작하는 구성원이 300명 일 때, 이름이 MangKyu인 사원은 1명이라 하자. 이 때 성(last_name)에만 인덱스가 걸려 있는 경우에, 성이 J로 시작하며 이름이 MangKyu인 구성원의 등록일을 변경하는 UPDATE 쿼리를 실행한다 하자.

# member 테이블에는 last_name 컬럼만으로 구성된 인덱스 KEY idx_last_name(last_name)가 존재한다.

# 해당 구성원의 등록일을 오늘로 변경하는 쿼리를 실행해보자.
UPDATE member SET register_date = NOW() WHERE last_name LIKE 'J%' AND first_name = 'MangKyu';

UPDATE 문에 의해 영향을 받는 레코드는 1건이지만, 이 1건을 업데이트 하기 위해 300건의 인덱스 레코드에 잠금이 걸린다. MySQL은 테이블 레코드가 아닌 인덱스에 잠금을 걸기 때문..

인덱스는 성(last_name)으로만 구성되어 있기 때문에, 해당 레코드를 갱신하기 위해서는 인덱스를 통해 검색되는 모든 레코드에 잠금을 걸게 된다. 만약에 적당한 인덱스가 없다면 모든 테이블의 레코드에 락을 거고, 테이블 풀스캔 하면서 작업을 처리하게 된다. 그러면 동시성은 상당히 떨어지고 특히 MySQL에서는 인덱스의 설계가 중요하다.

이렇듯 레코드 락은 트랜잭션이 DML 구문을 실행할 때 자동으로 거는 락이며, 레코드 락 덕분에 여러 트랜잭션이 동시에 서로 다른 레코드에 접근할 수 있는 것이다. ( 같은 인덱스 범위에 걸리지 않는다면 동시 실행이 가능하다. 예를 들어 `LIKE %H` 같은 경우에는 다른 인덱스이기에 동시 접근가능)

---

갭 락(Gap Lock)

• 레코드와 레코드 사이의 간격을 잠금(LOCK)
  • 예를 들어 성이 J로 시작하는 Jo,Joe 2개가 있을 때 Jang, Jeong과 같은 다른 데이터들이 추가 될 수 있다. 따라서 현재 트랜잭션에서 조회를 할 때, 다른 트랜잭션에서 임의의 데이터가 추가되지 않도록 잠그려면 아래와 같은 쿼리를 실행해야 한다. 여기서 SELECT … FOR UPDATE 구문은 배타적 잠금(비관적 잠금, 쓰기 잠금)을 거는 것이다. 읽기 잠금은 LOCK IN SHARE MODE 구문을 사용해야 한다. 락은 트랜잭션이 커밋 또는 롤백 될 때 해제된다.

SELECT * FROM member WHERE last_name LIKE "J%" FOR UPDATE;         // 쓰기 잠금(베타락)
SELECT * FROM member WHERE last_name LIKE "J%" LOCK IN SHARE MODE; // 읽기 잠금(공유락

갭 락은 인덱스 범위 조건 중에서 실제 레코드를 제외하고, 데이터가 추가될 수 있는 범위에 걸리게 된다. 인덱스는 정렬된 순서로 존재하므로, 현존하는 레코드의 앞 뒤에 갭 락이 걸린다.

아래에는 더 쉬운 예시로 num 테이블에 2,3이라는 2개의 인덱스 레코드가 존재할 때, 이 때 테이블에서 1이상 5이하의 조건으로 데이터를 검색한다면, 현존하는 레코드인 2와 3에 걸리는 락이 레코드 락이고, 아직 실존하지 않는 1과 4, 5가 추가될 수 있는 공간에 걸리는 락이 갭 락이다.

💡정리 ) 갭 락은 아직 존재하지 않지만 지정된 범위에 해당하는 인덱스 테이블 공간을 대상으로 거는 잠금이다. 따라서 데이터의 유일성이 보장되어야 하는 PK나 유니크 인덱스에 의한 작업에서는 갭 락이 사용되지 않는다.

Pantom Read(유령 읽기) 방지에도 도움이 된다.

갭 락은 단독으로 사용되기 보다 아래의 넥스트 키 락의 일부로 함께 사용된다.

---

넥스트 키 락(Next Key Lock)

레코드 락 + 갭 락 = 넥스트 키 락

넥스트 키 락을 사용하는 목표

• 팬텀 리드(Phantom Read) 방지
  • 팬텀 리드 : 같은 트랜잭션 내에서 같은 조건으로 조회했을 때, 처음에 없던 데이터가 갑자기 생기는 현상
  • 예를 들어 트랜잭션 A에서 age컬럼에 [20, 25, 30] 이 있을 때 SELECT문으로 나이가 가장 많은 사람을 조회하기 위해 age DESC로 정렬 후 LIMIT 1 로 한 명을 가져오는 쿼리를 실행한다 했을 때, 동시에 트랜잭션 B에서 INSERT문으로 나이가 35살인 사용자를 새로 추가하고 트랜잭션 A가 다시 나이가 가장 많은 사람을 조회하는 SELECT 문을 실행한다면 이전에는 30세였다가 갑자기 35세 사용자가 생긴다. = 이거시! 팬텀 리드 문제
  • 그래서 넥스트 키 락으로 30세 이상을 추가하는 행위를 차단시켜버림, 트랜잭션 A가 종료되기 전까지 새로운 사용자가 추가될 수 없기에 팬텀리드가 발생하지 않는다.
• 데이터 일관성 유지
  • 트랜잭션 실행 중일 때, 다른 트랜잭션이 새로운 레코드를 삽입하는 것을 방지
• 레코드 수준의 락보다 더 강력한 보호 기능

---

자동 증가 락(Auto Increment Lock)

• AUTO_INCREMENT 컬럼은 여러 레코드가 동시에 INSERT 되더라도 중복되지 않고 순차적으로 증가하는 일련번호를 제공해줘야 한다.
  • 이 때 내부적으로 테이블 수준의 잠금인 자동 증가 락을 사용한다.
• 해당 락은 INSERT와 REPLACE와 같이 새로운 레코드를 저장하는 쿼리에서만 사용한다.
• 트랜잭션과 관계없이 INSERT와 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간에 락이 걸린다. 자동 증가 락은 테이블에 1개만 존재하기 때문에, 한 쿼리에서 락을 획득하여 새로운 번호를 받는 중(채번중)이라면 다음 쿼리는 락을 대기해야 한다. 아주 짧은 순간에만 걸렸다가 즉시 해제되므로 대부분의 경우 문제 되지 않는다.
• 자동 증가 락은 잠금을 최소화하기 위해 한 번 증가하면 절대 자동으로 줄어들지 않는다.
  • 트랜잭션과도 무관한 것이 자동 증가값으로 새로운 번호를 받았지만 이후 쿼리에서 실패해 트랜잭션이 롤백되어도 자동 증가값은 복구되지 않고 그대로 남는다.