[Real MySQL 8.0]5장 - 트랜잭션

 

이번 장에서는 MySQL 동시성에 영향을 미치는 잠금, 트랜잭션, 트랜잭션 격리 수준에 대해 알아보겠습니다.

 

트랜잭션은 작업의 완전성을 보장해주는 것으로 논리적인 작업을 모두 완벽하게 처리하거나 모두 적용 안되게 만들어 주는 기능입니다.

 

잠금과 트랜잭션은 서로 비슷한 개념 같지만 사실 잠금은 동시성 제어를 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하는 기능입니다. 하나의 회원 정보 레코드를 여러 커넥션에서 동시에 변경하려고 하는데 잠금이 없다면 하나의 데이터를 여러 커넥션이 동시에 변경하게 됩니다. 결과적으로 해당 레코드의 값을 예측할 수 없는 상태가 되기 때문에 순서대로 한 시점에 하나씩 변경하는 역할을 합니다. 격리 수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 것입니다.

 

이렇게 글로 보면 감이 안오는데 대충 정리하자면

• 트랜잭션 : DB가 작업을 수행하는 논리적 단위 이 과정에서 ACID를 지킴
• 잠금 : 데이터에 대한 접근 권한을 제어, 이 권한은 트랜잭션 사이에서 제어함

 

📝5.1 트랜잭션

🧷5.1.1 MySQL에서의 트랜잭션

트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미하는 개념이 아닙니다.

트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 100% 적용되거나 아무것도 적용되지 않아야 함을 보장해 주는 것입니다.

 

간단한 예제로 InnoDB와 MyISAM 테이블의 차이로 트랜잭션의 특징을 살펴봅시다.

use new_schema;

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);

위처럼 M  InnoDB와 MyISAM 테이블을 만들어줍니다.

 

SET autocommit=ON;
INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3);

이 상태에서 추가 레코드를 넣으면서 마지막에 중복 프라이머리 키를 넣으면 어떻게 될까요??

 

물론 두 테이블 모두 프라이머리 키로 중복 오류가 발생합니다. 그런데 두 테이블의 레코드를 조회해보면 

MyISAM 테이블에 오류가 발생햇음에도 1과 2가 INSERT된 것을 알 수 있습니다.

 

결론적으로 MyISAM은 트랜잭션의 개념이 없기 때문에 추가 중에 마지막에 오류가 일어나도 롤백하지 않는 것이지요

 

 

이러한 부분 업데이트 현상은 테이블 데이터 정합을 맞추는데 매우 어려운 문제를 만들어냅니다. 오류 발생 시에 비즈니스 로직에 따라 사용자가 직접 DB를 건들여야하는 상황이 나옵니다.

 

이러한 상황을 막는 것이 100% 성공하거나 아무것도 적용되지 않게 하는 트랜잭션입니다.

🧷5.1.2 주의사항

트랜잭션 또한 DBMS 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋습니다.

이는 프로그램 코드에서 트랜잭션의 범위를 최소화하라는 의미입니다. 

 

예시로 배워보겠습니다. 

1. 처리 시작
2. 사용자의 로그인 여부 확인
3. 사용자의 글쓰기 내용의 오류 여부 확인
4. 첨부로 업로드된 파일 확인 및 저장
=> 데이터베이스 커넥션 생성 (또는 커넥션 풀에서 가져오기)
=> 트랜잭션 시작
5. 사용자의 입력 내용을 DBMS에 저장
6. 첨부 파일 정보를 DBMS에 저장
<= 트랜잭션 종료 (COMMIT)
7. 저장된 내용 또는 기타 정보를 DBMS에서 조회
8. 게시물 등록에 대한 알림 메일 발송 (= 네트워크 작업)
=> 트랜잭션 시작
9. 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료 (COMMIT)
<= 데이터베이스 커넥션 종료 (또는 커넥션 풀에 반납)
10. 처리 완료

 

분석: 트랜잭션 적용 시 비효율적인 부분

1. 1~4번 작업
   • 이 단계는 데이터베이스 커넥션이나 트랜잭션과 상관없는 작업입니다.
   • 트랜잭션 시작 시점을 이후로 미루는 것이 좋습니다.
2. 8번 작업
   • 네트워크 작업은 시간이 오래 걸릴 가능성이 높습니다.
   • 이 작업을 트랜잭션 안에 포함하면, 불필요하게 트랜잭션이 유지되어 자원이 낭비됩니다.
   • 해결 방법: 네트워크 작업은 트랜잭션 범위에서 제외하거나, Spring에서 비동기 작업으로 처리하는 것이 좋습니다.
3. 5~6번 작업
   • 하나의 테이블을 참조하거나, 관련된 데이터를 저장하는 경우는 하나의 트랜잭션으로 묶는 것이 적합합니다.
   • 이는 데이터 정합성을 보장하고, 작업 간 충돌을 방지하는 데 효과적입니다.

 

📝MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있습니다. MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 됩니다. 

MySQL 엔진 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 잠금은 스토리지 간에 영향을 미치지 않습니다.

 

🧷글로벌 락

글로벌락은 FLUSH TABLES WITH READ LOCK  명령으로 흭득 가능합니다.

해당락은 MySQL에서 제공되는 잠금 중에 가장 범위가 큽니다. 일단 한 세션에서 글로벌 락을 흭득하면 다른 세션에서 SELECT를 제외한 DDL, DML 전부 글로벌 락이 해제될 때까지 버퍼링됩니다. 

범위는 서버 전체이며 한마디로 정리하자면 MySQL 서버의 모든 변경 작업을 멈춥니다.

 

보통 글로벌락은 서버 백업 중에 사용합니다.

 

 

🧷테이블 락

테이블락은 개별 테이블 단위로 설정되는 잠금입니다. 

명시적, 묵시적으로 특정 테이블 락을 흭득 가능합니다.

• 명시적
  • LOCK TABLES table_name [ READ | WRITE ] 명령으로 특정 테이블의 락 획득 가능
  • UNLCOK TABLES 로 잠금 반납
• 묵시적
  • MyISAM이나 MEMORY 테이블에서 데이터 변경 쿼리 실행시 발생합니다. 
  • InnoDB는 DDL 사용 시에 발생합니다.(데이터 변경 시에는 레코드 범위 락입니다.)

 

🧷네임드 락

맞습니다! 네임드 락은 사용자가 지정한 문자열에 대해 잠금을 설정하는 방식입니다. 이 잠금은 테이블이나 레코드와 같은 특정 데이터베이스 객체에 대한 잠금이 아니라, 단순히 사용자가 정의한 이름을 기준으로 잠금을 설정하고 해제합니다.

 

어려운 거 같지만 그냥 사용자가 정의한 이름의 락을 만드는 것입니다. 이것은 여러 클라이언트가 상호 동기화 처리를 해야할때 주로 사용됩니다.

 

잘 사용하지는 않습니다.

 

🧷메타데이터 락

메타데이터 락은 데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금입니다. 메타데이터는 묵시적으로 획득하게 되는데 RENAME TABLE tab_a TO tab_b 처럼 테이블의 이름을 변경하는 경우 자동으로 획득합니다.

참고로 해당 변경 시 이름에 대해 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정합니다. 

주요 포인트:

• 자동 획득: RENAME TABLE, ALTER TABLE 등 데이터베이스 객체의 이름이나 구조를 변경할 때 자동으로 메타데이터 락이 획득됩니다.
• 이름에 대한 락: RENAME TABLE tab_a TO tab_b와 같은 명령을 실행하면 원본 이름과 변경될 이름 두 개 모두에 잠금이 설정됩니다.
• 잠금의 중요성: 메타데이터 락이 없다면, 두 개의 이름 변경 작업을 동시에 실행할 경우 이름을 찾을 수 없는 오류가 발생할 수 있습니다.

 

📝5.3 InnoDB 스토리지 엔진 잠금

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

해당 기능때문에 MyISAM보다 훨씬 뛰어난 동시성 처리를 제공할 수 있습니다.

 

🧷5.3.1 레코드 락

레코드 자체만을 잠그는 것을 레코드 락이라고 합니다.

다른 상용 DBMS의 레코드락과 동일한 역할을 하지만 차이점은 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠급니다. 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터링 인덱스를 이용해 잠금을 설정합니다.

 

인덱스를 사용하는 이유:

• 빠른 검색: 인덱스는 데이터를 빠르게 찾을 수 있도록 도와줍니다. 기본 키(primary key)나 유니크 인덱스는 각 레코드를 고유하게 식별하므로, 검색이 매우 효율적입니다.
• 정확한 잠금: 인덱스를 기반으로 잠금을 설정하면, 특정 레코드에 대해서만 잠금을 설정할 수 있어, 더 효율적이고 정확하게 동시성을 제어할 수 있습니다.
• 자동 생성된 클러스터링 인덱스: 테이블에 명시적인 인덱스가 없더라도, InnoDB는 클러스터링 인덱스(기본적으로 기본키를 인덱스로 사용)를 이용하여 내부적으로 잠금을 설정합니다.

🧷5.3.2 갭 락

갭 락은 InnoDB 스토리지 엔진의 독특한 특징으로, 다른 DBMS와 차별화되는 중요한 개념입니다. 갭 락은 레코드 자체를 잠그는 것이 아니라 레코드와 레코드 사이의 간격만을 잠그는 것입니다. 이 잠금의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것입니다.

갭 락은 보통 WHERE 절에 의해 생성됩니다.

특히, 갭 락은 그 자체보다는 넥스트 키 락(Next-Key Lock) 의 일부로 자주 사용됩니다.

 

🧷5.3.3 넥스트 키 락

넥스트 키 락은 갭 락과 레코드 락을 결합한 형태로, 레코드와 그 레코드와 인접한 간격(갭)을 동시에 잠그는 특성을 가집니다. 이 락은 InnoDB에서 REPEATABLE READ 격리 수준을 사용할 때 팬텀 리드를 방지하는 중요한 메커니즘으로 사용됩니다.

핵심 포인트:

• 넥스트 키 락은 범위 락을 걸어 두 번 읽기 시 중간 변경을 방지합니다. 이렇게 해서 트랜잭션이 데이터를 읽을 때, 다른 트랜잭션에서 레코드 삽입이나 수정이 일어나지 않도록 보호합니다.
• 하지만, 갭 락과 넥스트 키 락은 데드락을 유발할 수 있고, 트랜잭션을 오래 지속시키게 만들어 성능에 영향을 미칠 수 있습니다. 따라서 최소화하여 사용해야 하며, 필요한 범위에만 적용하는 것이 좋습니다.

🧷5.3.4 자동 증가 락

MySQL은 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공합니다. 

이 칼럼은 동시에 여러 레코드가 INSERT 된 경우의 잠금을 제공합니다.

테이블 당 하나만 존재하면 AUTO_INCREMENT는 절대 줄어들지 않습니다.

 

🧷5.3.5 인덱스와 잠금

InnoDB의 잠금은 인덱스와 상당히 중요한 연관 관계가 있습니다.

레코드 락에서 언급했듯이 InnoDB의 잠금은 레코드를 잠그는 것이 아닌 인덱스를 잠급니다. 즉 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 겁니다. 

 

예시를 들어봅시다.

//first_name은 인덱스가 존재하며 라스트는 없다.
// 253명의 사람
mysql> SELECT COUNT(*) FROM employees WHERE first_name = 'Georgi';

// 1명의 사람
mysql> SELECT COUNT(*) FROM employees WHERE first_name = 'Georgi' AND last_name='Klassen';

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

 

해당 쿼리처럼 한다고 생각해보자

• UPDATE 문장은 1건의 레코드가 업데이트될 것이다.
• 이 UPDATE 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name이 'Georgi'이며, last_name 칼럼은 인덱스에 없기 때문에  253개의 레코드를 모두 잠근다(인덱스를 잠금)

이는 인덱스 설계가 잘못되었을 때를 나타내는 것입니다.. 만약 이러한 상황이라면 클라이언트 간의 동시성이 상당히 떨어질 것이다. 덱스 설계가 잘못되었을 때, 쿼리의 조건에서 사용할 수 있는 인덱스가 부족하면 불필요한 레코드를 모두 잠그게 됩니다. 이로 인해 동시성 문제가 발생하고, 여러 클라이언트 간의 작업이 서로 차단될 수 있습니다. 특히, first_name만 인덱스가 있고 last_name에는 인덱스가 없을 경우, 해당 조건에 맞는 253개의 레코드가 잠기게 되므로 동시성 성능이 크게 저하됩니다.

 

만약 테이블에 인덱스가 없다면 테이블을 풀 스캔을 하면서 모든 레코드를 잠그게 됩니다. 그렇기에 명시적으로 InnoDB는 클러스터링 인덱스를 가집니다. 

 

 

📝5.4 MySQL 격리 수준 

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

종류는 READ_UNCOMMITTED, READ_COMMITED, REPEATABLE_READ, SERIALIZABLE이 있습니다.

뒤로 갈 수록 격리 수준은 높아지지만 동시성 효율 또한 떨어집니다.

 

설명을 하겠지만 더 자세한 설명은 이곳

 

🧷5.4.1 READ UNCOMMITTED 

READ UNCOMMITTED 격리 수준은 아래 그림과 같이 각 트랜잭션에서의 변경 내용이 COMMIT, ROLLBACK 여부와 상관없이 다른 트랜잭션에서 보입니다. 

위 사진처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty read)라고 합니다. 더티 리드가 허용되는 격리 수준이 바로 이 격리 수준이며 더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 절대 사용하지 맙시다!

 

🧷5.4.2 READ COMMITTED 

READ COMMITTED는 오라클 DBMS에서 기본을 사용되는 격리 수준입니다. 온라인 서비스에서 가장 많이 선택되는 격리 수준이며 이 레벨에서는 위에서 언급한 더티 리드 현상은 발생하지 않습니다. 

왜냐하면 오직 커밋된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문입니다. 

위 사진처럼 업데이트문을 실행해도 커밋되지 않는 한 언두 로그의 데이터를 이용해서 반영되지 않은 데이터를 읽습니다.

 

하지만 이 격리 수준도 문제가 있습니다.

📌NON_REPEATABLE_READ

NON_REPEATABLE_READ는 커밋한 데이터에 대해 변경 전과 변경 후의 조회가 달라지는 것을 의미합니다.

즉 하나의 트랜잭션이 조회할 때 같은 정보를 가져와야 하는 정합성에 위배됩니다. 

이러한 부정합 문제는 일반적인 웹 프로그램에서는 크게 문제가 되지 않을 수 있지만 하나의 트랜잭션에서 동일한 데이터 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수 있습니다.

 

🧷5.4.3 REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준입니다. 이 격리 수준에서는 NON_REPEATABLE READ가 발생하지 않습니다. InnoDB는 트랜잭션이 롤백될 가능성에 대비해 변경 전 레코드를 언두 공간에 백업하고 실제 레코드 값을 변경하는 MVCC (Multi-Version Concurrency Control) 방식을 사용합니다. REPEATABLE READ는 MVCC를 이용해 동일 트랜잭션 내에서 동일한 결과를 보장합니다. READ COMMITTED도 MVCC를 이용하지만, 차이점은 언두 영역에 백업된 레코드의 여러 버전 중 어떤 버전을 사용할지의 차이입니다. 모든 트랜잭션은 고유한 트랜잭션 번호를 가지고 있으며, 언두 영역에 백업된 모든 레코드에는 변경된 트랜잭션의 번호가 포함됩니다. REPEATABLE READ 격리 수준에서는 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 앞선 트랜잭션에서 수정된 언두 데이터를 삭제할 수 없으며, 결과적으로 자신의 트랜잭션 아이디보다 앞선 언두 데이터를 보지 않습니다.

 

📌PHANTOM READ

마찬가지로 REPEATABLE READ에서 부정합 문제가 발생합니다. 

이것은 한 트랜잭션에서 여러번의 SELECT ... FOR UPDATE 쿼리로 조회했을 때 다른 답이 나오는 것입니다.

 

밑의 사진은 이 오류에 대한 시나리오입니다.

원래라면 INSERT로 갱신되는 레코드는 사용자 B의 조회 정보에 들어오지 못하는게 맞습니다.

하지만 SELECT ... FOR UPDATE로 잠금을 걸어도 언두 로그의 갱신을 막을 수 없기 때문에 언두 로그가 갱신되면서 갱신된 데이터가 조회될 수 있다는 점입니다.

원래라면 INSERT로 갱신된 레코드는 사용자 B의 조회 정보에 들어오지 않아야 하지만, 언두 로그가 갱신되면서 갱신된 데이터가 조회되는 현상이 발생합니다.

 

하지만 위의 표처럼 InnoDB에서는 팬텀리드가 발생하지 않습니다. 

InnoDB에서 팬텀 리드가 발생하지 않는 이유는 첫 번째 SELECT ... FOR UPDATE 시에 갭락을 걸어 emp_no >= 500000 범위 내의 레코드 추가를 막기 때문입니다.

따라서 해당 갭락 범위에 대한 레코드 삽입이 막혀 트랜잭션 A가 커밋될 때까지 새로운 레코드가 조회되지 않게 되어 팬텀 리드가 발생하지 않습니다.

 

 

🧷5.4.4 SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준입니다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어집니다.

사실 이미 MySQL의 InnoDB 스토리지 엔진은 팬텀리드가 일어나지 않기 대문에 쓸 이유가 없습니다.