트랜잭션과 MySQL 잠금(락)

junto·5일 전

Database

database

목록 보기

10/11

트랜잭션

트랜잭션이란 작업의 완전성을 보장해 주는 것이다. 하나의 논리적인 작업을 모두 완전하게 처리하거나 처리하지 못할 때 원 상태로 복구하여 일부만 적용되는 현상을 만들어주지 않게 한다.
아래는 트랜잭션과 관련해서 헷갈릴 수 있는 개념이다.

잠금(Lock)은 동시성을 제어하기 위한 기능으로 여러 트랜잭션이 같은 데이터에 접근할 때 데이터 충돌이나 불일치를 방지한다.
트랜잭션은 데이터 정합성을 보장하기 위한 기능이다. 데이터 정합성(Constistency)이란 데이터가 일관성 있게 유지되는 것을 말한다. 이 외에도 원자성(Atomicity), 고립성(Isolation), Durability(지속성) 특징을 가진다.
데이터 무결성은 데이터가 정확한 상태를 유지하는 것을 말한다.

세 가지 부정합 문제

1. DIRTY READ

트랜잭션이 아직 커밋되지 않은 다른 트랜잭션의 변경사항을 읽는 경우를 말한다.

2. NON-REPEATABLE READ

같은 트랜잭션 내에서 동일한 쿼리를 여러 번 실행했을 때, 그 결과가 다른 경우를 말한다.

3. PHANTOM READ

트랜잭션이 동일한 범위 쿼리를 두 번 이상 실행할 때, 다른 트랜잭션이 데이터를 삽입하거나 삭제함으로써 결과 집합이 달라지는 현상이다.

트랜잭션 격리 수준

1. READ-UNCOMMITTED

각 트랜잭션 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.

사용자 A는 emp_no가 50000이고 first_name이 Lara인 사원을 INSERT 한다.

사용자 B가 변경된 내용을 커밋하기도 전에 사용자 B는 emp_no = 50000인 사원을 검색한다.

사용자 A가 처리 도중 알 수 없는 문제가 발생해 롤백한다고 해도 여전히 사용자 B는 "Lara"가 정상적인 사원이라고 생각하고 계속 처리할 것이 문제다.

2. READ-COMMITTED

COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.
오라클 DBMS에서 기본적으로 사용되는 격리 수준이다.

사용자 A는 emp_no=50000 사원의 first_name을 "Lara"에서 "Toto"로 변경했다. 이때, 새로운 값인 "Toto"는 테이블에 즉시 기록되고 이전값인 "Lara"는 언두 영역으로 백업된다.

사용자 A가 커밋하기 전에 B가 emp_no=50000을 조회하면 first_name 칼럼의 값은 "Toto"가 아니라 "Lara"로 조회된다. (언두 영역에 있는 데이터 조회)

NON-REPEATABLE READ 부정합 문제

사용자 B가 BEGIN 명령으로 트랜잭션을 시작하고 first_name이 "Toto"인 사원을 검색했는데 결과가 없었다. 하지만 사용자 A가 "Toto"로 변경 후 사용자 B가 똑같은 쿼리로 다시 조회하면 결과가 1건 조회된다.

3. REPEATABLE-READ

MySQL에선 MVCC를 이용해 언두 영역에 있는 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서 동일한 결과를 보여주는 것을 보장한다.
MySQL InnoDB 스토리지 엔진에서 기본으로 사용하는 격리 수준이다.

사용자 A의 트랜잭션 번호는 12, 사용자 B의 트랜잭션 번호는 10이다.

사용자 A는 사원의 이름을 "Toto"로 변경하고 커밋한다.

사용자 B는 A 트랜잭션의 변경 전후 각각 한 번씩 SELECT 했지만, 결과는 항상 "Lara"라는 값을 가져온다. B는 자신의 트랜잭션 번호(10)보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.

PHANTOM READ 부정합 문제

A가 employees 테이블에 INSERT를 실행하는 도중에 사용자 B가 SELECT FOR UPDATE 쿼리로 테이블을 조회하는 상황

B는 BEGIN 명령으로 트랜잭션을 시작한 후 SELECT를 수행한다. 위에서처럼 두 번의 SELECT 쿼리 결과는 똑같아야 한다.

하지만 첫 번째 SELECT FOR UPDATE에선 A가 커밋하기 전이므로 "Lara" 결과 한 건만 검색되고, 두 번째 SELECT FOR UPDATE에선 A가 커밋한 이후이므로 새롭게 추가된 "Georgi" 결과까지 추가로 검색된다.

쓰기 잠금을 거는 것인데, 언두 레코드에는 쓰기 잠금을 걸 수 없다. SELECT FOR UPDATE, SHARE 로 조회되는 레코드는 언두 영역의 레코드를 가져오는 것이 아니라 현재 레코드의 값을 가져오기 때문이다.

위의 예시는 PHANTOM READ의 예외적인 상황이라 설명하고, MySQL REPEATABLE-READ 격리 수준에선 팬텀 리드가 발생하지 않는다고 한다. 그럼 일반적인 팬텀 리드 상황을 생각해 보고, MYSQL에선 어떻게 방지되는지 찾아보자.

일반적인 PHANTOM READ 부정합 문제

사용자 A는 id > 50000 유저 조회, 결과 (50001, 50002)
사용자 B는 INSERT 완료
사용자 A는 id > 50000 유저 다시 조회, 결과 (50001, 50002, 50003)
초기 SELECT 쿼리와 다른 결과, PHANTOM READ 발생

하지만, MySQL에서는 갭 락과 넥스트 키 락 덕분에 PHANTOM READ 발생하지 않음

즉, id > 50000 조회할 때 읽기 락(갭락, 넥스트 키 락)이 걸리며 INSERT가 되어도 추가되지 않고, 트랜잭션 내에서 일관된 읽기를 할 수 있는 것이다.

4. SERIALIZABLE

한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 격리 수준이다.

MySQL 잠금(락)

MySQL 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간에는 영향을 미치지 않는다.

1. MySQL 엔진 잠금(락)

1) 글로벌 락

한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML을 실행할 때 글로벌 락이 해제될 때까지 해당 쿼리는 대기 상태가 된다.
FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 넓다.

2) 백업 락

백업 락은 MySQL에서 백업 수행 중 데이터의 일관성을 유지하고, 복제 및 백업 실패를 방지하기 위해 도입된 기능이다.
InnoDB 스토리지 엔진은 트랜잭션을 지원하므로 데이터 변경 작업을 멈출 필요 없다. 백업 락을 획득한 경우 테이블의 스키마나 사용자 인증 관련 정보를 변경할 수 없다. 즉, 일반적인 데이터 변경은 가능하지만, 스키마 변경은 불가능한 락이라고 생각하면 된다. (글로벌 락 보다 가벼운)

3) 테이블 락

개별 테이블 단위로 설정되는 락이다.
명시적 또는 묵시적으로 특정 테이블 락을 획득할 수 있다. 명시적으로는 LOCK TABLES table_name [ READ | WRITE] 해당 명령어를 통해 락을 획득하고, UNLOCK TABLES로 락을 반납한다.
InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 락을 제공하기에 단순 데이터 변경은 묵시적인 테이블 락이 설정되지 않는다. DDL의 경우에만 테이블 락이 설정된다.

4) 네임드 락

GET_LOCK() 함수를 이용해 임의의 문자열에 대해 락을 설정할 수 있다.
잠금 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니고, 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금이다.
많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다. 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 하는데, 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하는 게 아주 간단히 해결하는 방법이다.

5) 메타데이터 락

데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 락이다.
명시적으로 획득하거나 해제할 수 없다. RENAME TABLE tab_a TO tab_b 같이 테이블의 이름을 변경하는 경우 자동으로 획득한다.

2. InnoDB 스토리지 엔진 잠금

과거에는 MySQL 명령을 이용해 InnoDB 스토리지 엔진의 락 정보를 얻기 어려웠으나, 최근에는 MYSQL 서버의 infomation_schema에 있는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인하여 트랜잭션 정보를 확인할 수 있다.

1) 레코드 락

레코드 자체만을 잠그는 것을 레코드 락이라고 한다. InnoDB에서는 인덱스의 레코드를 잠근다. 인덱스가 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
비관적 쓰기 락(Select For Update), 비관적 읽기 락(Select For Share) 모두 포함하는 개념이다. 아래와 같이 특정 레코드만을 잠그는 쿼리를 생각해볼 수 있다.

SELECT * FROM time_slot WHERE id = 5 FOR UPDATE;

비관적 락을 사용하지 않더라도 REPEATABLE_READ 격리 수준에서 일관된 읽기를 위해 레코드에 읽기 락이 걸린다.

2) 갭 락

갭 락은 레코드 자체가 아니라 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.
레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하며, 갭 락 그 자체보다 넥스트 키 락의 일부로 자주 사용된다.
아래와 같이 1, 5 사이에 있는 레코드(2, 3, 4)를 잠그는 경우를 생각해 볼 수 있다.

SELECT * FROM time_slot WHERE id > 1 AND Id < 5 FOR UPDATE;

비관적 락을 사용하지 않더라도 REPEATABLE_READ 격리 수준에서 일관된 읽기를 위해 특정 조건에 속한 레코드가 락이 걸린다.

3) 넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다.
아래와 같이 1 ~ 5 레코드 (1, 2, 3, 4, 5)를 잠그는 경우를 생각해 볼 수 있다.

SELECT * FROM time_slot WHeRE id >= 1 AND id <= 5 FOR UPDATE;

비관적 락을 사용하지 않더라도 REPEATABLE_READ 격리 수준에서 일관된 읽기를 위해 특정 조건에 속한 레코드가 락이 걸린다.

4) 자동 증가 락

자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 컬럼 속성값을 제공한다. 한 번에 여러 레코드가 INSERT 되는 경우 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 하기에 AUTO_INCREMENT 락이 필요하다.
AUTO_INCREMENT 락을 명시적으로 획득하고 해제하는 방법은 없다. 아주 짧은 시간 동안 걸렸다가 해제되는 잠금이다.

인덱스와 락 동작 방식

InnoDB 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리한다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

mysql > SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
+----------+
| COUNT(*) |
+----------+
|      253 |
+----------+
 
mysql > SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_anme='Klassen';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

mysql > UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';

위와 같이 Update 쿼리를 실행하면 1건의 레코드가 변경될 것이다. 그럼 몇 개의 레코드에 락을 걸어야 할까?

조건은 first_name='Georgi'이며, last_name 컬럼은 인덱스에 없기에 first_name='Georgi'인 레코드 253건의 레코드가 모두 잠긴다.

만약 테이블에 인덱스가 하나도 없다면? 테이블을 풀 스캔하면서 UPDATE 작업을 하게 된다. 즉 전체 30여만 건의 모든 레코드를 잠그게 된다.

잠금 대기 순서 파악하기

performance_schema의 data_locks 테이블과 data_lock_waits 테이블을 조인해서 잠금 대기 순서를 살펴볼 수 있다.

select 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM
    performance_schema.data_lock_waits w
INNER JOIN 
    information_schema.innodb_trx b
ON
    b.trx_id = w.blocking_engin_transaction_id
INNER JOIN
    information_schema.innodb_trx r
ON
    r.trx_id = w.requesting_engine_transaction_id;

(Update 쿼리 3개를 실행 후 프로세스 목록을 조회한 상황)

현재 대기 중인 스레드는 18, 19번이다.

18번 스레드는 17번 스레드를 기다리고 있다.

19번 스레드는 17번 스레드와 18번 스레드를 기다리고 있다.

17번 스레드가 어떤 잠금을 가졌는지 더 상세히 확인하고 싶다면 performance_schma의 data_locks 테이블이 가진 칼럼을 모두 살펴본다.

SELECT * FROM performance_schema.data_locks\G

KILL 17 로 강제 종료하면, 나머지 UPDATE 들이 진행되면서 락이 풀린다.

참고 자료

RealMySQL 8.0

junto

꾸준하게

이전 포스트

MySQL 아키텍처 (MySQL 엔진, InnoDB 엔진)

다음 포스트

트랜잭션과 MySQL 잠금(락)

database

트랜잭션

세 가지 부정합 문제

1. DIRTY READ

2. NON-REPEATABLE READ

3. PHANTOM READ

트랜잭션 격리 수준

1. READ-UNCOMMITTED

2. READ-COMMITTED

NON-REPEATABLE READ 부정합 문제

3. REPEATABLE-READ

PHANTOM READ 부정합 문제

A가 employees 테이블에 INSERT를 실행하는 도중에 사용자 B가 SELECT FOR UPDATE 쿼리로 테이블을 조회하는 상황

일반적인 PHANTOM READ 부정합 문제

하지만, MySQL에서는 갭 락과 넥스트 키 락 덕분에 PHANTOM READ 발생하지 않음

4. SERIALIZABLE

MySQL 잠금(락)

1. MySQL 엔진 잠금(락)

1) 글로벌 락

2) 백업 락

3) 테이블 락

4) 네임드 락

5) 메타데이터 락

2. InnoDB 스토리지 엔진 잠금

1) 레코드 락

2) 갭 락

3) 넥스트 키 락

4) 자동 증가 락

인덱스와 락 동작 방식

잠금 대기 순서 파악하기

(Update 쿼리 3개를 실행 후 프로세스 목록을 조회한 상황)

참고 자료

MySQL 아키텍처 (MySQL 엔진, InnoDB 엔진)

예약 서비스 병행 제어 (낙관적 락, 비관적 락)

0개의 댓글