Real MySQL

[DB] Real MySQL - 4장 트랜잭션과 잠금

트랜잭션이란 작업의 완전성을 보장해주는 것으로 Lock은 동시성 제어를 위한다면 트랜잭션은 데이터 정합성 보장을 위함

InnoDB 엔진은 트랜잭션 처리가 필요하고 대용량의 데이터를 다루는 부분에서 효율적이고, MyISAM 엔진은 트랜잭션 처리가 필요 없고, Read only 기능이 많은 서비스일수록 효율적이다.

4.1 MySQL 트랜잭션

외부 서버를 사용하면 was 뿐만 아니라 db 서버도 위험해질 수 있으니 트랜잭션 범위를 최소화하고 꼭 포함되어야 하는지 생각하도록 하자.

프로그램의 코드가 db connection을 가지고 있는 범위와 트랜잭션 활성화 범위를 최소화해야한다.

4.2 MySQL 잠금

1. 글로벌 락

   Flush tables with read lock 으로만 획득할 수 있다.

   하나의 세션에서 글로벌 락을 얻으면 서버 전체에서 select를 제외한 쿼리 실행 시 락 해제까지 실행되지 않고 대기한다.

   쓰기 잠금을 걸고 있는 SQL이 실행되고 있었으면 트랜잭션이 완료될 때까지 기다렸다가 락이 실행된다.

2. 테이블 락

   테이블 단위 락으로 UNLOCK TABLES 로 해제할 수 있다.

   MySQL 서버가 변경되는 테이블을 잠금하고 변경을 마치면 즉시 잠금을 해제하면서 사용된다.

   묵시적인 테이블 락은 쿼리 실행동안 자동으로 얻었다가 자동으로 해제된다.

3. 유저 락

   GET_LOCK() 을 이용해 임의로 잠금할 수 있으며 단순히 사용자가 지정한 문자열에 대해 획득하고 해제하는 잠금이다.

   다수의 웹 서버에 상호 동기화해야할 때 사용한다.

4. 네임 락

   데이터베이스 객체의 이름을 변경하는 경우 자동으로 획득하는 락

4.3 MyISAM 스토리지 엔진 잠금

읽기 잠금: 쓰기 잠금이 걸려있지 않으면 바로 읽기 잠금을 획득하고 시작할 수 있다.

쓰기 잠금: 아무런 잠금이 걸려있지 않으면 획득할 수 있고, 걸려있으면 다른 잠금 해제까지 대기

테이블 단위 잠금이 이루어진다.

4.4 InnoDB 스토리지 엔진 잠금

INFORMATION_SCHEMA, INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 조회 시 어떤 트랜잭션이 어떤 잠금 대기 중인지 확인 가능

4.4.1 InnoDB 잠금 방식

낙관적 잠금: 각 트랜잭션이 같은 레코드 변경할 가능성은 희박할 거라고 생각하고 변경 작업을 먼저 수행하고 충돌을 확인해 문제가 있다면 롤백

비관적 잠금: 변경하고자 하는 레코드에 잠금 획득하고 변경 작업 처리(InnoDB 채택 방법)

4.4.2 InnoDB 잠금 종류

• record lock

  레코드 자체만 잠그는 것, InnoDB는 인덱스의 레코드 잠금

  보조 인덱스 변경은 next key lock, gap lock

  프라이머리 키 또는 유니크 인덱스 변경은 record lock

• Gap lock

  레코드와 바로 인접한 레코드 사이의 간격만 잠금, 레코드 간 새로운 레코드 생성 제어

  next key lock의 일부

• next key lock

  Record lock + gap lock

  mysql 서버는 STATEMENT 포맷의 바이너리 로그를 이용해 REPEATABLE READ 격리 수준 사용하는데 innodb_locks_unsafe_for_binlog 비활성화되면 next key lock 잠금 발생

  그래서 ROW 포맷 쓰는게 좋은데 안정되지 않은 상태

• auto increment lock

  여러 레코드 insert 시 내부적으로 발생하는 테이블 수준의 락, 하나만 생길 수 있어 두개 들어오면 대기

4.4.3 인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠근다.

변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠근다.

만약 인덱스가 하나도 없으면 테이블 풀 스캔을 하는데 모든 레코드를 잠근다.

4.4.4 트랜잭션 격리 수준과 잠금

인덱스 잠금은 next key lock 때문에 발생하는데 이것은 복제를 위한 바이너리 로그를 위함이다.

바이너리 로그를 비활성화 하고 격리수준을 read-committed로 두면 불필요한 잠금도 일부 해제할 수 있다.

4.5 MySQL 격리 수준

• READ UNCOMMITTED

  아직 끝나지 않은 트랜잭션에서의 변경 내역에 다른 트랜잭션이 접근해도 변경 내역 조회 가능

  DIRTY READ, NON-REPEATABLE READ, PHANTOM READ 발생

• READ COMMITTED

  oracle dbms에서 주로 사용되는 격리 수준

  트랜잭션 commit이 완료된 데이터만 다른 트랜잭션에서 조회 가능

  이때 다른 트랜잭션이 commit 전에 조회하면 언두 영역에 백업되어 있던 레코드가 반환된다.

  NON-REPEATABLE READ(하나의 트랜잭션에서 같은 select 했는데 다른 결과 나오는 문제), PHANTOM READ 발생

• REPEATABLE READ

  MySQL InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준

  언두영역에 저장한 값을 하나의 트랜잭션 내에서 계속 반환한다.

  MVCC: 트랜잭션이 rollback 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경

  PHANTOM READ(다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안보였다가 하는 현상) 발생

• SERIALIZABLE

  동시성이 제일 떨어지지만 가장 엄격한 격리 수준