Transaction

트랜잭션
작업의 완전성을 보장해주는 것으로 논리적인 작업 세트를 모두 완벽하게 처리하거나 처리하지 못할 경우에는 원 상태로 복구해서 일부만 적용되는 Partial Update가 발생하지 않도록 만들어 데이터 정합성을 보장하기 위한 기능이다.

MySQL 스토리지 엔진 중 InnoDB만 트랜잭션을 지원한다.

주의사항

트랜잭션 또한 DBMS 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.
이는 프로그램 코드에서 트랜잭션의 범위를 최소화하라는 의미이다.

트랜잭션에 안좋은 영향을 미치는 부분
1. 트랜잭션이 불필요한 곳에 트랜잭션을 거는 경우
2. 네트워크 작업에 트랜잭션을 거는 경우
3. 성격이 다른 여러 논리 세트를 하나의 트랜잭션으로 처리하는 경우

Short 트랜잭션이 Long 트랜잭션이 되지 않도록 주의해야 한다
데이터베이스 커넥션은 개수가 제한적이어서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션의 개수는 줄어들 것이다. 그리고 어느 순간에는 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수 있다.

Lock

잠금
잠금은 동시성을 제어하기 위한 기능으로 여러 커넥션에서 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다.

잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.

MySQL 엔진 레벨 잠금은 모든 스토리지 엔진에 영향을 미치지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.

MySQL 엔진의 잠금

글로벌 락

글로벌 락은 실행과 동시에 MySQL 서버의 모든 변경 작업을 멈춘다.

SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.

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

백업 락

백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 된다.

• 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
• REPAIR TABLE과 OPTIMIZE TABLE 명령
• 사용자 관리 및 비밀번호 변경

하지만 백업 락은 글로벌 락과는 달리 일반적인 테이블 데이터 변경은 허용된다.

MySQL 서버의 구성은 소스 서버와 레플리카 서버로 구성되는데 주로 백업은 레플리카 서버에서 실행된다.

테이블 락

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

명시적 테이블 락은 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치기 때문에 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없다.

묵시적 테이블 락은 쿼리가 실행되는 동안 자동으로 획득했다가 쿼리가 완료된 후 자동으로 해제된다.

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

네임드 락

네임드 락은 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.

특징은 대상 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것이다.
네임드 락은 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금이다.

주로 분산 락 구현에 사용되는 등 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다.

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

MySQL 8.0 버전부터는 네임드 락을 중첩해서 사용할 수 있으며 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가됐다.

메타데이터 락

메타데이터 락은 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.

메타데이터 락은 명시적으로 획득하거나 해제할 수 있는 것이 아니고 테이블 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

MySQL DDL은 단일 스레드로 작동한다.

InnoDB 스토리지 엔진 잠금

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

잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락 또는 테이블 락으로 레벨업되는 경우는 없다.

레코드 락

레코드 자체만을 잠근는 것을 말하며 다른 사용 DBMS의 레코드 락과 동일한 역할을 한다.
한 가지 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다.
인덱스가 하나도 없는 테이블이더라도 내부적으로는 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 Next key lock 또는 Gap lock 을 사용하지만 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 레코드 자체에 대해서만 락을 건다.

갭락

레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만 잠그는 것을 의미한다.
갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다.
갭 락은 넥스트 키 락의 일부로 자주 사용된다.

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 Next key lock 이라고 한다.
InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.

그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다. 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

자동 증가 락

MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공한다.
AUTO_INCREMENT 컬럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다.
InnoDB 스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

주의사항

• AUTO_INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며 UPDATE나 DELETE 등의 쿼리에서는 걸리지 않는다.

• InnoDB의 다른 잠금과는 달리 AUTO_INCREMENT 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.

• AUTO_INCREMENT 락은 테이블에 단 하나만 존재하기 때문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 AUTO_INCREMENT 락을 걸면 나머지 쿼리는 AUTO_INCREMENT 락을 기다려야 한다.

• AUTO_INCREMENT 락을 명시적으로 획득하고 해제하는 방법은 없다.

• 자동 증가 값이 한 번 증가하면 절대 줄어들지 않는 이유가 AUTO_INCREMENT 잠금을 최소화하기 위해서다.

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.
즉 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.
만약 인덱스가 준비돼 있지 않다면 검색 조건에 걸린 모든 레코드에 락이 걸리게 된다.
이것이 MySQL의 InnoDB에서 인덱스 설계가 중요한 이유 또한 이것이다.

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

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

레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다.
MySQL 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행해 보면 잠금과 잠금 대기를 바로 확인할 수 있다.

다양한 락

비관적 락 (Optimistic Lock)

비관적 락은 전통적인 데이터베이스 락 메커니즘을 사용합니다. 비관적 락은 충돌이 발생할 확률이 높다고 가정하며, 데이터에 액세스하려는 사용자가 레코드에 락을 걸어 다른 사용자가 동시에 수정하지 못하게 합니다. 이로 인해 데이터 충돌이 미리 방지됩니다.

비관적 락에서 주로 사용되는 락 유형은 다음과 같습니다.

• 공유 락(Shared Lock): 읽기 작업을 수행할 때 사용되며, 여러 사용자가 동시에 같은 레코드를 읽을 수 있지만, 레코드를 수정할 수는 없습니다.

• 배타적 락(Exclusive Lock): 쓰기 작업을 수행할 때 사용되며, 배타적 락이 걸린 레코드는 오직 하나의 트랜잭션만 수정할 수 있습니다. 다른 트랜잭션은 해당 레코드에 접근할 수 없습니다.

일반 select 는 별다른 lock 이 없기때문에 조회는 가능합니다.

낙관적 락 (Pessimistic Lock (exclusive lock))

낙관적 락에서는 실제로 전통적인 락 메커니즘이 사용되지 않습니다. 대신, 낙관적 락은 데이터 충돌이 발생할 확률이 낮다고 가정하고 데이터를 관리합니다. 이 방식에서는 레코드의 버전 정보를 추적하여 동시성 제어를 수행합니다.

버전 정보를 추적하는 방법은 다음과 같습니다.

• 버전 번호: 레코드가 수정될 때마다 증가하는 숫자를 사용하여 버전을 관리합니다.

• 타임스탬프: 레코드가 수정된 시간을 저장하여 버전을 관리합니다.

낙관적 락에서는 충돌이 발생할 경우, 충돌이 발생한 트랜잭션은 롤백되거나 재시도됩니다.
하지만 낙관적 락은 코드가 더러워지고 실패 처리가 까다롭다.

분산 락

분산 락(Distributed Lock)은 여러 서버, 프로세스 또는 노드 간에 공유되는 공유 리소스에 대한 동시 액세스를 조절하는 데 사용되는 동기화 메커니즘입니다. 분산 시스템에서 데이터 무결성과 일관성을 유지하기 위해 사용됩니다.

일반적인 락과 마찬가지로, 분산 락은 한 번에 하나의 클라이언트만이 공유 리소스에 액세스하거나 수정할 수 있도록 보장합니다. 그러나 분산 락은 분산 시스템의 환경에서 작동하므로, 이러한 락을 관리하기 위한 추가 전략과 메커니즘이 필요합니다.

최범균님 - 분산 락 (Distributed Lock)
우아한형제들 기술블로그 - 분산 락 (Distributed Lock)

공유 락(Shared Lock, S-Lock)

공유 락은 트랜잭션이 레코드를 읽기 위해 사용하는 락입니다. 공유 락은 여러 트랜잭션이 동시에 같은 레코드를 읽을 수 있게 해줍니다. 그러나 공유 락이 걸린 레코드는 배타적 락이 걸린 다른 트랜잭션에 의해 수정되지 못합니다.

InnoDB에서 일반적인 SELECT 쿼리는 공유 락을 사용하지 않고 레코드를 조회합니다. 그러나 트랜잭션 내에서 명시적으로 "SELECT ... FOR SHARE" 쿼리를 사용하여 공유 락을 걸 수 있습니다. 이렇게 하면 해당 레코드를 읽는 동안 다른 트랜잭션에 의한 수정을 방지할 수 있습니다.

배타적 락(Exclusive Lock, X-Lock)

배타적 락은 트랜잭션이 레코드를 수정하기 위해 사용하는 락입니다. 배타적 락이 걸린 레코드는 오직 하나의 트랜잭션만 수정할 수 있으며, 다른 트랜잭션은 해당 레코드에 접근할 수 없습니다. InnoDB에서는 INSERT, UPDATE, DELETE 등의 쓰기 작업을 수행할 때 자동으로 배타적 락이 걸립니다.

S락 끼리는 호환되고, X락이 끼면 호환되지 않는다.

이게 무슨 말이냐면, 1번 트랜잭션이 어떤 레코드 A에 대해 S락을 얻었다고 가정합시다.

• 2번 트랜잭션에서 레코드 A에 S락을 시도하면 성공합니다.
  왜냐하면 S락 끼리는 호환되기 때문입니다.
• 그러나 A레코드에 X락을 시도하면 대기상태에 빠집니다. (예를들어 UPDATE 쿼리)
  왜냐하면 이미 1번 트랜잭션이 S락을 가진 상태기 때문에 호환되지 않는 X락을 동시에 걸수가 없습니다.
  1번 트랜잭션이 종료되면 대기하던 2번 트랜잭션이 그제서야 X락을 획득하게 됩니다.
• 반대로 X락이 먼저 걸려있는 레코드에 S락을 시도하면, S락 시도도 대기상태에 빠집니다

InnoDB는 S락을 사용하지 않는다.

InnoDB는 또한 다중 버전 동시성 제어(MVCC, Multi-Version Concurrency Control)라는 메커니즘을 사용하여 동시성을 더욱 향상시킵니다. MVCC를 사용하면 일반적인 SELECT 쿼리가 공유 락을 걸지 않고도 일관된 데이터 스냅샷을 읽을 수 있습니다. 이 방식은 읽는 위치를 달리해 공유 락을 사용하지 않아도 데이터의 일관성을 유지하면서 동시성을 높이는 데 도움이 됩니다.

Isolation Level

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

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

READ UNCOMMITED

Dirty read 문제

READ UNCOMMITED 격리수준에서는 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상인 Dirty read가 발생한다. Dirty read 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래한다.

READ COMMITTED

Dirty read 문제 해결

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

NON-REPEATABLE READ 문제

하지만 READ COMMITED 격리 수준에서도 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는 NON-REPEATABLE READ 가 발생해 REPEATABLE READ 정합성에 어긋난다.

READ COMMITTED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장이 차이가 별로 없지만 REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동한다.

REPEATABLE READ

NON-REPEATABLE READ 문제 해결

REPEATABLE READ 격리수준에서는 트랜잭션 번호가 부여 돼 NON-REPEATABLE READ 부정합이 발생하지 않는다.
InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경한다.

REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다.
더 정확하게는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다.
따라서 모든 SELECT 쿼리는 트랜잭션 번호가 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 변경하는 것만 보게 된다.

READ COMMITTED도 MVCC를 이용해 COMMIT 되기 전의 데이터를 보여주지만 REPEATABLE READ와 READ COMMITTED의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다.

PHAONTOM READ 문제 (InnoDB 스토리지 엔진에서는 발생 안함)

REPEATABLE READ 격리 수준에서는 다른 트랜잭션에서 수행한 변경 작업에 의한 레코드가 보였다 안 보였다 하는 현상인 PHANTOM READ가 문제가 발생한다.

SELECT ... FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데 언두 레코드에는 잠금을 걸 수 없다. 그래서 SELECT ... FOR UDPATE나 SELECT ... LOCK IN SHARE MODE로 조회하는 레코드는 언두 영역 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다.

InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 팬텀 리드가 발생하지 않습니다. 이 메커니즘은 현재 레코드와 그 다음 레코드 사이의 간격에 락을 걸어 새로운 레코드 삽입을 차단합니다. 이렇게 하여 트랜잭션 진행 중 해당 범위에 새로운 레코드가 삽입되지 않도록 하여 팬텀 리드를 방지하게 됩니다.

SERIALIZABLE

가장 단순하면서도 가장 엄격한 격리 수준은 SERIALIZABLE 격리 수준이다. 이 격리 수준은 높은 데이터 일관성을 제공하지만, 동시 처리 성능이 다른 트랜잭션 격리 수준에 비해 떨어진다.

InnoDB 테이블에서 기본적으로 일반 SELECT 작업은 "잠금이 필요 없는 일관된 읽기(Non-locking consistent read)"를 사용하여 레코드 잠금 없이 실행됩니다. 이 방식은 데이터의 일관성을 유지하면서 동시성을 높인다.

그러나 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면, 읽기 작업에도 공유 잠금(읽기 잠금)을 획득해야 한다. 이로 인해 다른 트랜잭션에서 해당 레코드를 변경할 수 없게 된다. 이 격리 수준에서는 동시 처리 성능이 상대적으로 떨어지지만, 높은 데이터 일관성을 보장한다.