[Real MySQL] 트랜잭션과 잠금

트랜잭션(Transaction) : 하나의 논리적인 연산 단위

• 논리적인 작업 셋을 완벽하게 처리하거나 처리하지 못 한 경우 원 상태로 복구해서 작업의 일부만 적용되는 현상(partial update)을 방지하기 위함
• 데이터의 정합성을 보장하기 위한 기능

잠금(Lock) : 하나의 데이터를 하나의 커넥션에서만 접근할 수 있도록 해주는 기능

• 동시성을 제어하기 위한 기능
• MySQL에서는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.
  • MySQL 엔진 레벨 : 모든 스토리지 엔진에 영향을 미침
  • 스토리지 엔진 레벨 : 스토리지 엔진 간 상호 영향을 미치지 않음

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

트랜잭션

---

MySQL에서 MyISAM이나 MEMORY storage engine은 트랜잭션을 지원하지 않기 때문에 InnoDB 보다 빠르다.

• 하지만 트랜잭션을 지원하지 않기 때문에 더 많은 고민거리를 만들어 낸다는 것을 알아야 한다.
• 따라서 MyISAM이나 MEMORY에서는 하나의 논리적인 작업 셋을 수행하는 도중 에러가 발생해도 이전까지의 내용들이 그대로 테이블에 반영되게 된다.

-- 01. 테이블 생성
CREATE TABLE tab_myisam( fdpk INT NOT NULL, PRIMARY KEY (fdpk)) ENGINE=MyISAM;
INSERT INTO tab_myisam(fdpk) VALUES(3);

-- 02. 데이터 삽입
SET autocommit=ON;
INSERT INTO tab_myisam(fdpk) VALUES (1),(2),(3),(4);

-- 03. 데이터 조회
select * from tab_myisam;

위의 작업을 수행하는 경우 02. 데이터 삽입 부분에서 Error Code: 1062. Duplicate entry '3' for key 'tab_myisam.PRIMARY' 에러가 발생하게 된다.

 

하지만 실제 데이터 조회 시에는 이전에 추가된 데이터 1, 2가 조회되는 것을 볼 수 있다.

 

그와 반대로 InnoDB를 사용하는 경우에는 마지막 데이터를 조회 시 초기 데이터 3만 조회되는 것을 확인할 수 있다.

MySQL 엔진 잠금

---

글로벌 락

• MySQL에서 제공하는 잠금 중 가장 범위가 큰 락
• 테이블 상관없이 대부분의 DDL 문장과 DML 문장에서 락이 걸리게 된다.
• 여러 DB에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 하는 경우 주로 사용
  • 최근 InnoDB를 주로 사용하면서 일관된 데이터 상태를 위해 글로벌 락을 사용할 필요가 거의 없게 되었다.

테이블 락

• 개별 테이블 단위로 설정되는 락
• LOCK TABLES <table_name> [ READ | WRITE ] 명령어로 명시적으로 테이블 락을 설정할 수 있다.
• 하지만 명시적으로 테이블을 잠그는 작업은 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미친다.
  • 특별한 상황이 아니면 거의 사용할 일이 없음
• 묵시적인 락은 데이터를 변경하는 쿼리를 실행하면 자동으로 발생
  • InnoDB에서는 레코드 기반의 잠금을 제공

네임드 락

• 임의의 문자열을 이용해 사용자가 직접 락을 설정하는 방식
• DB 서버 1대에 5대의 웹 서버가 접속해 서비스 하는 상황에서 5대의 웹 서버가 어떤 정보를 동기화해야 하는 요건처럼 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 사용하면 쉽게 해결할 수 있다.

-- 'mylock'문자열에 대해 잠금을 획득
-- 이미 잠금을 사용 중이면 2초 동안만 대기 -> 2초 이후에는 자동 잠금 해제
SELECT GET_LOCK('mylock', 2);

-- 'mylock' 문자열이 잠금 설정이 되어 있는지 확인
SELECT IS_FREE_LOCK('mylock');

-- 'mylock' 문자열에 대해 잠금 반납
SELECT RELEASE_LOCK('mylock');

 

메타데이터 락

• 데이터베이스 객체(테이블, 뷰, ..)의 이름이나 구조를 변경하는 경우에 획득하는 잠금
• 메타데이터 락은 명시적으로 획득할 수는 없고, RENAME TABLE tab_a TO tab_b와 같이 테이블 명을 변경하는 경우 자동으로 획득한다.
• RENAME TABLE과 같은 경우 source Table과 destination Table 모두 LOCK을 획득한다.

배치 프로그램에서 실시간으로 테이블을 바꿔야 하는 경우, 연속된 Table Rename을 하나의 메타데이터 락 트랜잭션 내에서 실행시킬 수 있다.

RENAME TABLE rank TO rank_backup, rank_new TO rank;

• rank → rank_backup을 수행할 때 rank table, rank_backup lock 획득
• 동시에 rank_new → rank의 rank_new lock 획득

 

따라서 두 테이블이 RENAME될 때 다른 transaction에서 해당 테이블의 조회를 할 수 없기 때문에 중간에 rank 테이블을 조회할 수 없는 문제를 예방할 수 있다.

• 만약 두 번의 RENAME TABLE을 연속으로 수행하게 되면 중간에 rank table의 메타데이터 락이 풀려있는 구간이 발생하고, 이때 조회가 발생하면 해당 테이블이 존재하지 않는다는 에러가 발생한다.

 

테이블 크기가 큰 log 테이블의 구조를 변경해야 하는 경우에도 이러한 원리를 이용할 수 있다.

• 바로 DDL을 통해 테이블 구조를 변경하는 경우, “언두 로그 증가”, “Online DDL이 실행되는 동안 누적된 Online DDL 버퍼의 크기” 등 고민해야 할 것들이 많다.
• 추가적으로 MySQL서버의 DDL은 단일 스레드로 작동하기 때문에 많은 시간이 소모된다.

이러한 경우,

1. 새로운 구조의 테이블을 미리 생성하고, 최근의 데이터까지 id값을 범위별로 나눠 여러 쓰레드에서 빠르게 복사
2. 그리고 나머지 데이터는 트랜잭션, 테이블 잠금, RENAME TABLE 명령을 통해 응용 프로그램의 중단 없이 실행할 수 있다.
   • 이때에도 “남은 데이터를 복사”하는 시간 동안은 테이블의 잠금으로 인해 INSERT를 할 수 없게 된다.
   • 따라서 (1)에서 더 최근 데이터까지 복사할수록 잠금 시간을 최소화할 수 있다.

-- 01. 신규 테이블 생성
CREATE TABLE access_log_new)(
	id BIGINT NOT NULL AUTO_INCREMENT,
	...
	PRIMARY KEY(id)
) KEY_BLOCK_SIZE=4;

-- 02. id 범위별로 레코드를 신규 테이블로 복사
INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=0 AND id<100000
INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=100000 AND id<200000
....
INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=900000 AND id<1000000

-- 03. 두 테이블에 대해 쓰기 락 획득
SET autocommit=0;
LOCK TABLES acces_log WRITE, access_log_new WRITE;

-- 04. 최신 데이터 복사
SELECT max(id) as @MAX_ID FROM access_log_new;
INSERT INTO access_log_new SELECT * FROM access_log WHERE pk>@MAX_ID;
COMMIT;

-- 05. 테이블 명 변경
RENAME TABLE access_lkog TO access_log_old, access_log_new TO access_log;
UNLOCK TABLES;

-- 06. 이전 테이블 삭제
DROP TABLE access_log_old;

 

InnoDB 스토리지 엔진 잠금

---

레코드 락

• 테이블 내의 특정 레코드에 대해서만 잠금을 설정
• InnoDB는 다른 DBMS와 다르게 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
  • 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해서 잠금을 설정

레코드 자체를 잠그는 것과 인덱스의 레코드를 잠그는 것에는 많은 차이가 존재

-- employees table에는 first_name 컬럼만 멤버로 담긴 ix_firstname이라는 인덱스가 존재
-- employees table에는 first_name='Lim'인 사원은 전체 200명이 존재
-- employees table에는 first_name='Lim'이고, last_name="moon"인 사원은 전체 1명이 존재

UPDATE employees SET hire_date=NOW() WHERE first_name='Lim' AND last_name='moon';

• 위 Update의 경우 실제 update되는 레코드는 1개이다.
• 하지만 실제 Lock이 걸리는 것은 first_name='Lim'인 200개의 레코드 모두가 락이 걸리게 된다.

갭 락

• 레코드와 레코드 사이의 간격에 대해 새로운 레코드가 생성되는 것에 대한 잠금을 설정
• 아직 존재하지 않는 부분에 대한 Lock
• 다른 DBMS와의 차이점

ex) 테이블에 id 2, 3만 존재

• 갭락 1 ~ 5 설정 → 1, 4, 5 id에 대해 갭락이 설정된다.

넥스트 키 락

• 레코드 락 + 갭 락
• 특정 레코드에 대한 락과 아직 존재하지 않는 부분에 대한 락을 동시에 설정

ex) 테이블에 id 2, 3만 존재

• 넥스트 키 락 1~5 설정
  • 레코드 락 : 2, 3
  • 갭 락 : 1, 4, 5

자동 증가 락

• MySQL에서 auto_increment를 제공하기 위해 사용되는 잠금
• INSERT 및 REPLACE 쿼리와 같이 새로운 레코드를 저장하는 쿼리에서만 필요
  • UPDATE, DELETE 등의 쿼리에서는 설정되지 않는다.
• 자동 증가 락을 명시적으로 획득하고 해제하는 방법은 없다.
• 테이블에서 auto_increment 값을 가져오기 위해서 자동으로 획득되고 해제된다.

MySQL 격리 수준

---

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

• READ UNCOMMITED, READ COMMITED, REPEATABLE READ, SERIALZABLE

각 트랜잭션 격리 수준에서 발생하는 대표적인 부정합 문제점이 존재

• Dirty Read, Non-Repeatable Read, Phandom Read

• InnoDB에서는 next key lock 덕분에 Repeatable READ 격리 수준에서도 Phandom Read가 거의 발생하지 않는다.

일반적인 온라인 서비스 용도 DB에서는 READ COMMITED와 REPEATABLE READ 중 하나를 사용

• 오라클 DBMS에서는 주로 READ COMMITED를 사용
• MySQL에서는 REPEATABLE READ를 주로 사용

READ UNCOMMITED

• 각 트랜잭션의 변경사항이 commit 전에 다른 트랜잭션에서도 확인할 수 있다.

• READ UNCOMMITED에서는 Dirty Read 현상이 발생
  • Dirty Read : 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데 다른 트랜잭션에서 볼 수 있는 현상
• 만약 사용자A가 COMMIT하지 않고 마지막에 ROLLBACK을 한다면, 실제 DB에는 Lara가 없음에도 불구하고 사용자 B에서는 Lala가 있다는 가정하에 작업을 수행하게 된다.

READ COMMITED

• 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.

• 사용자 A 트랜잭션이 COMMIT되기 전까지 변경된 데이터는 모두 언두 로그에 쌓이게 된다.
• 그리고 다른 트랜잭션에서 해당 테이블을 조회하는 경우에는 언두 로그를 통해 데이터를 조회
• READ COMMITED에서는 RETEATABLE READ 정합성을 보장하지 못 한다.
  • REPEATABLE READ : 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 조건

Non-Repeatable Read

• 사용자 B의 첫 번째 query에서는 결과가 존재하지 않지만, 동일한 트랜잭션 내에서 이후에 조회할 때에는 1건의 결과가 반환된다.

이러한 REPEATABLE READ 문제는 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수 있다.

REPEATABLE READ

• 하나의 트랜잭션 내에서 동일한 SELECT 쿼리를 사용했을 때 항상 동일한 결과를 가져와야 한다.

• 사용자 B가 데이터를 읽을 때, 언두 로그에서 데이터를 읽어온다.
  • 여기서 추가적으로 자신의 Transaction-ID(TRX-ID) 보다 낮은 언두 로그에 대해서만 값을 가져온다.
• READ COMMITED와 다른 점은 결국 언두 로그에서 현재 자신의 TRX-ID보다 낮은 데이터에 대해서만 읽는다는 것이다.
• REPEATABLE READ 격리 수준에서는 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다.
  • 위에서 사용자A가 COMMIT이 완료되었음에도 불구하고, 기존 언두 로그에 쌓여있던 데이터의 TRX-ID가 현재 실행 중인 사용자B의 TRX-ID보다 낮기 때문에 삭제되지 않았다.

 

기존 READ UNCOMMITED와 READ COMMITED의 경우 “트랜잭션 내에서 실행되는 SELECT”와 “트랜잭션 외에서 실행되는 SELECT” 간에 차이가 없었다.

• 하지만 REPEATABLE READ에서는 두 SELECT에는 큰 차이가 존재
  • 트랜잭션 내에서 실행되는 SELECT는 해당 트랜잭션 내에서 항상 동일한 결괏값을 보장

 

하나의 트랜잭션이 장시간 동안 동작하게 되면 언두 영역에 백업된 데이터가 무한정 커질 수 있다.

• 이렇게 언두 영역의 데이터가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다.

Phandom Read

• Phandom Read : 다른 트랜잭션에서 수행한 작업으로 인해 레코드가 보였다 안 보였다 하는 현상

• 기존 REPEATABLE READ에서는 언두 로그를 통해 위의 문제를 해결하였지만 위와 같은 경우 쓰기 잠금을 사용하였기 때문에 Phandom Read가 발생
• 언두 로그에는 잠금을 걸 수 없기 때문에 쓰기 잠금을 사용한 경우 조회는 반드시 원본 테이블을 통해 조회가 발생하기 때문

 

하지만 InnoDB의 경우 Next key lock을 사용하기 때문에 위와 같은 문제가 거의 발생하지 않는다.

• 위 사용자B의 첫 번째 조회에서 Next Key Lock이 걸린다.
  • 따라서 emp_no ≥ 500000 이상인 데이터에 대해 Next Key Lock이 걸리게 된다.
• 사용자A가 emp_no = 500001 데이터를 추가하려고 해도, Next Key Lock이 걸려있기 때문에 사용자B의 트랜잭션이 끝날 때까지 대기하게 된다.
  • 이때 지나치게 오랜 시간 대기하면 락 타임아웃이 발생
• 사용자B가 두 번째 조회 시에도 동일한 결괏값을 얻을 수 있다.
• 사용자B의 트랜잭션이 끝나고 Next key lock이 풀리면서 사용자A의 작업이 재개

 

InnoDB에서 Phandom Read는 다음과 같은 경우에만 발생

• 사용자B가 첫 번째 조회를 쓰기 잠금을 걸지 않고 수행
• 사용자A가 emp_no = 500001 데이터 삽입
  • Next key lock이 걸려있지 않은 상태이므로 데이터 삽입이 정상적으로 수행됨
• 사용자B가 두 번째 조회에 쓰기 잠금을 한 뒤 수행
  • 언두 로그를 사용하지 못 하므로 Phandom Read가 발생

Serializable

• 여러 트랜잭션이 동일한 레코드에 접근할 수 없다.
• 가장 단순한 격리 수준이면서 가장 엄격한 격리 수준
  • 그만큼 동시성 처리 성능도 매우 떨어지게 된다.
• 읽기 작업에 대해서도 공유 잠금(읽기 잠금)을 획득해야 한다.
  • 따라서 하나의 트랜잭션에서 데이터를 읽고 있으면 다른 트랜잭션에서는 해당 레코드를 수정할 수 없다.

참고

• Real MySQL 8.0

[MySQL] 트랜잭션의 격리 수준(Isolation Level)에 대해 쉽고 완벽하게 이해하기