[RealMysql 8.0]5 트랜잭션과 잠금 5.3 ~ 5.3.3

• InnoDB 스토리지 엔진 잠금
  • InnoDB 스토리지 엔진은 mysql 에서 제공되는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 깁ㄴ의 잠금 방식을 탑재 함
  • 이원화된 잠금 방식 때문에 MyISAM 보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있다.
    • 이원화된 잠금
      • 데이터베이스 트랜잭션이 복수의 레코드에 적용되는 잠금을 말한다.
      • 이로인해 데이터베이스의 일관성을 보장하고, 복수의 사용자가 동시에 동일한 레코드에 접근하려 할 때 충돌을 방지 함.
  • 하지만 이원화된 잠금 처리 탓에 InnoDb 스토리지 엔진에서 사용되는 잠금에 대한 정보는 mysql 명령을 이용해 접근하기 까다롭다.
  • 하지만 최근 버전에서는 InnoDb의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다.
    • 아래 코드가 안된다면 권한이 없는거 같다.

SELECT * FROM information_schema.innodb_lock_waits;

SELECT trx.trx_id, trx.trx_state, lock_waits.requesting_trx_id, lock_waits.blocking_trx_id,
       locks.lock_table, locks.lock_index, locks.lock_mode
FROM information_schema.innodb_trx trx
JOIN information_schema.innodb_locks locks ON trx.trx_id = locks.lock_trx_id
JOIN information_schema.innodb_lock_waits lock_waits ON locks.lock_id = lock_waits.blocking_lock_id;

# KILL {trx_id}

• -
  • (Perfomance Schema를이용해 InnoDb 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링 방법도 추가됐다.
• InnoDB 스토리지 엔진의 잠금
  • InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공한다.
  • 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지락으로, 또는 테이블 락으로 레벨업되는 경우는 없다.
  • 일반 사용 dbms 와는 다르게 레코드와 레코드 사이를 잠그는 갭락이라는 것이 존재한다.
• 레코드 락
  • 레코드: 테이블 내의 단일 데이터 행
  • InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스 레코드를 잠근다.
  • InnoDB 에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용하지만 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭(간격)에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.
• 갭 락
  • 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.
  • 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 방지한다.
  • 넥스트 키 락의 일부로 자주 사용된다.
• 넥스트 키 락
  • 레코드 락 과 갭 락을 합쳐 놓은 형태의 락을 넥스트 키 락 이라 한다.
  • statement 포맷의 바이너리 로그를 사용하는 mysql 서버에서는 repeatable read 격리 수준을 사용해야 한다.
  • innodb의 갭락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 복제 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.
  • 의외로 넥스트 키 락과 갭 락으로 데드락이 발생하여 다른 트랜잭션을 기다리게 하는 일이 빈번하다.가능하다면 바이너리 로그 포맷을 row 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.
    • mysql 데이터 베이스에서는 데이터에 대한 모든 변경사항을 기록하는 로그인 이진(바이너리) 로그에 기록된다.
    • 이진 로그는 문 기반(statement-based), 행 기반(row-based) 일 수 있다.
      • 문 기반 이진로그는 실행되는 각 sql 문(insert, update, delete)를 기록한다.
      • 행 기반 이진로그는 수행된 각 개별 변경 사항을 기록한다. 즉 특정 데이터 변경을 기록한다.
      • 데이터베이스의 사용 사례 및 요구 사항에 따라 달라진다.
      • 일반적으로 문 기반 이진로그는 더 효율적이고 더 작은 로그 파일을 제공한다.문이 여러 행에 영향을 미치는 경우 문에 의해 수행된 모든 변경 사항을 캡처하지 못할 수 있다.
      • 행기반 이진 로그는 문에 의해 변경된 내용에 대한 보다 완전한 기록을 제공하지만 로그 파일이 더 커지고 효율성이 떨어질 수 있다.
        • 8.0 버전부터는 row 포맷의 바이너리 로그가 기본 설정으로 변경됐다.
        • 아래는 기본 포맷 설정 확인

SHOW VARIABLES LIKE 'binlog_format';

1. 자동 증가 락
   1. 5.0 버전 이전 에서는 auto_increment 값을 가져오는 테이블 락 방식을 명시적으로 가져올 수 있는 방법은 없었다.
   2. 5.1 버전 이상부터는 innodb_autoinc_lock_mode 시스템 변수를 통해 자동 증가 락의 작동 방식을 변경할 수 있다.
      • innodb_autoinc_lock_mode = 0
        • 5.0과 동일한 잠금 방식으로 모든 insert 문장은 자동 증가 락을 사용한다.
      • innodb_autoinc_lock_mode = 1
        • insert 되는 레코드의 건수를 정확히 예츨할 수 있을때는 자동 증가 락을 사용하지 않고 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다.
          • 뮤텍스, 세마포어: https://www.youtube.com/watch?v=oazGbhBCOfU
        • 개선된 래치는 자동 증가 락과 달리 아주 짧은 시간동안 잠금을 걸고 필요한 자동 증가값을 가져오면 즉시 잠금 해제된다.
        • 하지만 select와 같이 건수를 예측할 수 없을 때는 5.0과 같은 자동 증가 락을 사용 한다.이때는 insert 쿼리가 완료될때까지 자동 증가 락은 해제되지 않기 때문에 다른 커넥션에서는 insert 를 실행하지 못하고 대기한다.
        • 한번에 할당 받은 자동 증가 값이 남아서 사용되지 못하면 폐기한다. 그로인해 자동증가값은 연속되지 않고 누락될 수 있다.
      • innodb_autoinc_lock_mode = 2 (8.0 부터 기본 값이 2 이다)
        • 해당 모드는 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다.
        • 연속된 자동 증가 값을 보장하지는 않는다.
        • 이 설정 모드에서는 insert...select와 같은 대량 insert 문장이 실행되는 중에도 다른 커넥션에서 insert를 수행할 수 있다.
        • 해당 설정에서는 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장 한다.
      • 기타
        • 5.7 버전까지는 innodb_autoinc_lock_mode 기본값이 "1" 이였지만 8.0 부터는 "2"로 바뀌었다.
        • 이는 8.0 버전부터 이진 로그 포맷이 statement 에서 row 포맷으로 기본값이 됐기 때문이다.
        • 8.0에서도 row 포맷이 아닌 statement 포맷을 변경할 경우 innodb_autoinc_lock_mode 을 "1"로 변경해서 사용하는 것을 권장한다.

• 인덱스와 잠금
  • InnoDb의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식이다
  • 이는 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다
    • index 칼럼으로 조건을 검색과 더불어 다른 조건을 걸어 1개의 레코드만 찾았더라도 index 를 사용해 찾은 레코드의 n 개만큼 같이 레코드 락이 걸린다.
  • 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 update 작업이 진행되는데 이는 모든 레코드를 잠그게 된다.
  • 이것이 mysql의 innodb에서 인덱스 설계가 중요한 이유 이다.
• 레코드 수준의 잠금 확인 및 해제
• https://jeong0427.tistory.com/97