트랜잭션과 잠금

트랜잭션

• 작업의 완전성, 즉 데이터의 정합성을 보장해 주는 기능.

• 작업의 일부만 적용되는 현상이 발생하지 않게 만들어주는 기능이다. all or nothing

• 프로그램 코드에서 트랜잭션의 범위를 최소화하는 것이 좋다.

  • 네트워크 작업이 있는 경우 반드시 트랜잭션에서 배제해야 한다.

잠금

• 동시성을 제어하기 위한 기능.

• 잠금이 없다면 하나의 데이터를 여러 커넥션에서 동시에 변경할 수 있게 되어 레코드의 값을 예측할 수 없게 된다.

  • 여러 커넥션의 요청을 순서대로 진행할 수 있게 해준다.

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

  • MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진 레벨에 영향을 미친다.

  • 그 역은 영향을 미치지 않는다.

MySQL 엔진의 잠금

글로벌 락

• 락 획득 명령 FLUSH TABLES WITH READ LOCK

• MySQL 서버의 모든 변경 작업을 멈추는 락으로 제공되는 잠금 중 가장 범위가 크다.

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

• 온라인 작업에 상당한 영향을 미치기에 애플리케이션에서 사용할 일은 거의 없다.

테이블 락

• 락 획득 명령 LOCK TABLES table_name [READ|WRITE]

  • 명시적 획득 반납 명령 UNLOCK TABLES

• 온라인 작업에 상당한 영향을 미치기에 애플리케이션에서 사용할 일은 거의 없다.

묵시적 락

• InnoDB는 스토리지 엔진에서 레코드 기반의 잠금을 제공한다.

• 그래서 대부분의 DML 쿼리에서는 무시되고, DDL 쿼리에만 영향을 미친다.

네임드 락

• 락 획득 명령 GET_LOCK() 함수

• 임의의 문자열에 대해 잠금을 설정할 수 있다.

메타데이터 락

• 테이블이나 뷰 같은 데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득하는 잠금.

  • 명시적인 획득/해제가 아닌 자동으로 획득되는 잠금.

InnoDB 스토리지 엔진 잠금

• 8.0 부터 기본 스토리지 엔진으로 채택되었으며 트랜잭션을 지원한다.

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

  • MyISAM보다 뛰어난 동시성 처리 제공.

레코드 락

• 레코드 자체를 잠근다는 뜻이지만 InnoDB는 레코드가 아닌 인덱스의 레코드를 잠근다.

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

  • 레코드를 잠그느냐, 인덱스를 잠그느냐의 차이는 상당히 크고 중요하다.

갭 락

• 레코드 자체가 아닌 레코드와 바로 인접한 레코드 사이의 간격만의 잠금.

  • 즉 레코드와 레코드 사이에 새로운 레코드가 생성되는 것을 제어한다.

• 갭 락은 주로 넥스트 키 락의 일부로 사용된다.

넥스트 키 락

• 레코드 락과 갭 락을 합쳐놓은 형태.

• REPEATABLE READ 격리 수준을 사용해야 한다.

• 넥스트 키 락과 갭 락은 데드락이나 트랜잭션 지연을 유발하곤 하는데, 바이너리 로그 포맷을 ROW로 바꿔 이 락을 줄이는 것이 좋다.

  • 8.0 부터는 기본 설정 되었다.

자동 증가 락

• 테이블마다 유일한 AUTO_INCREMENT 속성을 지원하기 위한 테이블 수준의 잠금.

• INSERT, REPLACE와 같이 새로운 레코드를 저장하는 쿼리에서만 필요하다.

  • UPDATE, DELETE에서는 잠금되지 않음.

• 명시적 획득/해 방법은 없다.

• 다른 잠금과 달리 트랜잭션과 무관하게 AUTO_INCREMENT 값을 가져오는 순간만 잠금된다.

• 한번 증가된 AUTO_INCREMENT 값은 INSERT가 실패해도 증가된 채로 유지된다.

자동 증가 락의 작동 방식을 제어하는 시스템 변수. innodb_autoinc_lock_mode

• innodb_autoinc_lock_mode=0

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

• innodb_autoinc_lock_mode=1

  • 연속 모드 Consecutive mode 라고 불뤼며, 예측 가능한 개수의 INSERT에 사용.

    • 빠른 속도를 위해 자동 증가 락이 아닌 래치를 사용.

  • 여러 개의 자동 증가 값을 한번에 할당 받는다.

    • 하나의 INSERT문의 레코드는 AUTO_INCREMENT 값의 연속성이 보장된다.

    • 발급된 AUTO_INCREMENT 값을 사용하지 않는 경우 누락된다.

• innodb_autoinc_lock_mode=2

  • 하나의 INSERT문의 레코드라도 AUTO_INCREMENT 값의 연속성을 보장하지 않는다.

  • 인터리빙 모드 Interleaved mode 라고 불린다.

  • AUTO_INCREMENT 값의 유니크함을 보장한다.

인덱스와 잠금

• InnoDB의 잠금은 레코드가 아닌 인덱스를 잠근다.

• 만약 UPDATE를 위한 인덱스가 없는 테이블이라면 풀 스캔하면서 테이블 전체 레코드를 잠그게 되어 동시성이 떨어진다.

격리 수준

• 하나의 트랜잭션, 또는 복수 개의 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할지 결정하는 레벨

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	O	O	O
READ COMMITTED	X	O	O
REPEATABLE READ	X	X	O InnoDB X
SERIALIZABLE	X	X	X

READ UNCOMMITTED

• 서로 다른 트랜잭션에서의 쿼리 수행이 커밋이나 롤백 여부에 관계 없이 보인다. DIRTY READ

• 데이터의 정합성을 보장할 수 없어 일반적으로 사용되지 않는다.

READ COMMITTED

• 서로 다른 트랜잭션에서의 쿼리 수행은 커밋 된 것만 보인다.

  • 변경된 데이터는 실제 테이블에 업데이트 되지만, 실제 커밋이 되기 전의 조회는 언두 영역의 데이터를 가져온다.

  • 즉 커밋 되지 않은 데이터는 다른 트랜잭션에서 보이지 않는다.

• 오라클 DBMS의 기본 격리 레벨로 온라인 서비스에서 가장 많이 선택되는 레벨이다.

NON-REPEATABLE READ가 발생할 수 있다.

• 사용자 A 트랜잭션 시작 - BEGIN

• 사용자 B 트랜잭션 이름이 Toto인 회원을 조회한다. <- 결과 없음.

• 사용자 A 트랜잭션 id 500번의 회원 이름을 Toto로 변경한다. <- BEGIN, UPDATE, COMMIT

• 사용자 B 트랜잭션 이름이 Toto인 회원을 조회한다. <- 조회 성공.

• 위의 흐름에서 사용자 B 트랜잭션은 하나의 트랜잭션 내에서 서로 다른 조회 결과가 발생한다.

  • 정합성이 중요한 업무에서 문제가 될 수 있다.

REPEATABLE READ

• MySQL InnoDB의 기본 격리 레벨으로 여러 버전의 MVCC를 지원한다.

  • READ COMMITTED MVCC를 지원하지만 COMMIT되기 전까지의 언두 영역 레코드를 보여준다.

  • REPEATABLE READ 더 과거 버전의 레코드까지도 접근 가능하다.

  • 트랜잭션이 길어질수록 관리해야되는 버전이 많아짐으로 언두 영역의 레코드가 많아진다.

하나의 트랜잭션에서 일관된 조회를 보장한다.

• 모든 트랜잭션은 순차적으로 증가하는 번호를 가진다.

• 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 변경된 것만 보인다.

PHANTOM READ가 발생할 수 있다.

• SELECT ... FOR UPDATE 문은 SELECT하는 레코드에 쓰기 잠금을 걸어야 한다.

• 하지만 언두 영역에는 잠금을 걸 수 없기에 현재 레코드의 값을 가져온다.

• 이 때 이전 SELECT에서는 조회되지 않던 레코드가 조회될 수 있다.

• InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 에서도 PHANTOM READ가 발생하지 않는다.

SERIALIZABLE

• 가장 단순하지만 가장 엄격하기에 동시 처리 성능이 떨어진다.

• InnoDB 테이블에서 SELECT 문은 레코드 잠금이 필요하지 않지만, SERIALIZABLE 격리 레벨에서는 공유 잠금(읽기 잠금)을 획득해야 한다.

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