Chapter 05. 트랜잭션과 잠금

5.1 트랜잭션

• 트랜잭션이란 작업의 완전성을 보장해주는 것이다. 
• 논리적인 작업 셋을 모두 완벽 처리하거나 처리하지 못할 때에는 원 상태로 복구해서 작업의 일부만 적용되는 현상(partial update)이 발생하지 않도록 만들어주는 기능이다.
• MyISAM은 트랜잭션이 지원되지 않는다.

 

 

잠금(Lock)과 트랜잭션의 차이

• 잠금: 동시성을 제어하는 기능이다. ex) 여러 커넥션에서  하나의 데이터를 동시에 수정하려고 하는 경우에 적용하는 기능
• 트랜잭션: 데이터의 정합성을 보장하기 위한 기능이다.
  • 격리 수준: 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.
  • 데이터의 정합성(consistency): 데이터가 모순없이 일관되어야한다. 

 

 

 

5.1.1 MySQL에서의 트랜잭션

• 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 완전 적용(COMMIT 실행 시) 또는 아무것도 적용되지 않아야(ROLL BACK 또는 트랜잭션을 ROLL BACK 시키는 오류 발생 시)하는 것을 보장한다.

 

 

  Ex) 트랜잭션의 관점에서 InnoDB와 MySQL의 차이

 

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

 

• 위와 같은 테이블에 각각 레코드를 한 건씩 저장하고 AUTO-COMMIT 모드에서 다음 쿼리 문장을 InnoDB테이블과 MyISAM 테이블에서 각각 실행한다.

<hide/>
-- Auto Commit 활성화
SET autocommit = ON;

INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3);

 

 

• 두 개의 스토리지 엔진에서 결과
  • 두 INSERT 문 모두 PK 중복 오류로 쿼리가 실패한다.
  • MyISAM  테이블에는 오류가 발생했더라도 1, 2는 INSERT 된 상태로 남아 있는 것을 확인할 수 있다.
  • MyISAM 테이블에 INSERT 문이 실행되면서 1,2가 저장되고, 그 다음 3을 저장하려는 순간 중복 키 오류(이미 3이 있다.)가 발생한다.
  • 하지만, MyISAM 테이블에서 실행되는 쿼리는 이미 INSERT 된 1, 2 를 그대로 두고 쿼리 실행을 종료한다.
  • InooDB는 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 문을 실행 전 상태로 복구한다.  => "partial update"
  • 부분 업데이트는 데이터의 정합성을 맞추는데 문제가 있다.

<hide/>

INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
ERROR 1062 (23000) : Duplicate entry '3' for key 'PRIMARY';

INSERT INTI tab_innodb (fdpk) VALUES (1), (2), (3);
ERROR 1062 (23000) : Duplicate entry '3' for key 'PRIMARY';

SELECT * FROM tab_myisam;
fdpk
1
2
3

SELECT * FROM tab_innodb;
fdpk
3

 

 

 

5.1.3 주의 사항

• 트랜잭션은 DBMS의 커넥션과 동일하게 꼭 필요한 최소한의 코드에만 적용하는 게 좋다. 
  • 트랜잭션의 범위를 최소화하는 것이 좋다. 

 

 

Ex) 게시물 작성 후 저장했을 때, 서버의 처리 과정

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

 

 

위 트랜잭션의 문제점

• 1번, 2번 사이에 트랜잭션 생성, 9, 10번 사이에 트랜잭션 종료
  • 실제 데이터 저장은 5번 부터 시작한다. 따라서, 2, 3, 4를 트랜잭션에 포함시킬 필요가 없다.
  • 커넥션 개수는 제한적이므로 커넥션 소유하는 시간이 길어질수록 여유 커넥션의 개수는 줄어든다.
• 8번 작업: 어떤 작업(메일 전송, FTP 파일 전송,네트워크를 통해 원격 서버와 통신하는 작업)은 DBMS의 트랜잭션 내에서 제거하는 게 좋다. 프로그램이 실행되는 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버뿐 아니라 DBMS  서버까지 위험해지는 상황이 발생한다.
• DBMS의 작업이 크게 4개가 있다. 사용자의 입력 정보를 저장하는 5, 6번은 하나의 트랜잭션으로 묶어야한다.
• 7번은 저장된 데이터의 단순 확인 및 조회이므로 트랜잭션에 포함할 필요가 없다.  
• 9번은 성격이 다르니까 5, 6번에 묶지 않고 별도의 트랜잭션으로 분리한다.

 

 

변경 후 처리 과정

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

 

 

 

5.2 MySQL 엔진의 잠금

• MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 준다.
• 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 주지 않는다. 
• MySQL 엔진이 제공하는 락 기능
  • 메타데이터 락(Metadata Lock): 테이블의 구조를 잠근다.
  • 네임드 락(Named Lock): 사용자의 필요에 맞게 사용할 수 있는 잠금 기능

 

 

5.2.1 글로벌 락(Global Lock)

• 글로벌 락(global lock)은 FLUSH TABLES WITH READ LOCK 명령으로 획득 가능
• MySQL 에서 제공하는 잠금 가운데 범위가 가장 크다. 
• MySQL 서버 전체에 영향을 미친다. => 따라서 웹 서비스용 MySQL 서버에서는 글로벌 락은 가급적 사용하지 않는 게 좋다.
• 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야할 때 글로벌 락을 이용한다.
• FLUSH TABLES WITH READ LOCK: 실행과 동시에  MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 건다.
  • 이 명령을 걸기 전에 쓰기 잠금이 먼저 실행 중인 상태라면 먼저 실행된 SQL과  그 트랜잭션이 완료될 때까지 기다린 다음에 실행된다.
• FLUSH TABLES WITH READ LOCK 명령을 이용한 글로벌 락은 MySQL 서버의 모든 변경 작업을 멈춘다.
• InnoDB는 트랜잭션을 지원하므로 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈츨 수 없다.
• MySQL 8.0부터는 InnoDB를 기본 스토리지 엔진으로 채택되면서 좀 더 가벼운 글로벌 락의 필요성이 생겼다.
• 따라서, Xtra Backup, Enterprise Backup 과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.

 

• 특정 세션에서  백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보 변경 불가
  • 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
  • REPAIR TABLE과 OPTIMIZE TABLE 명령
  • 사용자 관리 및 비밀번호 변경
• 백업 락은 일반적인 테이블의 데이터 변경은 허용된다.
• MySQL 서버는 소스 서버(Source Server)와 레플리카 서버(Replica Server)로 구성된다.
  • 소스 서버
  • 레플리카 서버: 백업은 주로 레플리카 서버에서 실행된다.
  • ex) 백업이 글로벌 락을 획들하면 복제는 백업 시간만큼 지연될 수 밖에 없다.

 

5.2.2 테이블 락(Table Lock)

• 개별 테이블 단위로 설정되는 잠금을 말한다.
• 명시적, 묵시적으로 테이블 락을 획득 가능
• LOCK TABLES table_name [ READ : WRITE ]  : 명시적으로 특정 테이블의 락을 획득할 수 있다. 
• 묵시적 테이블 락: MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.
  • MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다.
  • 쿼리가 실행하는 동안 자동으로 획득했다가 쿼리가 완료된 후 자동으로 해체된다.
• InnoDB 테이블에도 테이블 락이 설정되지만 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.

 

 

5.2.3 네임드 락(Named Lock)

• 네임드 락(named lock)은 GET_LOCK() 함수를 이용해 임의의 문자열(String)에 대해 잠금을 설정할 수 있다. 
• 잠금의 대상은 데이터베이스 객체(테이블, 레코드, AUTO_INCREMENT)가 아니라 임의의 문자열이다.
• 사용자가 지정한 문자열에 대해 획득하고 반납(해제)하는 잠금이다.
• ex) 데이터베이스 서버 1대에 5대의 웹 서버가 접속해서 서비스하는 상황에서 5대의 웹 서버가 어떤 정보를 동기화해야하는 요건 처럼 여러 클라이언트가 상호 동기화를 처리해야할 때, 네임드락을 이용하면 쉽게 해결 가능하다.
• ex) 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락이 발생한다.
  • 동일한 데이터를 변경하거나 참조하는 프로그램끼지 분류해서 네임드 락을 걸고 쿼리를 실행하면 간단히 해결 가능하다.

 

 

5.2.4 메타데이터 락(Metadata Lock)

• 메타데이터 락은 데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.
• 명시적으로 메타데이터 락을 획득하거나 해제할 수 있는 게 아니다.
• RENAME  TABLE  tab_a  TO  tab_b 처럼 테이블명을 변경하는 경우 자동으로 획득하는 잠금이다.
• 메타데이터 잠금과 InnoDB의 트랜잭션을 동시에 사용해야하는 경우도 있다.

 

 

5.3 InnoDB 스토리지 엔진 잠금

• InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다.
  • 레코드 기반 잠금 방식 => 뛰어난 동시성 처리
• 이원화된 잠금 처리 때문에 InnoDB 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해서 접근하기가 까다롭다.
  • 잠금정보 진단 방법 (예전 버전): lock_monitor, SHOW_ENGINE_INNODB_STATUS 
    •  ... => 이해하기 어렵다.
  • 잠금정보 진단 방법 (최근 버전): InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다.
    •  INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인 가능, 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수 있다.

 

 

5.3.1 InnoDB 스토리지 엔진의 잠금

• InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공한다.
• 락 에스컬레이션(레코드 락이 페이지 락으로 또는 테이블 락으로 레벨업 되는 경우) 는 없다.
• InnoDB 스토리지 엔진은 상용 DBMS와는 다르게 레코드 락 뿐만 아니라 갭 락(Gap Lock)이 존재한다.

 

5.3.1.1 레코드 락(Record Lock, Record only Lock)

• 다른 상용 DBMS의 "레코드 락"과 동일한 기능이다. 
• InnoDB스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이 다르다. =>  크고 중요한 차이점
• 인덱스가 없는 테이블은 내부적으로 자동 생성된 클러스터 인덱스를 이용해서 잠금을 설정한다. 

 

 

5.3.1.2 갭 락(Gap Lock)

• 다른 DBMS와의 차이점이다.
• 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 
• 레코드와 레코드 사이에 새로운 레코드가 INSERT 되는 것을 제어한다.

 

 

5.3.1.3 넥스트 키 락(Next Key Lock)

• 레코드 락 + 갭 락
• innodb_locks_unsafe_for_binlog 시스켐 변수가 비활성화(0)되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.

 

 

 

5.3.1.4 자동 증가 락(Auto Increment Lock)

• MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT 라는 칼럼 속성을 제공한다.
• AUTO_INCREMENT  칼럼이 사용된 테이블이 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련 번호 값을 가져야한다. 
  • InnoDB엔진에서는 이를 위해 내부적으로 "AUTO_INCREMENT 락"이라고 하는 테이블 수준의 잠금을 사용한다.
• INSERT, REPLACE 같은 새로운 레코드를 저장하는 쿼리에서만 필요하다.
  • UPDATE, DELETE같은 쿼리에서는 걸리지 않는다.
• 다른 잠금과 다르게 트랜잭션과 관련없이 INSERT, REPLACE 문장에서 AUTO_INCREMENT  값을 가져오는 순간만 락이 걸렸다가 즉시 해체된다. 
• AUTO_INCREMENT 락은 테이블 단 하나만 존재한다.
  • 두 개의 INSERT 쿼리가 동시 실행되는 경우, 하나의 쿼리가 AUTO_INCREMENT 락을 걸면 나머지 쿼리는 AUTO_INCREMENT 락을 기다려야한다.
• innodb_autoinc_lock_mode = 0
  • 모든 INSERT 문장은 자동 증가 락을 사용한다. 
• innodb_autoinc_lock_mode = 1
  • 레코드를 INSERT하는 SQL 중에서 MySQL 서버가 INSERT 되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락을 걸지 않고  가볍고 빠른 래치(뮤텍스)를 이용해 처리한다. 
  • 연속 모드(Consecutive mode)라고도 한다.
• innodb_autoinc_lock_mode = 2
  • 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다. 
  • 하나의 INSERT 문장으로 INSERT 되는 레코드라고 하더라고 연속된 자동 증가 값을 보장하지는 않는다.
  • 인터리빙 모드(Interleaved mode)

 

 

 

5.3.2 인덱스와 잠금

 

• InnoDB 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 것으로 처리된다. 
• 변경할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야한다.

 

 

  Ex) 인덱스와 잠금

<hide/>
SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
> 253

SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
> 1

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

• update 문이 실행되면 한 개의 레코드가 업데이트 된다.
• 1건의 업데이트를 위해 253건의 레코드에 대해 모두 락을 걸어야한다.
• 아래 그림으 업데이트 문이 어떻게 레코드를 검색하고 실제 변경이 수행되는지 보여준다.
• 이 테이블에 인덱스가 하나도 없다면 ?
  • 테이블을 풀 스캔하면서 UPDATE 작업을 한다. 
  • 테이블에 있는 30여 만건의 모든 레코드를 잠근다.

 

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

 

 

 

5.4 MySQL의 격리 수준

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

 

 

트랜잭션의  격리 수준

• READ UNCOMMITTED
• READ COMMITTED: 오라클에서 많이 사용한다.
• REPEATABLE READ
• SERIALIZABLE
• UNCOMMITTED (= DIRTY READ): 일반적인 DB에서는 사용하지 않는다. 
• 아래로 내려올수록 격리(고립) 수준이 높아지고 동시 처리 성능도 낮아진다. 
• 온라인 서비스 용도의 데이터베이스는 READ COMMITTED와 REPEATABLE READ중 하나를 사용한다.

 

 

 

5.4.1 READ UNCOMMITTED

 

• READ UNCOMMITTED에서는 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.
• 사용자 A는 emp_no = 500000 이고 first_name = "Lara"인 새로운 사원을 INSERT한다.
• B가 변경된 내용을 커밋하기도 전에 B는 emp_no = 500000인 사원을 검색하고 있다. 
• B는 A가 INSERT한 사원의 정보를 커밋되지 않은 상태에서도 조회할 수 있다. 
• 여기서의 문제점은?
  • 부정합 문제가 발생한다.
  • 더티 리드(Dirty read): 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상 
  • 문제가 발생해서 A가 INSERT된 내용을 롤백하더라도 여전히 사용자 B는 "Lara"가 정상적인 사원이라고 생각하고 처리할 것이다. 
• READ UNCOMMITTED는 더티리드가 허용되는 격리 수준이다. 
• 즉, READ UNCOMMITTED는 RDBMS 표준에서는 트랜잭션 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 있는 격리 수준이다.
• READ COMMITTED 이상의 격리 수준을 권장한다.

 

 

5.4.2 READ COMMITTED

READ COMMITTED에서 A 가 변경한 내용이 어떻게 B에게 어떻게 조회되는지 보여준다.

 

• 오라클 DBMS에서 기본으로 사용되는 격리 수준이다. 
• 온라인 서비스에서 가장 많이 선택한다. 
• 어떤 트랜잭션의 내용이 변경되서 COMMIT이 완료된 데이터만  다른 트랜잭션에서 조회 가능하다. 더티리드(Dirty read)가 발생하지않는다.
• A는 emp_no = 500000인 사원의 first_name을 "Lara"에서 "Toto"로 변경했는데 새로운 값인 "Toto"는 employee 테이블에 즉시 기록되고 기존값 "Lara"는 언두 영역으로 백업된다.
• A가 커밋하기 전에 B가  emp_no = 500000를 SELECT 하면?
  • Lara로 조회된다.
  • 이 결과는 테이블이 아니라 언두 영역에 백업된 레코드에서 가져온 것이다. 
• 커밋하고 난 다음에야 새롭게 변경된 값을 참조할 수 있다. 

 

NON-REPEATABLE READ

사진 출처 - https://zzang9ha.tistory.com/381

 

 

• READ COMMITTED 수준에서도 NON-REPEATABLE READ 라는 부정합의 문제가 있다. 
• B가 BEGIN으로 트랜잭션을 시작
• B가 first_name = Toto 사용자를 검색한다. => 결과 없음
• A가  emp_no = 500000 인 사원 이름을 Toto로 바꾸고 커밋을 실행한다.
• B가 first_name = Toto 사용자를 검색 => 결과가 1개 조회된다. 
• 여기서 문제는?
  • B가 하나의 트랜잭션에서 똑같은 SELECT 쿼리를 실행했을 때, 항상 같은 결과를 가져와야한다는 "REPEATABLE READ"라는 정합성에 어긋나는 것이다. (NON-REPEATABLE READ)
  • 부정합 현상은 금전적인 처리와 연결되는 경우 문제가 된다.

 

 

5.4.3 REPEATABLE READ

• 언두 영역에 백업된 이전 데이터를 이용해서 동일 트랜잭션 내에서 동일 결과를 보여줄 수 있도록 보장한다. 
  • READ COMMITTED 와의 차이는? 언두 영역에 백업된 레코드의 여러 버전 중 몇 번째 이전 버전까지 찾아서 들어갈 것인지에 대한 차이이다. 
• REPEATABLE READ는 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 
• 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야한다. 
• " NON-REPEATABLE READ "라는 부정합 문제가 발생하지 않는다.
• InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해서 변경되기 전에 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경한다. "MVCC(Multi Version Concurrency Control)"
• REPEATABLE READ는 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해서 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장한다. 
• REPEATABLE READ에서는 MVCC를 보장하기 위해 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 앖다. 
• REPEATABLE READ 에서도 부정합 문제가 발생할 수 있다. 
  • PHANTOM READ(PHANTOM ROW): 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 말한다.
  • SELECT .. FOR UPDATE 쿼리는 SELECT 하는 쿼리에  쓰기 잠금을 걸어야하는데,  언두 레코드에는 잠금을 걸 수 없다. 그래서 SELECT .. FOR UPDATE 쿼리나  SELECT ... LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 게 아니라 현재 레코드의 값을 가져오는 것이다. 
  • A에서 쓰기 잠금을 거는 경우 언두 로그가 아닌 최신 버전(?)  데이터를 가져오므로 B 트랜잭션에서 데이터를 추가하는 경우 팬텀리드 문제가 생길 수 있다.
  • MySQL는 팬텀리드가 거의 발생하지 않는다.

 

 

5.4 SERIALIZABLE

• 가장 단순하면서 가장 엄격한 격리 수준이다.
• 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다. 
• 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행된다. 
  • "Non-locking consistent read(잠금이 필요없는 일관된 읽기)"가 이를 의미한다. 
  • 그러나 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 작금)을 획득해야 가능하다.
  • 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근 불가
• PHANTOM READ 라는 문제가 발생하지 않는다.
  • InnoDB 스토리지 엔진에서는 넥스트 키 락과 갭 락 덕분에  REPEATABLE READ 수준에서도 이미 "PHANTOM READ"라는 문제가 발생하지 않는다. =>  굳이 SERIALIZABLE를 사용할 필요는 없다. 

 

 

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」 위키북스