[MySQL] 잠금과 트랜잭션

#해당 내용들은 RealMySQL 책에 기반하고 있습니다

잠금: 동시성을 제어하기 위함

• unless: 하나의 데이터를 여러 커넥션에서 동시에 변경 -> 레코드의 값 예측 불가 상태
• 여러 커넥션에서 동시에 동일한 자원 요청 시, 한 시점에는 하나의 커넥션만 변경할 수 있도록 해주는 역할
• 트랜잭션: 데이터의 정합성을 보장하기 위함
  • 트랜잭션 격리수준
    • 트랜잭션 간의 작업 내용의 공유/차단의 레벨을 결정하는 지표
• 트랜잭션 cf) MyISAM, MEMORY: 트랜잭션 미지원
  • InnoDB: 트랜잭션 지원: 주로 사용
  • 한 트랜잭션 내의 여러 쿼리들의 100% / non-adapt를 보장 ex) PK 3이 있는 상태에서 insert 1, 2, 3을 수행했을 때
  • InnoDB: insert 1, 2, 3이 모두 실패하여 테이블에 PK 3만 그대로 있는 상태 (트랜잭션으로 인한 ACID 보장)
  • MyISAM: insert 3만 실패하여 테이블에 PK 1, 2, 3이 있는 상태 (ACID 보장 X) : "Partial Update"
    • 데이터의 정합성을 맞추기 hard (쓰레기 데이터가 테이블에 지속적으로 남게 됨)
  • MyISAM에서 수동으로 If/Else 로 정합성 보장할 바에야, 애초에 트랜잭션이 지원되는 InnoDB를 사용하자: 단지 try {start Transaction/Commit} catch (Exception) {RollBack}만 하면 된다는 점~!
  • 트랜잭션은 최소의 코드에만 적용하는 것이 좋음!
    • 일반적으로 DB 커넥션의 개수는 제한적이므로, 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 여유 커넥션의 갯수가 줄어들기 때문
      • 다른 곳에서 커넥션을 기다리는 상황이 발생할 수도 있음
    • 네트워크를 통해 원격 서버와 통신하는 작업은 어떻게 해서든 DBMS의 트랜잭션 내에서 제거하는 것이 좋음: 프로그램 실행 도중 외부 서버와 통신할 수 없는 상황 발생 시, 트랜잭션 연속 실패로 DBMS 서버까지 위험해지는 상황이 발생 가능하기 때문
• 잠금
  • 스토리지 엔진 레벨: 스토리지 엔진 간 상호 영향을 미치지 않음
  • MySQL 엔진 레벨: 모든 스토리지 엔진에 영향
    • 테이블락, 유저락, 네임락, ...
  • 글로벌락
    • MySQL의 잠금 중 가장 범위가 큰 잠금
    • FLUSH TABLES WITH READ LOCK 명령으로만 획득 가능
    • 한 세션에서 글로벌 락을 획득한 경우, 다른 세션에서 select를 제외한 대부분의 DDL, DML 문장이 글로벌 락 해제시까지 대기 상태로 유지
    • MySQL 서버 전체에 영향 (MySQL 서버에 존재하는 모든 테이블에 잠금)
    • mysqldump로 일관된 백업을 받아야할 때 사용
    • 읽기 잠금만 걸기 전에 먼저 테이블을 flush 해야하므로 테이블에 실행되고 있는 모든 종류의 쿼리가 완료돼야만 잠금 걸기 가능
  • 테이블락
    • 개별 테이블 단위로 설정
    • 명시적 테이블 락도 영향이 크기 때문에 명시적으로는 사용하지 않고, 보통 묵시적으로 사용됨
    • InnoDB의 경우, DML(데이터 변경 쿼리) 에서는 무시되고, DDL(스키마 변경 쿼리) 에만 묵시적 테이블락이 설정됨
  • 유저락
    • 대상이 테이블/레코드와 같은 DB 객체가 아니라, 단순히 사용자가 지정한 문자열에 대한 락: 자주 사용되지는 않음
    • 여러 클라이언트가 상호 동기화 처리시 사용 (그냥 임의적으로 락을 유동적으로 사용해야할 때 쓰는 듯..?)
  • 네임락
    • RENAME TABLE, 즉 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금: 원본 이름과 변경될 이름 두개 모두에 대해서 잠금을 설정
    • 예시 이해가 어려운데, rank_new 테이블을 rank로 변경하는데, 꼭 rank 테이블에 기존에 존재해야하는 것? Table not found 'rank' 오류가 어떤 경우에 발생하는 걸까?
    • SLock & XLock
• 잠금 방식
  • Optimistic locking
    • 각 트랜잭션이 같은 레코드를 변경할 가능성이 희미하다고 가정하여, 우선 변경작업 수행 후, 마지막에 잠금 충돌 여부 확인하여 문제 있을 시 RollBack하는 방식
  • Pessimistic locking
    • 현재 변경하는 레코드가 다른 트랜잭션에 의해 변경될 가능성이 높다고 판단하여, 레코드에 대해 잠금을 우선적으로 획득하고 변경 작업을 처리하는 방식
    • 높은 동시성 처리에 용이, InnoDB는 Pessimistic locking 방식을 사용
• InnoDB의 잠금
  • 레코드 기반의 잠금 기능을 제공하고 잠금 정보가 작은 공간으로 관리되어 페이지락, 테이블락까지 레벨업 되는 경우 (락 에스컬레이션)은 없음
  • 레코드락 & 갭락(레코드와 레코드 간의 간격을 잠그는 락)으로 구성
  • 레코드락
    • 레코드 자체가 아니라 인덱스의 레코드를 잠금 (둘의 차이는 크고 중요한 차이를 만들어 냄 : 요게 뭘까)
    • 인덱스가 없더라도 innoDB 스토리지 엔진이 자동으로 생성한 클러스터 인덱스를 활용하여 잠금
    • 보조 인덱스를 이용한 변경 작업은 넥스트 키락, 갭락도 사용하지만, PK, Unique index에 대한 변경 작업은 Record lock만 수행함
  • 갭락
    • 변경 대상 레코드 뿐만 아니라, 레코드와 바로 인접한 레코드 사이의 간격을 잠금
    • 레코드 간에 새로운 레코드가 insert되는 것을 제어
    • next key lock의 일부로 사용
  • 넥스트키락
    • RecordLock + GabLock
    • statement 포맷의 binlog (sql문을 기록하는 방식) 를 사용하면 REPEATABLE_READ 격리 수준이 필수
    • 이 때 변경이 일어나는 레코드에 대하여 next key lock이 발생
    • 목적: binlog에 기록되는 쿼리가 slave에서 수행될 때 master에서 만들어낸 결과와 동일한 결과를 만들어 내도록 보장하는 것
    • 하지만 오히려 이로 인해 데드락이 생겨서 트랜잭션을 기다리게 하기도 함
      • 사용자의 쿼리 요청 동시 처리 hard
  • Auto Increment Lock
    • 동시에 record가 insert되었을 경우, AI를 보장시키기 위해 사용하는 락
    • 트랜잭션과 관계 없이 insert/replace 문장에서만 AI 락을 사용
    • AI 락은 테이블에서 하나만 존재하므로, 두개의 insert 쿼리가 동시에 수행되더라도 하나의 쿼리가 해당 락이 해제될때까지 대기하는 방식으로 AI를 보장하는 것
    • 명시적으로 획득/해제하는 경우 X
    • 버전이 올라가면서 AI 필드에 대해 사용하는 락 방식의 차이가 있음
      • insert되는 레코드 건수에 대한 예측이 정확할 경우에는, 더 가볍고 빠른 "래치"를 하도록 구성
      • 대량 insert가 수행될 때는 매번 AI를 할당받는 방식이 아니라, 여러개의 AI 값을 한번에 할당 받아서 사용하게 되는데, 이때 할당받은 AI 값들 일부가 남게 되면, 폐기함
      • 따라서 대량 insert 후 다음 insert의 AI 필드는 연속되지 않고 누락된 값이 발생 가능
      • 또는 그냥 예측의 정확 여부에 관계없이 항상 "래치"만 사용하는 경우도 있음
        • 요 경우는 AI의 자동 증가 값을 보장하지는 않음 (PK 조건은 유지하겠다만)
        • AI 락을 아예 사용하지 않아서 동시 처리 성능은 더 높을 수 있음
        • 그리고 마스터랑 slave의 AI 필드 값이 달라질 수도 있음 -> 엄청 critical 하네.. 안쓸듯 그러면 ㅇ_ㅇ;
    • AI 값은 한번 증가하면 절대 줄어들지 않음
      • AI Lock을 최소화하기 위해서!
      • 음.. AI Lock의 최소화랑 어떤 관련이 있을까 갭락에 대해서 아무 생각없이 그냥 잡을 수 있어서 그런가 AI 가 중간값을 할 수 있게 되면 어떻게 AI 값을 줄지에 대한 것도 덜생각해도 돼서..?
  • 인덱스와 잠금
    • 레코드 자체 잠금 vs. 레코드의 인덱스만 잠금
    • 변경해야할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠가야함!
    • update 문에서 사용 가능한 인덱스 조건을 찾고, 해당 인덱스 조건에 해당하는 row n줄이 모두 잠김
    • nbase-T는 클러스터 전체에 대해서 해당 테이블에 대해 uniqueness가 보장됨 (테이블 별로만 보장되는 것이 아니라)
    • 인덱스가 하나도 없는 경우는 테이블을 풀스캔하면서, 테이블에 있는 모든 레코드를 잠그게 됨
    • 엥 아까 인덱스 없으면 자체 생성한 클러스터 인덱스 사용해서 잠근다며..?
    • MySQL 구조: leaf Node에 PK가 있음, PK로 메모리를 찾아가기 위해서는 메모리가 sequential 해야함
    • PK가 비슷한 아이들끼리는 같은 페이지에 담겨야함 : clustering index
    • 앞에서 클러스터링 인덱스 얘기는 PK만 있고 다른 인덱스가 없는 경우
    • 그리고 사용 가능한 인덱스가 많은 경우에는 어떤 인덱스에 따라서 row를 잠그나? 쿼리 플랜 1순위에 해당하는 인덱스가 잠기는걸까?
    • 사용한 index말고도 unique index랑 PK는 (다음 row까지) 락이 잠김
  • 트랜잭션 격리 수준 & 잠금
    • 트랜잭션 격리 수준을 READ_COMITTED로 세팅하고 뭐 더하면 조건 하나하나 확인하며 해당하는 record 찾을 때마다 아닌 row에 대해서는 잠금을 풀게 하여 불필요한 잠금 현상을 막게 되는데..
    • 우리는 READ_COMITTED만 사용할수 없는 환경이니까 update 끝나기 전까지 인덱스에 대한 락이 쭉 걸려 있겠네용? ㅎㅎ 맞나용?
    • DB CAP 정리
  • 레코드 수준의 잠금은, 해당 레코드가 자주 사용되지 않으면 오랫동안 잠겨져 있어도 잘 발견되지 않음
  • 응용단에서 직접 데드락을 해결하기 위해 process를 kill을 하지는 않는다. (자동으로 QTO 나서 대부분은 해결됨)