MySQL 트랜잭션과 락 - InnoDB 락, 이렇게 동작한다!

트랜잭션과 락(Transaction, Lock)

트랜잭션과 락

트랜잭션 : 논리적인 작업 단위로 전부 처리되거나 처리되지 않는 (commit/rollback) 원자성을 보장하기 위한 기능이다.

락 : 서로 다른 작업에서 같은 자원을 동시에 필요로 할 때 자원 경쟁이 일어나는데, 이때 순서대로 사용되는 동시성을 보장하기 위한 기능이다.

MySQL에서 사용되는 락(LocK)은 크게 MySQL 엔진 레벨의 락과 스토리지 엔진 레벨의 락으로 나눠볼 수 있다.

스토리지 엔진 레벨의 락

스토리지 엔진(InnoDB)에서 제공하는 락(Lock, 잠금)이 있다.

기본적으로 비관적 락(Pessimistic locking)을 사용한다.

• 비관적 락 : 트랜잭션에서 변경하려는 레코드에 대해 락을 획득하고 쿼리를 수행하는 방식 (같은 레코드를 변경하려는 경우가 많을 것이라고 비관적인 생각을 하기 때문에 비관적 락)
• 낙관적 락 : 트랜잭션에서 락 없이 일단 쿼리 수행을 하고 마칠 때 서로 다른 트랜잭션에서 충돌이 있었는지 확인하고 문제가 있으면 충돌이 난 트랜잭션을 롤백하는 방식 (같은 레코드를 변경하려는 경우가 거의 없을 것이라고 낙관적인 생각을 하기 때문에 낙관적 락)

보통 대규모 트래픽을 처리하는 애플리케이션에서는 성능 이슈 때문에 락을 최소화 해야하기 때문에 낙관적 락으로 변경하여 사용하기도 한다.

스토리지 엔진(InnoDB)이 제공하는 락

✔️ 참고로 아래에서 "인덱스 레코드" 라는 표현을 사용할 것인데, 그냥 "인덱스"로 이해하면 된다. 필자는 잠깐 헷갈렸기에 비슷하게 느끼는 사람이 있을까 봐 참고로 적어놓았다. (인덱스도 레코드 형식으로 저장되니까...?)

• 레코드 락 (Record lock)
  • SELECT c1 FROM t WHERE c1=10 FOR UPDATE; 일 때, c1=10인 인덱스 레코드에 락을 걸어서 수정, 삽입, 삭제등의 다른 트랜잭션을 막는다. (이때 c1은 유니크하다는 가정 한다.)
  • 실제 테이블의 레코드에 대해 락을 걸지 않고 인덱스 레코드에 락을 건다.
  • 따로 생성한 인덱스가 없는 테이블은 InnoDB가 자체적으로 생성한 클러스터 인덱스를 이용해 락을 건다.
  • 기본키나 유니크 키에 의한 변경 작업은 갭 락 없이 딱 인덱스 레코드에만 락을 건다.
• 갭 락 (Gap lock)
  • 인덱스 레코드와 인접한 앞/뒤 사이 공간에 락을 거는 것인데, 개념적인 용어로 단독으로는 사용되지 않고 넥스트 키 락에서 사용된다. (자세한 건 넥스트 키 락에서 예제를 보자)
  • 갭 락은 READ_COMMITED 이하에서는 거의 발생하지 않고 REPEATABLE_READ 이상 격리 수준일 때에 주로 발생한다. ('거의'라고 한 이유는 외래 키 검사나 중복 키 검사할 때는 READ_COMMITED에서도 발생하기 때문이다.)
    • INSERT INTO ... ON DUPLICATE KEY UPDATE 는 INSERT를 하려는데 "유니크 키"나 "기본 키"에 중복이 일어나면 UPDATE로 동작하도록 한다. 당연히 중복된 키가 없으면 INSERT로 동작한다.
• 넥스트 키 락 (Next key lock)
  • 레코드 락과 갭 락을 합쳐놓은 형태다. 인덱스 레코드도 잠그고 그 인덱스 레코드 앞, 뒤 갭도 잠근다.
  • SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE; 일 때, c1=15 인 레코드를 insert 하는 트랜잭션이 있다면 막는다. 반드시 인덱스 레코드 사이에 있는 갭만 락을 거는 게 아니라 제일 앞 또는 뒤에 있는 인덱스 레코드의 갭도 락을 건다. 예를 들어 c1=10인 레코드 인덱스 바로 앞에 c1=8인 레코드 인덱스가 있는 상태라면, c1=9인 레코드를 insert 하려고 하면 막힌다. (그 갭에도 락이 걸려있기 때문에!)
• 오토 인크리먼트 락 (Auto increment lock)
  • MySQL에서 자동 증가하는 숫자 값을 채번하기 위해 AUTO_INCREMENT라는 컬럼 속성을 정의할 때가 있다. 이때 같이 INSERT 하려는 요청이 올 때 거는 락이다.

인덱스의 락을 거는 MySQL InnoDB의 동작

테이블 락이 아닌 레코드 단위의 락을 기본으로 하는 InnoDB의 동작에 대해서 알아본다.

앞서 언급했듯 레코드 락은 변경하려고 하는 레코드에 락을 거는 게 아니라 인덱스 레코드에 락을 건다.

아래 예시로 동작을 살펴보겠다.

SELECT COUNT(*) FROM employees WHERE first_name='DK'; -- 250건 조회된다고 가정

SELECT COUNT(*) FROM employees WHERE first_name='DK' AND last_name='J'; -- 1건 조회된다고 가정

UPDATE employees SET hire_date=NOW() WHERE first_name='DK' AND last_name='J'; --어디에 락이 걸릴까?

만약 first_name 컬럼에만 인덱스(보조 인덱스)가 적용되어 있다고 가정하면 마지막 UPDATE 쿼리에서 넥스트 키 락에 의해 락이 걸리는 레코드는 몇 개 일까?

답은 250개의 레코드다.

first_name이 'DK'고 last_name이 'J'인 사람은 한 명(1건)이지만 인덱스에 락을 거는 InnoDB의 기본 정책상 인덱스가 있는 first_name에 락을 걸게 되므로 250건 전체에 락을 걸고 실제 수정은 last_name이 'J'인 1건에 대해서만 수정이 일어난다.

이 동작을 이해하고 나면 예상되는 위험을 알 수 있다.🕳️

만약 위 경우에서 first_name이 같은 레코드가 엄청나게 많으면 분명 이름은 레코드 단위 락(넥스트 키 락)인데 불필요하게 많은 레코드에 락이 걸리게 된다. (= 동시성 강화로 인한 성능 저하)

넥스트 키 락 사용 줄이기

넥스트 키 락이 필요하게 된 주된 이유는 복제를 위한 바이너리 로그 때문이다.

MySQL 5.0 이전 버전에서는 바이너리 로그를 비활성화하지 않아도 READ_COMMITED 격리 레벨을 쓸 수 있었다. (innodb_locks_unsafe_for_binlog=1 설정만으로 넥스트 키 락이 줄어듦)

MySQL 5.1 이후 버전에서는 바이너리 로그가 활성화되면 최소 REPEATABLE_READ 이상의 격리 레벨을 쓰도록 강제하기 때문에 넥스트 키 락 사용을 줄이기 위해서는 바이너리 로그를 비활성화하거나 바이너리 로그를 활성화하더라도 레코드 기반의 바이너리 로그를 사용하도록 설정한 후에 innodb_locks_unsafe_for_binlog=1 로 설정하여 넥스트 키 락을 줄일 수 있다.

이렇게 설정하면 앞서 언급한 인덱스 레코드에 락을 걸 때 불필요한 레코드에 락이 걸리는 문제를 어느 정도 해결할 수 있다.

⇒ 앞서 예제를 기준으로 250개의 인덱스 레코드에 락이 걸리는 것은 똑같지만, 다음 where 조건에 의해 1개의 레코드로 줄어드는 경우에 불필요한 249개의 인덱스 레코드에 락을 풀어주는 방식으로 해결한다.

사례로 살펴보면 락

InnoDB의 기본 격리 레벨(isolation level)이 REPEATABLE READ 이기 때문에 이 레벨에서 발생할 수 있는 사례를 살펴본다.

🔹 사례 1. 넥스트 키 락은 갭에도 락을 건다.

# session 1
DELETE FROM users WHERE company_name = '네카라쿠배' AND age > 50;
# session 2
INSERT INTO users(pk, name, company_name, age) VALUE(3, '정민', '네카라쿠배', 49);

# (company_name, age) 인덱스를 생성했다고 가정
# users 테이블 현황
# 45 | 최팡 | 네카라쿠배 | 46
# 66 | 김이버 | 네카라쿠배 | 48
# 76 | 박카오 | 네카라쿠배 | 51
# 88 | 이라인 | 네카라쿠배 | 53  

위 쿼리에서 두 세션의 쿼리는 조건이 겹치는 레코드가 없지만, 넥스트 키락에 의해서 인덱스 레코드에 있는 갭(session 1의 조건에 만족한 제일 앞에 있는 레코드 이전의 갭)에 락이 걸리면서 session 2의 경우 대기하게 된다.

당연히 앞선 session 1이 금방 끝나 준다면, 대기하던 session 2의 쿼리가 수행될 것이지만, session 수가 엄청나게 많다고 가정하고 저런 쿼리가 많이 발생하면 점점 대기 시간이 길어져 timeout이 발생하거나, 굳이 비슷한 쿼리가 많지 않더라도 어떤 쿼리에 의해 데드락이 걸려있는 경우, 풀릴 때까지 기다리다가 예외가 발생하게 될 것이다.

REPEATABLE READ 수준에서 넥스트 키 락을 피할 수 없기 때문에 위와 같은 쿼리로 지우지 말고 최대한 인덱스 레코드 하나하나 집어서 지울 수 있도록 하는 식으로 우회하는 방법을 쓸 수 있다.

🔹 사례 2. 서브 쿼리의 SELECT 문은 락을 건다.

# session 1
DELETE FROM user_details WHERE account_id = 99; # X-LOCK

# session 2
UPDATE user_details 
SET vip_flag = 1
WHERE account_id in (SELECT account_id FROM accounts WHERE amount >= 100000000) # S-LOCK

# session 1
DELETE FROM accounts WHERE account_id = 99;

# session 2의 서브쿼리에 account_id = 99 인 레코드가 있다고 가정

위 쿼리에서는 데드락이 걸린다.

session 1은 user_details 테이블에 account_id로 인덱스가 걸려있어서 해당 레코드를 지우는 쿼리를 처리하기 위해 인덱스 레코드에 락을 걸었다.

마침 session 2는 accounts 테이블에 잔액이 1억 원 이상 가지고 있는 유저를 VIP로 수정하는 쿼리를 쐈는데 서브 쿼리에 SELECT 절에 의해서 accounts의 많은 레코드와 갭에 락이 걸었다.

(SELECT 쿼리라 Shared 락이다. 기본적으로 MySQL에서 SELECT 쿼리에 락을 걸지 않지만 서브 쿼리에는 Shared 락을 건다.)

session 2는 앞선 session 1에서 account_id가 99인 user_details 테이블의 인덱스 레코드에 잠금이 걸려있어 업데이트를 하지 못하고 기다리게 되었다.

또 바로 session 1에서 accounts 테이블에 account_id가 99인 레코드를 지우는 쿼리를 보냈다고 해보자.

session 1은 앞선 session 2에서 accounts 인덱스 레코드에 Shared 락이 걸린 상태이기 때문에 account_id 가 99인 레코드를 지우지 못하고 대기한다.

서로 락이 풀리기 기다리므로 데드 락이 발생한다.

MySQL 엔진 레벨의 락

1. 글로벌 락
   • 모든 테이블에 잠금을 건다.
   • "FLUSH TABLES WITH READ LOCK" 명령으로 락을 걸 수 있는데 굳이 일부러 걸지 않는다. 특히나 운영 중에 전체를 멈추는 락을 건다는 건 서비스 장애로 이어지기 때문이다.
2. 테이블 락
   • 각각 테이블에 테이블 단위로 락을 건다.
   • MySQL InnoDB는 기본적으로 스토리지에서 레코드 락을 사용하므로 DML에서는 주로 사용되지 않지만, DDL 같이 테이블의 스키마에 변화를 줄 때는 사용된다.
   • 마찬가지로 특정 명령어로 락을 걸 수 있으나 이 역시도 장애로 이어질 확률이 높아서 명시적으로 사용하지는 않는다.
3. 네임 락
   • 테이블 또는 뷰등의 스키마 객체의 이름을 변경하는 경우(rename ...)에 자동으로 락을 건다.
4. 유저 락
   • 사용자가 지정한 문자열에 락을 건다.

정리

• 트랜잭션은 원자성(COMMIT OR ROLLBACK)을 보장하기 위한 기능이고 락은 동시성을 보장하는 기능이다.
• MySQL은 크게 스토리지 엔진 레벨의 락과 MySQL 엔진 레벨의 락으로 나눌 수 있다.
• MySQL의 기본 격리 수준은 "REPEATABLE READ"고, 레코드가 아닌 인덱스 레코드에 락을 건다.
• REPEATABLE READ 격리 수준에서는 넥스트 키 락이 기본적으로 동작하고 해당 인덱스 레코드뿐만 아니라 사이의 갭까지 락을 거는 특성이 있다.
• 넥스트 키락이 갭에도 락을 걸기 때문에 의도치 않게 넓은 범위의 레코드에 락이 걸려 문제가 발생할 확률이 높아진다. 이를 주의해서 최적화된 쿼리를 만들 수 있도록 해야 한다.

💎 참고 도서 및 사이트

Real MySQL 위키북스, 이성욱 지음

https://taes-k.github.io/2020/05/17/mysql-transaction-lock/