트랜잭션과 잠금

트랜잭션

• 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나, 아무것도 적용되지 않아야 함을 보장해 주는 것
• 데이터의 정합성을 보장해 줌

 

MySQL에서의 트랜잭션

• InnoDB스토리지엔진은 트랜잭션을 제공함.
• MEMORY와 MyISAM 스토리지 엔진은 트랜잭션을 제공하지 않음
• 이로 인해 MyISAM에서는 부분 업데이트(Partial Update)가 발생하고, 이러한 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추는데 상당히 어려운 문제를 만듦 -> 데이터 클렌징 코드를 따로 작성해주어야 함

 

주의사항

• 트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋음
• 일반적으로 데이터베이스 커넥션은 개수가 제한적임 -> 각 단위 프로그램이 커넥션을 소유하는 시간이 길어지면 사용가능한 여유 커넥션의 개수가 줄어들음 -> 어느 순간 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생
• 메일전송이나  FTP파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 작업이 트랜잭션에 포함되면, 외부 서버에서 문제가 발생할 경우 DBMS 서버에도 문제가 발생할 수 있음

 

MySQL 엔진의 잠금

동시성을 제어하기 위함

 

• MySQL 엔진 레벨 잠금: 모든 스토리지 엔진에 영향을 미침 - ex) 테이블락, 메타데이터락, 네임드락
• 스토리지 엔진 레벨 잠금: 스토리지 엔진 간 상호 영향을 미치지 않음 - ex) InnoDB의 레코드 기반 잠금

 

글로벌 락

• FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있다.
• 실행과 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 건다. - 명령 실행 이전에 먼저 실행된 SQL과 트랜잭션이 완료될 때까지 기다려야 함.
• 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다. 
• 글로벌 락은 MySQL 서버의 모든 테이블에 큰 영향을 미친다.
• MyISAM, MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때 글로벌 락을 사용함.

 

백업 락

• InnoDB 스토리지 엔진의 사용이 일반화됨 -> InnoDB는 트랜잭션을 지원하기에, 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없음 -> 좀 더 가벼운 글로벌 락의 필요성이 생김

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

 

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

일반적인 테이블의 데이터 변경은 허용됨

Xtrabackup, Enterprise Backup 등 백업 툴들의 안정적인 실행

 

테이블 락

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

명시적

• LOK TABELS table_name [READ | WRITE] 명령으로 특정 테이블의 락을 획득할 수 있음.
• UNLOCK TABLES 명령으로 잠금을 해제할 수 있음
• MyISAM, InnoDB 스토리지 엔진을 사용하는 테이블 모두에 적용 가능
• 테이블을 잠그는 작업은 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치기에 거의 사용하지 않음

묵시적

• MyISAM, MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생함
• MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용됨
• InnoDB의 경우 테이블 락이 설정되지만 스토리지 엔진 차원에서 레코드 기반 잠금을 제공함 -> 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.

 

네임드 락

• GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정한다.
• 사용자가 지정한 문자열(String)에 대해 획득하고 반납하는 잠금이다.
•  테이블이나, 레코드 같은 데이터베이스 객체가 대상이 아니다.

여러 클라이언트의 상호 동기화 처리: 데이터베이스 서버 1대에 5대의 웹서버가 접속하는 서비스에서 5대의 웹서버가 어떤 정보를 동기화해야 하는 요건

 

복잡한 요건으로 레코드를 변경하는 트랜잭션: 배치 프로그램 등 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 된다. 이때, 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류하여 네임드 락을 걸고 쿼리를 실행하면 쉽게 문제를 해결할 수 있다.

 

MySQL 8.0부터는 네임드 락을 중첩해서 사용가능하고, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가됨

 

 

메타데이터 락

• 데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금
• RENAM TABLE tab_a TO tab_b 같이 테이블 이름을 변경하는 경우 자동으로 획득
• 명시적으로 획득하거나 해제하는 것이 아님
• RENAME TABLE 명령의 경우 원복 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정함

 

InnoDB 스토리지 엔진 잠금

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

• 레코드 기반 잠금 방식 덕분에 MyISAM보다 뛰어난 동시성 처리를 제공함
• 잠금 정보가 작은 공간으로 관리되어 락 에스컬케이션(레코드락->페이지락->테이블락)이 되지 않음
• 이원화된 잠금 처리 탓에 InnoDB에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기 까다로움

 

잠금/트랜잭션 모니터링 방법

• MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODBTRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 조인해서 조회하면, 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있음
• 장시간 장금을 가지고 있는 클라이언트를 찾아 종료할 수도 있음
• Performance Schema 스토리지 엔진을 이용하는 InnoDB 내부 잠금(세마포어)에 대한 모니터링 방법도 추가됨

InnoDB에는 레코드 락과 레코드 사이를 잠그는 갭 락이 존재한다.

RealMySQL 8.0 p167.

 

레코드 락

• 레코드 자체만을 잠그는 것을 레코드 락이라고 한다.
• MySQL은 특이하게 레코드 자체가 아닌 인덱스의 레코드를 잠근다 - 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
• InnoDB의 보조 인덱스를 이용한 변경 작업은 대부분 넥스트 키락 또는 갭 락을 사용한다.
• InnoDB의 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭에 대해서는 잠그지 않고, 레코드 자체에만 락을 건다.

 

갭 락

• 레코드 자체가 아닌 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미함
• 레코드와 레코드 사이 간격에 새로운 레코드가 insert 되는 것을 제어한다.

 

넥스트 키 락

• 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금이다.
•  바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.
• 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다.
• 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락, 갭 락을 줄이는 것이 좋다. 

 

자동 증가 락

AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 insert 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다. 

InnoDB에서는 이를 위해 내부적으로 AUTO_INCREMENT 락이라는 테이블 수준 잠금을 사용한다.

 

• 자동 증가 값이 한 번 증가하면 절대 줄어들지 않는 이유는 자동 증가 락을 최소화하기 위해서다.
• INSERT 쿼리가 실패해도 한 번 증가된 AUTO_INCREMENT 값은 다시 줄어들지 않고 그대로 남는다.
• INSERT, REPLACE 쿼리 문장처럼 새로운 레코드를 저장하는 쿼리에서만 필요하다.
• InnoDB의 다른 잠금(레코드 락이나 넥스트 키 락)과 달리 트랜잭션과 관계없이 INSERT, REPLACE 문장에서 AUTOINCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.
• AUTO_INCREMENT 칼럼에 명시적으로 값을 설정해도 자동 증가 락을 건다.
• AUTO_INCREMENT 락은 명시적으로 획득하고 해제할 수 없다.

 

MySQL 5.1 이상부터 innodb_autoinc_lock_mode라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.

 

- innodb_autoinc_lock_mode = 0

• MySQL 5.0과 동일한 잠금 방식, 모든 INSERT 문장은 자동 증가 락을 사용한다.

 

- innodb_autoinc_lock_mode = 1

• INSERT 되는 레코드 건수를 정확히 예측할 수 있으면, 자동 증가 락보다 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다. 
• INSERT... SELECT처럼 건수를 예측할 수 없을 때는 자동 증가 락을 사용한다.
• INSERT 되는 레코드는 연속된 증가 값을 갖으므로, 해당 설정을 연속 모드(Concecutive Mode)라고도 한다.
• 대량 INSERT가 수행될 때 InnoDB는 여러 개의 자동 증가 값을 한 번에 할당받아 INSERT 되는 레코드에 사용한다.
• MySQL 5.7 버전의 기본값이다.
• 한 번에 할당받은 값들이 남아서 사용되지 못하면 폐기하므로 대량 INSERT 문장의 실행 후 INSERT 되는 레코드의 자동 증가 값은 연속되지 않고 누락된 값이 발생할 수 있다.

 

 

- innodb_autoinc_lock_mode = 2

• 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다.
• 하나의 INSERT 문장으로 INSERT 되는 레코드라도 연속된 자동 증가 값을 보장하지 않는다. 따라서 인터리브드(Interleaved) 모드라고도 부른다.
• MySQL 8.0  버전의 기본값이다. 바이너리 로그 포맷의 기본값이 ROW 포맷이 되었기 때문이다.
• ROW 포맷이 아니라 STATEMENT 포맷의 바이너리 로그를 사용한다면 설정 값을 1로 변경해서 사용할 것을 권장한다.
• INSERT... SELECT와 같은 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아진다.
  이 설정에서 작동하는 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장한다.
• STATEMENT 포맷 바이너리 로그를 사용하여 복제하는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수 있다. 

 

인덱스와 잠금

• InnoDB의 잠금은 인덱스를 잠그는 방식이므로, 변경할 레코드를 찾기 위해 검색한 인덱스의 레코드에 모두 락을 걸어야 한다.
• 주의사항: UPDATE를 위한 적절한 인덱스가 준비돼 있어야 한다.
• ex) 30만 개의 레코드가 있는 사원 테이블에서 first_name='Georgi', last_name='Klassen'인 레코드를 검색한다고 가정하자.
• 이때 first_name은 인덱스, last_name은 인덱스라고 하면 first_name='Georgi'인 모든 레코드가 잠긴다.
• 만약에 테이블의 인덱스가 하나도 없다면, 30만 개의 모든 레코드가 잠긴다.

RealMySQL 8.0 p 172.

 

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

• 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 있어도 잘 발견되지 않는다.
• MySQL 5.1부터 information_schema라는 DB에 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 통해 레코드 잠금과 잠금 대기에 대한 조회가 가능하다.
• 쿼리 하나만 실행하면 잠금과 잠금 대기를 볼 수 있다.   
• 강제로 잠금을 해제하려면 KILL 명령을 이용해 MySQL 서버의 프로세스를 강제 종료한다.
• MySQL 8.0부터는 information_schem의 정보들이 조금씩 Deprecated 되고, performance_schema의 data_locks와 data_lock_waits 테이블로 대체되고 있다.

 

MySQL의 격리 수준

• 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.
• 수준은 크게 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 4가지로 나뉜다.
• 뒤로 갈수록 각 트랜잭션 간의 데이터 격리 정도가 심해지며, 동시 처리 성능은 떨어진다.
•  DIRTY READ라고도 하는 READ UNCOMMITTED는 일반적인 데이터베이스에서 거의 사용하지 않음
• 동시성이 중요한 데이터베이스에서 SERIALIZABLE은 거의 사용하지 않음
• SERIALIZABLE 격리 수준이 아니면, 다른 격리 수준의 처리 성능 차이는 거의 없다.
• 격리 수준에 따라 세 가지 부정합이 발생할 수도 않을 수도 있다.

• DIRTY READ: 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데 다른 트랜잭션에서 볼 수 있는 현상
• NON_REPEATABLE READ: 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 서로 다른 결과가 발생하는 현상
• PHANTOM READ: 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보여다 안 보였다 하는 현상

RealMySQL 8.0 p176.

InnoDB에서는 독특한 특성 때문에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않는다.

 

 

READ UNCOMMITTED

• 각 트랜잭션에서 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보이는 격리 수준
• 더티 리드가 발생한다.
• 사용자 A가 데이터를 INSERT 요청 후 커밋되기 전에, 사용자 B가 동일한 테이블에 대해 SELECT 하고, 사용자 A의 데이터가 롤백될 때 발생한다.
• RDBMS 표준에서는 트랜잭션 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다 - MySQL을 사용한다면, 최소 READ COMMITTED 이상의 격리 수준을 권장한다.

RealMySQL 8.0 p177.

 

READ COMMITTED

• COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있는 격리 수준
• 더티 리드가 발생하지 않는다.
• NON-REPEATABLE READ가 발생한다.
• 사용자 A가 Begin 명령으로 트랜잭션을 시작한 후 첫 번째 SELECT를 요청하고, 사용자 B가 데이터를 변경하고 커밋한 후, 사용자 A가 두 번째로 SELECT를 요청했을 때, 첫 번째와 두 번째 SELECT 결과가 다를 때 발생한다.
• 오라클 DBMS에서 기본으로 사용되는 격리 수준

RealMySQL 8.0 p178.

RealMySQL 8.0 p179.

 

REPEATABLE READ

• NON-REPEATABLE READ 부정합이 발생하지 않는 격리 수준
• MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.
• MVCC: InnoDB 스토리지 엔진에서 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해 두고 실제 레코드 값을 변경하는 기술이다.
• READ COMMITTED도 MVCC를 통해 COMMIT 되기 전의 데이터를 보여주지만, 두 격리 수준은 '언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 보여주느냐'가 다르다.
• 언두 영역의 백업된 데이터는 불필요하다고 판단하는 시점에 주기적으로 삭제되지만, REAPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터를 삭제할 수 없다.
• InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.
• 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을  사용해야 한다.
• PHANTOM READ가 발생한다.
• 사용자 A가 INSET 하는 중에, 사용자 B가 SELECT... FOR UPDATE 쿼리로 동일한 테이블을 조회했을 때 발생할 수 있다.
• SELECT... FOR UPDATE 쿼리를 수행할 때, 레코드에 쓰기 잠금을 걸어야 하는데 언두 레코드는 잠금을 걸 수 없다.

RealMySQL 8.0 p181.

RealMySQL 8.0 p182.

 

SERIALIZABLE

• 가장 단순하지만, 가장 엄격한 격리 수준
• 동시 처리 성능이 가장 떨어진다.
• InnoDB 테이블에서 순수한 SELECT 작업(INSERT... SELECT... 또는 CREATE TABLE... AS SELECT... 가 아닌)은 아무런 레코드 잠금을 설정하지 않고 실행된다.
• InnoDB 매뉴얼에서 자주 등장하는 "Non-locking consistent read, 잠금이 필요 없는 일관된 읽기"가 이것을 의미한다.
• 하지만 SERIALIZABLE 격리 수준에서는 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 동시에 다른 트랜잭션은 해당 레코드를 변경하지 못한다.
• PHANTOM READ가 발생하지 않는다.
• InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 "PHANTOM READ"가 발생하지 않기 때문에 SERIALIZABLE을 사용할 필요는 없다.

 

 

 

출처

RealMySQL 8.0 1권