5.3 InnoDB 스토리지 엔진 잠금

5.3 InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL과 별개로 잠금을 제공한다.

💡 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다.

5.3.1 InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 레코드 기반의 잠금 을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 *락 에스컬레이션(레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업되는 경우)되는 경우는 없다.

또한 InnoDB에서는 특징인 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이라는 것이 존재한다.

점선으로 표시된 것은 존재하지 않는 레코드를 뜻한다.

5.3.1.1 레코드 락

레코드 자체만을 잠그는 것을 *레코드 락이라고 한다.

• 레코드 ?
• 레코드란 테이블의 한 데이터의 집합(=튜플, 행, 레코드)를 말한다.

💡 다른 DBMS 레코드 락과 동일한 역할을 하지만, InnoDB 스토리지 엔진은 레코드 자체가 아니라, `인덱스의 레코드`를 잠근다.

• 인덱스가 없는 테이블이라면?
• 내부적으로 자동 생성된 인덱스의 레코드를 잠근다.

5.3.1.2 갭 락

InnoDB 스토리지 엔진에만 있는 갭 락이라는 개념이다.

갭 락은 레코드를 잠그는게 아닌, 레코드와 레코드 사이의 그 간격(갭)을 잠그는 것이다. 레코드와 레코드 사이의 새로운 레코드가 생성되지 않도록 하기 위함이다.

갭 락은 다음에 설명할 넥스트 키 락과 함께 쓰인다.

5.3.1.3 넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락(Next Key Lock)이라고 한다.

• innodb_locks_unsafe_for_binlog 기본값: 활성화
• innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화가 되면(0으로 설정) 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다. (공식문서에는 Deprecated라고 표시되고, 8.0 기준으로는 해당 변수가 사라짐)

하지만, 넥스트 키 락의 주 목적은 소스 서버와 레플리카 서버의 데이터 일치를 위해 사용하므로 레플리카 서버를 사용하지 않는다면 굳이 사용하지 않는 것이 좋다.

아래는 캡락, 넥스트-키 락에 대해서 자세하게 설명해준다.

[MySQL]MySQL 벼락치기(5) - 갭락(Gap Lock)과 넥스트 키 락(Next-Key Lock)

5.3.1.4 자동 증가 락

MySQL에서는 자동 증가하는 숫자 값을 추출(채번)하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공한다. 해당 값은 여러 레코드가 동시에 INSERT가 되는 경우 중복되지 않고 순서대로 증가하는 숫자를 가져야 한다.

💡 이를 해결하기 위해 InnoDB 스토리지 엔진 내부적으로 `AUTO_INCREMENT 락` 이라고 하는 테이블 수준의 잠금을 사용한다.

AUTO_INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE, DELETE 쿼리에서는 걸리지 않는다.

특징으로는, 트랜잭션 관계 없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간에만 락이 걸렸다가 즉시 해제된다. (AUTO_INCREMENT에 명시적으로 값을 지정해도 자동 증가 락이 걸리게 된다)

• 아주 짧은 순간에만 락이 걸렸다가 해제되는 잠금이라 문제가 되진 않지만 작동 방식을 변경할 수 있다.
  • innodb_autoinc_lock_mode=0
    • 모든 INSERT 문장은 자동 증가 락을 사용한다.
  • innodb_autoinc_lock_mode=1
    • 단순한 1건 또는 여러건의 레코드를 MySQL 서버가 레코드의 건수를 정확하게 예측할 수 있는 경우에는 자동 증가 락을 사용하지 않고 래치(뮤텍스)를 이용해 처리한다. (이하 생략…)
  • innodb_autoinc_lock_mode=2
    • 자동 증가 락을 사용하지 않고, 래치(뮤텍스)를 사용한다. 동시 처리 성능이 향상되지만, 연속된 자동 증가 값을 보장하지 않는다.

5.3.2 인덱스와 잠금

앞서 innoDB는 레코드를 자체를 잠그는 것이 아닌 인덱스를 잠그는 방식이라고 했다.

즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

예를 들어 UPDATE 문을 살펴보자.

• emp 테이블에는 first_name 컬럼에만 인덱스가 걸려있다.

mysql> SELECT COUNT(*) FROM emp WHERE first_name='Georgi';
+------+
|  253 |
+------+

mysql> SELECT COUNT(*) FROM emp WHERE first_name='Georgi' and last_name='Klassen';
+------+
|    1 |
+------+

mysql> UPDATE emp SET hire_date=NOW() WHERE first_name='Georgi' and last_name='Klassen';
--- 1건의 레코드 업데이트

과연 단 1건의 데이터를 변경하기 위한 UPDATE 쿼리를 실행했을 때 락이 걸리는 레코드는 몇개일까?

바로 253개의 레코드이다.

💡 왜 253개인가? 앞서 InnoDB 스토리지 엔진은 **인덱스를 잠그는 방식의 락**을 제공한다고 했다. UPDATE 문의 WHERE 절에 쓰인 두 컬럼(first_name, last_name) 모두 인덱스가 걸려있지 않았다. 즉, first_name만 걸려있기 때문에 `first_name=’Georgi’` 레코드가 모두 잠기는 것이다.

• 만약 인덱스가 하나도 없었다면?이것이 바로 MySQL 만의 방식이며 InnoDB에서 인덱스 설계가 중요한 이유이다.
• where절에 자주 들어가는 컬럼에 인덱스를 거는 식으로 많이 사용한다. (where절에 두 컬럼이 쓰이는데 하나만 걸면 의미 없다)
• 테이블 풀 스캔하면서 몇천..몇억개의 모든 레코드를 잠그게 된다.

5.3.3 레코드 수준의 잠금 확인 및 해제

InnoDB 스토리지 엔진의 레코드 수준 잠금은 테이블 수준 잠금보다는 조금 더 복잡하다. 테이블 잠금은 대상 테이블이 잠겼는지 쉽게 파악이 되지만, 레코드 잠금은 잘 안쓰이는 레코드가 잠기면 한참동안이나 잠겨있는 상태로 남아있는 경우가 있다. (강제로 종료하려면 KILL 명령어를 사용하자)

다음 시나리오를 보면서 잠금에 대해서 이해해보자.

connection 1:
    BEGIN;
    UPDATE emp SET hire_date=NOW() WHERE emp_no=100;

    connection 2:
        UPDATE emp SET hire_date=NOW() WHERE emp_no=100;

        connection 3:
            UPDATE emp SET hire_date=NOW() WHERE emp_no=100;

MySQL 8.0부터는 performance_schema의 data_locks와 data_lock_waits 테이블을 이용해 트랜잭션이 어떤 잠금을 기다리고 있는지, 기다리고 있는 잠금은 어떤 트랜잭션을 가지고 있는지 확인할 수 있다.

mysql> SHOW PROCESSLIST;

 

id	time	state	info
17	607		NULL
18	22	updating	UPDATE emp SET hire_date=NOW() WHERE emp_no=100
19	21	updating	UPDATE emp SET hire_date=NOW() WHERE emp_no=100