MySQL 트랜잭션이란? MySQL 잠금(Lock)

이번 게시글은 MySQL의 동시성에 영향을 미치는 잠금(Lock)과 트랜잭션, 트랜잭션의 격리 수준(Isolation level)에 관한 내용이다.

 

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

 

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

 

→ 잠금은 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다.

→ 격리 수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미

 

 

 

 

▶ MySQL에서의 트랜잭션

---

• 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나(COMMIT을 실행했을 때) 아무것도 적용되지 않아야(ROLLBACK 또는 트랜잭션을 ROLLBACK시키는 오류가 발생했을 때) 함을 보장해주는 것이다.

 

mysql> CREATE TABLE tab_myisam( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MyISAM;
mysql> INSERT INTO tab_myisam (fdpk) values (3);

mysql> CREATE TABLE tab_innodb( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=INNODB;
mysql> INSERT INTO tab_innodb (fdpk) values (3);

위 테스트용 테이블 레코드에 1건 씩 저장한 후 Auto-Commit 모드에서 다음 쿼리를 실행시킨다.

 

mysql> SET autocommit=ON;

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

 

→ 위 문장은 모두 프라이머리 키 중복 오류로 쿼리가 실패한다. 근데 두 테이블의 레코드를 조회해보면

→ MyISAM ('1', '2', '3')가 존재하며

→ InnoDB ('3')만 존재한다.

 

InnoDB는 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT문장을 실행하기 전 상태로 그대로 복구한다. MyISAM 테이블에서 발생하는 이러한 현상을 부분 업데이트(Partial Update)라고 표현한다.

 

• 트랜잭션에서 네트워크 작업이 있는 경우는 반드시 트랜잭션과 분리한 후 배제시켜야 한다.

 

 

 

 

 

 

 

▶ MySQL 엔진의 잠금

---

• MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 구분
• MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지는 않는다.
• MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블의 구조를 잠그는 메트데이터 락(Metadata Lock)
• 사용자의 필요에 맞게 사용할 수 있는 네임드 락(Named Lock)이라는 잠금 기능 제공한다

 

 

 

 

 

 

 

▶ 글로벌 락

---

• MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
• 일단 한 세션에서 글로벌 락을 흭득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.
• 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.
• 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 한다.
• 글로벌 락은 MySQL 서버의 모든 테이블에 큰 영향을 미치기 때문에 웹 서비스용으로 사용되는 MySQL 서버에서는 가급적 사용하지 않는 것이 좋다.
• MySQL 8.0부터는 InnoDB가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성이 생겼다. 그래서 MySQL 8.0버전부터는 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.

 

※ 백업락

• 특정 세션에서 백업 락을 흭득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 된다.
  
  • DB 및 테이블 등 모든 객체 생성 및 변경, 삭제
  • REPAIR TABLE과 OPTIMIZE TABLE 명령
  • 사용자 관리 및 비밀번호 변경
• 하지만 백업 락은 일반적인 테이블의 데이터 변경은 허용된다.
• 일반적인 MySQL 서버의 구성은 소스 서버(Source server)와 레플리카 서버(Replica server)로 구성되는데, 주로 백업은 레플리카 서버에서 실행된다.
• 레플리카 서버에서 백업을 실행하는 도중에 소스 서버에 문제가 생기면 레플리카 서버의 데이터가 최산 상태가 될 때까지 서비스를 멈춰야 할 수도 있다. 백업 도중 스키마 변경이 일어나면 실패하게 된다.
• 백업 락은 이러한 목적으로 도입됐으며, 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 한다.

 

 

 

 

 

 

 

 

▶ 테이블 락

---

• 테이블 락(Table Lock)은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 흭득할 수 있다.
• 테이블 락은 MyISAM 뿐만 아니라 InnoDB 스토리지 엔진을 사용하는 테이블도 동일하게 설정할 수 있다.
• 명시적 테이블 락은 사용할 일이 거의 없다.
• 묵시적 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.
• MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다.
• 즉, 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득했다가 쿼리가 완료된 후 자동 해제된다.
• 하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다.
• 더 정확히는 InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.

 

 

 

 

 

▶ 네임드 락

---

• 네임드 락(Named Lock)은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.
• 이 잠금의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것이다.
• 네임드 락은 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하는 잠금이며 자주 사용되지 않는다.

예를 들어, 데이터베이스 서버 1대에 5대의 웹 서버가 접속해서 서비스하는 상황에서 5대의 웹 서버가 어떤 정보를 동기화해야 하는 요건처럼 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 이용하면 쉽게 해결할 수 있다.

• 네임드 락의 경우 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다.
• 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 한다. 각 프로그램의 실행 시간을 분산하거나 프로그램의 코드를 수정해서 데드락을 최소화할 수는 있지만, 이는 간단한 방법이 아니며 완전한 해결책이 될 수도 없다.
• 이러한 경우 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있다.

 

 

 

 

 

 

▶  메타데이터 락

---

• 메타데이터 락(Metadata Lock)은 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.

-- // 배치 프로그램에서 별도의 임시 테이블(rank_new)에 서비스용 랭킹 데이터를 생성

-- // 랭킹 배치가 완료도면 현재 서비스용 랭킹 테이블(rank)을 rank_backup으로 백업하고
-- // 새로 만들어진 랭킹 테이블(rank_new)을 서비스용으로 대체하고자 하는 경우

mysql> RENAME TABLE rank TO rank_backup, rank_new TO rank;

- 위와 같이 하나의 RENAME TABLE 명령문에 두 개의 RENAME 작업을 한꺼번에 실행하면 실제 애플리케이션에서는 'Table not found rank'같은 상황을 발생시키지 않고 적용하는 것이 가능하다.

- 하지만 이 문장을 2개로 나눠서 실행하면 아주 짧은 시간이지만 rank 테이블이 존재하지 않는 순간이 발생하며, 그 순간에 쿼리는 'Table not found 'rank''오류를 발생시킨다.

 

mysql> RENAME TABLE rank TO rank_backup;
mysql> RENAME TABLE rank_new TO rank;

 

 

 

때로는 메타데이터 잠금과 InnoDB의 트랜잭션을 동시에 사용해야 하는 경우도 있다. 예를 들어. 다음과 같은 구조의 INSERT만 실행되는 로그 테이블을 가정해보자. 이 테이블은 웹 서버의 액세스(접근) 로그를 저장만 하기 때문에 UPDATE와 DELETE가 없다.

mysql> CREATE TABLE access_log (
    id BIGINT NOT NULL AUTO_INCREMENT,
    client_ip INT UNSIGNDED,
    access_dttm TIMESTAMP,
    ...
    PRIMARY KEY(id)
);

 

그런데 어느 날 이 테이블의 구조를 변경해야 할 요건이 발생했다. 물론 MySQL 서버의 Online DDL을 이용해서 변경할 수도 있지만 시간이 너무 오래 걸리는 경우라면 언두 로그의 증가와 Online DDL이 실행되는 동안 누적된 Online DDL 버퍼의 크기 등 고민해야 할 문제가 많다. 

 

더 큰 문제는 MySQL 서버의 DDL은 단일 스레드로 작동하기 때문에 상당히 많은 시간이 소모될 것이라는 점이다.

이때는 새로운 구조의 테이블을 생성하고 먼저 최근(1시간 직전 또는 하루 전)의 데이터까지는 프라이머리 키인 id값을 범위별로 나눠서 여러 개의 스레드로 빠르게 복사한다.

 

mysql> CREATE TABLE access_log (
    id BIGINT NOT NULL AUTO_INCREMENT,
    client_ip INT UNSIGNDED,
    access_dttm TIMESTAMP,
    ...
    PRIMARY KEY(id)
) KEY_BLOCK_SIZE=4;

--// 4개의 스레드를 이용해 범위별로 레코드를 신규 테이블로 복사
mysql_thread1> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=0 AND id<10000;
mysql_thread2> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=10000 AND id<20000;
mysql_thread3> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=20000 AND id<30000;
mysql_thread4> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=30000 AND id<40000;

그리고 나머지 데이터는 다음과 같이 트랜잭션과 테이블 잠금, RENAME TABLE 명령으로 응용 프로그램의 중단 없이 실행할 수 있다. 이때 "남은 데이터를 복사"하는 시간 동안은 테이블의 잠금으로 인해 INSERT를 할 수 없게 된다.

 

그래서 가능하면 미리 아주 최근 데이터까지 복사해둬야 잠금 시간을 최소화해서 서비스에 미치는 영향을 줄일 수 있다.

 

--// 트랜잭션을 autocommit으로 실행(BEGIN이나 START TRANSACTION으로 실행하면 안됨)
mysql> SET autocommit=0;

--// 작업 대상 테이블 2개에 대해 테이블 쓰기 락을 획득
mysql> LOCK TABLES access_log WRITE, access_log_new WRITE;

--// 남은 데티터를 복사
mysql> SELECT MAX(id) as @MAX_ID FROM access_log_new;
mysql> INSERT INTO access_log_new SELECT * FROM access_log WHERE pk>@MAX_ID;
mysql> COMMIT;

--// 새로운 테이블로 데이터 복사가 완료되면 RENAME 명령으로 새로운 테이블을 서비스로 투입
mysql> RENAME TABLE access_log TO access_log_old, access_log_new TO access_log;
mysql> UNLOCK TABLES;

--// 불필요한 테이블 삭제
mysql> DROP TABLE access_log_old;