트랜잭션과 잠금

트랜잭션은 작업의 완전성을 보장해 주는 것이다. 즉 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는(Partial Update)이 발생하지 않게 만들어주는 기능

Lock과 트랜잭션은 서로 비슷한 개념 같지만 잠금은 동시성을 제어하기 위한 기능이고 트랜잭셔은 데이터의 정합성을 보장하기 위한 기능

Keyword Partial Update → 정합성을 맞추는 데 상당히 어려운 문제

• MyISAM테이블과 InnoDB와의 차이

트랜잭션은?

📝 애플리케이션 개발에서 고민해야 할 문제를 줄여주는 아주 필수적인 DBMS의 기능이라는 점을 기억하자. 부분 업데이트 현상이 발생한다면 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요할 수 있기 때문이다.

주의 사항

• 트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋음
• 즉, 트랜잭션의 범위를 최소화

트랜잭션 처리에 좋지 않은 영향을 미치는 부분들

• 커넥션의 시간이 길어지는 경우
• 메일 전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 DBMS의 트랜잭션 내에서 제거하는 것이 좋음
• 중요한 것은 프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화해야 한다는 것이다.
• 특히 네트워크 작업이 있는 경우 트랜잭션에서 배제해야 함

MySQL 엔진의 잠금

• mysql engine 레벨과 storage engine 레벨로 구분할 수 있음
  • MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지는 않음
  • MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 Lock 이외에도 테이블의 구조를 잠그는 메타 데이터 락 그리고 사용자의 필요에 맞게 사용할 수 있는 Named Lock이라는 잠금 기능도 제공
  • Table Lock은 DDL(Create, Alter, drop 등) 구문과 함께 사용 DDL Lock이라고도 함

GLOBAL LOCK

• FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며 MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
• 서버 전체에 영향, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미침

MySQL 8.0으로 넘어오면서

• GLOBAL LOCK이 장시간 실행되는 쿼리와 함께 사용된다면 최악의 케이스로 아주 오랫동안 다른 쿼리들이 실행되지 못한다.
• 서버가 업그레이드 되면서 InnoDB 사용이 일반화 → 조금 더 가벼운 글로벌 락의 필요성이 생김
• Xtrabackup이나 Enterprise Backup과 같은 백업툴 들의 안정적인 실행을 위한 백업 락이 도입

Table Lock

📝 Table Lock은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다. 명시적인 획득은 잠금을 명령으로 반납할 수 있음. 명시적인 테이블 락도 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없음

묵시적인 테이블 락은 MyISAM이나 Memory 테이블에서 데이터를 변경하는 쿼리를 실행하면 발생한다.쿼리가 실행되는 동안 자동으로 획득했다가 쿼리가 완료된 후 자동 해제됨

InnoDB 테이블의 경우 잠금

📝 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않음

InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미침

Named Lock

사용자의 필요에 맞게 사용할 수 있는 Lock, GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 이 잠금의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것이다.

사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금인데 자주 사용하지 않는다.

InnoDB 스토리지 엔진 잠금

💡 InnoDB 스토리지 엔진은 mySQL 에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재

MyISAM보다 훨씬 뛰어난 동시성 처리를 제공
잠금 정보가 또한 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업되는 경우는 업다.

Record Lock

💡 레코드 자체만을 잠그는 것을 레코드 락이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할 → 하지만 중요한 차이가 존재

InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠금다는 점이다. 많은 사용자들이 간과하는 부분이지만 레코드 자체를 잠구는 것과 인덱스를 잠구는 것은 상당히 크고 중요한 차이를 만들어 냄

레플리카 서버: 백업을 실행하는 서버

• InnoDB의 갭 락이나 넥스트키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적

하지만 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생함 따라서, ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

AUTO_INCREMENT 락

💡 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는쿼리에서만 필요 → 테이블 수준의 잠금을 사용

하지만 이 내용은 MySQL 5.0 이하 버전에서 사용하던 방식

💡 5.1 이상부터는 innodb_autoinc_lock_mode라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있음

• innodb_autoinc_lock_mode = 0

  • 5.0과 동일한 잠금 방식으로 모든 INSERT 문장은 자동 증가 락을 사용한다.

• innodb_autoinc_lock_mode =1

  • 단순히 한 건 또는 여러 건의 레코드를 INSERT하는 SQL 중에서 MySQL 서버가 INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때는 훨씬 가볍고 빠른 래치(Mutex)를 이용해 처리 → 개선된 래치는 자동 증가 락과 달리 아주 짧은 시간 동안만 잠금을 걸고 필요한 자동 증가 값을 가져오면 즉시 잠금이 해제된다

• innodb_autoinc_lock_mode = 2

  • mode가 2로 설정되면 innoDB 스토리지 엔진은 절대 자동 증가 락을 걸지 않고 경량화된 래치를 사용. 하지만 INSERT 문장으로 INSERT되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하지는 않는다. 그래서 이 설정 모드를 인터리빙 모드라고도 한다.
  • 이 설정 모드에서는 INSERT … SELECT와 같은 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아짐
  • 하지만 이 설정에서 작동하는 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장

MySQL 8.0부터 바이너리 로그 포맷이 STATEMENT가 아니라 ROW 포맷이 기본 값이 됐기 때문에 innodb_autoinc_lock_mode의 기본 값이 2로 바뀌었다.

인덱스와 잠금

InnoDB에서의 레코드 잠금은 인덱스를 활용한다고 언급했기 때문에 상당히 중요하다.

즉, 변경해야 하는 쿼리 수행을 위해

1. 레코드를 찾아야함
   1. 레코드를 찾기 위해서 검색한 인덱스의 레코드를 모두 락을 걸어야 함.

EXAMPLE

KEY ix_fisrtname (first_name) 멤버로 담긴 ix_fisrtname이라는 인덱스가 준비돼 있다.

SELECT COUNT(*) FROM employees where first_name='Georgi';

253개의 쿼리 결과

SELECT COUNT(*) FROM employees where first_name='Georgi' AND last_name='Klassen';
1개의 쿼리 결과

//UPDATE -> employees 테이블에서 first_name='Georgi' 이고 last_name='Klassen'인 사원의
입사 일자를 오늘로 변경하는 쿼리를 실행
UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';

Update가 실행되었다면?

1건의 레코드가 업데이트될 것이다.

하지만 서버 성능에 대해서 생각해보고 몇 개의 레코드에 락을 걸어야 할지를 생각해보자

이 업데이트 조건에서 인덱스를 이용할 수 있는 조건은 first_name=’Georgi’이며, last_name 칼럼은 인덱스가 없기 때문에 first_name=’Georgi’인 253건의 레코드가 모두 잠긴다.

이러한 부분을 잘 모르고 개발한다면?

MySQL 서버를 제대로 이용하지 못할 것이다. 이 예제에서는 몇 건 안되는 레코드만 잠그지만 UPDATE 문장을 위해 적절히 인덱스가 준비돼 있지 않다면 각 클라이언트 간의 동시성이 상당히 떨어져서 UPDATE 작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생

즉 → 성능이 떨어지는 현상, 클라이언트에게 직결

테이블에 인덱스가 하나도 없다면?

💡 테이블을 풀 스캔하면서 UPDATE 작업을 하는데, 이 과정에서 당연히 테이블에 잇는 30여만 건의 모든 레코드를 잠그게 된다. 이것이 MySQL의 InnoDB에서 인덱스 설계가 중요한 이유