[MySQL] : MySQL에서의 트랜잭션과 잠금(Transaction & Lock)

트랜잭션(Transaction)은

작업의 완전성을 보장해 주는 것

입니다. 따라서, 다음과 같은 두 가지 기능을 제공합니다.

• 논리적 작업 셋을 모두 완벽히 처리
• 처리하지 못할 경우 원 상태로 복구

잠금(Lock) VS 트랜잭션(Transaction)

• Lock := 동시성을 제어하기 위한 기능
• Transaction := 데이터의 정합성을 보장하기 위한 기능

격리 수준

하나 혹은 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨

 

트랜잭션을 지원하지 않는 MyISAM과 트랜잭션을 지원하는 InnoDB의 처리 방식 차이를 살펴봅시다.

MySQL에서의 트랜잭션

트랜잭션이란,

논리적인 작업 셋 자체가 100% 적용되거나 아무것도 적용되지 않아야 함을 보장해 주는 것입니다.

결론부터 이야기하면,

• InnoDB := 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만드는 트랜잭션이 존재
• MyISAM := 트랜잭션을 지원하지 않기 때문에 원 상태의 복원 절차가 매우 복잡

간단한 예시로, 테이블에 레코드 1건씩을 저장하는 코드를 살펴봅시다.

// MyISAM
CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY ( fdpk ) ) ENGINE=MyISAM;
INSERT INTO tab_myisam (fdpk) VALUES (3);

// InnoDB
CREATE TABLE tab_innodb ( fdpk INT NOT NULL, PRIMARY KEY ( fdpk ) ) ENGINE=INNODB;
INSERT INTO tab_innodb (fdpk) VALUES (3);

이제 AUTO_COMMIT 모드에서 다음 쿼리를 각각 시행해 봅니다.

SET autocommit=ON;

INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3);

위 쿼리문을 실행하면 모두 3이라는 값의 PK 중복으로 인해 오류가 발생하게 됩니다. 이때, 각 테이블의 결과를 확인해보면, 결과는 다른 것을 확인할 수 있습니다.

• tab_myisam의 결과 := 1, 2, 3
• tab_innodb의 결과 := 3

이 결과에서 알 수 있듯, MyISAM 엔진의 경우, 트랜잭션을 지원하지 않기에 1과 2를 넣는 과정이 commit 되었습니다. 하지만, InnoDB 엔진의 경우, 트랜잭션을 지원해 오류가 발생해 원 상태로 복원된 것을 확인할 수 있습니다.

주의 사항

트랜잭션은 꼭 필요한 최소의 코드에만 적용하는 것이 좋습니다. 즉,

트랜잭션의 범위를 최소화하라는 의미입니다.

• DBMS의 커넥션도 최소한으로 적용 := 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션의 수는 줄어들 것입니다.
• 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 어떻게 해서든 DBMS의 트랜잭션 내에서 제거하는 것이 좋습니다.

---

MySQL에서 사용되는 잠금은 크게

• MySQL 엔진 레벨(MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분)
• 스토리지 엔진 레벨

로 나눌 수 있습니다.

• MySQL 엔진 레벨 잠금 : 모든 스토리지 엔진에 영향을 미칩니다.
• 스토리지 엔진 레벨 잠금 : 스트로지 엔진 간 상호 영향을 미치지 않습니다.

MySQL 엔진의 잠금

1. 글로벌 락 : Global Lock

SELECT를 제외한 대부분의 DML, DDL 쿼리에 대한 잠금

• `FLUSH TABLES WITH READ LOCK` 명령어로 획득할 수 있습니다.
• MySQL 서버의 모든 변경 작업을 멈춥니다.
• MySQL에서 제공하는 잠금 중 가장 범위가 큽니다.
• MySQL 서버 전체에 영향을 미칩니다.
  • 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미칩니다.
• 웹 서비스 용으로 사용되는 MySQL 서버에서는 가급적 사용하지 않는 것이 좋습니다.

2. 백업 락 : BackUp Lock

백업 툴들의 안정적인 실행을 위해 도입된 잠금

LOCK INSTANCE FOR BACKUP;
...
UNLOCK INSTANCE;

특정 세션에서 백업 락을 획득하면 다음과 같은 객체에 대해 잠금이 적용됩니다.

• 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
• REPAIR TABLE과 OPTIMIZE TABLE 변경
• 사용자 관리 및 비밀번호 변경

하지만, 백업 락은 일반적인 테이블의 데이터 변경은 허용됩니다.

MySQL 서버 구성

• 소스 서버 : Source Server
• 레플리카 서버 : Replica Server

백업은 주로 레플리카 서버에서 실행됩니다.

3. 테이블 락 : Table Lock

개별 테이블 단위로 설정되는 잠금

3-1. 명시적 테이블 락

• `LOCK TABLES table_name [ READ | WRITE ]` 명령어를 통해 특정 테이블의 락을 획득
• `UNLOCK TABLES`를 통해 잠금을 반납
• 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치기 때문에 애플리케이션에서 사용할 필요가 거의 없습니다.

3-2. 묵시적 테이블 락

• 테이블에 데이터를 변경하는 쿼리를 실행하면 발생
• MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용됩니다.
• MyISAM, MEMORY 테이블 = 데이터를 변경하는 쿼리에서 발생
• InnoDB 테이블 = 대부분의 데이터 변경(DML) 쿼리에서는 무시되고, 스키마를 변경하는 쿼리(DDL)의 경우에만 발생

4. 네임드 락 : Named Lock

`GET_LOCK()` 함수를 이용해 임의의 문자열에 대한 잠금

• 데이터베이스의 객체를 잠그는 것이 아니라, 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납하는 잠금

// "mylock"이라는 문자열에 대해 잠금을 획득
// 이미 잠금을 사용 중이면 2초 동안만 대기(2초 이후 자동 잠금 해제)
SELECT GET_LOCK('mylock', 2);

// 잠금이 설정되어 있는지 확인
SELECT IS_FREE_LOCK('mylock');

// 획득했던 잠금을 반납(해제)
SELECT RELEASE_LOCK('mylock');

// 획득한 모든 네임드 락을 해제(MySQL 8.0 ~ )
SELECT RELEASE_ALL_LOCKS();

• 위 함수가 정상적으로 락을 획득하거나 해제한 경우 : return 1
• 아닌 경우 : return NULL or 0

5. 메타데이터 락 : Metadata Lock

데이터베이스 객체(테이블, 뷰 …)의 이름이나 구조를 변경하는 경우 획득하는 잠금

• 명시적으로 획득하는 것이 아닌, 테이블 이름 변경과 같은 경우 자동으로 획득하는 잠금
• `RENAME TABLE` 명령의 경우, 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정

 

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은

스토리지 엔진 내부에서 레코드 기반의 잠금 방식

을 탑재하고 있습니다.

 

최근 MySQL에서는 `information_schema` 데이터에베이스에 존재하는 `INNODB_TRX`, `INNODB_LOCKS`, `INNODB_LOCK_WAITS`라는 테이블을 조인해 “현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지”를 확인할 수 있습니다. 또한, `Performance Schema`를 이용하면 내부 잠금(세마포어)에 대한 모니터링도 가능합니다.

 

InnoDB 스토리지 엔진의 경우 레코드 기반의 잠금 기능을 제공합니다.

1. 레코드 락 : Record Lock

레코드 자체만을 잠그는 것

• 다른 DBMS의 레코드 락과의 차이 : 레코드 자체가 아니라 인덱스의 레코드를 잠금
  • 인덱스가 하나도 없다면 내부적으로 자동 생성된 클러스터 인덱스를 사용

2. 갭 락 : Gap Lock

레코드 자체가 아니라, 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 방식

• 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(`INSERT`)되는 것을 제어

3. 넥스트 키 락 : Next Key Lock

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금

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

4. 자동 증가 락 : Auto Increment Lock

`AUTO_INCREMENT` 칼럼이 중복되지 않고 저장된 순서대로 증가하도록 저장되기 위한 잠금

• `INSERT`, `REPLACE` 쿼리와 같이 새로운 레코드를 저장하는 쿼리에서만 필요
• 트랜잭션과 관계없이 `INSERT`나 `REPLACE` 문장에서 `AUTO_INCREMENT` 값을 가져오는 순간만 락이 걸렸다가 즉시 해제됩니다.
• 명시적으로 획득하고 해제하는 방법은 없습니다.