[DB] MySQL 락 걸어보고 분석해보기!

MySQL InnoDB의 잠금에 대해 공부하다가 직접 락을 확인해보고 싶어서 실습해보았다. 

---

1. Autocommit이란? 

Auto commit은 DML문이 실행될 때, 사용자가 명시적으로 저장 명령을 내리지 않아도 즉시 DB에 영구적으로 반영하는 설정이다.

일반적인 DB 작업은 START TRANSACTION → 작업 → COMMIT의 단계를 거치는데, Auto Commit이 켜져 있으면, SQL 문 하나가 실행될 때마다 Commit 과정이 자동으로 처리된다. 

 

해당 설정이 기본적으로 켜져 있는데, 그러면 작업 중에 걸린 Lock 정보를 확인하기가 어려우니까 SET 명령어로 Auto Commit을 꺼주어야 한다. 

Default 값인 1(on)으로 설정되어 있다.

SET autocommit = 0; -- Auto Commit 끄기 (수동 커밋 모드)
SET autocommit = 1; -- Auto Commit 켜기 (자동 커밋 모드)

 

금융이나 결제 시스템처럼 데이터의 정밀함이 요구되는 곳에서는 Auto Commit을 끄고 작업하는 경우가 있다고 들었는데, 그 이유는 Rollback 명령으로 되돌리기 편하고, 트랜잭션 범위를 임의로 조정하여 커밋할 수 있다는 장점이 있기 때문이다.

 

2. SQL 쿼리 실행 & 락 분석

이제 auto commit을 끄고 BEIGN으로 트랜잭션 시작하고 쿼리를 날려보자. 

BEGIN;

UPDATE user
SET IS_DELETED = 1
WHERE ID IN (770868821036233836);

SELECT index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'user';

ROLLBACK;

 

performanace_schema 데이터베이스의 data_locks 테이블을 통해 InnoDB가 걸은 락 정보를 확인할 수 있다.

• 첫 번째 줄: TABLE / IX (의도 배타적 락)
  • 해당 테이블의 행(들)을 수정할 것이라고 표시해두어 테이블 구조를 변경(ALTER TABLE)하는 것을 막는다. 다만, 다른 행을 UPDATE하거나 INSERT하는 것은 전혀 방해하지 않는다.
• 두 번째 줄: RECORD / X, REC_NOT_GAP (레코드 배타적 락)
  • index_name (PRIMARY): ID 컬럼(PK) 인덱스를 타고 락이 걸렸음을 보여준다.
  • lock_mode (X, REC_NOT_GAP)
    • X (Exclusive): 쓰기 락. 내가 수정 중이니 커밋 전까지 아무도 이 데이터를 수정하거나 삭제할 수 없다.
    • REC_NOT_GAP: 갭 락(Gap Lock)은 없다는 뜻이다. 딱 해당 레코드만 정확히 잠궜다는 의미다.

좀더 자세히 봐보면

이렇게 여러 정보들이 존재한다. 이를 통해 락을 잡고 있는 트랜잭션을 파악해서 의도적으로 락을 해제시킬 수도 있고, 락 관련 문제가 생겼을때 트러블슈팅 할 수 있다.

 

하지만, 아래와 같이 범위 기반으로 WHERE 검색을 하면?  

UPDATE user
SET IS_DELETED = 0
WHERE ID BETWEEN 1 AND 3;

 

이전과 다르게 lock_data: supremum pseudo-record락이 추가되었다. 이는 현재 테이블의 마지막 레코드 이후 범위에 새로운 데이터가 끼어들지 못하도록 넥스트 키 락을 건 것이다. 데이터가 많았다면, 3번 레코드 바로 다음 간격까지만 락이 걸리고 supremum까지 가지 않았을 것 같은데, 데이터가 적어서(10개 미만이었음) 전체 범위가 스캔된 것 같다. 

*MySQL의 repeatable read에서는 갭락과 넥스트 키 락 덕분에 phantom read 정합성이 만족된다. 

 

read commited 격리수준에서는 범위로 수정을 한다해도 갭락과 넥스트락이 잡히지 않는다.
격리수준은 다음과 같은 명령어를 통해 알 수 있다. 

SELECT @@transaction_isolation;

그리고 아래의 명령어로 격리수준을 변경할 수 있다. 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

 

참고로 Unique 제약으로 생긴 인덱스를 타면 아래와 같이 락이 걸린다. 

 

덧붙여 말하자면, 보조 인덱스를 이용한 변경 작업에는 넥스트 키 락 또는 갭락을 사용하는데, PK 또는 UNIQUE 인덱스에 대한 변경 작업에서는 갭에 대해 잠그지 않고 레코드 자체에 대해서만 잠군다. 왜냐하면, 보조 인덱스의 경우에는 유일성이 보장되지 않기에 해당 인덱스의 레코드가 잠겼다 하더라도 다른 똑같은 이름의 보조 인덱스 레코드가 추가되면, 조회 시 데이터 개수가 변하는 Phantom Read가 발생할 수 있기 때문이다. (헷갈릴까봐 말하지면, 보조 인덱스는 같은 값이라도 모두 [보조 인덱스 키 + PK] 조합으로 각기 따로 저장된다.)

 

다만, PK 혹은 UNIQUE 인덱스를 쓰더라도 위에서 봤던 것 처럼 범위 검색을 하게 되면, 그 사이에 새로운 값이 들어오는 것을 막아야 하기에 갭락이 사용된다.

 

만일 WHERE 검색 조건이 인덱스를 타지 않는다면, MYSQL은 어떤 레코드를 수정해야 할지 모르기에 테이블의 모든 PK '인덱스 레코드'를 잠그고 실행한다. 

 

3. 프로세스 종료

auto commit이 비활성화된 상태에서 끝나지 않은 트랜잭션 때문에 락이 걸려 있다면, 아래와 같이 프로세스를 종료시킬 수 있다. 

// INNODB_TRX 테이블 조회해서 해당 트랜잭션 실행중인 쓰레드 종료
SELECT * FROM information_schema.INNODB_TRX;
// trx_mysql_thread_id로 프로세스 종료
KILL 47544;

 

 

참고로, 스프링에서의 @Transactional을 사용하면 동작 과정은 다음과 같다. 

1. Spring이 트랜잭션 시작을 감지하고, IoC에 있던 Proxy를 호출해서 트랜잭션을 시작한다. 이때, HikariCP 등에서 커넥션을 가져오고, connection.setAutoCommit(false)를 실행하여 자동 저장을 막는다. 

2. 로직을 실행하여 영속성 컨텍스트(1차 캐시)의 객체를 수정하지만, DB에는 아직 반영되지 않는다. 논리 트랜잭션이 전부 성공해서 바깥쪽의 물리 트랜잭션이 끝나면 TransactionManager.commit()이 호출된다. 

3. JPA의 flush()가 호출되면서 Dirty Checking을 통해 스냅샷과 현재 엔티티 상태를 비교하고 그 차이를 반영하기 위한 SQL문을 생성한 후, 쓰기 지연 SQL 저장소에 적재하고 저장되어있던 SQL을 전송하는 과정을 반복한다.

4. DB가 SQL을 받아 메모리에서 실행하고, 이때 데이터에 락을 걸어 다른 트랜잭션이 수정하지 못하게 막는다(레코드 락, 넥스트 키락, 갭락).

5. 스프링이 connection.commit()을 호출해 REDO 로그를 디스크에 저장(WAL)하여 완결성을 보장하고, 실패하면 connection.rollback()을 통해 전체 취소를 하여 원자성을 보장한다. *Checkpoint로 수시로 디스크에 Flush되기도 한다. 

6. 다음 사용자를 위해 autocommit을 다시 true로 돌려놓고 커넥션을 반납한다.