Chapter 5. 트랜잭션과 잠금

[hubtwork]

Chapter Ownership @KangHun-Lee

[CHOICORE]

05. 트랜잭션과 잠금

Lock

락의 종류

• 테이블 락 (Table Lock) : 테이블 전체를 잠그는 방식
  • MyISAM에서 사용
  • 테이블 단위로 잠금을 걸기 때문에 동시성이 떨어짐
  • 테이블 단위로 잠금을 걸기 때문에 데드락이 발생할 수 있음
• 네임드 락 (Named Lock) : 이름을 지정하여 잠금을 거는 방식
  • 복잡한 요건으로 레코드를 변경할 때, 배치 작업으로 인한 데드락 방지에 유용
• 메타데이터 락 (Metadata Lock) : 데이터베이스의 메타데이터를 잠그는 방식
  • 데이터베이스 객체의 생성, 변경, 삭제 등의 작업을 수행할 때 사용
  • 데이터베이스 객체의 락을 걸어 다른 사용자가 해당 객체를 변경하지 못하도록 함
  • 테이블을 교체하거나 백업할 때 MySQL 서버 입장의 객체들을 조작할 때 거는 락
• 레코드 락 (Record Lock) : 레코드 단위로 잠그는 방식
  • InnoDB에서 사용
  • 레코드 단위로 잠금을 걸기 때문에 테이블 락보다 동시성이 높음
• 갭 락 (Gap Lock) : 인덱스의 갭을 잠그는 방식
  • InnoDB에서 사용
  • 레코드 락과 함께 사용되어 레코드 사이의 갭을 잠그는 방식
• 넥스트 키 락 (Next-Key Lock)
• 자동 증가 락 (Auto Increment Lock)

---

Auto Increment Lock

MySQL에서는 Auto Increment 값을 증가시키는 동안 Auto Increment Lock이 발생할 수 있다. 이는 해당 테이블의 레코드 삽입 작업이 이루어질 때 발생하는 테이블 레벨의 락이다. 이로 인해 해당 테이블에 대한 모든 작업이 일시 중단된다.

Auto Increment Lock은 다음과 같은 상황에서 발생한다.

• INSERT INTO ... 쿼리를 사용할 때
• REPLACE INTO ... 쿼리를 사용할 때

트랜잭션의 여부와 상관없이 AUTO_INCREMENT 값을 가져오는 시점에만 잠긴다.

(Auto Increment 이 적용된 열에 명시적으로 값을 설정하더라도 해당 락이 적용됨)

5.1 버전부터는 innodb_autoinc_lock_mode라는 설정을 통해 Auto Increment Lock 모드를 조절할 수 있습니다.

• innodb_autoinc_lock_mode = 0: 모든 INSERT 문장에 대해 AUTO_INCREMENT 값을 가져올 때마다 락을 걸게 된다.
• innodb_autoinc_lock_mode = 1: MySQL 서버가 삽입되는 레코드 수를 정확하게 예측할 수 있는 경우 개선된 래치(mutex) 메커니즘을 사용한다. 이 경우 필요한 AUTO_INCREMENT 값을 가져온 후 락을 즉시 해제함
• innodb_autoinc_lock_mode = 2: AUTO_INCREMENT 값을 가져올 때 InnoDB 스토리지 엔진은 절대 자동 증가 락을 걸지 않고 래치(mutex)를 사용한다. 하나의 INSERT 문장으로 삽입되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하지 않고 유니크한 값을 생성한다는 것만 보장

InnoDB의 래치(mutex)는 동시성 제어를 위한 도구로, 여러 스레드가 동시에 데이터에 접근하는 것을 방지한다. 이는 데이터의 일관성을 유지하고 동시에 여러 작업을 안전하게 수행할 수 있게 하는데, 래치는 락과 비슷하지만, 래치는 일반적으로 더 낮은 수준에서 작동하고, 짧은 시간 동안 유지된다.

InnoDB의 두 가지 주요한 래치 유형, 공유 래치와 배타적 래치

공유 래치는 여러 스레드가 동시에 데이터를 읽을 수 있게 해주지만, 데이터를 변경하는 것은 허용하지 않는다. 반면에 배타적 래치는 한 스레드만이 데이터에 접근하고 변경할 수 있게 한다.

흥미로웠던 점

자동 증가 락이 당연히 있어야 순서 보장을 할 것이라 생각했는데, 버전 별로 설정을 통해 조절할 수 있다는 것이 신기했다. 8.0 버전 부터는 innodb_autoinc_lock_mode의 기본값이 2로 변경되어서 래치로 처리하게 되어 더 이상 자동 증가 락이 발생하지 않고, 순서대로 채번되는 것이 아닌 유니크한 값을 생성한다는 사실을 알게 되었다.

---

Index Lock

InnoDB의 잠금은 레코드(데이터 로우) 를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 변경행랴할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠그는 방식이다. 그렇기 때문에 MySQL의 인덱스 잠금을 알지 못한다면, 데이터베이스의 성능을 떨어뜨리는 원인이 될 수 있다. 질의 성능을 높이기 위해서는 인덱스 잠금을 이해하고 적절히 사용하는 것이 중요하다.

흥미로웠던 점

조회 성능을 위해 복합 인덱스, 인덱스를 가지고 질의 조건을 만들어내야하는 방식은 알고 있었지만, 데이터를 변경해야할 때 질의 조건에 따라 성능 차이가 발생한다는 것을 처음 알게 되었다.

---

MySQL의 격리 수준 (isolation level)

트랜잭션의 격리 수준은 트랜잭션 간의 데이터 접근을 어떻게 제어할 것인지를 결정하는 것이다.

• READ UNCOMMITTED: 다른 트랜잭션이 커밋하지 않은 데이터를 읽을 수 있다. (Dirty Read)
• READ COMMITTED: 다른 트랜잭션이 커밋한 데이터만 읽을 수 있다. (Non-Repeatable Read)
• REPEATABLE READ: 같은 쿼리를 실행해도 결과가 항상 같다. (Phantom Read)
• SERIALIZABLE: 트랜잭션 간의 데이터 접근을 완전히 격리한다.

격리 수준	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	O	O	O
READ COMMITTED	X	O	O
REPEATABLE READ	X	X	O (InnoDB 제외)
SERIALIZABLE	X	X	X

• Dirty Read: 다른 트랜잭션이 커밋하지 않은 데이터를 읽을 수 있는 현상
• Non-Repeatable Read: 한 트랜잭션 내에서 같은 쿼리를 여러번 실행했을 때, 결과가 일관되지 않게 나타나는 현상
• Phantom Read: 다른 트랜잭션의 변경 사항으로 인해 같은 쿼리를 실행 했을 때 , 쿼리의 결과 집합 자체가 변경되는 현상

REPEATABLE READ

MySQL에서는 기본적으로 REPEATABLE READ 격리 수준을 사용한다. (Non-Repeatable Read, Phantom Read 방지)를 위한 최소 수준의 격리 수준이다.

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 ID를 가지며, 이를 통해 트랜잭션의 격리 수준을 관리한다. 언두 영역에 백업 된 레코드는 트랜잭션 ID도 함께 관리되고 있어 트랜잭션 ID를 통해 이전의 데이터를 참조할 수 있다.

하지만 SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE와 같은 명령을 사용하면 SELECT 해야하는 레코드에 쓰기 잠금을 걸게 되는데 언두 영역의 변경 전 데이터를 가져오는게 아니라 현재 최종 커밋된 레코드의 값을 가져오게 된다.

흥미로웠던 점

A 사용자와 B 사용자가 동시에 같은 데이터를 읽고 쓰는 상황에서 겪을 수 있는 부정합 상황을 크게 의식하지 못했었는데, A 사용자가 트랜잭션을 먼저 열고 트랜잭션을 종료하지 않은 상황에서 B 사용자가 트랜잭션을 열고 데이터를 변경하고 커밋하면 A 사용자는 변경 된 데이터를 읽으면서 부정합 문제가 발생할 수 있고, 이를 방지하기 위해 고유의 트랜잭션 ID를 부여하여 A 사용자가 바라볼 수있는 트랜잭션 ID를 통해 이전 데이터를 참조할 수 있게 한다는 것을 알게 되었다. 그리고, 쓰기 잠금을 걸어야하는 경우는 언두 영역의 변경 전 데이터를 가져오는게 아니라 현재 최종 커밋된 레코드의 값을 가져오게 된다는 것도 알게 되었다.

[KangHun-Lee]

5. 트랜잭션과 잠금

요약 및 정리

트랜잭션

하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나 아무것도 적용되지 않아야 함을 보장해 주는 것

 MyISAM

• 트랜잭션 관점에서 1,2,3 데이터 삽입시 3 의 중복으로 실패해도 1,2 는 삽입 되어 있음 (=Memory 스토리지 엔진)
• 이러한 현상을 부분 업데이트 (Partial Update) 라고 표현
• 데이터 정합성을 맞추는데 상당히 어려움

 InnoDB

• 쿼리 중 오류가 발생하면 전체를 원 상태로 복구

 주의사항

• DB 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화해야 함
• 코드에서 라인 수는 한두 줄이더라도 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야 한다.

 네트워크 작업 예시

1. 외부 API 호출
2. 파일 시스템 접근
3. 3.메일 서버를 통한 이메일 전송

MySQL 엔진의 잠금

크게 스토리지 엔진 과 MySQL 엔진 레벨로 나뉨

• MySQL 엔진 레벨의 잠금은 스토리지 엔진에 여향을 미침
• 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 영향을 미치지 않음
• 테이블 데이터 동기화를 위한 테이블 락
• 테이블의 구조를 잠그는 메타데이터 락
• 사용자의 필요에 맞게 사용할 수 있는 네임드 락

 글로벌 락

• SELECT를 제외한 대부분의 DDL 문장, DML 문장이 대기 상태로 남는다
• 미치는 영향 범위는 MySQL 서버 전체(작업 대상 테이블/데이터베이스가 달라도 영향을 받음)
• FLUSH TABLES WITH READ LOCK MySQL 서버에 존재하는 모든 테이블을 닫고 잠금
  • 최악의 경우 장시간 실행되는 쿼리와 실행되면 INSERT, UPDATE, DELETE 쿼리가 오랜 시간 실행되지 못함
• 웹 서비스용으로 사용되는 MySQL 에서는 가급적 사용하지 않는 것이 좋다
• 가벼운 글로벌 락의 필요성으로 Xtrabackup, Enterprise Backup 과 같은 백업 락이 도입됨
• 백업 락을 획득시 아래 정보를 변경 할 수 없음
  • 데이터베이스 및 테이블 모든 객체 생성 및 변경, 삭제
  • REPAIR TABLE과 OPTIMIZE TABLE 명령
  • 사용자 관리 및 비밀번호 변경

  •  테이블 락

• 명시적 또는 묵시적으로 특정 테이블 락 획득 가능
  • 명시적 락은 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없음
  • 묵시적 락은 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제
  • InnoDB 의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공
  • 그래서 단순 데이터 변경 쿼리로 묵시적 테이블 락이 설정 되지 않음
  • 더 정확히는 테이블 락이 설정되지만 대부분 DML 쿼리에서 무시되고, DDL 쿼리의 경우에만 영향을 미침

 네임드 락

• GET LOCK() 함수로 임의의 문자열에 대해 잠금 설정
  • 테이블이나 레코드 또는 Auto Increment와 같은 데이터베이스 객체가 아님
  • 사용자가 지정한 문자열에 대해 획득하고 반납
  • 자주 사용되지 않음
  • 8.0 부터는 중첩해서 사용할 수 있게 됨(현재 세션에서 획득한 네임드 락 모두 해제 가능)

 메타데이터 락

• 데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금
  • 명시적으로 획득하거나 해제할 수 없음
  • 메타데이터 잠금과 InnoDB 트랜잭션을 동시에 사용해야 하는 경우도 있음

InnoDB 스토리지 엔진 잠금

레코드 기반의 잠금 방식을 탑재하고 있음

• MyISAM 보다 훨씬 뛰어난 동시성 처리를 제공함
• 트랜잭션과 잠금, 잠금 대기중인 목록 조회 및 종료 가능
  • infomation_schema의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블
  • 조금씩 Deprecated 되고 있음(performance_schema의 data_locks, data_lock_waits)

 레코드 락

• 레코드 자체가 아니라 인덱스의 레코드를 잠금
• 인덱스가 없는 테이블이라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정

 갭 락

• 레코드와 바로 인접한 레코드 사이의 간격만을 잠금
• 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어

 넥스트 키 락

• 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금
• 의외로 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생
  • 가능하면 바이너리 로그 포맷을 ROW 형태로 바꾸는게 좋음(8.0 부터는 기본 설정)

 자동 증가 락

• INSERT, REPLACE 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요
  • UPDATE, DELETE 에서는 걸리지 않음

인덱스와 잠금

적절한 인덱스가 준비되어 있지 않다면 동시성이 상당히 떨어짐 레코드를 변경해야 할 경우 검색한 인덱스의 레코드를 모두 락을 걸게 됨

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

• performance_schema의 data_locks, data_lock_waits

MySQL의 격리 수준

크게 4가지로 나뉘며, 하위로 갈수록 데이터 격리(고립) 정도가 높아짐

1. READ UNCOMMITTED (거의 사용하지 않음)
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE

SERIALIZABLE 격리 수준이 아니면 크게 성능의 개선이나 저하는 발생하지 않음

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

 READ UNCOMMITTED

• 트랜잭션에서의 변경 내용이 COMMIT 이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보임

 READ COMMITTED

• COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회 가능
  • Undo 영역으로 백업된 데이터로 인해 COMMIT 수행 전에도 조회가 가능하나 COMMIT 완료되기 이전의 데이터가 보임
  • NON-REPEATABLE READ 문제

 REPEATABLE READ

• InnoDB 스토리지 엔진 기본 값
• NON-REPEATABLE READ 부정합이 발생하지 않음
• 단 PHANTOM READ 는 발생할 수 있음
  • InnoDB는 발생하지 않음(갭 락과 넥스트 키 락 때문에)
  • 예외 : 잠금을 동반한 SELECT 쿼리는 발생함

 SERIALIZABLE

• 가장 단순한 격리이면서 동시에 가장 엄격한 격리
• 읽기 작업도 공유 잠금을 획득해야 함
• 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없음

[hubtwork]

우선 전체적으로 MyISAM 시절에는 존재하지 않았던 트랜잭션은 이제 RDBMS 전반적으로 중요한 개념이 되었고, InnoDB 는 이 부분을 훌륭히 지원한다는 점에 감사하다. 다음과 같은 특성은 특히나 InnoDB 스토리지 엔진을 다루고 있는 우리에게 중요하다고 생각한다.

• Record Lock : Index 의 레코드를 이용한 잠금 ( 잠금 전파 영향 고려해야함. but Table Lock 보다 잠금 범위가 적어 동시성 처리 용이 ) - 인덱스 대상이 아닐 경우 기본적으로 PK - Clustered Index 활용
• Gap Lock & Next Key Lock - 명시적으로 활용할 일은 없으나, INSERT 동작이나 Replication 에서 발생하는 문제점을 보완
• AutoIncrement Lock - A.I. 를 위해 각 테이블 마다 필요하다면 1개씩 존재.

  여기서 재밌는 점. 실시간성 과 정합성 은 같이 갈 수 없다 라는 점을 A.I. Lock 에서 명실 상부히 보여주고 있는 점 ( Mutex 기반일 경우, 순서보장 X ) Index 기반의 Record Lock 을 사용하기 때문에 Update 구문을 위한 Select 구문의 Lock 전파 에 대한 이해.. 굿

또한 내가 수 차례 말하던 트랜잭션의 범위 축소 및 격리 ( 비즈니스 로직에서 부가로직의 분리 등과 함께 달성 ) 이 더 중요해지는 이유는 다른 RDBMS 와는 다른 특징인 Index 기반의 Record Lock 때문에 영향범위 또한 더 커지는 점이 있다. 이 부분도 절대적으로 기억하고, 사실 비즈니스 로직 레벨에서는 이런 조회 및 수정 자체가 없을 경우가 99% 지만 배치 애플리케이션 을 구성하여 Job 을 구성할 때, 이를 유념하여 작성할 것.

뭐 격리 수준이야.. 이번에 면접 때 대답 까먹었던 터라 ㅋㅋㅋ 다시 한번 보고 아 이런거였지.. 싶었다.. ( 여러분은 면접 때 까먹지 마시길 ㅋㅋ ) 그리고 SERIALIZABLE 은 진짜 속도가 너무 안나오는 수준의 격리레벨이라 안써봤다... 써본 사람은 후기좀요;;

[zinokim]

Chapter 05. 트랜잭션과 잠금

Transaction and Lock

• Transaction: 데이터의 정합성을 보장하기 위한 기능
• Lock: 동시성을 제어하기 위한 기능

Transaction

• MyISAM: 트랜잭션 지원하지 않음
• InnoDB: 트랜잭션 지원
• 트랜잭션은 쿼리의 수와 관계없이 데이터의 정합성을 보장해주기 위한 기능
• Database Connection 시작 시점과 종료 시점 주의

MySQL Isolation Level

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE
• DIRTY READ
• NON-REPEATABLE READ
• PHANTOM READ

후기(?)

• InnoDB Storage Engine Lock
• Auto Increment Lock
• Index and Lock
• 실제 MySQL을 사용하며 Framework 또는 Database에서 기본값으로 획득하는 Lock을 사용했는데 구체적으로 이해하고 사용하는 데 도움이 될 것 같아 좋았음
• InnoDB의 Record 수준의 Lock에 대한 이해를 할 수 있어 실제 업무에 적절하게 사용할 수 있는 연습 필요
• 모호하게 알고 있던 MySQL Isolation Level에 대해 알게 된 것이 좋았으나, 실제 Spring ApplicationEventListener 등을 사용할 때 적절하게 사용할 수 있도록 복습 예정

[hubtwork]

e.g. API 요청 분리 및 사이드 이펙트 처리보장 로직

business {
  Transaction 처리() // @Transactional
  business_success_event 발행()
}

@TransactionalEventListener(BEFORE_COMMIT)
successHandler {
  push_request_history DB저장() // INIT
}

@Async
@TransactionalEventListener(AFTER_COMMIT)
pushSender {  
  API 통한 Push 요청()
  push_request_history DB 상태 변경() // FAIL or SUCCESS
}

@Scheduled
pushChecker {
  push_request_history bulk 조회 및 재시도 처리() // INIT or FAIL
}