[RealMySQL 8.0] 5장 트랜잭션과 잠금

5.1 트랜잭션

• 트랜잭션은 쿼리의 개수와는 상관없이, 논리적인 작업셋이 Atomic 하게 적용되어야 한다는 것을 의미한다.
  • 단일 쿼리 여러 데이터를 변경할 수 있으므로 트랜잭션 적용 대상이다.
  • 트랜잭션의 범위는 가급적 짧게 유지해야 한다.
  • 커넥션을 길게 잡고 있으면 커넥션 풀 성능 저하를 일으킨다.
  • 외부 IO 작업은 불확실성이 크므로 Tx에서 제거하자.
  • 원자적 일관성이 필요하지 않은 처리는 별도의 트랜잭션으로 분리하자.
  • 트랜잭션을 길게 잡고 있을 경우 DBMS 의 과도한 부하를 일으킬 수 있다.

5.2 MySQL 엔진의 잠금

• 잠금은 스토리지 잠금과 MySQL 엔진 잠금으로 나눌 수 있다.
  • 스토리지 잠금은 스토리지 엔진 간 영향을 미치지 않는다.
  • MySQL 잠금은 모든 스토리지 엔진에 영향을 미친다.
• 글로벌락
  • MySQL에서 제공하는 잠금 중 가장 범위가 크다.
  • MySQL 서버 전체에 영향을 미친다.
  • 다른 세션에서 SELECT를 제외한 문장을 실행할 수 없다.
  • MyISAM이나 MEMORY 테이블을 덤프뜰 때 사용한다.
  • mysqldump 같은 백업 프로그램이 내부적으로 실행할 수 있으므로, 옵션에 따라 어떤 잠금을 거는지 확인하자.
  • FLUSH TABLES WITH READ LOCk 명령으로 실행할 수 있다.
• 백업락
  • InnoDB 엔진은 트랜잭션을 지원하기 때문에 모든 변경을 멈출 필요가 없다.
  • 따라서 글로벌락보다 가벼운 백업 락이 8.0 부터 도입됐다.
  • DDL 명령, REPAIR TABLE, OPTIMIZE TABLE, 사용자 관리 및 비밀번호 명령만 수행할 수 없다.
  • 레플리카에서 백업 중, DDL 명령 실행 시 복제를 일시 중단한다.
  • LOCK INSTANCE FOR BACKUP 명령으로 수행할 수 있다.
• 테이블락
  • 개별 테이블 단위로 설정되는 잠금이다.
  • InnoDB가 아닌 경우, DML 시 묵시적 테이블 락이 발생하지만, InnoDB인 경우 DDL 시에만 사용된다.
  • 명시적인 테이블락은 잘 사용할 일이 없다.
  • LOCK TABLES table_name (READ or WRITE) 명령으로 획득한다.
• 네임드락
  • GET_LOCK() 함수를 이용해 임의의 문자열에 잠금을 걸 수 있다.
  • 분산락 대신 사용. 8.0 부턴 네임드락 중첨해서 사용할 수도, 한번에 중첩된 락을 모두 해제할 수도 있다.
• 메타데이터락
  • 데이터베이스 객체를 변경할 때 사용하는 락이다.
  • 이름이나 구조를 변경할 때 사용한다.

5.3 InnoDB 스토리지 엔진 잠금

• information_schema 데이터베이스의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 조인하여 어떤 트랜잭션이 잠금을 대기하고, 어떤 트랜잭션이 잠금을 가지고 있는지 확인할 수 있다.
• 레코드락
  • 레코드 자체를 잠그는 락이다.
  • 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
  • PK 또는 UK를 이용한 변경 작업은 레코드락만을 사용한다.
• 갭락
  • 레코드와 바로 인접한 레코드 사이의 간격을 잠그는 것을 의미한다.
  • 레코드와 레코드 사이에 새로운 레코드가 생성되는 것을 제어한다.
• 넥스트키락
  • 갭락과 레코드락을 합쳐놓은 락을 의미한다.
  • REPEATABLE READ 격리 수주을 사용해야한다.
  • 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때, 소스서버와 같은 결과를 내도록 보장한다.
  • 데드락이나 Tx 지연이 발생한다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트키락이나 갭락을 줄이는 것이 좋다. ( 참고 : https://waterfogsw.tistory.com/55
• 자동 증가락
  • auto increment할 때 사용한다.
  • innodb_autoinc_lock_mode 로 자동 증가락의 동작 방식을 변경할 수 있다.
  • 짧은 잠금을 사용하기 때문에 잘 문제가 되진 않는다
• 레코드락은 왜 인덱스를 잠그는가?
  • 잠금 대상을 좁히기 위해 업데이트 시 인덱스에 잠금을 걸어야 한다.
  • 인덱스를 사용하지 않으면, 업데이트를 위해 모든 레코드에 잠금을 걸어야 한다.
  • 의문점) 왜 업데이트 대상을 찾을 때 잠금을 계속 유지하고 있어야하지?
    • B라는 데이터를 업데이트하기 위해 찾은 ABC에 잠금을 거는게 아니라 B를 찾았을 때 잠금을 걸고 변경하고 해제하면 되지 않나?
    • 만약 다른 Tx가 C를 B로 업데이트하고 기존 B에 업데이트하고 B가된 C를 기존 Tx가 본다면 일관성이 깨진 상태가 될것.
    • MVCC 기능을 사용한다면 기존 Tx가 본 C는 언두로그를 타서 B 일텐데....아마 자세한 원리는 격리수준과 관련된 것같다.
• performance_schema의 data_locks 테이블과 data_lock_waits 테이블을 조인해서 잠금 대기 현황을 알 수 있다.

5.4 MySQL의 격리 수준

• SERIALIZABLE 격리 수준이 아니라면, 격리 수준을 낮춘다고 해서 성능의 개선이나 저하는 발생하지 않는다.
• InnoDB는 REPEATABLE READ 수준에서도 PAHNTOM READ 가 발생하지 않는다.
• READ COMMITED
  • 커밋이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.
  • 커밋이 되기 전이라면 언두 데이터를 보여준다.
  • 조회 -> 다른 트랜잭션 커밋 -> 다시 조회 시 서로 다른 조회 결과가 나온다.
• REPEATABLE READ
  • 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.
  • MVCC를 이용해서 구현된다.
  • 언두 로그는 Tx ID 와 같이 저장되며, REPEATABLE READ를 구현하기 위해 현재 실행중인 가장 오래된 Tx ID 보다 높은 Tx ID값을 가진 언두로그는 삭제할 수 없다.
  • 데이터를 읽을 때 언두 로그와 데이터풀에서 자신의 TX ID보다 낮거나 같은 값(동일 트랜잭션)의 데이터만 읽는다.
  • SELECT FOR UPDATE는 쓰기 잠금을 사용하는데, UNDO 로그에는 잠금을 걸 수 없으므로 팬텀 리드 현상이 발생한다.