Real Mysql 8.0 : Transaction & Lock

시작하기 전

이번 장에서는 아래에 대하여 알아보겠습니다.
1. 트랜잭션이 무엇일까
2. MySQL에는 어떤 잠금이 존재하며 어떻게 사용될까
3. 격리수준에는 무엇인 존재할까

이러한 내용들을 개념 설명 1개와 간단한 구현 1개로 알아보겠습니다.

---

트랜잭션이 무엇일까

트랜잭션

프로그래밍관점에서 트랜잭션은 데이터의 정합성을 보장해 주는 것입니다.
논리적인 작업 셋을 모두 성공하거나 모두 실패하는 것을 보장합니다.

요즘 응용 프로그래머는 특별한 셋업이 없어도 기본적인 정합성을 보장받습니다.
그리고 MySQL의 Storage Engin인 MyISAM과 InnoDB에서 정합성 보장은 큰 차이가 존재합니다.
2개의 Storage Engine을 이용해 정합성 보장의 유무가 어떤 것인지 알아보겠습니다.

1. 다른 Storage Engine을 사용하는 table을 2개 생성합니다.

CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY (fdpk)) ENGINE=MYISAM;
CREATE TABLE tab_innodb ( fdpk INT NOT NULL, PRIMARY KEY (fdpk)) ENGINE=innodb;

2. 생성한 table에 데이터를 넣어줍니다.

insert into tab_myisam (fdpk) values (3);
insert into tab_innodb (fdpk) values (3);

3. 3을 포함하는 데이터를 넣어줍니다.

insert into tab_myisam (fdpk) values (1), (2), (3);
insert into tab_innodb (fdpk) values (1), (2), (3);

모든 과정은 종료되었고 3번 작업에서 Duplicate entry를 얻습니다.
3의 데이터는 2번 작업에서 이미 넣었으므로 당연하게도 실패입니다!

결과를 한 번 살펴보겠습니다.
InnoDB같이 모든 작업이 수행이 안되는 것을 기대했지만 MyISAM은 duplicate entry를 제외한 모든 작업이 성공하였습니다.
MyISAM은 이러한 부분에 대하여 분기처리를 통하여 모두 보상해줘야 합니다.
비즈니스 로직에만 집중하고 싶은 우리 백엔드들은 슬슬 피곤해집니다.
그리고 이러한 현상을 Partial Update라고 표현합니다.

트랜잭션의 범위를 어떻게 잡아야 할까?

무조건적으로 트랜잭션의 시작과 종료를 DB커넥션의 생명주기(혹은 반납주기)와 동일하게 운용하는 것을 지양해야합니다.

나의 트랙잭션 앞에서는 모든 것이 평등해지는 상남자 전략

@Service
public class UserService { 
	@Transactional
    public void execute(request) {
    	// Task1 : verify dto
        // Task2 : select
        // Task3 : call external api (mail)
        // Task4 : publish event
        // Task5 : write new state
    }
}

DB Transaction은 최소한으로 유지하는 것이 좋습니다.
자원을 점유하고 오래 유지하면 교착 상태에 빠지기 쉬워지며 자원을 오래 점유하는 것중에 하나가 네트워크 통신입니다.

필요한 부분만 트랜잭션이 적용되어야 하며 통신의 결과에 따라서 롤백이 필요하다면 실패 허용 + 추후 보정을 하거나 분산 트랜잭션을 적용하는 것을 추천합니다.

Is it better to make database calls or external API calls first in the context of a single Web request?
https://softwareengineering.stackexchange.com/questions/280324/is-it-better-to-make-database-calls-or-external-api-calls-first-in-the-context-o

---

MySQL에는 어떤 잠금이 존재하며 어떻게 사용될까 1

MySQL Lock

MySQL의 잠금은 크게 MySQL Engine 잠금, Storage Engine 잠금 2개로 나눌 수 있습니다.

MySQL Engine레벨의 잠금은 모든 스토리지 엔진에 영향을 미칩니다.
InnoDB Storage Engine는 내부에서 레코드 기반의 잠금 방식을 사용하여 MyISAM방식보다 훨씬 뛰어난 동시성 처리를 제공합니다.

MySQL Engine Lock

글로벌 락

• MySQL에서 제공하는 잠금에서 가장 범위가 큼
• 글로벌 락을 얻은 세션을 제외한 다른 세션이 DDL, DML문장을 실행하는 경우 그로벌 락이 해제될 때까지 대기 상태로 남습니다.

flush tables with read lock 명령어를 사용하면 MySQL 서버 전체의 잠금을 얻습니다.

위의 명령어 입력 후 다른 Process의 DML이 대기 상태에서 기다림

💀mysqldump같은 명령어가 내부적으로Global Lock을 실행하고 백업을 할 때도 있습니다. mysqldump 옵션에 따라 어떤 잠금이 적용되는지 확인하자!

테이블 락

Lock TABLES table_name [ READ | WRITE]
개별 테이블 단위로 설정되는 잠금이며 명시적 또는 묵시적으로 특정 테이블의 잠금을 획득할 수 있습니다.
명시적인 테이블 락도 특별한 상황이 아니면 사용할 필요가 거의 없습니다.
묵시적 테이블 락은 Storage Engine차원에서 제공되며 DML 쿼리에서
발생하며 DML 쿼리 종로 후 즉시 잠금이 해제됩니다.

InnoDB의 경우 레코드 기반의 잠금을 제공하기 때문에 데이터 변경에 서나오는
묵시적 테이블 락이 설정되지 않습니다.
대부분 DML에서 무시되고 DDL의 경우에 영향을 미칩니다.

네임드 락
GET_LOCK()을 통해 사용되며 임의의 문자열에 대해 exclusive lock을 얻습니다.
동시성 제어가 필요하지만 그렇다고 새로운 인프라나 시스템을 구축하기 애매할 때
좋을 것 같습니다.

GET_LOCK() in docs
https://dev.mysql.com/doc/refman/8.0/en/locking-functions.html
Redis 사용안하고 MySQL로 분산 락 구현하기
https://techblog.woowahan.com/2631/

Metadata Lock
데이터 베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금
메타데이터 락은 명시적으로 획득하거나 해제할 수 있는 것이 아닙니다.

간단한 재현에서 알아보는 메타데이터 락
스키마를 수정하거나 하면 metadata_lock..같은 키워드와 함께
서버까지 문제가 생기는 일을 많이 겪어 해당 파트는 직접 실습도 해봅니다.
결론적으로 DDL을 날리는 세션에서 exclusive lock을 얻어서
다른 세션에서 해당 테이블을 조회 시 무한 sleep상태로 변하게 됩니다.(time_wait_out 설정이 없다면..)

😭 운영db 스키마 변경은 조심해서 하자 😭

1. 단순히 조회를 한 것만으로 메타데이터 락을 얻습니다.

2. shared lock이여서 여러 곳에서 잠금을 얻을 수 있습니다.

3. DDL 을 날리니 exclusive lock이 생겼습니다.
   추측하기로는 해당 행은 session3가 수정을 하기위하여 잠금을 얻었으니 앞으로 추가적으로 들어오는 session에서는 조회가 안될 것입니다.
   

참고 문서
Metadata_locks
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-metadata-locks-table.html
Metadata-Locking
https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html

InnoDB Storage Engine Lock

MySQL에서 제공하는 잠금 방식과 다르게 독자적인 잠금 방식을 탑재하고 있습니다.
그렇다 보니 MySQL 명령어를 통해 스토리지 엔진을 이용한 락을 조회하기가 까다로우나 최근 버전(8.0을 읽으니 8.0이 최근 버전이겠지..?)에서는information_schema 데이터베이스에 InnoDB와 관련된 내용을 확인 할 수 있습니다.

..

InnoDB Storage Engine Lock에는 4가지가 존재합니다.

레코드 락 : 레코드 자체만을 잠그는 것
다른 상용 DBMS의 레코드 락과 동일한 역할을 합니다.
특이한 점은 잠금 시 레코드 자체가 아니라 인덱스 레코드를 잠금합니다.

갭 락 : 다른 DBMS와의 차이, 레코드와 인접한 레코드 사이 간격을 잠그는 것, 레코드와 레코드 사이의 간격에 새로운 레코드가 생기는 것을 제어하며 넥스트 키 락의 일부로 자주 사용됩니다.

넥스트 키 락 : 레코드 락과 갭 락을 합쳐 놓은 형태의 락

자동 증가 락 : 자동 증가 PK가 사용되는 테이블에 여러 레코드가 Insert가 삽입될 때 증가하는 일련번호를 위해 사용되는 것

---

MySQL에는 어떤 잠금이 존재하며 어떻게 사용될까 2

InnoDB의 특이한 Lock

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

상황
먼저 멍멍이1이 그림을 좋아하는 고양이들의 이름을 철쭉이로 변경하는 중입니다.
그런데 갑자기 멍멍이2와 멍멍이3도 고양이의 이름을 변경하려고 합니다.
이 중에서 1마리의 멍멍이는 멈칫했습니다.
Q. 어떤 멍멍이가 당황했을까요?

고양이 정보

고양이들 이름을 바꿔보자!

멍멍이의 이름바꾸기 자세히((펼치기/접기가 안먹힌다!!))

🐰 불필요한 이름은 제거합니다.

초기 셋업입니다.
이름은 보기 쉽게 알파벳 + 숫자로 변경하였고 data_locks table은 비어있습니다.(favorite column은 index로 설정)

실제로 수정 대상은 a2 + drawing의 조합을 가진 고양이 1개의 행 이지만 아래 영역을 보면 data_locks에는 4개의 Record가 Lock이 걸렸습니다.

update cat set name = "c2" where name = "a2" and favorite = "drawing";

😭 제일 하단에 Sleep Type Cat이 Lock이 걸렸다. 😭
favorite index가 unique하지 않아서 추가된 gap lock으로 추측
격리 수준을 낮춰서 시도하면 gap lock이 사라질 것으로 예상됩니다.

Todo : ClustIndex Lock 학습하고 포스팅 내용 추가하기

---

격리수준에는 무엇인 존재할까

여러 트랜잭션이 동시에 처리될 때 다른 트랜잭션의 변경하는 데이터를 어떻게 볼 지 결정하는 것, 위에서 아래로 갈수록 격리수준이 엄격해집니다.
보통 다른 DB에서도 최소한 Read committed의 격리수준을 사용합니다.

PANTHOM READ 동일한 트랜잭션에서 같은 질의에서 없는 레코드가 추가되는 것
NON_REPEATABLE READ 동일한 트랜잭션에서 같은 질의에서 동일 레코드의 결과가 다른 것
DIRTY READ 다른 트랜잭션에서 commit을 안한 결과가 나오는 것

PANTHOM READ, NON_REPEATABLE READ 둘 다 비슷한 놈이다.
일각에서는 둘 다 같은 종류지만 PANTHOM READ를 Special case라고도 부름

1. READ UNCOMMITTED

일단 읽는다.
다른 트랜잭션이 Commit하지 않은 것을 읽는다는 것은 위험합니다.
Rollback예정인 Transaction에서의 변경 내역도 잡아와서 사용합니다.
격리수준 이름 그대로를 이해하면 됩니다.

PANTHOM READ O
NON_REPEATABLE READ O
DIRTY READ O

2. READ COMMITTED

확정된 놈만 읽는다.
다른 트랜잭션이 Commit한 것만 읽습니다. (commit전에는 Undo Log를 읽는다.)
이제 Rollback의 위험도 없으며 Oracle은 해당 격리수준이 기본 값입니다.
하지만 같은 Transaction에서 일관된 값을 읽지 않습니다.
해당 격리수준도 이름 그대로 이해를 해도 좋습니다.

PANTHOM READ O
NON_REPEATABLE READ O
DIRTY READ X

3. REPEATABLE READ

한 장면만 읽는다.
MySQL InnoDB의 기본 값입니다.
읽어야 하는 값은 자신의 트랜잭션 값보다 아래인 것만 조회합니다.
그리고 InnoDB는 Gap Lock, Next Key Lock, Record Lock을 모두 활용하기 때문에 해당 격리수준에서도 PANTHOM READ가 없습니다.

PANTHOM READ O ( InnoDB는 X )
NON_REPEATABLE READ X
DIRTY READ X

4. SERIALIZABLE

잠그고 읽는다.

PANTHOM READ X
NON_REPEATABLE READ X
DIRTY READ X

---

학습 해보기

현재 활성화 된 Lock 정보 조회
select * from performance_schema.data_locks;

1. Repeatable Read - Panthom Read ( MVCC 이것때문에 보장이 된다.)
2. 낙관적 락과 격리수준 상관관계