[Real MySQL 8.0] 05. 트랜잭션과 잠금 (1) 트랜잭션, MySQL의 잠금

공부/Database

[Real MySQL 8.0] 05. 트랜잭션과 잠금 (1) 트랜잭션, MySQL의 잠금

d02 2023. 9. 12. 00:51

[Real MySQL 8.0]을 읽으며 정리한 내용입니다.

💭 부분 은 좀 더 공부가 필요하거나, 생각해보고 싶은 내용들 ~

트랜잭션

InnoDB vs MyISAM

InnoDB 엔진으로 만든 테이블과 MyISAM 엔진으로 만든 테이블에 트랜잭션 동작을 비교해보자.
- MyISAM 엔진에서는 부분 업데이트가 발생할 수 있다. 이는 테이블 데이터의 정합성을 맞추는 데 어려움을 준다.
- 각 테이블에 fdpk 컬럼 값이 3인 레코드를 저장해두고 아래 쿼리를 각각 실행해 중복 키 오류를 발생시켜보자.

SET autocommit=ON;

INSERT INTO tab_myisam (fdpk) VALUES (1),(2),(3); // MyISAM 테이블에는 1, 2 레코드가 남아있다.
INSERT INTO tab_innodb (fdpk) VALUES (1),(2),(3); // InnoDB 테이블에는 3 레코드만 있다.

MyISAM은 이미 insert된 1, 2는 그대로 두고 쿼리 실행을 종료한다. -> 부분 업데이트
InnoDB는 쿼리 중 일부더라도, 오류가 발생했다면 전체를 원 상태로 돌린다.
참고로, MySQL 8.0 버전부터 InnoDB가 기본 스토리지 엔진으로 채택되었다.

💭 MyISAM 엔진의 위와 같은 동작은 치명적인 단점인 것 같은데, 그럼에도 이 엔진을 쓰는 경우는 언제가 있을까?

MySQL의 잠금(Lock)

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.

스토리지 엔진을 제외한 부분이 MySQL 엔진 부분이라고 이해하면 된다.

출처: https://dev.mysql.com
구조를 보면 알 수 있듯이,
- MySQL 엔진 레벨의 잠금 -> 모든 스토리지 엔진에 영향을 끼친다.
- 스토리지 엔진 레벨의 잠금 -> 스토리지 엔진 간 상호 영향을 끼치지 않는다.

MySQL 엔진의 잠금

글로벌 락 (Global Lock)

한 세션에서 글로벌 락을 획득하면, 다른 세션에서 쿼리를 실행한 경우 글로벌 락이 해제될 떄까지 대기 상태로 남는다.
- SELECT를 제외한 대부분의 DDL, DML이 해당된다.
MySQL 서버 전체에 영향을 미친다.
- 작업 대상 테이블, 데이터베이스가 달라도 마찬가지다.
획득 방법
FLUSH TABLES WITH READ LOCK;
사용 예시
- 여러 DB에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump(=백업 프로그램)로 일관된 백업을 받아야 할 때 사용
- 웹 서비스용으로 사용되는 MySQL 서버에서는 가급적 사용 X

💭 왜 InnoDB 스토리지 엔진에는 해당되지 않을까? 왜 더 가벼운 글로벌 락이 필요할까?
(8.0 버전부터는 다른 백업 툴들의 안정적 실행을 위한 백업 락이 도입됨) -> p.162를 읽어보기

테이블 락 (Table Lock)

개별 테이블 단위로 설정되는 락
획득 방법
1. 명시적 방법 : 이 작업은 글로벌 락과 같이 온라인 작업에 상당한 영향을 미치므로, 특별한 상황이 아니면 사용하지 않는다.
```
LOCK TABLES table_name [ READ | WRITE ];
```
2. 묵시적 방법 : 데이터 변경 쿼리를 실행하면 자동으로 발생한다.
  - 쿼리 실행 동안 자동 획득 -> 쿼리 완료 후 자동 해제
  - MyISAM, MEMORY 테이블인 경우, 위와 같이 동작함
  - 하지만 InnoDB 테이블인 경우, 대부분의 DML에서는 무시되고, DDL에서만 락 설정됨

네임드 락 (Named Lock)

GET_LOCK() 함수를 이용해 사용자가 지정한 임의의 문자열에 대해 잠금 설정하는 락
- 문자열의 대상은 데이터베이스 객체가 아니다. 사용자가 단순히 지정한 문자열이다.
사용 예시
- 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션
- 한꺼번에 많은 레코드를 변경해야 함 -> 자주 데드락의 원인이 됨

💭 네임드 락을 사용하면 데이터 베이스 객체 기준이 아니라, 사용자가 지정한 기준으로 분류해서 락을 걸어줄 수 있다는 뜻일까?
ex) A, B 데이터베이스에 'mylock'을 걸어주고 변경 작업을 할 때에는 데이터베이스 별로 'mylock' 잠금 여부를 확인한다.

메타데이터 락 (Meta Data Lock)

데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 락
- MySQL 서버의 Online DDL은 많은 시간과 자원을 소모할 수도 있다. 언두로그의 증가, 버퍼의 크기 뿐만 아니라 DDL은 단일 스레드로 작동한다.
사용 예시
- 저장된 데이터가 많은 테이블의 구조를 변경해야 할 때
- DDL 대신, 임시 테이블을 생성 -> 임시 테이블에 데이터 복사 -> 임시 테이블의 이름 변경

InnoDB 스토리지 엔진의 잠금

레코드 기반의 잠금 방식을 탑재하여 동시성 처리를 제공할 수 있다.
MySQL 서버의 information_schema 데이터베이스에서 InnoDB의 트랜잭션, 잠금, 잠금 대기중인 트랜잭션 목록을 조회할 수 있다.

레코드 락 (Record Lock)

다른 상용 DBMS의 레코드 락과 동일하다.
단 InnoDB 스토리지 엔진은 레코드 자체가 아닌 인덱스의 레코드를 잠근다.

갭 락 (Gap Lock)

레코드 자체가 아니라, 레코드와 바로 인접한 레코드 사이의 간격만을 잠근다. ->?
다른 DBMS에는 없다.
넥스트 키 락의 일부로 자주 사용된다.

넥스트 키 락 (Next key Lock)

레코드 락과 갭 락을 합쳐 놓은 형태의 락이다.

💭 갭 락, 넥스트 키 락은 아직은 어떤 것인지 잘 와닿지 않는다. 경험해봐야 알려나

자동 증가 락

AUTO_INCREMENT 값이 중복되지 않고 저장된 순서대로 증가하도록 보장해주기 위한 락 (두 개의 INSERT 쿼리가 동시에 실행되는 경우를 생각해보자.)
- 트랜잭션과 관계 없이, INSERT, REPLACE와 같이 새로운 레코드를 저장하는 쿼리에서만 아주 짧은 시간 락이 걸렸다가 즉시 해제된다.
5.1 버전 이상 부터는 innodb_autoinc_lock_mode 라는 시스템 변수를 이용해 작동 방식을 변경할 수 있다.

인덱스와 잠금

인덱스의 레코드를 잠근다는 사실이 중요한 이유

-- employees 테이블에 first_name 컬럼만 멤버로 담긴 인덱스가 준비되어 있다고 하자.
-- 아래와 같이 first_name='Georgi'인 사원은 253명,
SELECT COUNT(*) FROM employees WHERE first_name='Georgi'; 
+--------------+
|           253|
+--------------+

-- first_name='Georgi' and last_name='Klassen'인 사원은 딱 1명이라고 했을 때
SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen'; 
+--------------+
|             1|
+--------------+

-- 아래와 같은 조건으로 변경 쿼리를 날리면 어떻게 될까?
UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' and last_name='Klassen';

변경할 레코드는 딱 1개임에도, first_name='Georgi'인 레코드 253건이 모두 잠긴다.
해당 UPDATE 쿼리에서 인덱스를 이용할 수 있는 조건은 첫 번째 조건인 first_name='Georgi' 뿐이기 때문이다.
만약 인덱스가 하나도 없다면?
- 풀 스캔으로 UPDATE 작업을 하면서, 테이블에 있는 모든 레코드를 잠근다.

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

5.1 버전부터 레코드 잠금에 대한 메타 정보를 제공한다. 이를 통해 레코드 잠금과 잠금 대기에 대한 조회가 가능하다.
- 테이블들을 적절히 조인하여 대기중인 스레드와, 이를 막고 있는(blocking) 스레드를 확인할 수 있다. -> p.174 참고하기
- 강제로 잠금을 해제하려면, KILL 명령을 이용해 해당하는 스레드의 MySQL 서버 프로세스를 강제 종료한다

💭 메타 정보를 조회하고 해제할 수 있다는 것을 인지해두고, 필요할 때 구체적인 방법을 찾아 활용해보자