[RealMysql 8.0]5 트랜잭션과 잠금 5.1 ~ 5.2.2 테이블 락

• 읽어보면 좋은?https://jeong0427.tistory.com/81

mysql 트랜잭션 ACID

• 트랜잭션은 작업의 완전성을 보장해 주는것이다.논리적인 작업 셋을 모두 완벽하게 처리하거나 처리하지 못할 경우 원 상태로 복구해서 작업의 일부분만 적용되는 현상이 발생하지 않게 만들어 준다.
• 잠금과 트랜잭션은 비슷한 개념 같지만 잠금은 동시성을 ㅔ저아힉 위한 기능이고, 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.
• 트랜잭션
  • 기존 MyISAM, MEMORY 스토리지 엔진은 트랜잭션을 지원하지 않는다.
    • 예제와 같이 InnoDB 와의 데이터 가 어떻게 진행되는지 잘 성명해 주고있다.
    • 부분 업데이트가 되는 부분을 처리하기위해 MyISAM, MEMORY 에서는 난잡한 if else 안에 if else 식으로 코드가 실패했을경우를 대비하여 코드를 처리하고 있었다.
• 주의사항
  • 트랜잭션은 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 즉 트랜잭션의 범위를 최소화 하라는 것이다.
  • 해당 시스템 마다 다를 수 있지만, 커넥션 생성, 트랜잭션 시작부분을 실제 dbms 에서 시작되는 부분에 적용하고, 트랜잭션의 종료, 커넥션 반납하기전 외부 통신 같은 위험한 작업은 삼가하는게 좋다.
  • 또는 반드시 하나로 묶어야할 트랜잭션별로 풀어서 개별 작업을 진행하자.
• Mysql 엔진의 잠금
  • mysql 에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 mysql 엔진 레벨로 나눌 수 있다
  • mysql 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미친다.
  • 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.
• 글로버 락
  • mysql에서 제공하는 잠금 가운ㄷ 가장 범위가 크다.
  • 한 세션에서 글로버 락을 획득하면 다른세션에서 select 를 제외하고 대부분의 ddl 문장이나 dml 문장을 실행하는 경우 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.
    • ddl: 데이터베이스 구조를 정의하는..(테이블을 생성, 수정 삭제..)
    • dml: 데이터베이스 내용을 조작하는..(테이블 데이터 삽입, 삭제, 업데이트..)
  • MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 한다.
    • 글로벌 락을 거는 flush tables with read lock 명령은 실행과 동시에 mysql 서버에 존재하는 모든 테이블을 닫고 잠금을 건다.
      • 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.
    • 명령어가 실행되기전에 테이블이나, 레코드에 쓰기 잠금을 거는 sql 이 있다면 이 명령이 완료될 때 까지 기다려야 한다.
    • flush tables with read lock 명령은 테이블에 읽기 잠금을 걸 전에 먼저 테이블을 플러시 해야하기 때문에 모든 쿼리가 완료돼야 한다.
    • 장시간 실행되는 쿼리와 flush tables with read lock 명령이 최악의 케이스로 실행된다면 모든 테이블에 대한 dml 쿼리가 아주 오랜 시간 동안 실행되지 못하고 기다릴 수 있다.
  • mysql 서버가 업그레이드 되면서 InnoDB 스토리지 엔진의 사용화가 일반화 됐다. innodb 스토리지 엔진은 트랜잭션을 지원하기 때문에, 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다.
  • 8.0 부터는 innoDb가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성이 생겨 Xtrabackup, Enterprise Backup 과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.
  • 특정 세션에서 백업 락을 회득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 업다.
    • 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제 (ddl)
    • REPAIR TABLE과 OPTIMIZE TABLE명령
      • https://jeong0427.tistory.com/92

mysql REPAIR TABLE과 OPTIMIZE TABLE

 

• -
  • -
    • 사용자 관리 및 비밀번호 변경
  • 백업 락은 일반적인 테이블의 데이터 변경은 허용된다.
  • 일반적인 mysql 서버의 구성은 소스서버와 레플리카 서버로 구성되는데 주로 백업은 레플리카 서버에서 실행된다.
    • https://jeong0427.tistory.com/93

mysql 소스서버 레플리카 서버

• -
  • -
    • 복제 서버에서 백업이 flust tables with read lock 명령을 이용해 글로벌 락을 획득하면 원본 서버에서 복제 서버로 데이터 복제는 백업 시간만큼 지연될 수 밖에 없다.
    • 복제 서버에서 백업을 실행하는 도중 문제가 생긴다면 복제 서버의 데이터가 최신 상태가 될 때까지 서비스를 멈춰야 할 수도 있다.
    • Xtrabackup, Enterprise Backup 툴들을 통해 모두 복제가 진행되는 상태에서도 일관된 백업을 만들 수 있지만
      • Xtrabackup, Enterprise Backup 툴이 실행되는 도중에 스키마 변경이 실행되면 백업은 실패하게되어 장시간 동안 백업이 진행되던 상황만큼 다시 시간을 들여 백업을 실행해야 한다.
      • mysql 백업락은 이런 목적으로 도입됐으며, 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 ddl 명령이 실행되면 복제를 일시 중지하는 역할을 한다.
• 테이블 락
  • 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.
    • https://jeong0427.tistory.com/94

mysql 잠금: 명시적 잠금, 묵시적 잠금

• -
  • 위 블로그에 따로 정리했음.
  • MyISAM, MEMORY 테이블은 dml 을 통해 자동으로 묵시적 락을 취득, InnoDB 는 스토리지 엔진에서 레코드 락을 제공하기 때문에 묵시적 락이 설정되지않는다. 좀 더 정확히 말하자면, InnoDb에서도 테이블 락이 설정되지만 dml 쿼리에서는 무시되고 ddl 쿼리의 경우에만 영향을 미친다