5. 트랜잭션

 5장은 Mysql 동시성에 영향을 미치는 Lock과 트랜잭션, 트랜잭션의 격리 수준을 살펴보겠다. 트랜잭션은 작업의 완선성을 보장해 주는 것이다. 즉 논리적인 작업 셋을 모두 완벽하게 처리하거나, 그렇지 않으면 원 상태로 복구해서 작업의 일부만 적용되는 현상이 발생하지 않게 해주는 기능이다.

 

Lock, 트랜잭션은 서로 비슷하지만 사실 Lock은 동시성을 제어하기 위한 기능이고, 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다. 예로 하나의 회원 정보 레코드를 여러 커넥션에서 동시에 변경하는데 Lock이 없다면 하나의 데이터를 여러 커넥션에서 변경할 수 있다. 레코드의 값은 예측할 수 없다. Lock은 여러 커넥션에서 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해준다. 격리 수준 이라는 것은 하나의 트랜잭션 내 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.

 

 

5.1 트랜잭션

 Mysql 서버에서는 MyISAM, MEMORY 스토리지 엔진이 더 빠르다고 생각하고, InnoDB 스토리지 엔진은 사용하기 복잡하고 번거롭다고 생각한다. 하지만 사실 MyISAM, MEMORY 같이 트랜잭션을 지원하지 않는 스토리지 엔진의 테이블이 더 많은 고민거리가 있다. 이번 절에서는 트랜잭션을 지원하지 않는 MyISAM과 트랜잭션을 지원하는 InnoDB 처리 방식 차이를 잠깐 살펴볼 예정이다.

 

 

5.1.1 Mysql에서의 트랜잭션

 트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의마 있는 개념이 아니다. 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있는 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용, 아무것도 적용되지 않아야 함을 보장해주는 것이다.

 

간단한 예제를 트랜잭션 관점에서 InnoDB, MyISAM 테이블의 차이를 살펴보겠다. 아래처럼 각각 레코드를 1건씩 저장한 후 AUTO-COMMIT 모드에서 쿼리 문장을 각각 테이블에서 실행하자.

========================================================================================

mysql > CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE = MyISAM;

mysql > INSERT INTO tab_myisam (fdpk) VALUES (3);

 

mysql > CREATE TABLE tab_innodb ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE = INNODB;

mysql > INSERT INTO tab_innodb (fdpk) VALUES (3);

 

mysql > SET autocommit=ON;

 

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

ERROR 1062 (23000) : Duplicate entry '3' for key 'PRIMARY'

 

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

ERROR 1062 (23000) : Duplicate entry '3' for key 'PRIMARY'

========================================================================================

 

 두 INSERT 모두 기본키 중복 오류로 쿼리가 실패했다. 하지만 2개의 테이블을 조회해보면 MyISAM 테이블에 '1', '2' INSERT 된 상태로 남아있다. 즉, MyISAM 테이블에 INSERT 문장이 실행되면서 차례로 '1', '2'를 저장하고 '3'을 저장하는 순간 중복 키 오류가 발생한 것이다. 하지만 MyISAM 테이블에서 실행되는 쿼리는 이미 '1', '2' 를 그대로 두고 쿼리 실행을 종료해버린다.

 

MEMORY 스토리지 엔진을 사용하는 테이블도 MyISAM 테이블과 동일하게 작동한다. 하지만 InnoDB는 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 문장을 실행하기 전 상태 그대로 복구했다. MyISAM 테이블에서 발생하는 이러한 현상을 부분 업데이트 (Partial Update)라고 표현하며, 이러한 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추는데 어지럽다.

 

 

5.1.2 주의사항

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

 

========================================================================================

1) 처리 시작

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

  => 트랜잭션 시작

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

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

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

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

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

7) 저장된 내용 또는 기타 정보를 DBMS에 저장

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

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

  => 트랜잭션 종료 (COMMIT)

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

10) 처리완료

========================================================================================

위 처리 절차에서 DBMS의 트랜잭션 처리에 좋지 않은 영향을 미치는 부분을 나눠서 확인해보자

 

- 실제로 많은 개발자가 데이터베이스의 커넥션을 생성하는 코드를 1번과 2번 사이에 구현하며, 동시에 START TRANSACTION 명령으로 트랜잭션을 시작한다. 그리고 9번과 10번 사이에서 트랜잭션을 COMMIT하고 커넥션을 종료한다. 실제로 DBMS에 데이터를 저장하는 작업은 5번부터 시작된다. 그래서 2,3,4 번 절차가 빨리 처리된다 하더라도 DBMS의 트랜잭션에 포함시킬 필요는 없다. 일반적으로 데이터베이스 케넥션은 개수가 제한적이라 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용가능한 여유 커넥션의 개수가 줄어든다.

 

- 제일 큰 위험은 8번이라고 볼 수 있다. 메일 전송, FTP 파일 전송 작업, 네트워크를 통해 원격 서버와 통신하는 작업은 어떻게 해서든 DBMS 트랜잭션 내에서 제거하는 것이 좋다. 프로그램이 실행되는 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버 뿐 아니라 DBMS 서버까지 위험할 수 있기 떄문이다.

 

- 또한 이 처리 절차는 DBMS 작업이 크게 4개 있다. 사용자가 입력한 정보를 저장하는 5, 6번 작업은 반드시 하나로 묶어야하고, 7번 작업은 저장된 데이터의 단순 확인 및 조회이므로 트랜잭션에 포함할 필요는 없다. 9번 작업은 성격이 다르기에 이전 트랜잭션 (5,6)과 함꼐 묶지 않아도 무방해 보인다. 이러한 작업은 별도의 트랜잭션으로 분리하는 것이 좋다.

 

그럼 문제가 될 만한 부분 3가지를 보완해서 처리 절차를 다시 한번 설계해보자. 아래처럼 한 것의 궁극적인 목표는 프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 범위를 최소화해야 하는 것이다. 또한 프로그램의 코드에서 코드가 적더라도 네트워크 작업이 있는 경우 반드시 트랜잭션에서 배제해야 한다.

========================================================================================

1) 처리 시작

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

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

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

  => 데이터베이스 커넥셩 생성(또는 커넥션 풀에서 가져오기)

  => 트랜잭션 시작

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

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

  => 트랜잭션 종료 (COMMIT)

7) 저장된 내용 또는 기타 정보를 DBMS에 저장

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

  => 트랜잭션 시작

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

  => 트랜잭션 종료 (COMMIT)

  => 데이터베이스 커넥션 종료(또는 커넥션 풀에 반납)

10) 처리완료

========================================================================================

 

 

5.2 Mysql 엔진의 잠금

 Mysql에서 사용되는 Lock은 크게 스토리지 엔진 레벨과 Mysql 엔진 레벨로 나눌 수 있다. Mysql 엔진은 Mysql 서버에서 스토리지 엔진을 제외한 나머지로 이해하면 되는데, Mysql 엔진 레벨의 Lock은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 Lock은 스토리지 엔진 간 상호 영향을 미치지는 않는다. Mysql 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블의 구조를 잠그는 메타데이터 락(Metadata Lock) 그리고 사용자의 필요에 맞게 사용할 수 있는 네임드 락(Named LOck)이라는 Lock 기능도 제공한다. 이러한 잠금의 특징과 어떤 경우에 사용되는지 알아보자.

 

 

5.2.1 글로벌 락

 글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있고, Mysql에서 제공하는 잠금 가운데 가장 범위가 크다. 일단 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다. 글로벌 락이 영향을 미치는 범위는 Mysql 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다. 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때 글로벌 락을 사용한다. 

 

 이미 살펴본 바와 같이 FLUSH TABLES WITG READ LOCK 명령을 이용한 글로벌 락은 Mysql 서버의 모든 변경 작업을 멈춘다. 하지만 Mysql 서버가 업그레이드 되면서 MyISAM이나 MEMORY 스토리지 엔진보다는 InnoDB 스토리지 엔진의 사용이 일반화되었다. InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다. 또한 Mysql 8.0부터 InnoDB가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성이 생겼다. 그래서 8.0 버전부터는 Xtrabackup이나 Enterprise Backup과 같은 백업 툴의 안정적인 실행을 위해 백업 락이 도입됐다.

 

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

- 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제

- REPAIR TABLE 과 OPTIMIZE TABLE 명령

- 사용자 관리 및 비밀번호 변경

 

 But 백업 락은 일반적인 테이블의 변경은 허용된다. 일반적인 Mysql 서버의 구성은 소스 서버(Source server)와 레플리카 서버(Replica server)로 구성되는데, 주로 백업은 레플리카 서버에서 실행된다. Mysql 백업 락은 복제는 실행되지만, 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 한다.

 

 

5.2.2 테이블 락

 테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적 특정 테이블의 락을 획득할 수 있다. 명시적으로는 "LOCK TABLES table_name [ READ | WRITE ]" 명령으로 특정 테이블의 락을 획득할 수 있다. MyISAM, InnoDB 스토리지 엔진을 사용하는 테이블도 동일하게 설정할 수 있다. 명시적인 테이블 락도 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없다. 명시적으로 테이블을 잠그는 작업은 글로벌 락과 비슷하게 온라인 작업에 영향을 미치기 때문이다.

 

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

 

 

5.2.3 네임드 락

 네임드 락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 대상이 테이블이나 레코드 또는 AUTO_INCREMENT 같은 데이터베이스 객체가 아니라는 것이다. 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금이다.

자주 사용되지 않으며, 예로 데이터베이스 서버 1대에 5대 웹 서버가 접속해서 서비스하는 상황에서 5대의 웹 서버가 어떤 정보를 동기화해야하는 요건처럼 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 이용하면 쉽게 해결할 수 있다.

 

 네임드 락의 경우 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다. 한꺼번에 많은 레코드를 변경하는 쿼리의 경우 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 간단히 해결할 수 있다.

 

 

5.2.4 메타데이터 락

 메타데이터 락은 데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 명시적으로 획득하거나 해제할 수 있는 것이 아니고, "RENAME TABLE tab_a To tab_b" 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다. 때로는 메타데이터 잠금과 InnoDB 트랜잭션을 동시에 사용해야 하는 경우도 있다.

 

 INSERT만 실행되는 로그 테이블을 가정해보자. 그런데 로그 테이블의 구조를 변경해야 할 요건이 발생했다. 물론 Mysql 서버의 Online DDL을 이용해 변경할 수 있지만, 시간이 오래 걸리는 경우라면 언두 로그의 증가와 Online DDL이 실행되는 동안 누적된 Online DDL 버퍼의 크기 등 고민해야 할 문제가 많다. 또한 Mysql 서버의 DDL은 단일 스레드로 작동하기 때문에 많은 시간이 소모된다. 이때는 새로운 구조의 테이블을 생성하고 먼저 최근의 데이터까지는 프라이머리 키인 id값을 범위별로 나눠서 여러개의 스레드로 빠르게 복사한다. 그리고 나머지 데이터는 트렌잭션과 테이블 잠금, RENAME TABLE 명령으로 중단 없이 실행할 수 있다. 

 

 

5.3 InnoDB 스토리지 엔진 잠금

 InnoDB 스토리지 엔진은 Mysql에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. 레코드 기반의 잠금 방식 덕분에 MyISAM 보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있다. 하지만 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 Mysql 명령을 이용해 접근하기 까다롭다.

 

 예전 버전의 Mysql 서버에서는 InnoDB 잠금 정보를 진단할 수 있는 도구는 lock_monitor(innodb_lock_monito 라는 이름의 InnoDB 테이블을 생성해서 InnoDB의 잠금 정보를 덤프하는 방법) 와 SHOW ENGINE INNODB STATUS 명령이 전부였다.

 

 하지만 최근 버전에서는 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다. InnoDB의 잠금에 대한 모니터링도 더 강화되면서 Performance Schema를 이용해 InnoDB 스토리지 엔진의 내부 잠금에 대한 모니터링 방법도 추가됐다.

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

 

레코드 락

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

 

 

갭 락

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

 

 

넥스트 키 락

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

 

 

자동 증가 락

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

 

 AUTO_INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE나 DELETE 등의 쿼리에서는 걸리지 않는다. 또한 다른 잠금과 달리 트랜잭션 관계없이 INSERT, REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다. 5.1 버전 이상부터는 innodb_autoinc_lock_mode 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.

 

 

5.3.2 인덱스와 잠금

 InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계가 있다. "레코드 락"을 소개하면서 잠깐 언급했듯이 InnoDB 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다. 아래 예시를 보자

 

// 멤버로 담긴 ix_firstname이라는 인덱스가 준비돼 있다.

mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi';   => 253

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

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

 

위 UPDATE 문장이 실행되면 1건의 레코드가 업데이트 된다. 하지만 1건의 업데이트를 위해 몇 개의 레코드 락을 걸어야 할까? 이 UPDATE 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi'이며, last_name 칼럼은 인덱스에 없기 때문에 first_name='Georgi' 인 레코드 253건의 레코드가 모두 잠긴다. 만약 테이블에 인덱스가 하나도 없다면 풀 스캔하면서 모든 레코드를 잠그게 된다. 이것이 Mysql 방식이며, InnoDB에서 인덱스 설계가 중요한 이유 또한 이것이다.

 

 

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

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

 

강제로 잠금을 해제하려면 KILL 명령을 이용해 Mysql 서버의 프로세스를 강제 종료하면 된다. 만약 아래와 같은 잠금 시나리오를 가정하자

 

커넥션 1                                                                                      커넥션 2                                                                 커넥션3

BEGIN;

UPDATE employees

SET birth_date=NOW() WHERE

emp_no=100001;

                                                                                 UPDATE employees

                                                                                 SET hire_date=NOW() WHERE emp_

                                                                                 no=100001;

                                                                                                                                                                 UPDATE employees

                                                                                                                                                                 SET hire_date=NOW(),

                                                                                                                                                                 birth_date=NOW()

                                                                                                                                                                 WHERE emp_no=100001

각 트랜잭션이 어떤 잠금을 기다리고, 기다리고 있는 잠금을 어떤 트랜잭션이 가지고 있는지를 쉽게 메타 정보를 통해 조회할 수 있다. 5.1 버전부터는 information_schema라는 DB에 INNODB_TRX라는 테이블과 INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 통해 확인이 가능했다. 하지만 8.0버전부터는 information_schema의 정보들은 조금씩 Deprecated되고 있으며, 대신 performance_schema의 data_locks와 data_lock_waits 테이블로 대체되고 있다. 

 

 우선 아래 내용은 Mysql 서버에서 앞의 UPDATE 명령 3개가 실행된 상태의 프로세스 목록을 조회한 것이다. 17번 스레드는 지금 아무것도 하지 않고 있지만, 트랜잭션을 시작하고 UPDATE 명령이 실행 완료된 것이다. 하지만 아직 17번 스레드는 COMMIT을 실행하지 않은 상태이므로 업데이트한 레코드의 잠금을 그대로 가지고 있는 상태다. 18번 스레드가 그다음으로 UPDATE 명령을 실행했으며, 이후 19번 스레드에서 UPDATE 명령을 실행했다. 프로세스 목록에서 18, 19번 스레드는 잠금 대기로 인해 아직 UPDATE 명령을 실행중인 것으로 표시된다.

 

mysql> SHOW PROCESSLIST;

========================================================================================||        Id        ||       Time       ||       State         ||      Info                                                                                                 

||                    ||                      ||                        ||                                                                                       

||        17        ||          607     ||                         ||        NULL                                                                     

||        18        ||         22        ||     updating    ||    UPDATE employees SET birt~ emp_no=100001  

||        19        ||          21       ||     updating     ||    UPDATE employees SET birt~ emp_no=100001 

||                    ||                     ||                         ||                                                                                      

||                    ||                     ||                         ||                                                                                      

========================================================================================

 

 

 이제 performance_schema의 data_locks 테이블과 data_lock_waits 테이블을 조인해서 잠금 대기 순서를 확인해보자. 결과는 쿼리의 실행 결과를 보면 현재 대기중인 스레드는 18,19번인 것을 알 수 있다. 18번 스레드는 17번 스레드를 기다리고 있고, 19번 스레드는 18, 17번 스레드를 기다리고 있다. 이는 잠금 대기 큐의 내용을 그대로 보여주기 때문에 이렇게 표시되는 것이다.

 

 즉 17번 스레드가 잠금을 해제하고, 18번 스레드가 그 잠금을 획득하고 UPDATE 완료 후 잠금을 풀어여만 19번 스레드가 UPDATE를 실행할 수 있음을 의미한다. 만약 위 상황에서 17번 스레드를 KILL 한다면 나머지 UPDATE 명령들이 진행되며 잠금 경합이 끝날 것이다.

 

 

5.4 Mysql 격리 수준

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

 

"READ UNCOMMITTED", "READ COMMITTED", "REPEATABLE READ", "SERIALIZABLE" 크게 4가지로 나뉜다. "DIRTY READ" 라고 불리는 READ UNCOMMITTED는 일반적인 데이터베이스에서 사용되지 않고, SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다.

 

 4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시 처리 성능도 떨어지는 것이 일반적이라고 볼 수 있다. 격리 수준이 높아질수록 Mysql 서버 처리 성능이 떨어질 것 같지만, 사질 SERIALIZABLE 수준이 아니면 크게 상관없다.

 

데이터베이스의 격리 수준을 이야기하면 항상 언급되는 3가지 부정합의 문제점이 있다. 이 세가지 부정합의 문제는 격리 수준의 레벨에 따라 발생할 수도, 아닐 수도 있다.

세 가지 부정합 문제점

 일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITTED, REPEATABLE READ 중 하나 사용한다. 오라클 같은 DBMS에서는 주로 READ COMMITTED, Mysql에서는 주로 REPEATABLE READ 사용한다.

 

 

READ UNCOMMITTED

:  각 트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK 여부 상관없이 다른 트랜잭션에서 보인다.

READ UNCOMMITTED

 사용자 A와 사용자 B가 각각 트랜잭션을 시작한 상태에서 사용자 A가 "LaLa"라는 사원을 넣었고 커밋을 하지 않은상태인데도 불구하고, 사용자 B는 "LaLa"라는 사원을 조회할수 있는 모습입니다. 여기서 사용자A의 트랜잭션에서 문제가 발생해 Rollback을 한다 쳐도 사용자 B는 "LaLa"가 정상적인 회원이라고 판단해 로직을 처리할 수 있다.

 

 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 Dirty read라 하고, 이가 허용되는 격리 수준이 READ_UNCOMMITTED다. 문제가 많은 격리 수준이다.

 

 

READ COMMITTED

: 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다. 

 

READ UNCOMMITTED

 사용자 B의 SELECT 쿼리 결과는 employees 테이블이 아니라 언두 영역에 백업된 레코드에서 가져온 것이다. READ COMMITTED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없기 때문이다. 최종적으로 사용자 A가 변경된 내용을 커밋하면 그때부터는 다른 트랜잭션에서도 백언된 언두 레코드("Lara")가 아니라 새롭게 변경된 ("Toto") 값을 참조할 수 있게 된다.

 

 

NON-REPEATABLE READ

: READ COMMITTED 격리 수준에서도 "NON_REPEATABLE READ" 라는 부정합 문제가 있다. 

NON-REPEATABLE READ

 위에서 'Toto' 결과가 없었고, 사용자 A가 "Lara" -> "Toto" 변경하고 커밋 후 다시 사용자 B가 조회하면 처음과 다르게 1건이 조회된다. 문제 없어 보이지만, 사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는 "REPEATABLE READ" 정합성에 어긋나는 것이다.

 

 READ COMMITTED 격리 수준에서는 트랜잭션 내부, 외부에서 실행되는 SELECT 문장의 차이가 없다.

REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동한다. (아무리 다른 트랜잭션에서 COMMIT 하고 변경해도 항상 같은 동일한 결과만 보임)

 

 

REPEATABLE READ

: Mysql의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 바이너리 로그를 가진 Mysql 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다. 이 격리 수준에서는 위와 같은 부정합이 발생하지 않는다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경한다. 이러한 변경 방식을 MVCC라고 앞에서 배웠다.

 

 REPEATABLE READ는 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다. 사실 READ COMMITTED도 MVCC를 이용해 COMMIT 되기 전의 데이터를 보여준다. REPEATABLE READ와 READ COMMITTED 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다.

 

모든 InnoDB 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 언두영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다. 

REPEATABLE READ

  사용자B의 트랜잭션 번호는 10이고 사용자A의 트랜잭션 번호는 12입니다. 그러면 10번 트랜잭션 안에서 실행되는 모든 select 쿼리는 자신의 트랜잭션 번호보다 작은 트랜잭션에서 변경한 것만 보이게 됩니다.

 

 

PHANTOM READ

: 사실 언두 영역에 백업된 데이터가 하나만 있다고 위에서 가정했지만, 얼마든지 더 존재할 수 있다. 한 사용자가 BEGIN으로 트랜잭션을 시작하고, 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수 있다. REPEATABLE READ 격리 수준에서도 다음과 같은 부정합이 발생할 수 있다. 사용자 A가 테이블에 INSERT 도중 사용자 B가 SELECT ... FOR UPDATE 쿼리로 테이블을 조회했을 때 어던 결과를 가져올까?

PHANTOM READ

 Select ..for update 쿼리는 select하는 쿼리에 쓰기 잠금을 걸어야하는데 undo레코드에는 잠금을 걸수 없습니다. 그래서 사용자 B는 다른 트랜잭션이 변경한 게 보였다가 안보였다가 하는 PhantomRead 현상이 일어납니다.

 

 

SERIALIZABLE

: 가장 단순한 격리 수준이면서 동시에 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다. InnoDB 테이블에서 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행된다. 잠금이 필요 없는 일관된 읽기를 뜻한다. 트랜잭션의 격리 수준이 이와 같다면, 읽기 작업도 공유 잠금을 획득해야만 하고, 동시에 다른 트랜잭션은 그러한 레코드 변경이 불가하다.

 

 한 트랙잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다. 하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키락 덕분에 REPEATABLE READ 격리 수준에서도 PHANTOM READ 가 발생하지 않기에 굳이 SERIALIZABLE을 사용할 필요성은 없어 보인다.