[DB] 트랜잭션과 잠금

Real MySQL 8.0을 참고하여 본인의 생각과 고민을 기록한 글로 기술한 내용이 공식 문서 내용과 상이할 수 있음을 밝힙니다.
Mysql 공식문서 참고: https://dev.mysql.com/doc/refman/8.0/en/

프로젝트를 진행하면서 트랜잭션을 그저 갖다쓰기만 했지, DB에서 어떻게 동작하는지, 격리수준은 어떻게 되는지에 대한 깊은 고민을 한 적이 없다. 따라서 MySQL InnoDB 기준으로 기록한 글이다.

트랜잭션(Transaction)

트랜잭션에 대해서 먼저 생각해보아야할 것 같다. 트랜잭션은 작업의 완전성을 보장해준다. 작업을 완벽하게 처리하거나, 그러지 못했을 경우 원 상태로 복구해서 데이터의 부정합을 방지한다. 트랜잭션은 데이터베이스의 ACID 원칙을 따르는 특징을 가지고 있다.

1. 원자성(Atomicity): 트랜잭션은 작업의 모든 단계가 완전히 실행되거나 아무것도 실행되지 않은 상태를 보장한다.
2. 일관성(Consistency): 트랜잭션이 실행 전후에 데이터베이스는 일관된 상태를 유지해야 한다.
3. 고립성(Isolation): 동시에 여러 개의 트랜잭션이 실행될 때, 각 트랜잭션은 서로에게 영향을 주지 않고 독립적으로 실행되는 것을 보장해야 한다.
4. 지속성(Durability): 트랜잭션이 성공적으로 완료되면, 그 결과는 영구적으로 저장되어야 한다.

InnoDB 스토리지 엔진 잠금

레코드 락 (Record Lock)

레코드 락은 DB에서 특정 레코드(데이터 행)를 잠그는 것을 의미한다.
InnoDB 엔진에서 레코드 락은 실제 레코드 자체가 아닌 인덱스의 레코드를 잠그는 방식으로 동작한다. 다만, PK 또는 유니크 인덱스에 의한 변경 작업에서는 레코드 자체에 락을 건다.

갭 락 (Gap Lock)

갭 락은 레코드 자체가 아니라 레코드와 가장 인접한 레코드 사이의 간격만을 잠그는 것이다. 이는 레코드와 레코드 사이의 간격에 새로운 레코드가 INSERT되는 것을 제어한다. 레코드 간의 공간에 대한 락으로, Phantom Read 현상을 방지하기 위해 사용된다.

넥스트 키 락 (Next Key Lock)

특정 레코드에 대한 공유 락이나 배타적 락을 포함하여, 그 레코드와 인접한 다음 레코드 사이의 갭에도 락을 거는 것을 의미한다.
갭 락은 넥스트 키 락의 일부로 자주 사용되는데, InnoDB의 갭 락이나 넥스트 키 락은 Binary Log에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 소스 서버에서 생성된 넥스트 키 락과 일치하는 넥스트 키 락을 생성한다.

이전 글에서 Master-Slave Replication을 할 때 복제가 되는 과정에 대해 설명했는데, 해당 과정에서 적용되는 동작 원리다.

STATEMENT 포맷을 사용하면 SQL 명령과 그 실행 결과를 Binlog에 기록한다. 그러나 슬레이브 서버에서 동일한 SQL 명령을 실행하면, 데이터의 현재 상태에 따라 다른 결과를 얻을 수 있다. 이런 상황을 방지하기 위해 넥스트 키 락이나 갭 락을 사용할 수 있다.
반면, ROW 포맷을 사용하면 변경된 각 행의 내용을 Binlog에 직접 기록한다. Slave 서버는 이 로그를 읽어서 동일한 행 변경 작업을 수행하므로, Master 서버에서의 원래 변경 작업과 동일한 결과를 얻을 수 있다.

ROW 포맷 형태로 바꾸어 넥스트 키락을 줄이는게 좋다고 하나, MySQL 8.0 버전부터는 ROW 포맷의 binlog가 기본 설정이다.

인덱스와 잠금

InnoDB의 레코드 락은 레코드 자체를 잠그는게 아닌, 인덱스를 잠그는 방식이라고 말했었다. 따라서 변경이 필요한 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

KEY friend_firstname(first_name) // 인덱스
mysql> SELECT count(*) FROM friends;
+----------+
|  1000000 |
+----------+
mysql> SELECT count(*) FROM friends first_name='kim';
+----------+
|	   300 |
+----------+
mysql> SELECT count(*) FROM friends first_name='kim' AND last_name='minji';
+----------+
|	     1 |
+----------+
mysql> UPDATE friends SET move_date=NOW() WHERE first_name='kim' AND last_name='minji';

UPDATE 쿼리가 실행 되면 1건이 업데이트가 된다. 하지만 1건의 업데이트를 위해서 인덱스가 first_name 하나이므로 first_name='kim'인 레코드 300건이 모두 락이 걸린다. 적절한 인덱스 설계로 동시성을 높이도록 해야한다. 만약 인덱스가 하나도 없다면, 이 경우에는 TABLE FULL SCAN을 하면서 UPDATE 과정을 진행하는데 테이블 안의 데이터 100만건이 모두 잠길 수 있다.

ALTER TABLE friends ADD INDEX idx_firstname_lastname (first_name, last_name);

위와 같이 복합 인덱스를 만들면, first_name='kim' AND 'last_name='minji' 조건을 만족하는 레코드만 락이 걸리게 되므로, 불필요한 레코드에 락을 거는 것을 피할 수 있다. 이렇게 하면 동시성이 향상되고, 락 경합이 줄어들어 시스템의 전반적인 성능이 향상될 수 있다.

MySQL의 격리수준

트랜잭션의 격리수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다. 격리 수준이 높아질수록 동시성은 감소하고, 격리 수준이 낮아질수록 동시성은 증가한다.

트랜잭션 사이에 발생하는 격리수준이므로 AUTOCOMMIT=OFF이다.

	DIRTY READ	NON-REPEATABLE READ	PANTHOM READ
READ UNCOMMITED	발생	발생	발생
READ COMMITED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(InnoDB는 X)
SERIALIZABLE	없음	없음	없음

READ UNCOMMITED

가장 낮은 격리 수준이다. 트랜잭션 A에서 아직 커밋되지 않은 변경을 트랜잭션 B에서 볼 수 있다.

트랜잭션 A가 INSERT된 내용을 롤백할 경우에도 트랜잭션B는 정상적으로 처리됐다고 생각하여 데이터 부정합 문제가 발생할 수 있다.
이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지도 않았는데 다른 트랜잭션에서 볼 수 있는 현상을 DIRTY READ라고 한다.

READ COMMITED

온라인 서비스에서 가장 많이 사용되는 격리 수준이다. READ UNCOMMITED랑 다르게 트랜잭션 A가 데이터를 변경했더라도 COMMIT이 완료된 데이터만 트랜잭션 B에서 조회할 수 있다.

사용자 A는 menuId=100인 메뉴의 food_name을 Beverage에서 Apple로 변경했는데, 변경된 값인 Apple은 menu 테이블에 즉시 기록되고 이전 값인 Beverage는 언두 영역으로 백업된다.
사용자 A가 COMMIT을 수행하기 전에 사용자 B가 menuId=100인 메뉴를 SELECT하면 조회결과가 food_name 칼럼의 값은 Apple이 아니라 Beverage로 조회된다. 사용자 B의 조회 결과는 menu 테이블이 아니라 언두 영역에 백업된 레코드에서 가져온 것이다.
사용자 A가 변경된 내용을 COMMIT을 하면 그제서야 다른 사용자들도 언두 영역의 Beverage가 아니라 새로 변경된 Apple 값을 조회할 수 있다.

NON-REPEATABLE READ

사용자 B가 BEGIN 명령으로 트랜잭션을 시작하고 food_name이 Apple인 메뉴를 검색했는데 일치하는 결과가 없었다. 사용자 A가 menuId=100인 메뉴의 이름을 Apple로 변경하고 커밋을 실행한 후에 사용자 B가 다시 조회 쿼리를 보내면 결과가 조회된다.
사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 하는 REPEATABLE READ 정합성에 어긋난다.

READ COMMITED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 쿼리나 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 없다. COMMIT 이후에 값이 변경되어 다른 트랜잭션에서 범위 내에 데이터 변경 조회를 허용하는 격리수준이니 말이다.
REPEATABLE READ 격리수준은 BEGIN 명령으로 트랜잭션을 시작한 상태에서 END까지(다른 트랜잭션에서 데이터를 변경하고 COMMIT을 실행해도) 똑같은 쿼리를 반복해서 실행해도 같은 결과만 조회된다.

REPEATABLE READ

MySQL InnoDB 스토리지 엔진에서 사용되는 격리 수준이다. InnoDB 스토리지 엔진은 트랜잭션이 롤백될 가능성에 대비해 변경되기 전 레코드를 언두 영역에 저장해두고 실제 데이터를 변경한다. 이를 MVCC라고 한다.

MVCC(Multi-Version Concurrency Control)는 여러 트랜잭션이 동시에 같은 데이터에 접근해도 각 트랜잭션은 서로 독립적으로 실행된 것처럼 보이게 하는데 사용된다.
MVCC는 각 트랜잭션에 대해 데이터의 특정 버전, 즉 '스냅샷'을 제공함으로써 한 트랜잭션이 데이터를 변경하는 동안 다른 트랜잭션은 해당 데이터의 이전 버전을 볼 수 있다. 따라서 한 트랜잭션은 다른 트랜잭션의 작업으로 인해 영향을 받지 않게 된다.

REPEATABLE READ와 READ COMMITED의 차이는 언두 영역에 백업된 레코드의 스냅샷을 언제 찍는지에 있다.

• READ COMMITTED: 트랜잭션 내의 각 쿼리가 실행될 때마다 새로운 스냅샷을 생성
• REPEATABLE READ: 트랜잭션이 시작될 때 한 번만 스냅샷을 생성

모든 InnoDB 트랜잭션은 고유한 TXID를 가지며 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 TXID가 포함돼 있다. REPEATABLE READ는 MVCC를 보장하기 위해 실행 중인 TX 가운데 가장 오래된 TXID보다 TXID가 앞선 언두 영역의 데이터는 삭제할 수 없다.
menu 테이블의 초기 두 레코드는 TXID가 1이다. 사용자 A가 menuID가 100인 메뉴명을 변경하는 과정에서 사용자 B가 menuId=100을 조회할 때 아래와 같다.

1. 사용자 A의 TXID = 10, 사용자 B의 TXID = 5이다.
2. 사용자 A는 메뉴 이름을 Bread로 변경하고 COMMIT을 실행했다.
3. 사용자 B는 menuId = 100인 메뉴를 트랜잭션 A 변경 전후로 각각 한 번씩 SELECT를 했지만 결과는 항상 같은 Beverage이다.
4. 사용자 B의 TXID인 5안에서 실행되는 모든 SELECT 쿼리는 TXID가 5보다 작은 TXID에서 변경한 것만 보게 된다.

언두 영역은 트랜잭션이 종료되기 전까지 무한정 커질 수 있다. 트랜잭션이 너무 길어지면 언두 영역이 과도하게 커질 수 있으며, 이는 디스크 공간을 많이 사용하고 성능 저하를 일으킬 수 있다.
따라서 트랜잭션은 가능한 한 범위를 최소화하면 동시성이 향상되고, 롤백이 발생할 경우에도 처리 시간을 줄일 수 있다.

PANTHOM READ

위의 표에서 InnoDB 엔진은 REPEATABLE READ는 유령읽기가 발생하지 않는다고 되어있다.
유령 읽기란 한 트랜잭션 내에서 같은 쿼리를 두 번 실행했을 때 첫 번째 쿼리에서는 없었던 새로운 레코드가 두 번째 쿼리에서 나타나는 현상을 말한다.

InnoDB의 넥스트 키 락은 인덱스 레코드에 락을 거는 것뿐만 아니라, 그 레코드와 다음 레코드 사이의 갭에도 락을 걸어서 유령 읽기를 방지한다. 트랜잭션이 진행되는 동안 다른 트랜잭션이 해당 영역에 새로운 레코드를 삽입하는 것을 막을 수 있다.

SERIALIZABLE

Non-locking consistent read로 아무런 레코드 잠금 설정 없이 실행된다. 이 격리수준에서는 읽기 작업도 공유 잠금을 획득해야 한다. 트랜잭션 A가 읽고 쓰는 레코드를 트랜잭션 B에서는 절대 접근할 수 없다.
가장 단순하면서 가장 엄격한 격리수준이다.

---

내용이 길어져서 트랜잭션에 대해서는 좀더 자세하게 다음에 설명하겠다.