05. 트랜잭션과 잠금

MySQL의 동시성에 영향을 미치는

잠금(Lock)와 트랜잭션(Transaction), 트랜잭션의 격리 수준(Isolation level)

• 트랜잭션: 작업의 일부만 적용되는 현상(Partial update) 가 발생하지 않게 하여 작업의 완전성을 보장
• 잠금: 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우, 순서대로 한 시점에 하나의 커넥션만 변경할 수 있게 해주는 역할 (동시성 제어)
• 격리 수준: 하나의 트랜잭션 내에서, 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지 결정하는 레벨

5.1 트랜잭션

트랜잭션을 지원하지 않는 MyISAM 과 트랜잭션을 지원하는 InnoDB 의 처리 방식 차이

트랜잭션 사용시 주의 사항

MySQL 에서의 트랜잭션

트랜잭션은 논리적인 변경 작업의 개수와 관계없이 100% 적용(COMMIT) 과 적용되지 않음(ROLLBACK 또는 트랜잭션을 ROLLBACK 시키는 오류가 발생했을 때) 의 두 가지 상태만을 지원하고 보장한다.

 

예) 트랜잭션 관점에서 MyISAM 테이블과 InnoDB 테이블의 차이

테이블 생성, INSERT

MyISAM 테이블과 InnoDB 테이블을 각각 생성하고 fdpk 컬럼에 3 이라는 값을 가진 레코드를 저장했다.

fdpk 는 PK 로 지정되었기 때문에 중복된 값을 가진 레코드를 저장할 수 없다.

 

AUTO-COMMIT, INSERT

 

키 중복 오류, SELECT

 

'1'과 '2'를 저장하고 '3'을 저장하려는 순간 중복 키 오류 발생

MyISAM 테이블: 오류가 발생했음에도 '1'과 '2'가 INSERT 된 상태 -> Partial Update

InnoDB 테이블: 트랜잭션이 롤백되어 기존에 저장한 '3'만 있음

주의사항

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

 

예) 사용자가 게시물 작성 후 저장 버튼을 클릭했을 때 서버에서 처리하는 내용

 

1. 처리 시작

  => 데이터베이스 커넥션 생성

  => 트랜잭션 시작

2. 사용자의 로그인 여부 확인

3. 사용자의 글쓰기 내용의 오류 여부 확인

4. 첨부로 업로드된 파일 확인 및 저장

5. 사용자의 입력 내용을 DBMS 에 저장

6. 첨부 파일 정보를 DBMS 에 저장

7. 저장된 내용 또는 기타 정보를 DBMS 에서 조회

8. 게시물 등록에 대한 알림 메일 발송

9. 알림 메일 발송 이력을 DBMS 에 저장

  <= 트랜잭션 종료(COMMIT)

  <= 데이터베이스 커넥션 반납

10. 처리 완료

 

위 처리 절차 중 DBMS의 트랜잭션 처리에 좋지 않은 영향을 미치는 부분

• 실제로 DBMS에 데이터를 저장하는 작업(트랜잭션)은 5번부터 시작되지만 트랜잭션과 관련없는 다른 비지니스 로직이 중간에 껴있다. 일반적으로 데이터베이스 커넥션은 개수가 제한적이어서 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션의 개수는 줄어들 것이다. 그리고 어느 순간에는 커넥션을 가져가기 위해 기다리는 상황이 발생할 수도 있다.
• 더 큰 위험은 8번 작업. 메일 전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 DBMS 트랜잭션 내에서 제거하는 것이 좋다. 프로그램이 실행되는 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버 뿐만 아니라 DBMS 서버까지 위험해지는 상황이 발생할 것이다.
• 사용자가 입력한 정보를 저장하는 5번, 6번 작업은 하나의 트랜잭션 내에서 관리해야 한다.
• 7번 작업은 저장된 데이터의 단순 확인 및 조회로 트랜잭션이 필요하지 않다.
• 이전 트랜잭션(5번,6번)과는 조금 성격이 다른 9번 작업은 별도의 트랜잭션으로 분리하는 것이 좋다.

 

1. 처리 시작

2. 사용자의 로그인 여부 확인

3. 사용자의 글쓰기 내용의 오류 여부 확인

4. 첨부로 업로드된 파일 확인 및 저장

  => 데이터베이스 커넥션 생성

  => 트랜잭션 시작

5. 사용자의 입력 내용을 DBMS 에 저장

6. 첨부 파일 정보를 DBMS 에 저장

  <= 트랜잭션 종료(COMMIT)

7. 저장된 내용 또는 기타 정보를 DBMS 에서 조회

8. 게시물 등록에 대한 알림 메일 발송

  => 트랜잭션 시작

9. 알림 메일 발송 이력을 DBMS 에 저장

  <= 트랜잭션 종료(COMMIT)

  <= 데이터베이스 커넥션 반납

10. 처리 완료

 

중요한 것은 프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화된 프로그램의 범위를 최소화해야 하는 것이다. 그리고 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야 한다.

5.2 MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.

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

MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블 구조를 잠그는 메타데이터 락(Metadata Lock) 그리고 사용자의 필요에 맞게 사용할 수 있는 네임드락(Named Lock)이라는 잠금 기능도 제공한다. 

글로벌 락(GLOBAL LOCK)

//global lock 획득 명령
mysql> FLUSH TABLE WITH READ LOCK;

 

MySQL 에서 제공하는 잠금 가운데 가장 범위가 크다. 일단 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT 를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다. 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다. 

 

FLUSH TABLE WITH READ LOCK 명령은 실행과 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 건다.

테이블이나 레코드에 쓰기 잠금을 거는 SQL이 실행되기 전에, 해당 테이블의 읽기 잠금을 걸기 위해 먼저 실행된 SQL 과 그 트랜잭션이 완료될 때까지 기다려야 한다. FLUSH TABLE WITH READ LOCK 명령은 테이블에 읽기 잠금을 걸기 전에 먼저 테이블을 플러시 해야 하기 때문에 테이블에 실행 중인 모든 종류의 쿼리가 완료돼야 한다. 그래서 장시간 SELCET 쿼리가 실행되고 있을 때는 FLUSH TABLE WITH READ LOCK 명령은 SELECT 쿼리가 종료될 때까지 기다려야 한다.

장시간 실행되는 쿼리와 FLUSH TABLE WITH READ LOCK 명령이 최악의 케이스로 실행되면 MySQL 서버의 모든 테이블에 대한 쿼리(INSERT, UPDATE, DELETE)가 아주 오랜 시간동안 실행되지 못하고 기다릴 수도 있다. 글로벌 락은 MySQL 서버의 모든 테이블에 큰 영향을 미치기 때문에 웹 서비스로 사용되는 MySQL 서버에서는 가급적 사용하지 않는 것이 좋다.

 

백업 락 

글로벌 락은 MySQL 서버의 모든 변경 작업을 멈춘다. 그러나 InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다. 그래서 8.0 버전부터 Xtrabackup이나 Enterprise Backup 과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다. (가벼운 글로벌 락)

 

//backlock 획득 명령
mysql> LOCK INSTANCE FOR BACKUP;

//unlock 명령
mysql> UNLOCK INSTANCE;

 

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

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

하지만 일반적인 테이블의 데이터 변경은 허용된다. 즉, 정상적으로 복제는 실행되지만 스키마 변경으로 인한 백업 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 한다.

 

참고: 여러 데이터베이스에 존재하는 MyISAM 이나 MEMORY 테이블에 대해 mysqldump 로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 한다.

테이블 락(Table Lock)

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

 

//테이블 락 명시적 획득 명령
mysql> LOCK TABLES table_name [READ|WRITE];

 

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

 

묵시적인 테이블 락은 MyISAM 이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다. MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다. 즉, 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제된다. InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다. 정확히는 테이블 락이 대부분의 데이터 변경 쿼리(DML)에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.

네임드 락(Named Lock)

네임드 락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 이 잠금의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT 와 같은 데이터베이스 객체가 아니라는 것이다. 예를 들어, 데이터베이스 서버 1대에 5대의 웹 서버가 접속해서 서비스하는 상황에서 5대의 웹 서버가 어떤 정보를 동기화해야 하는 요건처럼 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 이용하면 쉽게 해결할 수 있다.

 

-- // "mylock" 이라는 문자열에 대해 잠금 획득
-- // 이미 잠금을 사용 중이면 2초 동안 대기 (2초 이후 자동 잠금 해제)
mysql> SELECT GET_LOCK('mylock', 2);

-- // "mylock" 이라는 문자열에 대해 잠금이 설정되어 있는지 확인
mysql> SELECT IS_FREE_LOCK('mylock');

-- // "mylock" 이라는 문자열에 대해 획득했던 잠금을 반납(해제)
mysql> SELECT RELEASE_LOCK('mylock');

-- // 3개 함수 모두 정상적으로 락을 획득하거나 해제한 경우에는 1을,
-- // 아니면 NULL 이나 0 을 반환

 

또한 네임드 락은 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다. 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 한다. 각 프로그램의 실행 시간을 분산하거나 프로그램의 코드를 수정해서 데드락을 최소화할 수는 있지만, 이는 간단한 방법이 아니며 완전한 해결책이 될 수 없다. 이러한 경우에 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있다.

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

 

mysql> SELECT GET_LOCK('mylock_1', 10);
-- // mylock_1 에 대한 작업 실행
mysql> SELECT GET_LOCK('mylock_2', 10);
-- // mylock_1 과 mylock_2 에 대한 작업 실행

mysql> SELECT RELEASE_LOCK('mylock_2');
mysql> SELECT RELEASE_LOCK('mylock_1');

-- // mylock_1 과 mylock_2 를 동시에 모두 해제하고자 한다면 RELEASE_ALL_LOCKS() 함수 사용
mysql> SELECT RELEASE_ALL_LOCKS();

메타데이터 락(Metadata Lock)

메타데이터 락은 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 메타데이터 락은 명시적으로 획득하거나 해제할 수 있는 것이 아니고 "RENAME TABLE tab_a TO tab_b" 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다. RENAME TABLE 명령의 경우 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정한다.

 

실시간으로 테이블을 바꿔야 하는 요건이 배치 프로그램에서 자주 발생한다.

 

-- // 배치 프로그램에서 별도의 임시 테이블(rank_new)에 서비스용 랭킹 데이터를 생성

-- // 랭킹 배치가 완료되면 현재 서비스용 랭킹 테이블(rank)을 rank_backup으로 백업하고
-- // 새로 만들어진 랭킹 테이블(rank_new)을 서비스용으로 대체하고자 하는 경우
mysql> RENAME TABLE rank TO rank_backup , rank_new TO rank;

 

위와 같이 하나의 RENAME TABLE 명령문에 두 개의 RENAME 작업을 한꺼번에 실행하면 실제 애플리케이션에서 "Table not found 'rank'" 같은 상황을 발생시키지 않고 적용하는 것이 가능하다. 하지만 이 문장을 2개로 나눠서 실행하면 아주 짧은 시간이지만 rank 테이블이 존재하지 않는 순간이 생기며, 그 순간에 실행되는 쿼리는 "Table not found 'rank'" 오류를 발생시킨다.

 

때로는 메타데이터 잠금과 InnoDB의 트랜잭션을 동시에 사용해야 하는 경우도 있다.

예) INSERT 만 실행되는 로그 테이블 (웹 서버의 액세스 로그 저장 테이블)

 

어느 날 이 테이블의 구조를 변경해야 할 요건이 발생했다. 물론 MySQL 서버의 Online DDL 을 이용해서 변경할 수도 있지만 시간이 너무 오래 걸리는 경우라면 Undo Log 의 증가와 Online DDL 이 실행되는 동안 누적된 Online DDL 버퍼의 크기 등 고민해야 할 문제가 많다. 더 큰 문제는 MySQL 서버의 DDL은 단일 스레드로 작동하기 때문에 상당히 많은 시간이 소모될 것이라는 점이다. 이때는 새로운 구조의 테이블을 생성하고 먼저 최근(1시간 직전 또는 하루 전)의 데이터까지는 프라이머리 키인 id 값을 범위별로 나눠서 여러 개의 스레드로 빠르게 복사한다.

 

 

그리고 나머지 데이터는 다음과 같이 트랜잭션과 테이블 잠금, RENAME TABLE 명령으로 응용 프로그램의 중단 없이 실행할 수 있다. 이때 "남은 데이터를 복사"하는 시간 동안은 테이블의 잠금으로 인해 INSERT를 할 수 없게 된다. 그래서 가능하면 미리 아주 최근 데이터까지 복사해둬야 잠금 시간을 최소화해서 서비스에 미치는 영향을 줄일 수 있다.

 

5.3 InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL 에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. InnoDB 는 레코드 기반의 잠금 방식 때문에 MyISAM 보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있다. 하지만 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금 정보는 MySQL 명령을 통해 접근하기가 상당히 까다로웠다. 최근 버전의 MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있으며, 또한 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수도 있다. Performance Schema 를 이용해 InnoDB 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링 방법도 추가됐다.

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업 되는 경우(락 에스컬레이션)는 없다. 일반 상용 DBMS 와는 다르게 InnoDB 스토리지 엔진에는 레코드 락 뿐만 아니라 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이라는 것이 존재한다.

 

InnoDB 잠금의 종류(점선의 레코드는 실제로 존재하지 않는 레코드)

레코드 락(Record Lock)

레코드 자체만을 잠그는 것을 레코드 락(Record lock, Record only lock)이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 한다. 한 가지 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다. 인덱스가 없는 테이블이라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다. 레코드 자체를 잠그느냐, 아니면 인덱스를 잠그느냐는 상당히 큰 차이를 만들어 낸다.

InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락(Next key lock) 또는 갭 락(Gap lock)을 사용하지만 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭(Gap, 간격)에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.

갭 락(Gap Lock)

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

넥스트 키 락(Next Key Lock)

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금이다. STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다. 또한 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화되면(0으로 설정) 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결관와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다. 그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 하는 일이 자주 발생한다. 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

 

참고: 바이너리 로그

MySQL 서버에서 DDL과 변경을 위한 DML 쿼리에 대한 이벤트를 기록하는 이진 파일 (show 나 select 같은 조회 문법 제외)

binlog_format

• STATEMENT: 가장 오래된 format 으로 데이터 변경에서 사용되는 모든 쿼리를 저장하는 방식 (5.7까지 기본 format)
• ROW: 변경 작업으로 변경된 모든 row 정보 기록
• MIXED: statement 방식을 기본으로, 부분적으로 row 방식 적용

자동 증가 락(Auto Increment Lock)

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

AUTO_INCREMENT 락은 INSERT 와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE 나 DELETE 등의 쿼리에는 걸리지 않는다. 다른 잠금들과 달리 트랜잭션과 관계없이 INSERT/REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다. AUTO_INCREMENT 락은 테이블에 단 하나만 존재하기 때문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 AUTO_INCREMENT 락을 걸면 나머지 쿼리는 AUTO_INCREMENT 락을 기다려야 한다. (AUTO_INCREMENT 칼럼에 명시적으로 값을 설정하더라도 자동 증가 락을 건다.)

AUTO_INCREMENT 락을 명시적으로 획득하고 해제하는 방법은 없다. AUTO_INCREMENT 락은 아주 짧은 시작동안 걸렸다가 해제되는 잠금이라서 대부분의 경우 문제가 되지 않는다.

지금까지 설명은 MySQL 5.0 이하 버전에서 사용되던 방식으로 5.1 이상부터는 시스템 변수를 통해 자동 증가 락의 작동방식을 변경할 수 있다.

 

innodb_autoinc_lock_mode

• 0: 5.0 과 동일한 잠금 방식. 모든 INSERT 문장은 자동 증가 락을 사용한다.
• 1: MySQL 서버가 INSERT 되는 레코드 건수를 정확히 예측할 수 있을 때는 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다. 하지만 INSERT ... SELECT 와 같이 쿼리를 실행하기 전에 건수를 예측할 수 없을 때는 자동 증가 락을 사용한다. INSERT 문장이 완료되기 전까지는 자동 증가 락은 해제되지 않기 때문에 다른 커넥션에서는 INSERT 를 실행하지 못하고 대기한다. 이렇게 대량 INSERT가 수행될 때 InnoDB 스토리지 엔진은 여러 개의 자동 증가 값을 한 번에 할당받아서 INSERT 되는 레코드에 사용한다. 이 설정에서는 연속된 자동 증가 값을 가지기 때문에 연속 모드(Consecutive mode)라고도 한다.
• 2: 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다. 이 설정에서는 하나의 INSERT 문장으로 INSERT 되는 레코드라도 연속된 자동 증가 값을 보장하지 않기 때문에 인터리빙 모드(Interleaved mode)라고도 한다. 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아지지만 STATEMENT 포맷 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수 있다.

자동 증가 값이 한 번 증가하면 절대 줄어들지 않는 이유가 AUTO_INCREMENT 잠금을 최소화하기 위해서다. 설령 INSERT 쿼리가 실패했더라도 한 번 증가된 AUTO_INCREMENT 값은 다시 줄어들지 않고 그대로 남는다.

 

참고: innodb_autoinc_lock_mode 의 기본값

MySQL 5.7 버전까지는 1, 8.0 버전 부터는 2 로 바뀌었다. 왜냐하면 8.0 버전부터 바이너리 로그 포맷의 기본값이 ROW 포맷이 됐기 때문이다. 만일 STATEMENT 포맷의 바이너리 로그를 사용한다면 innodb_autoinc_lock_mode 를 1로 변경해서 사용할 것을 권장한다.

인덱스와 잠금

InnoDB의 잠금과 인덱스는 중요한 연관 관계가 있다. InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드에 모두 락을 걸어야 한다.

 

 

UPDATE 문장이 실행되면 1건의 레코드가 업데이트 될 것이다. 하지만 이 1건의 업데이트를 위해 인덱스를 이용할 수 있는 조건 "first_name='Georgi'" 에 해당하는 레코드 253건의 레코드가 모두 잠긴다. (last_name 컬럼은 인덱스에 없다.)

만일 UPDATE 문장을 위해 적절한 인덱스가 준비돼 있지 않다면 각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 UPDATE 작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생할 것이다.

 

업데이트를 위해 잠긴 레코드와 실제 업데이트 된 레코드

위 그림은 예제의 UPDATE 문장이 어떻게 변경 대상 레코드를 검색하고 실제 변경이 수행되는지를 보여준다.

이 테이블에 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 UPDATE 작업을 하는데 이 과정에서 테이블에 있는 30여건의 모든 레코드를 잠그게 된다. 이것이 MySQL 의 방식이며, MySQL의 InnoDB에서 인덱스 설계가 중요한 이유이다.

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

InnoDB 스토리지 엔진을 사용하는 테이블의 '레코드 수준 잠금'은 '테이블 수준 잠금'보다 더 복잡하다. 테이블 잠금에서는 잠금의 대상이 테이블 자체이므로 쉽게 문제의 원인이 발견되고 해결될 수 있다. 하지만 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다. 

하지만 5.1 버전부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행해보면 잠금과 잠금 대기를 바로 확인할 수 있다. 

 

레코드 잠금과 잠금을 대기하는 클라이언트 정보 확인

• MySQL 5.1~ : information_schema 의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블
• MySQL 8.0 : performance_schema 의 data_locks, data_lock_waits 테이블
• 강제 잠금 해제: KILL 명령으로 MySQL 서버의 프로세스를 강제 종료하기

 

예) 커넥션1, 2, 3 에서 UPDATE 쿼리 실행

UPDATE 문 실행

 

UPDATE 명령 3개가 실행된 상태의 프로세스 목록

 

• 17번 스레드: 현재는 아무 것도 하지 않음. 트랜잭션을 시작하고 UPDATE 명령이 실행 완료된 상태. 하지만 아직 COMMIT 을 실행하지 않아서 업데이트한 레코드의 잠금을 그대로 가지고 있는 상태
• 18번 스레드: 17번 이후로 UPDATE 명령 실행. 잠금 대기 상태로 아직 UPDATE 명령 실행 중 (updating)
• 19번 스레드: 18번 이후로 UPDATE 명령 실행. 잠금 대기 상태로 아직 UPDATE 명령 실행 중 (updating)

 

잠금 대기 순서 (data_locks 테이블과 data_lock_waits 테이블 조인)

 

• 현재 대기 중인 스레드(wating_thread): 18번, 19번 스레드
• blocking_thread 컬럼을 보면 19번 스레드는 17번과 18번 스레드를 기다리고 있고, 18번 스레드는 17번 스레드를 기다리고 있다는 것을 알 수 있다. 이는 잠금 대기 큐의 내용을 그대로 보여준다.

5.4 MySQL의 격리 수준(Isolation Level)

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

• READ UNCOMMITTED: "DIRTY READ" 라고도 하며 일반적인 데이터베이스에서는 거의 사용하지 않는다.
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE: 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다.

뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시 처리 성능도 떨어진다. 격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것이라고 생각하는 사용자가 많은데, 사실 SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.

 

격리 수준에 따른 부정합 문제 발생 여부

 

일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITTED 나 REPEATABLE READ 중 하나를 사용한다.

• 오라클: READ COMMITTED 주로 사용
• MySQL: REPEATABLE READ 주로 사용

이후에 설명하는 SQL 예제는 모두 Auto-commit 이 off 인 상태(SET autocommit=OFF, 수동 커밋 모드)에서만 테스트 할 수 있다.

READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다. 

 

 

1. 사용자 A: INSERT INTO employees (emp_no, first_name) VALUES (500000, Lara) 
2. 사용자 B: SELECT * FROM employees WHERE emp_no=500000 -> 결과 정상 반환
3. 사용자 A: COMMIT

 

사용자 B는 사용자 A 가 INSERT한 사원의 정보를 커밋하지 않은 상태에서도 조회할 수 있다. 그런데 문제는 사용자 A가 처리 도중 문제가 발생해 INSERT된 내용을 롤백한다고 하더라도 여전히 사용자 B는 "Lara"가 정상적인 사원이라고 생각하고 계속 처리할 것이라는 점이다.

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty read)라 하고, 격리 수준 READ UNCOMMITTED 에서는 더티 리드가 허용된다. DBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준으로 MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장한다.

READ COMMITTED

READ COMMITTED 는 오라클 DBMS 에서 기본으로 사용되는 격리 수준이다. 온라인 서비스에서 가장 많이 선택되는 격리 수준으로, 더티 리드 같은 현상은 발생하지 않는다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT 이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.

 

1. 사용자 A: UPDATE employees SET first_name='Toto' WHERE emp_no=500000
2. 사용자 B: SELECT * FROM employees WHERE emp_no=500000 -> 'Lara' 반환
3. 사용자 A: COMMIT

사용자 A가 커밋을 수행하기 전에 사용자 B가 조회한 사원 'Lara'는 employees 테이블이 아닌 Undo 영역에 백업된 레코드에서 가져온 것이다. READ COMMITTED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 그러한 변경 내역을 조회할 수 없기 때문이다.

READ COMMITTED 격리 수준에서도 "Non-Repeatable Read"(Repeatable Read 불가) 라는 부정합의 문제가 있다.

 

NON-REPEATABLE READ

NON-REPEATABLE READ

 

1. 사용자 B: BEGIN TRANSACTION
2. 사용자 B: SELECT * FROM employees WHERE first_name='Toto' -> 결과 없음
3. 사용자 A: UPDATE employees SET first_name='Toto' WHERE emp_no=500000
4. 사용자 A: COMMIT
5. 사용자 B: SELECT * FROM employees WHERE first_name='Toto' -> 결과 1건 조회

 

사용자 B가 하나의 트랜잭션 내에서 같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는 Repeatable Read 정합성에 어긋난다.

 

이러한 부정합 현상은 일반적인 웹 프로그램에서는 크게 문제되지 않을 수 있지만 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수도 있다. 예를 들어, 다른 트랜잭션에서 입금과 출금 처리가 계속 진행될 때 다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회한다고 가정해보자. 그런데 "Repeatable Read"가 보장되지 않기 때문에 총합을 계산하는 SELECT 쿼리는 실행될 때마다 다른 결과를 가져올 것이다.

중요한 것은 사용 중인 트랜잭션의 격리 수준에 의해 실행하는 SQL 문장이 어떤 결과를 가져오게 되는지 정확히 예측할 수 있어야 한다는 것이다. 그리고 이를 위해서는 각 트랜잭션의 격리 수준이 어떻게 작동하는지 알아야 한다.

 

트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 없이 실행되는 SELECT 문장의 차이를 혼동하는 경우가 있다.

READ COMMITTED 격리 수준에서는 두 문장의 차이가 별로 없지만, REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리도 트랜잭션 범위 내에서만 작동한다. 즉, START TRANSACTION 명령으로 트랜잭션을 시작한 상태에서 동일한 쿼리를 반복해서 실행해도 동일한 결과만 보게된다. 아무리 다른 트랜잭션에서 그 데이터를 변경하고 커밋하더라도 말이다.

REPEATABLE READ

REPEATABLE READ 는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 이상의 격리 수준을 사용해야 한다. 이 격리 수준에서는 READ COMMITTED 에서 발생하는 "Non-Repeatable Read" 부정합이 발생하지 않는다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 Undo 공간에 백업해두고 실제 레코드 값을 변경한다. 이러한 변경 방식을 MVCC(Multi Version Concurrency Control) 이라고 한다. REPEATABLE READ 는 이 MVCC를 위해 Undo 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.

 

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

모든 InnoDB 의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, Undo 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함된다. 그리고 Undo 영역의 백업된 데이터는 불필요하다고 판단되는 시점에 주기적으로 삭제한다. REPEATABLE READ 격리 수준에서는 MVCC 를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 Undo 영역의 데이터는 삭제할 수 없다. 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 Undo 데이터가 필요한 것은 아니다. 더 정확히는 특정 트랜잭션 번호의 구간 내에서 백업된 Undo 데이터가 보존돼야 한다.

 

예) REPEATABLE READ 격리 수준이 작동하는 방식

employees 테이블은 트랜잭션 번호(TRX-ID)가 6인 트랜잭션에 의해 초기화 되었다.

 

1. 사용자 B: BEGIN TRANSACTION (TRX-ID:10)
2. 사용자 B: SELECT * FROM employees WHERE emp_no=500000 -> 'Lara' 반환
3. 사용자 A: BEGIN TRANSACTION (TRX-ID:12)
4. 사용자 A: UPDATE employees SET first_name='Toto' WHERE emp_no=500000
5. 사용자 A: COMMIT (TRX-ID:12)
6. 사용자 B: SELECT * FROM employees WHERE emp_no=500000 -> 'Lara' 반환

 

사용자 B는 사용자 A가 데이터를 변경하는 전후로 한 번씩 SELECT 했지만 결과는 항상 "Lara"라는 값을 가져온다.

사용자 B가 시작한 트랜잭션은 10번을 부여 받았는데, 그때부터 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10(자신의 트랜잭션 번호)보다 작은 트랜잭션 번호에서 변경한 것만 보이게 된다. 따라서 12번인 사용자 A가 시작한 트랜잭션에서 변경한 내용은 보이지 않고 Undo 로그의 데이터를 조회하게 된다.

 

PHANTOM READ(또는 PHANTOM ROW)

예) 사용자 A가 employees 테이블에 INSERT를 실행하는 도중에 사용자 B가 SELECT...FOR UPDATE 쿼리로 employees 테이블 조회

PHANTOM READ

 

사용자 B가 트랜잭션을 시작(TRX-10)하고 실행하는 두 번의 SELECT...FOR UPDATE 쿼리의 결과가 서로 다르다. 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 Phantom Read 라고 한다. SELECT...FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데, Undo 레코드에는 잠금을 걸 수 없다. 그래서 SELECT...FOR UPDATE 나 SELECT...LOCK IN SHARE MODE 로 조회되는 레코드는 현재 레코드의 값을 가져오게 되는 것이다.

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다. InnoDB 테이블에서 기본적으로 순수한 SELECT 작업(INSERT...SELECT... 또는 CREATE TABLE...AS SELECT... 가 아닌)은 아무런 레코드 잠금도 설정하지 않고 실행된다.  InnoDB 메뉴얼에서 자주 나타나는 "Non-locking consistent read(잠금이 필요 없는 일관된 읽기)"는 이를 의미한다. 하지만 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다. SERIALIZABLE 격리 수준에서는 일반적인 DBMS 에서 일어나는 "Phantom Read" 문제가 발생하지 않는다. 하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 에서도 이미 "Phantom Read"가 발생하지 않기 때문에 굳이 SERIALIZABLE 을 사용할 필요성은 없어 보인다.

 

참고: 엄밀하게는 위에서 설명한 대로 SELECT...FOR UPDATE 나 SELECT...FOR SHARE 쿼리의 경우 REPEATABLE READ 격리 수준에서 PHANTOM READ 현상이 발생할 수 있다. 하지만 레코드 변경 이력(Undo Record)에 잠금을 걸 수 없기 때문에 이러한 잠금을 동반한 SELECT 쿼리는 예외적인 상황으로 볼 수 있다.