트랜잭션과 잠금

개요

• 트랜잭션은 작업의 완전성을 보장해 주는 것이다.

• 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상이 발생하지 않게 만들어준다.

• 잠금: 동시성을 제어하기 위한 기능
  - 여러 커넥션이 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해준다.

• 트랜잭션: 데이터의 정합성을 보장하기 위한 기능

트랜잭션

• 애플리케이션레벨에서 트랜잭션의 범위를 초소화 해야한다.

• 트랜잭션 내에 네트워크 작업이 있는 경우 트랜잭션의 처리 속도가 느려져 DB에 부하를 줄 수 있다.

MySQL 엔진의 잠금

• 잠금은 MySQL엔진 레벨과 스토리지 엔진 레벨의 잠금으로 나눌 수 있다.

• MySQL엔진은 MySQL 서버에서 스토리지 엔진의 잠금을 제외한 나머지 부분이다.

• MySQL 엔진은 테이블 데이터 동기화를 위한 테이블 락, 테이블의 구조를 잠그는 메타데이터 락, 사용자의 필요에 맞게 사용할 수 있는 네임드 락이 존재한다.

글로벌 락

• FLUSH TABLES WITH READ LOCK명령으로 획득할 수 있다.

• 범위가 MySQL 서버 전체다.

• MyISAM이나 MEMORY엔진은 백업작업을 할 때 이 글로벌 락을 사용해야 한다.

• InnoDB에서는 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요가 없다.

• Xtrabackup, Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.

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

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

- REPAIR TABLE과 OPTIMIZE TABLE 명령

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

• 백업 락은 일반적인 테이블의 데이터 변경은 허용된다.

• MySQL 서버의 구성은 소스 서버, 레플리카 서버로 구성된다.

• 백업은 레플리카 서버에서 실행된다.

• Xtrabackup, Enterprise Backup 툴이 실행되는 도중에 스키마 변경이 실행되면 백업은 실패하게 된다.

• 백업 락은 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지한다.

테이블 락

• 개발 테이블 단위로 설정되는 잠금이다.

• 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

• 명시적으로는 LOCK TABLES table_name [ READ | WROTE ] 명령으로 특정 테이블의 락을 획득할 수 있다.

• 명시적으로 획득한 잠금은 UNLOCK TABLES명령으로 잠금을 반납 할 수 있다.

• 애플리케이션에서 사용할 필요가 거의 없다.

• 명시적 테이블 잠금은 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미친다.

• 묵시적 락은 MyISAM, MEMORY테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.

• MySQL서버가 쿼리가 실행되면 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경 후 쿼리가 종료되면 잠금을 해제한다.

• InnoDB는 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경으로 묵시적 테이블 락이 발생하지는 않는다.

• 정확히는 InnoDB테이블에도 테이블 락이 설정되지만 대부분의 DML 쿼리에서는 무시되고 스키마를 변경하는 DDL의 경우에만 영향을 미친다.

네임드 락

• GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.

• 테이블, 레코드, DB 객체가 아니라 단순히 사용자가 지정한 문자열에 대한 잠금이다.

• 자주 사용되지는 않는다.

메타데이터 락

• 데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 볍ㄴ경하는 경우에 획득하는 잠금이다.

• 명시적으로 획득하거나 반납할 수 있는게 아니고, 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

InnoDB 스토리지 엔진 잠금

• MySQL에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반 잠금 방식을 탑재하고 있다.

• MySQL 서버의 information_sheme 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있다.

• 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수도 있다.

• InnoDB 엔진의 내부 잠금(세마포어)에 대한 모니터링 방법도 추가됐다.

레코드 락

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

• 레코드 자체가 아니라 인덱스의 레코드를 잠근다.

• 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠근다.

자동증가 락

• MySQL에서 자동 증가하는 숫자 값을 채번하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공한다.

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

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

• 트랜잭션과 관계없이 insert, replace 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.

• 자동증가 락은 테이블에 단 하나만 존재하기 때문에 두 개의 insert쿼리가 동시에 실행되는 경우 나머지 하나는 락을 기다려야 한다.

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

- innodb_autoinc_lock_mode=0: 5.0과 같은 방식

- innodb_autoinc_lock_mode=1: MySQL서버가 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락보다 
가볍고 빠른 래치(뮤텍스)를 이용해 처리한다. (자동증가 값을 가져오면 즉시 잠금이 해제된다.)
MySQL서버가 건수를 예측할 수 없을 때는 '0'과 같은 방식으로 처리한다.
(insert문장이 완료되기 전까지 잠금이 해제되지 않는다.)

- innodb_autoinc_lock_mode=2: 래치(뮤텍스)만 사용한다. 동시처리 성능이 높다.
하지만 유니크한 값이 생성된다는 것만 보장한다.
STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동증가 값이 달라질 수도 있다.

- 8.0부터는 바이너리 로그 포맷이 STATEMENT가 아니라 ROW 포맷이 기본값이 됐기 때문에 기본값이 2다.

인덱스와 잠금

• 레코드 자체가 아니라 인덱스의 레코드를 잠근다.

• 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

• 인덱스가 없으면 쿼리의 조건을 가진 모든 레코드가 락이 걸린다.

• update문장을 위한 인덱스가 준비돼있지 않다면 클라이언트 간 동시성이 떨어져서 한 세션에서 update작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 할 수도 있다.

• 쿼리의 조건에 인덱스가 하나도 없다면, 테이블을 풀스캔 하면서 모든 데이터를 잠그게 될 수도 있다.

• 인덱스 설계가 중요하다.

MySQL 격리 수준

• MySQL은 REPEATABLE READ를 주로 사용한다.

READ UNCOMMITED

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

• 이러한 현상을 더티 리드(Dirty read)라 한다.

• 쓰지 말자.

READ COMMITED

• 오라클에서 기본으로 사용되는 격리 수준

• 온라인 서비스에서 가장 많이 선택되는 격리 수준이다.

• 더티 리드같은 현상은 발생하지 않는다.

• 어떤 트랜잭션에서 데이터를 변경했더라도 commit이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다. (언두 영역에 백업된 데이터를 가져온다.)

• NON-REPEATABLE READ라는 부정합 문제가 있다.

• 처음 조회 한 후 다른 트랜잭션에서 데이터를 변경하고 커밋 한 경우 다시 조회를 하면 결과가 다르다. (select 쿼리를 실행했을 때, 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋난다.)

REPEATABLE READ

• InnoDB엔진에서 기본으로 사용하는 격리 수준이다.

• 바이너리 로그를 가진 MySQL서버에서는 최소 REAPEATABLE READ 격리 수준을 사용해야 한다.

• 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.

• MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다.

SERIALIZABLE

• 가장 단순하면서도 엄격한 격리수준이다.

• 동시 처리 성능이 떨어진다.

• InnoDB 테이블에서 기본적으로 순수한 select작업은 잠금을 설정하지 않는다.(잠금이 필요 없는 일관된 읽기)

• SERIALIZEBLE은 읽기 작업도 잠금을 획득해야 한다. 다른 트랜잭션은 레코드를 변경하지 못하게 된다.

• 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다.