[MySQL] 트랜잭션과 MySQL 잠금(Lock) 종류에 대해 알아보자

Loopy·2023년 7월 31일
db
0
post-thumbnail

MySQL 트랜잭션과 잠금은, 헷갈릴 수 있지만 엄연히 다른 목적을 위한 기능이다.

트랜잭션 : 작업의 완전성(일부만 적용되는 일이 없도록)을 보장하는 데이터의 정합성을 위한 기능이다.
잠금(Lock) : 레코드에 대한 동시성을 제어하기 위한 기능이다. 한 레코드를 동시에 여러 커넥션에서 변경하면 값이 예측할 수 없는 상태가 되기 때문이다.

MyISAM 이나 MEMORY 스토리지 엔진은 InnoDB 와 달리 트랜잭션을 지원하지 않는다. 트랜잭션 기능이 얼마나 강력한지 이번 장에서 배우면, 왜 MySQL 에서 기본 스토리지 엔진으로 InnoDB 를 택했는지 이해할 수 있을 것이다.

☁️ 트랜잭션

트랜잭션은 쿼리 개수에 상관없이, 하나의 쿼리만 있더라도 논리적인 작업 자체가 100% 적용되거나(커밋) 0% 적용(롤백) 됨을 보장해주는 기능이다. 예시를 들어보자.

MyISAM 과 InnoDB 에 각각 테이블을 생성하고 id = 3 인 데이터를 넣었다.

mysql> CREATE TABLE tab_myisam ( id INT NOT NULL, PRIMARY
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO tab_myisam(id) values(3);
Query OK, 1 row affected (0.03 sec)

mysql> CREATE TABLE tab_innodb ( id INT NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO tab_innodb(id) values(3);
  1. MyISAM
mysql> INSERT INTO tab_myisam(id) values(1), (2), (3);
ERROR 1062 (23000): Duplicate entry '3' for key 'tab_myisam.PRIMARY'

키 중복 오류로 쿼리가 실패했을 때, 실패한 데이터들인 1과 2까지 저장이 되었다.

  1. InnoDB
mysql> INSERT INTO tab_innodb(id) values(1), (2), (3);
ERROR 1062 (23000): Duplicate entry '3' for key 'tab_innodb.PRIMARY'

InnoDB 테이블에서는, 트랜잭션 원칙대로 쿼리 수행 중 일부라도 오류가 발생하면 아예 쿼리 자체를 실행시키지 전 상태로 되돌려버렸다.

하지만, MyISAM 테이블에서는 3 을 추가할 때 오류가 발생했음에도 앞서서 추가되었던 1 과 2 값을 그대로 저장하고 끝나버린다. 이렇게 부분만 업데이트가 되면, 테이블 데이터의 정합성을 맞추기 힘들어진다.

혹은, 아래와 같이 다수의 쿼리를 요청했을 때 실패한 남은 데이터들을 if-else 분기문을 통해 삭제하는 과정이 매우 복잡해 머리가 아파질 수 있다.

1. 테이블 A에 데이터를 저장한다.
2. 테이블 A에 데이터 저장이 성공하면, B에 데이터를 저장한다.
3. B에 데이터를 저장하는 것이 실패하면, A 저장했던 데이터를 다시 삭제한다. 
4. A에 저장했던 데이터 삭제하는 작업조차 실패하면, 그 이후에는 어떻게 해야 하는가?

하지만 트랜잭션을 이용한다면, 아래와 같이 매우 깔끔한 코드를 얻을 수 있다.

try {
	START TRANSACTION;
    INSERT INTO tab_A;
    INSERT INTO tab_B;
    COMMIT;
} catch(exception) {
	ROLLBACK;
}

🫧 주의사항: 트랜잭션 범위는 좁게 잡아야 한다.

예를 들어, 다음은 게시물을 저장하고 등록에 대한 알림 메일을 발송하는 기능에 대한 트랜잭션 설계도이다. 모든 과정을 하나의 트랜잭션 안에서 처리하고 있다.

1. 처리 시작
=== 데이터베이스 커넥션 생성 ===
=== 트랜잭션 시작 ====

2. 사용자 로그인 여부 확인
3. 글쓰기 내용 오류 여부 확인
4. 첨부로 업로드된 파일 확인 내용 및 저장
5. 사용자의 입력 내용을 DBMS에 저장
6. 저장된 내용 또는 기타 정보를 DBMS에서 조회
7. 게시물 등록에 대한 알림 메일 발송
8. 알림 메일 발송 이력을 DBMS에 저장

=== 트랜잭션 종료 ===
=== 데이터베이스 커넥션 반납 ===
9. 완료

해당 설계안에서 잘못된 점이 보이는가?

  1. 2-4 번 작업에서 데이터베이스의 커넥션이 필요 없는데도 커넥션을 맺고 트랜잭션을 시작하고 있다. DB 커넥션은 개수가 제한적이여서, DBMS 서버가 노높은 부하 상태에 빠질 수 있기 때문에 트랜잭션을 소유하는 시간을 최소화 하는 것이 좋다.

  2. 8 번 알림 발송과 같이 네트워크를 타는 로직은 트랜잭션 내에서 제거해야 한다. 외부 발송 서버와 통신할 수 없는 상황이 발생한다면, 웹 서버 뿐만 아니라DBMS 서버까지 위험해지기 때문이다.

  3. 입력한 정보 저장하는 4-5 번을 하나로 묶고, 단순 조회할 때는 트랜잭션에 포함하지 않아도 된다. 8 번 역시 별개의 트랜잭션으로 두는게 적합하다.

결론은, 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화 되어 있는 코드의 범위를 최대한 줄여야 한다.

MySQL 엔진 잠금

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

MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.

☁️ 글로벌 락

글로벌 락은 FLUSH_TABLE_WITH_READ_LOCK 명령을 통해 획득할 수 있다. 한 세션에서 글로벌 락을 획득하면, MySQL 서버 전체(다른 데이터베이스)에 존재하는 모든 테이블을 닫고 잠금이 걸린다. 따라서 SELECT 문장을 제외한 DDL/DM 문장을 실행하는 경우 락 해제 전까지 대기해야 한다.

글로벌 락은 언제 사용하면 좋을까?

여러 테이블에 존재하는 MyISAM 이나 MEMORY 테이블에 대해 mysqldump 로 일관된 백업을 받아야 하는 경우 사용하면 좋다.

🔖 세션
데이터베이스 접속을 시작으로, 여러 데이터베이스에서 관련 작업을 수행한 후 접속을 종료하기까지 전체 기간(연결 시간)을 의미하며, 쉽게 커넥션이라고 생각하면 된다.

☁️ 테이블 락

테이블 락이란 개별 테이블 단위로 설정되는 잠금이며, 명시적 락과 묵시적 락으로 나뉜다.

  1. 명시적 락
    명령어를 통해 락을 획득하고 해제한다. 하지만, 글로벌 락과 동일하게 작업에 상당한 영향을 끼치기 때문에 거의 사용할 일이 없다.
LOCK TABLES table_name [ READ | WRITE]
UNLOCK TABLES table_name [ READ | WRITE]
  1. 묵시적 락
    MyISAM 이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행할 때 락이 걸리고, 쿼리가 완료된 후 자동으로 해제된다.
    InnoDB 테이블은 스토리지 엔진 차원에서 레코드 기반 잠금을 제공하기 때문에 DML 쿼리에서는 묵시적인 테이블 락이 설정되지 않고, DDL 쿼리에서만 설정된다.

☁️ 네임드 락

GET_LOCK() 함수를 이용해 테이블이나 레코드와 같은 데이터베이스 객체 가 아닌 단순 문자열에 대한 잠금을 거는 것을 의미한다. 한 세션에서 해당 잠금을 유지하고 있는 동안, 다른 세션에서 동일한 이름의 잠금을 획득할 수 없다.

자주 사용되지는 않는데, 어디에 사용할 수 있을까? 바로 Redis를 사용하기 위한 인프라 구축 및 유지보수 비용을 발생하지 않고, MySQL 을 사용해 분산 락을 구현할 수 있다고 한다.

🔖 분산 락이란?
단일 서버에서는 synchronized 로 스레드 간 접근을 조절할 수 있지만, 다수 서버가 되는 경우 별개의 프로세스로 동작하고 있기 때문에 락을 거는 것이 의미가 없어진다. 이렇듯 다수의 서버에서 사용하는 락을 분산 락이라고 한다.

  1. 클라이언트 상호 동기화 처리
    DB 서버 한대에 웹 서버가 여러개라면 아래와 같이 네임드 락을 이용해 상호 동기화할 수 있다. 

    // 문자열에 대해 잠금을 획득하고, 이미 사용중이면 2초 간 대기
SELECT GET_LOCK("mylock", 2)

// 잠금이 설정되어 있는지 확인
SELECT IS_FREE_LOCK("mylock")

// 획득했던 잠금을 반납
SELECT RELEASE_LOCK("mylock")

  2. 다수의 레코드에 대해 복잡한 요건으로 변경하는 트랜잭션
    배치 프로그램 처럼 한꺼번에 다수의 레코드를 변경하면 데드락이 발생할 수 있는데, 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행할 수 있다.

MySQL 8.0 버전

8.0 버전에서는 네임드 락을 중첩해서 사용할 수 있고, 한번에 해제하는 기능이 추가되었다.

SELECT GET_LOCK("mylock_1", 10);
SELECT GET_LOCK("mylock_2", 10);

SELECT RELEASE_ALL_LOCKS();

☁️ 메타데이터 락

테이블이나 뷰 등의 데이터베이스 객체의 이름이나 구조를 변경해야 할 때 묵시적(자동으로) 획득하는 잠금이다.

언제 문제가 될까?

아래와 같은 상황을 가정해보자.

  1. table_a 에 select 쿼리를 날렸다.

  2. 그때 다른 개발자가 table_a 의 DDL을 날렸다.

  3. table_a 에 테이블 구조 변경으로 인해 메타 데이터 락이 걸렸다.

  4. 이 때 다른 세션에서 table_a 에 조회 쿼리를 날렸다면, 메타 데이터 락이 풀릴 때 까지 무한 대기하는 문제가 발생한다.

즉 메타데이터 잠금을 일으키는 DDL과 다른 쿼리들을 동시에 사용하는 경우(InnoDB 트랜잭션) 문제가 발생한다. 특히나 DDL은 단일 스레드로 동작하기 때문에 시간이 많이 소요되고, 그만큼 언두 로그도 증가하면서 고민해야할 문제가 많아진다.

이럴 때는 DDL 로 구조를 변경하지 말고, 아예 새로운 구조의 테이블을 생성하고 데이터를 가능한 한 많이 복사한 이후 RENAME 명령어를 통해 새로운 테이블을 실제 서비스 테이블로 변경하면 된다. 

RENAME TABLE access_log TO access_log_old, access_log_new TO access_log;

MySQL에서 DDL과 Metadata Lock, 장애와 자동화

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은, 스토리지 엔진 내부에서 자체적으로 레코드 기반의 잠금 방식을 탑재하고 있다. 레코드 기반이기 때문에, MyISAM 보다 훨씬 뛰어난 동시성 처리를 제공할 수 있다.

트랜잭션 잠금 정보 보는 방법

SELECT INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 
	FROM information_schema

8.0에서는 Performance Schema 를 이용해 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링 방법도 추가되었다.

☁️ InnoDB 엔진 잠금 종류(1)

1. Shared-Lock(공유락)

S-Lock는 읽기 락이라고도 불린다.

다른 트랜잭션에서 잠긴 데이터를 읽을 수 있고 다른 공유 락을 획득할 수 있지만, 쓰기는 허용하지 않는다. 즉, 같은 레코드에 대해 읽기 락이 여러개 존재할 수 있어 공유(share) 락이라고 한다.

생각해보면 다른 트랜잭션에서 읽기 조차 잠금을 걸어 허용하지 않으면, 트랜잭션 격리 수준의 4단계인 Serializeable 처럼 동시성이 너무 떨어져 오히려 성능 저하가 일어날 수 있으므로 쓰기만 허용하지 않도록 한 것으로 보인다.

SELECT ~ LOCK IN SHARE MODE  // 읽기 락 얻는 방법

2. Exclusive-Lock(배타적 락)

X-Lock 은 쓰기 락이라고도 불린다.

다른 트랜잭션에서 같은 레코드에 대해 락을 걸지 못하도록 한다. 기본적으로 쓰기 연산은 불가능하며, 읽기 연산은 트랜잭션 격리 레벨을 어떻게 설정하느냐에 따라 허용할 수도, 안할 수도 있다.

SELECT ~ FOR UPDATE  // 쓰기 락 얻는 방법

🫧 InnoDB 에서는 쓰기 락에 대한 읽기 연산을 허용하고 있을까?

InnoDB 기본 트랜잭션 격리 수준은 REPEATABLE READ 이다.
InnoDB 는 언두 로그를 통해 배타적 잠금이 걸려 있는 행도 읽을 수 있도록 함으로써, 더 높은 동시성을 제공한다.( Consistent Nonlocking Read) (다만 변경중인 데이터가 아닌 이전 데이터를 읽어온다.) 즉, InnoDB는 읽기 작업에 공유 락 자체를 걸지 않는다.

  1. SELECT : 순수 조회 문은 락이 걸린 데이터를 읽지 못하고, 언두 로그에서 해당 트랜잭션 ID보다 작은 ID를 가진 레코드들만 읽어온다.
  2. SELECT ... FOR UPDATE : 락이 걸린 레코드(최신 데이터)를 읽어올 수 있다.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

☁️ InnoDB 엔진 잠금 종류(2)

1. 레코드 락

그냥 레코드가 아니라, 레코드의 인덱스에 잠금을 건다. 인덱스가 생성되지 않았다면, 클러스터 인덱스(PK)를 이용해 잠금을 설정한다.

SELECT id FROM t WHERE id = 10 // 10개의 데이터에 락이 걸린다.
  1. 유니크 하지 않은 세컨더리 인덱스 : 넥스트 키 락 혹은 갭 락을 사용한다. row 를 찾기위해 스캔했던 index range에 대해서 적용된다.
  2. PK 또는 유니크 인덱스 : 레코드 락만 사용 (갭 락 사용 X)

주의해야할 점은, 인덱스에 잠금을 걸게 되면서 인덱스를 올바르게 설정하는 것이 성능에 매우 중요해졌다는 점이다.

예시를 들어보자. employees 테이블에는 현재 first_name 에만 인덱스가 걸려있고, 이름이 kim minsu 인 사람은 30만 건의 데이터 중 단 하나라고 가정한다. 이 때, kim minsu 의 입사 일자를 변경하는 UPDATE 쿼리를 날리게 된다면? 

UPDATE employees SET hire_date = NOW() WHERE first_name="kim" AND last_name="minsu";

놀랍게도, last_name 에는 인덱스가 걸려 있지 않기 때문에 first_name 인덱스로 걸러진 성이 김씨인 100 개의 데이터에 잠금이 모두 걸려버린다.

심각한 점은 만약에 두 칼럼 모두에 인덱스가 걸려 있지 않았다면, 김민수 1 명의 데이터를 업데이트하기 위해 30만 건의 데이터에 잠금이 걸려버리는 문제가 발생할 것이라는 것이다.

반대로 두 칼럼 모두에 인덱스가 걸려 있었다면, 우리가 기대했던 바대로 오직 한개의 데이터에만 락이 걸렸을 것이다.

2. 갭 락

레코드 자체가 아닌, 레코드와 바로 인접한 레코드 사이의 간격(범위)만을 잠근다. 갭 락의 주요 용도는 레코드 사이 간격에, 새로운 레코드가 생성되는 것을 제어하는 것이다.

갭 락을 통해서 트랜잭션 격리 레벨 REPETABLE READ 단계에서 아예 새로운 데이터가 추가되는 것을 막아주기 때문에,Phantom Read 을 방지할 수 있다.

즉 SELECT 쿼리를 두번 실행했을때 다른 트랜잭션에서 데이터가 수정되었더라도 같은 결과가 리턴되는 것을 보장해준다.

🔖 Phantom Read
한 트랜잭션 안에서 일정 범위의 레코드를 두번 이상 읽을 때, 첫 번째 쿼리에서 없던 레코드가 두 번째 쿼리에서 나타나는 현상이다. 트랜잭션 도중 새로운 레코드가 삽입되는 것을 허용하기 때문에 나타난다.

SELECT id FROM t WHERE id BETWEEN 10 and 20 FOR UPDATE

위 문장에서 id=15인 데이터를 삽입하려 한다면, 락이 걸려 있기 때문에 대기 상태에 빠지게 된다.

🔖 주의사항
트랜잭션 격리 수준을 READ COMMITTED 로 변경하면, 갭 락은 비활성화 된다. 이 경우 갭 락은 검색 및 인덱스 스캔에 대해 비활성화되며 외래 키 제약 조건 확인 및 중복 키 확인에만 사용된다.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks
https://medium.com/daangn/mysql-gap-lock-다시보기-7f47ea3f68bc

3. 넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 의미한다. 범위를 지정한 쿼리를 실행하게 되면, 실제로는 넥스크 키 락이 걸린다.

SELECT * WHERE PK > 99 FOR UPDATE

  1. pk > 99 를 만족하는 첫 번째 인덱스 레코드 pk=101 을 발견한다.
  2. 이제 각 레코드 사이에 데이터가 삽입되는 것을 방지하기 위해, 갭 락이 걸린다. 97 < lock < 101 부터 시작해 107 < lock < infinity 까지 걸린다.
  3. pk > 99 인 모든 인덱스 레코드들에 각각 레코드 락이 걸린다.

이렇게 갭 락과 레코드 락이 복합적으로 적용된다.

복제 구성을 위한 로깅 방식

갭 락과 넥스트 키 락의 주요 목적은, 바이너리 로그에 기록된 쿼리가 레플리카 서버(복제)에서 실행되었을 때 원본 서버에서 만들어낸 결과와 같도록 보장하는데 있다.

🫧 바이너리 로그란?

MySQL 서버 인스턴스의 데이터 변경사항들에 대한 정보를 포함하는 로그 파일의 세트이이다. 아래와 같이 두 가지 종류가 있다.

  1. STATEMENT 포맷의 바이너리 로그
    실행된 SQL 문을 그대로 바이너리 로그에 저장하는 방식이다.
    복제하려는 서버에서 해당 쿼리를 가지고 다시 실행을 시켜야 하므로, 락이 많이 걸린다.
  1. ROW 포맷의 바이너리 로그
    쿼리 실행 후 반환된 데이터 자체를 저장하는 방식이기에, 복제 시 소스와 레플리카 서버의 데이터 일관성을 유지할 수 있어 안전하다.

넥스트 키 락이나 갭 락 사용이 마냥 좋은 것은 아닌데, 오히려 데드락이 발생하거나 다른 트랜잭션을 기다리게 만들 수 있기 때문이다. 따라서 ROW 포맷으로 바꾸게 되면 넥스트 키 락이나 갭 락 사용이 줄어들기 때문에 MySQL 8.0 에서는 ROW 포맷이 기본값으로 변경되었다.

[MySQL] 복제 구성을 위한 바이너리 로깅 형식

4. 자동 증가 락(AUTO-INCREMENT)

테이블에 동시에 레코드가 삽입되는 경우, 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다. 이를 위해, 스토리지 엔진에서는 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

해당 락은 INSERT, REPLACE 와 같이 변경이 아닌 새로운 레코드를 저장하는 쿼리에서만 필요하다.

또한 보통은 트랜잭션이 끝나면 락이 해제되는 반면, 트랜잭션과 관계 없이 AUTO_INCREMENT 값을 가져오는 순간에만 락이 걸렸다가 즉시 해제된다. 만약 테이블 수준에 걸게 되면, 자동 증가 락은 테이블 당 하나이기 때문에 INSERT 쿼리가 동시에 실행되는 경우 대기 시간이 매우 길어질 것이다.

☁️ 레코드 수준의 잠금 확인 및 해제하기

레코드 잠금에 대한 메타 정보(ex) 잠금을 대기하는 클라이언트 정보, 잠금이 걸린 레코드 등) 를 확인하는 방법은 아래와 같다.

  • MySQL 5.1 이후 : information_schema 의 INNODB_TRX, INNODB_LOCK_WAITS
  • MySQL 8.0 이후 : performance_schema의 data_locks, data_lock_waits 테이블

직접 테스트하기

아까 언급했듯이, InnoDB 는 레코드가 자체가 아닌 인덱스를 잠근다.

트랜잭션을 시작하고, emp_no=10001 인 사원의 컬럼을 변경하는 쿼리를 날려보자. 

SET autocommit=0;
START TRANSACTION; 
UPDATE employees SET birth_date=NOW() WHERE emp_no=10001;

그리고 아래 명령어를 통해, 실행 중인 트랜잭션 내에서 어떠한 락들이 걸려 있는지 확인할 수 있다.

SELECT * from performance_schema.data_locks;

해당 쿼리를 실행하면, 당연히 검색 조건이 PK 인 emp_no 이므로 갭 락과 넥스트 키 락이 아닌 레코드 락이 걸린 것을 볼 수 있다. 또한, 테이블에 대해 IX 잠금 을 가지고 있다.

이 상황에서, 트랜잭션을 종료하지 않고 다른 스레드에서 같은 컬럼에 대해 UPDATE 쿼리를 실행한다면?

10번 프로세스가 updating 으로 바뀌고, 타임 아웃 에러가 발생했다. 앞선 트랜잭션에서 커밋을 하지 않았으니 잠금을 계속 가지고 있는 바람에 대기가 무한정 길어졌기 때문이다. 참고로 InnoDB의 MVCC 로 인해 읽기 연산이 가능해져, hire_date 가 변경되기 이전 데이터로 조회된 것을 볼 수 있다.

이후 기존 스레드에서 커밋을 하고 트랜잭션을 종료시키면, 그제서야 변경된 데이터로 조회가 된다.

profile
Loopy
개인용으로 공부하는 공간입니다. 잘못된 부분은 피드백 부탁드립니다!
이전 포스트

[AWS] 우리의 연약한 프리티어 서버가..또 터졌어요😇

다음 포스트

[MySQL] 트랜잭션 격리 수준(Isolated-level) 완전 정복하기

0개의 댓글