MySQL 스터디 - 트랜잭션과 잠금

Real MySQL 8.0 1권 <= 해당 책 내용을 바탕으로 스터디를 진행하였습니다.

이번엔 MySQL의 동시성에 영향을 미치는 잠금(Lock)과 트랜잭션, 트랜잭션의 격리 수준에 대해 알아보겠습니다.

---

1. 트랜잭션

트랜잭션(Transaction)은 데이터베이스 시스템에서 하나의 작업 단위(연산 묶음)를 의미하며,

이 작업이 전부 성공하거나, 전부 실패해도 아무 일도 없었던 것처럼 되도록 보장하는 메커니즘입니다.

트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미 있는 개념은 아닙니다.

하나의 논리적인 작업 셋에 하나의 쿼리가 있던 두 개 이상의 쿼리가 있던 관계 없이 논리적인 작업 셋 자체가 100% 적용되거나 아무것도 적용되지 않아야함을 보장해주는 것 입니다.

1-1. MySQL에서의 트랜잭션

간단 예제로 트랜잭션 관점에서 InnoDB 테이블과 MyISAM 테이블의 차이를 알아보겠습니다.

mysql> create table tab_myisam ( fdpk int not null, PRIMARY key (fdpk) ) engine=MyISAM;
mysql> insert into tab_myisam (fdpk) values (3);

mysql> create table tab_innodb ( fdpk int not null, PRIMARY key (fdpk) ) engine=INNODB;
mysql> insert into tab_innodb (fdpk) values (3);

위와 같이 테스트용 테이블에 각각 레코드를 1건씩 저장한 후 AUTO-COMMIT 모드에서 다음 쿼리 문장을 InnoDB 테이블과 MyISAM 테이블에서 각각 실행해보겠습니다.

mysql> insert into tab_myisam (fdpk) VALUES (1),(2),(3);
SQL Error [1062] [23000]: Duplicate entry '3' for key 'tab_myisam.PRIMARY'

mysql> insert into tab_innodb (fdpk) VALUES (1),(2),(3);
SQL Error [1062] [23000]: Duplicate entry '3' for key 'tab_innodb.PRIMARY'

mysql> select * from tab_myisam;
fdpk|
----+
   1|
   2|
   3|

mysql> select * from tab_innodb;
fdpk|
----+
   3|

이 결과로 확인 할 수 있는 것은, MyISAM과 다르게InnoDB는 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 동작을 합니다.

1-2. 주의사항

트랜잭션 또한 DBMS의 커넥션과 동일하게, 꼭 필요한 최소의 코드에만 적용하는 것이 좋습니다.

이 말은 즉, 프로그램 코드에서 트랜잭션의 범위를 최소화 하라는 의미 입니다.

예시를 들어보자면,

1. 처리 시작
=> 데이터베이스 커넥션 생성
=> 트랜잭션 시작
2. 사용자의 로그인 여부 확인
3. 사용자의 글쓰기 내용의 오류 여부 확인
4. 첨부로 업로드된 파일 확인 및 저장
5. 사용자의 입력 내용을 DBMS에 저장
6. 첨부 파일 정보를 DBMS에 저장
7. 저장된 내용 또는 기타 정보를 DBMS에서 조회
8. 게시물 등록에 대한 알림 메일 발송
9. 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료(COMMIT)
<= 데이터베이스 커넥션 반납
10. 처리 완료

위 과정에서 DBMS의 트랜잭션 처리에 안좋은 영향을 미치는 부분이 있습니다.

✅ 8. 게시물 등록에 대한 알림 메일 발송

• 문제점: 메일 발송은 외부 SMTP 서버와의 통신이 필요한 I/O 작업입니다. 실패할 가능성이 높고, 재시도 로직이 필요합니다.
• 영향: 이 작업이 트랜잭션 내부에 있으면, 메일 서버의 일시적 장애나 느린 응답으로 인해 전체 DB 트랜잭션이 지연되거나 롤백될 수 있습니다.

✅ 4. 첨부로 업로드된 파일 확인 및 저장

• 문제점: 보통 파일은 DB가 아닌 파일시스템이나 오브젝트 스토리지(S3 등)에 저장됩니다. 이 역시 외부 시스템 의존 작업입니다.
• 영향: 파일 업로드 중 문제 발생 시 DB 트랜잭션이 불필요하게 길어지거나 실패하게 만들 수 있습니다.

따라서 트랜잭션 범위를 아래처럼 좁히는 것이 이상적 입니다.

1. 처리 시작
2. 사용자의 로그인 여부 확인
3. 사용자의 글쓰기 내용의 오류 여부 확인
4. 첨부로 업로드된 파일 확인 및 저장
=> 데이터베이스 커넥션 생성 (또는 커넥션 풀에서 가져오기)
=> 트랜잭션 시작
5. 사용자의 입력 내용을 DBMS에 저장
6. 첨부 파일 정보를 DBMS에 저장
<= 트랜잭션 종료(COMMIT)
7. 저장된 내용 또는 기타 정보를 DBMS에서 조회
8. 게시물 등록에 대한 알림 메일 발송
=> 트랜잭션 시작
9. 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료(COMMIT)
<= 데이터베이스 커넥션 반납
10. 처리 완료

---

2. MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 두 가지가 있습니다.

• 스토리지 엔진 레벨
• MySQL 엔진 레벨

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

이제 어떨 때 잠기고, 어떤 잠금 기능이 있는 지 알아보겠습니다.

2-1. 글로벌 락

글로벌 락(GLOBAL LOCK)은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 큽니다.

글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라고 모두 영향을 받습니다.

따라서, 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때, 글로벌 락을 사용합니다.

✅ 글로벌 락의 주요 목적

• 데이터 정합성 보장: 전체 데이터베이스가 특정 시점에 일관된 상태를 유지
• 백업 중 쓰기 차단: 백업 도중 데이터 변경을 막아 복원 시 오류 방지
• 레플리카 생성 시 데이터 동기화 보장

하지만 MySQL 서버가 업그레이드 되면서 InnoDB 스토리지 엔진 사용이 일반화되면서 좀 더 가벼운 글로벌 락의 필요성이 생겼습니다.

그래서 MySQL 8.0부터 Xtrabackup 이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 생긴 게 백업 락 입니다.

특정 세션에서 백업 락을 휙득하면 모든 세션에서 아래 같은 정보를 변경할 수 없습니다.

• 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
• REPAIR TABLE과 OPTIMIZE TABLE 명령
• 사용자 관리 및 비밀번호 변경용자 관리 및 비밀번호 변경

# 글로벌 락 실행하는 명령어
mysql> FLUSH TABLES WITH READ LOCK

# 백업 락 실행하는 명령어
mysql> LOCK INSTANCE FOR BACKUP;

# 백업 락 해제하는 명령어
mysql> UNLOCK INSTANCE;

그러나 백업락은 일반적인 테이블의 데이터 변경은 허용됩니다.

✅ 왜 일반 테이블 데이터 변경이 허용될까?

🔹 이유 1: 백업 락은 DML(INSERT, UPDATE, DELETE)을 차단하지 않음

• 백업 락은 데이터 사본을 만드는 동안 메타데이터가 변경되지 않도록 하기 위한 락입니다.
• 즉, 데이터 사본을 일관되게 만들 수 있는 시점을 확보할 뿐이지, 실제 데이터에 대한 읽기/쓰기를 차단하지 않습니다.
  
  🔹 이유 2: 비차단 백업을 위한 설계
• 운영 중인 서비스에서 데이터 변경을 차단하면 서비스 중단이 발생하기 때문에,
• 백업 락은 오직 DDL(테이블 생성/삭제, ALTER 등)만 차단하고, DML(데이터 변경)은 허용합니다.

2-2. 테이블 락

테이블 락(Table Lock)은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 걸 수 있습니다.

# 테이블 락 거는 명령어
mysql> LOCK TABLES <table_name> [ READ | WRITE ];

# 테이블 락 해제하는 명령어
mysql> UNLOCK TABLES;

실제로 테이블 락을 많이 사용 하진 않습니다.

2-3. 네임드 락

네임드 락(Named Lock)은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있습니다.

이 잠금의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것 입니다.

즉, 정리를 하자면

• 테이블의 데이터를 잠그지 않습니다.
• 레코드(Row)나 컬럼도 전혀 관여하지 않습니다.
• AUTO_INCREMENT 같이 DB 내부 구조에 관련된 것도 잠그지 않습니다.
• 단순히 사용자가 지정한 문자열(String)에 대해 휙득하고 반납(해제)하는 잠금 입니다.
  
  이러한 기능이다 보니 그렇게 자주 사용되지는 않습니다. 주로 아래와 같은 상황에서 사용 됩니다.
• 애플리케이션 레벨에서 병렬 실행 제어
• 중복 실행 방지
  
  간단한 예시를 통해 네임드 락 사용법을 알아보겠습니다.

  # 'mylock' 이라는 문자열에 대해 잠금을 휙득
  # 혹여 이미 잠금을 사용 중이라면 2초 동안만 대기 (2초 이후 자동 잠금 해제)
  mysql> select get_lock('mylock',2);
  `
  # 'mylock' 이라는 문자열에 대해 잠금이 설정돼 있는지 확인
  mysql> select is_free_lock('mylock');
  `
  # 'mylock' 이라는 문자열에 대해 획득했던 잠금을 반납(해제)한다.
  mysql> select release_lock('mylock');
  `
  # `mylock_1`, `mylock_2` 동시에 모두 해제하고자 할 때 사용
  mysql> select release_all_locks();
  `
  # 3개 함수 모두 정상적으로 락을 획득하거나 해제를 한 경우에는 1을,
  # 아니면 NULL이나 0을 반환한다.

2-4. 메타데이터 락

메타데이터 락(Metadata Lock)은 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 휙득하는 잠금 입니다.

MySQL은 쿼리를 실행할 때, 관련된 테이블의 메타데이터 정보를 참조합니다.

이때 동시에 다른 세션이 이 테이블의 구조를 변경하지 못하도록 락을 걸어줍니다.

앞서 소개한 락(LOCK)들과 다르게 메타데이터 락은 관리자가 명시적으로 걸어줄 수 없습니다.

RENAME TABLE table_a TO table_b와 같이 테이블의 이름을 변경하는 경우 자동으로 잠금이 걸립니다.

간단한 예제를 통해 살펴보겠습니다.

# rank 테이블의 이름을 rank_backup으로 변경할 때 메타데이터 락이 걸리고,
# 이 때 DDL 실행이 안됩니다.
mysql> RENAME TABLE rank TO rank_backup;

---

3. InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있습니다.

MySQL과 별개의 잠금 방식 처리 덕분에 동시성 처리가 가능해졌지만, 이 때문에 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기 까다롭습니다.

하지만 최근 버전에서는 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션 목록을 조회 할 수 있는 방법이 생겼습니다.

MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인 해서 조회하면 확인 할 수 있습니다.

3-1. InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리됩니다.

이에 따라 레코드 락이 페이지 락이나 테이블 락으로 레벨업되는 경우는 없습니다.

여기서 레코드 란?

• 하나의 행(row), 즉 테이블의 한 줄에 해당하는 데이터 단위

# 아래의 쿼리를 실행하면 결과가 나오는 데,
# 3개의 행 = 3개의 레코드 라고 생각하면 됩니다.
# 즉, id=1, name=Alice, age=30 -> 하나의 레코드
mysql> select * from test_table;
`
id|name  |age|
-----------+
1|Alice  |30 |
2|Bob    |25 |
3|Charile|27 |

일반 상용 DBMS와는 조금 다르게 InnoDB 스토리지 엔진에서는 레코드 락뿐 아니라 레코드와 레코드 사의 간격을 잠그는 갭(GAP) 락이라는 것이 존재합니다.

3-2. 레코드 락

레코드 자체만을 잠그는 것을 레코드 락(Record lock)이라고 하며, 다른 사용 DBMS의 레코드 락과 동일한 역할을 합니다.

한 가지 차이점이 있는데, InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점 입니다.

인덱스가 하나도 없는 테이블이라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정합니다.

3-3. 갭 락

갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미합니다.

갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어합니다.

3-4. 넥스트 키 락

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

• 레코드 락 (Record Lock): 특정 행(Row)에 걸리는 락
• 갭 락 (Gap Lock): 특정 값 사이의 “틈”에 걸리는 락 (예: 5 < x < 10)
• 넥스트 키 락 = 갭 + 해당 레코드까지 포함

즉, 현재 행 + 그 앞의 갭 까지 동시에 잠그는 방식 입니다.

3-5. 자동 증가 락

MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT 라는 칼럼 속성을 제공합니다.

AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우,

저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 합니다.

InnoDB 스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT 락 이라고 하는 테이블 수준의 잠금을 사용합니다.

AUTO_INCREMENT락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제 됩니다.

3-6. 인덱스와 잠금

InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계가 있습니다.

간단한 예시를 통해 알아보겠습니다.

# 현재 더미데이터 employees 테이블에 first_name='Georgi'인 사원은 17명이 있습니다.
mysql> select COUNT(*) from employees where first_name='Georgi';
---
COUNT(*)|
--------+
      17|

# 그리고 first_name='Georgi' and last_name = 'Cooke' 인 사람은 1명 뿐 입니다.
mysql> select COUNT(*) from employees where first_name='Georgi' and last_name = 'Cooke';
---
COUNT(*)|
--------+
       1|

# 이 상태에서 first_name='Georgi' and last_name = 'Cooke' 인 사람의 입사일자를 오늘로 변경하겠습니다.
mysql> update employees set hire_date=NOW() where first_name='Georgi' and last_name = 'Cooke';

이렇게 예시에 나와있는 SQL 문을 따라 해보면, 마지막 UPDATE 문장이 실행되면 1건의 레코드가 업데이트 됩니다.

그런데, InnoDB 스토리지 엔진은 인덱스를 찾아서 레코드를 잠근다고 하였습니다.

이 때, first_name이 인덱스로 잡혀 있다면, 마지막 UPDATE 문장을 실행할 때, 인덱스 값이first_name='Georgi' 인 17개의 레코드가 같이 잠기게 됩니다.

만약 테이블에 인덱스가 하나도 없다면 UPDATE 문 사용하게 되면 모든 레코드를 잠그게 됩니다.

이 예제를 통해서 강조하고 싶은 것이 있다면 MySQL의 InnoDB에서 인덱스 설계가 중요하다는 말을 하고 싶습니다.

---

4. MySQL의 격리 수준

MySQL의 격리 수준(Isolation Level)은 트랜잭션 간에 어떤 방식으로 데이터를 읽고 쓸 수 있는지를 정의하며, 동시성 제어와 관련된 중요한 개념입니다.

이는 ACID 원칙 중 “Isolation”에 해당하며, 데이터의 일관성과 무결성을 유지하기 위해 필수적입니다.

✅ MySQL에서 지원하는 크게 4가지 격리 수준으로 나뉩니다.

1. READ UNCOMMITTED (읽기 미확정)
2. READ COMMITTED (읽기 확정)
3. REPEATABLE READ (반복 가능 읽기)
4. SERIALIZABLE (직렬화 가능)

4-1. READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 각 트랜잭션에서 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보입니다.

즉, 커밋되지 않은 트랜잭션의 변경 사항도 읽을 수 있도록 허용합니다.



위 그림을 예시로 들어보자면,

사용자 A는 emp_no가 500000이고 first_name이 "Lara"인 새로운 사원을 INSERT 합니다.

사용자 B는 emp_no=500000인 사원을 검색하고 있습니다.

하지만 사용자 B는 사용자 A가 INSERT한 사원의 정보를 커밋되지 않은 상태에서도 조회할 수 있습니다.

문제는 사용자 A가 처리 도중 알수 없는 문제로 ROLLBACK을 한다고 하더라도 여전히 사용자 B는 "Lara"가 정상적인 사원이라고 생각하고 처리하게 됩니다.

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 형상을 더티 리드(Dirty read)라 하고, 더티 리드가 허용되는 격리 수준이 READ UNCOMMITED 입니다.

따라서 MySQL을 사용하다면 최소한 READ COMMITTED 이상의 격리 수준을 사용하는 것을 권장 합니다.

4-2. READ COMMITED

READ COMMITED는 오라클 DBMS에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준 입니다.

READ COMMITTED 상태에서는 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회를 할 수 있습니다.



위 그림을 보면, 사용자 A가 Lara에서 Toto로 변경을 하고 COMMIT 한 상태가 아닐 때, 사용자 B가 조회를 하면, 언두 로그에 백업된 'Lara' 값을 반환 해줍니다.

그리고 COMMIT이 완료되고 난 후에 조회를 하면 Toto 로 값이 변경된 것을 확인 할 수 있습니다.

그러나, READ COMMITED 상태에서도 Non-Repeatable Read라는 예외 상태가 있어

동일한 SELECT 쿼리를 같은 트랜잭션 내에서 2번 했을 때, 결과가 다르게 나옴

예시를 들어보자면,

-- 트랜잭션 A
START TRANSACTION;
SELECT balance FROM account WHERE user_id = 1;  -- 결과: 1000

-- 트랜잭션 B (다른 세션)
UPDATE account SET balance = 500 WHERE user_id = 1;
COMMIT;

-- 트랜잭션 A
SELECT balance FROM account WHERE user_id = 1;  -- 결과: 500 (변경됨)

💡 왜 이런 문제가 생기는가?

• READ COMMITTED는 각 쿼리 시점마다 COMMIT된 최신 데이터를 읽음
• 그래서 동일 트랜잭션 내라도 이전과 다른 결과를 볼 수 있어
• 따라서 데이터 일관성이 중요한 로직에서는 REPEATABLE READ 이상을 사용해야 합니다.

4-3. REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준 입니다.

이 격리 수준에서는 앞서 소개했던 NON-REPEATABLE READ 같은 부정합이 발생하지 않습니다.

InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경합니다. 이러한 변경 방식을 MVCC라고 합니다.

REPEATABLE READ은 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장 합니다.

간략하게 예시를 들어보자면,

-- 트랜잭션 A
START TRANSACTION;
SELECT salary FROM employees WHERE emp_no = 100; -- 결과: 5000

-- 트랜잭션 B (다른 세션)
UPDATE employees SET salary = 8000 WHERE emp_no = 100;
COMMIT;

-- 트랜잭션 A
SELECT salary FROM employees WHERE emp_no = 100; -- 결과: 여전히 5000 (스냅샷 기준)

이처럼 트랜잭션 내에서는 외부에서 COMMIT된 변경 사항을 볼 수 없음

4-4. SERIALIZABLE

이 격리 단계는 가장 단순하면서도 가장 엄격한 격리 수준 입니다.

그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어집니다.

해당 격리 수준에서는 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 접근 조차도 하지 못합니다.

하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ와 같은 데이터 일관성에 문제가 되는 상황이 발생되지 않아, 실제로 SERIALIZABLE 까지의 격리 수준은 잘 사용되지 않습니다.

---

이번 스터디는 여기 까지 입니다.

감사합니다.