MySQL 트랜잭션과 잠금

트랜잭션: 작업의 데이터 정합성을 보장한다.

• 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상(Partial update)이 발생하지 않게 만들어주는 기능

잠금(Lock): 동시성을 제어하기 위한 기능

격리 수준: 하나의 트랜잭션 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 말한다.

---

트랜잭션

MyISAM이나 MEMORY 같이 트랜잭션을 지원하지 않는 스토리지 엔진의 테이블이 InnoDB 스토리지 엔진보다 더 많은 고민거리를 만들어 낸다.

MySQL에서의 트랜잭션

트랜잭션이란 논리적인 작업 셋 자체가 100% 적용되거나(Commit) 아무것도 적용되지 않아야(Rollback)함을 보장해 주는 것이다.

CREATE TABLE tab_myisam
( fdpk INT NOT NULL, PRIMARY KEY (fdpk)) ENGINE=MyISAM;
INSERT INTO tab_myisam (fdpk) VALUES (3);

CREATE TABLE tab_innodb
( fdpk INT NOT NULL, PRIMARY KEY (fdpk)) ENGINE=INNODB;
INSERT INTO tab_innodb (fdpk) VALUES (3);

 

AUTO-COMMIT 모드

• 두 개의 스토리지에 아래 INSERT 쿼리를 실행한다면 pk 3이 중복되어 두 table 모두 오류가 발생한다.

// myisam은 1, 2가 저장되지만 innodb는 1, 2가 저장되지 않는다.
INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3);

 

MyISAM과 InnoDB 차이점

MyISAM : (1),(2) 값에 대하여 레코드 삽입 결과가 성공하고 (3)이 실패했더라도 (1),(2)값이 남아 있게 된다.

• 부분 업데이트(Partial Update)라고 표현하며 테이블 데이터 정합성을 맞추는데 상당히 어려운 문제를 만들어 낸다.

InnoDB : 쿼리 중 일부라도 오류가 발생하며 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 문장을 실행하기 전 상태로 그대로 복구한다. 따라서 (1)과 (2)의 삽입 결과는 되돌려진다.

MyISAM 스토리지 엔진을 사용할 때 위 정합성 문제를 해결하려면 if문을 사용해서 분기처리 해야 하지만, innodb는 Rollback을 통해서 고민해야 하는 부분을 많이 줄일 수 있다.

트랜잭션 범위를 최소화하자

• 트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.
• 트랜잭션은 InnoDB 엔진에서 언두 로그를 예상보다 오랫동안 길게 남아 있게 할 수 있으며 적용해야하는 최적의 구간에만 트랜잭션을 적용해야한다.

하나의 트랜잭션에서 모든 로직을 처리하고 있다면?

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

프로그램의 코드가 DB 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화 하자

• 실제로 DBMS에 데이터를 저장하는 작업은 (3)번부터 시작되며 트랜잭션에 이전 과정을 포함시킬 필요는 없다.
• (7)번 작업의 경우 메일 전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업(외부 API 포함) DBMS 트랜잭션 내에서 제거하는 것이 좋다.
• 8번 작업은 (3,4,5)번 트랜잭션과 함께 묶지 않아도 무방하다.

---

MySQL 엔진의 잠금

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

MySQL 엔진 레벨의 잠금: 모든 스토리지 엔진에 영향을 미침

• 테이블 락: 테이블 데이터 동기화를 위해 건다.
• 메타데이터 락: 테이블의 구조를 잠근다.
• 네임드 락: 사용자의 필요에 맞게 사용할 수 있다.

스토리지 엔진 레벨의 잠금: 스토리지 엔진 간 상호 영향을 미치지 않는다.

글로벌 락 (MySQL 서버 전체 영향)

FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있고 MySQL에서 제공하는 가장 범위가 큰 락이다.

• 한 세션에서 글로벌 락 획득 시 다른 세션의 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 대기 상태로 남는다.
• MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때 사용한다.

InnoDB 스토리지 엔진이 일반화되면서 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요가 없어졌다.

LOCK INSTANCE FOR BACKUP;
-- // 백업 실행
UNLOCK INSTANCE;

• 8.0부터 InnoDB가 기본 스토리지로 채택되면서 Xtrabackup이나 Enterprise Backup과 같은 백업 락이 도입되었다.
• Xtrabackup이나 Enterprise Backup 툴이 실행되는 도중 스키마 변경이 실행되면 백업이 실패되면서 다시 백업을 해야하는 문제가 발생한다.
  • DDL 명령이 실행되면 복제(백업은 레플리카 서버에서 진행)를 일시 중지하기 위해 MySQL 서버의 백업 락이 도입되었다.

테이블 락

개별 테이블 단위로 설정되는 잠금으로 명시적 혹은 묵시적으로 특정테이블의 락을 획득할 수 있다.

명시적

획득하는 방법: LOCK TABLES table_name [READ | WRITE]

반납하는 방법: UNLOCK TABLES

• 테이블 락은 MyISAM뿐 아니라 InnoDB도 동일하게 설정한다.
• 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미친다.

묵시적

MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.

• 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제된다.

InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공한다.

• 락이 설정되지만 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는(DDL) 경우에만 영향을 미친다.

네임드 락

GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.

• 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 DB 객체가 아니라 사용자가 지정한 문자열에 대해 잠금을 획득하고 반납하는 잠금이다.

// mylock이라는 문자열에 대해 잠금을 획득하고, 이미 잠금을 사용 중이면 2초 동안만 대기한다.
SELECT GET_LOCK('mylock', 2);

// mylock이라는 문자열에 대해 잠금이 설정돼 있는지 확인한다.
SELECT IS_FREE_LOCK('mylock');

// mylock이라는 문자열에 대해 획득했던 잠금을 반납한다.
SELECT RELEASE_LOCK('mylock');

// MySQL 8.0 버전부터 네임드 락을 중첩해서 사용할 수 있고 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가되었다.
SELECT RELEASE_ALL_LOCKS();

• 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되는데 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 간단하게 해결할 수 있다.

메타데이터 락

데이터베이스 객체(테이블이나 뷰)의 이름이나 구조를 변경하는 경우 획득하는 잠금이다.

• RENAME TABLE tab_a TO tab_B 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

-- // 배치 프로그램에서 별도의 임시 테이블(rank_new)에 서비스용 랭킹 데이터 생성
RENAME TABLE rank TO rank_backup, rank_new TO rank;

• 현재 서비스용 랭킹 테이블(rank)는 백업하고 새로 만들어진 랭킹 테이블을 서비스용으로 대체할 수 있다.
  • Table not found rank 같은 상황을 발생시키지 않고 적용하는 것이 가능하다.
• 명시적으로 획득하거나 해제할 수 있는 것이 아닌 자동으로 락을 획득한다.

테이블의 구조를 변경해야 하는 경우

문제점

• 시간이 너무 오래 걸리는 경우 언두 로그의 증가와 Online DDL이 실행되는 동안 누적된 Online DDL 버퍼의 크기 등 고민해야 할 문제가 많다.
• MySQL 서버의 DDL은 단일 스레드로 동작하기 때문에 상당히 많은 시간이 소모된다.

해결법

새로운 구조의 테이블을 생성하고 프라이머리 키인 id값을 범위별로 나눠서 여러 개의 스레드로 빠르게 복사한다.

INSERT INTO new_table_name SELECT * FROM table_name WHERE id ≥ 0 AND id < 10000;
INSERT INTO new_table_name SELECT * FROM table_name WHERE id ≥ 10000 AND id < 20000;
INSERT INTO new_table_name SELECT * FROM table_name WHERE id ≥ 10000 AND id < 20000;

이후 나머지 데이터 처리해서 RENAME 명령을 통해서 새로운 테이블을 서비스로 투입할 수 있다.

SET AUTOCOMMIT = FALSE;

-- // 작업 대상 테이블 2개에 대해 테이블 쓰기 락 획득
LOCK TABLES table_name WRITE, new_table_name WRITE;

-- // 남은 데이터를 복사
SELECT MAX(id) as @MAX_ID FROM new_table_name;
INSERT INTO new_table_name SELECT * FROM table_name WHERE id > @MAX_ID;
COMMIT;

-- // 새로운 테이블로 데이터 복사가 완료되면 RENAME 명령으로 새로운 테이블을 서비스로 투입
RENAME TABLE table_name TO table_name_old, new_table_name TO table_name;
UNLOCK TABLES;

-- // 불필요한 테이블 삭제
DROP TABLE table_name_old;

 

---

InnoDB 스토리지 엔진 잠금

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

• 레코드 기반의 잠금 방식 덕분에 InnoDB 엔진은 높은 동시성 처리를 제공할 수 있다.
• 레코드 기반의 잠금 기능 제공하며 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락 또는 테이블 락으로 레벨업 되는 경우는 없다
• 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용한다.
• 프라이머리 키 또는 유니크 인덱스(클러스터 인덱스)에 의한 변경 작업에서는 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.

레코드 락(Record lock, Record only lock)

InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.

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

갭 락과 넥스트 키 락의 목적

바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.

갭 락(Gap lock)

레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만 잠근다.

• 레코드와 레코드 사이에 새로운 레코드가 생성(INSERT)되는 것을 제어한다.
• 넥스트 키 락의 일부로 자주 사용된다.

넥스트 키 락(Next Key lock)

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

• STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다.
• innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.
• 넥스트 키락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생하여 최신 버전에서는 ROW 포맷의 바이너리 로그가 기본 설정으로 변경되어 넥스트 키 락이나 갭 락을 줄일 수 있다.

자동 증가 락

여러 레코드가 저장되는 경우 중복되지 않고 저장된 순서대로 증가하는 인련번호 값을 가져야 하기에 자동으로 내부적으로 auto increment lock을 사용한다.

• INSERT, REPLACE같이 새로운 레코드를 저장하는 쿼리에만 필요하다. (UPDATE나 DELETE에서는 필요하지 않다.)

MySQL 8.0 버전 부터는 바이너리 로그 포맷으로 ROW 포맷이 기본값이기 때문에 innodb_auto_lock_mode = 2가 기본값이다. 
STATEMENT 포맷의 바이너리 로그를 사용한다면 innodb_auto_lock_mode = 1로 변경해서 사용하자.

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.

• 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

해당 조건으로 검색되는 레코드 개수

// 253건
SELECT COUNT(*) FROM employees WHERE first_name = 'Georgi'
// 1건
SELECT COUNT(*) FROM employees WHERE first_name = 'Georgi' AND last_name = 'Klassen'

employees 테이블에 다음과 같은 DML 쿼리를 사용한다고 했을 때 몇 개의 레코드에 락이 걸릴까?

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

• employees 테이블에 first_name 컬럼에만 인덱스가 만들어져 있으면 last_name 컬럼은 인덱스가 없기 때문에 253건의 레코드가 모두 잠긴다.

인덱스 설계가 중요한 이유

• 적절한 인덱스가 준비돼 있지 않다면 각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 update를 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생한다.
• 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 UPDATE를 진행하는데 이 과정에서 테이블에 있는 모든 레코드를 잠그게된다.

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

테이블의 레코드 수준의 잠금은 테이블 수준의 잠금보다 더 복잡하다.

• 잠금이 걸린 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨있어도 잘 발견되지 않기 때문이다.

프로세스 목록 조회하기

• SHOW PROCESSLIST

강제로 잠금을 해제하려면 KILL 명령을 이용해 MySQL 서버의 프로세스를 강제로 종료하면 된다.

• KILL {id}

최신 버전은 information_schema의 정보들이 조금씩 Deprecated 되면서 performance_schema의 data_locks와 data_lock_waits 테이블로 대체되고 있다.

• 자세한 내용은 공식문서를 확인하자

---

MySQL의 격리 수준(isolation level)

여러 트랜재션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생 (InnoDB는 없음)
SERIALIZABLE	없음	없음	없음

• SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.
• REPEATABLE READ 격리 수준에서는 THANTOM READ가 발생할 수 있지만, InnoDB에서는 독특한 특성 때문에 발생하지 않는다.
• 일반적인 온라인 서비스 용도의 DB는 READ COMMITTED와 REPEATABLE READ중 하나를 사용한다.

READ UNCOMMITTED

각 트랜잭션에서의 변경 내용이 COMMIT 이나 ROLLBACK 여부에 상관없이 모두 다른 세션에서 보이게 된다.

• 사용자 A가 commit 하지 않고 rollback 한다면 유저 B는 해당 테이블에서 제거된 비정상적인 레코드를 보게 된다.
• 이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty read)라고 한다.
• 더티 리드는 실제 어플리케이션에서 상당한 혼란을 유발하므로 MySQL에서는 최소한 격리 수준을 READ COMMITTED 이상을 권장한다.

READ COMMITTED

COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문에 더티 리드는 발생하지 않는 격리 수준이다.

• 온라인 서비스에서 가장 많이 선택되는 격리 수준이다.

사용자 A가 first_name이 Lara인 것을 Toto로 update하면 테이블에 즉시 기록되고 이전 값인 Lara 는 언두 영역으로 백업된다.

• 사용자 B가 조회하면 테이블이 아니라 언두 영역에 백업된 레코드에서 가져오기 때문에 Lara를 반환한다.
• 사용자 A가 변경된 내용을 commit 하면 다른 트랜잭션에서도 백업된 언두 레코드가 아니라 테이블에서 변경된 값을 조회할 수 있게 된다.

READ COMMITTED 문제점

• 사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋나게 된다.
• 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결된다면 문제가 될 수도 있다.

트랜잭션에 있고 없고에 따라서 SELECT 문장의 차이점이 발생한다.

READ COMMITTED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로 없다.

REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동한다.

• 다른 트랜잭션이 update를 하더라도 트랜잭션을 내에서 항상 동일한 결과만 보게 된다.

REPEATABLE READ

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

• NON-REPEATABLE READ 부정합이 발생하지 않는다.

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다.

• 언두 영역의 백업된 데이터는 InnoDB가 불필요하다고 판단하는 시점(checkpoint, commit, rollback)에 주기적으로 삭제한다.
• REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다.

• 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10(자신의 트랜잭션 번호)보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.
• 한 사용자가 BEGIN으로 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수 있으며 MySQL 서버의 처리 성능이 떨어질 수 있다.

REPEATABLE READ 문제점

하나의 트랜잭션에서 수행되는 SELECT 쿼리 결과는 똑같아야 하는데 사용자 B가 실행한 두 번의 SELECT .. FOR UPDATE 쿼리는 결과가 다르게 나온다.

• SELECT .. FOR UPDATE 쿼리의 경우 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다.
  • 따라서 SELECT … FOR UPDATE, SELECT … LOCK IN SHARE MODE로 조회하는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 현상이 발생하기 때문이다.
• 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ라고 부른다.

InnoDB 스토리지 엔진에서는 SELECT ... FOR UPDATE나 SELECT ... FOR SHARE 등 잠금을 동반한 SELECT 쿼리에서 PHANTOM READ 현상이 발생할 수 있지만, 이는 예외적인 상항으로 볼 수 있다.

• 일반적인 상황이라면 InnoDB 스토리지 엔진의 REPEATABLE-READ 격리 수준에서는 갭 락과 넥스트 키 락 덕분에 PHANTOM READ가 발생하지 않는다.

SERIALIZABLE

가장 단순하면서도 가장 엄격한 격리 수준으로 동시 처리 성능이 다른 트랜잭션 격리 수준보다 떨어진다.

InnoDB 테이블에서 순수한 SELECT 작업(Non-locking consistent read)은 아무런 레코드 잠금도 설정하지 않고 실행된다.

• 격리 수준이 SERIALIZABLE인 경우 읽기 작업도 공유 잠금을 반드시 획득해야하며 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.

InnoDB에서는 REPEATABLE-READ 격리 수준에서도 PHANTOM READ가 발생하지 않으므로 굳이 SERIALIZABLE 격리 수준을 사용할 이유가 없다.

---

Spring에서 MySQL 기본 Isolation 확인하는 방법

DatabaseMetaData 인터페이스의 getDefaultTransactionIsolation() 메서드 구현체를 찾아보면 된다.

 

구현체의 return 4의 의미는 무엇일까?

 

Isolation 열거형 클래스를 확인하면 MySQL의 기본값은 REPEATABLE_READ인 것을 확인할 수 있다.

 

여기 블로그를 통해서 좀 더 자세한 예제를 확인할 수 있습니다.