트랜잭션과 잠금

개발자와 DBA를 위한 Real MySQL 책을 보고 정리한 내용이다.

트랜잭션

• 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나 또는 아무것도 적용되지 않아야 함을 보장해주는 것

트랜잭션 관점에서 InnoDB/MyISAM 스토리지 엔진 차이

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);

-- INSERT 1,2,3 

mysql> INSERT INTO tab_myisam(fdpk) VALUES (1), (2), (3);
ERROR 1062(23000): Duplicate entry '3' for key 'PRIMARY';

mysql> INSERT INTO tab_innodb(fdpk) VALUES (1), (2), (3);
ERROR 1062(23000): Duplicate entry '3' for key 'PRIMARY';

mysql> SELECT * FROM tab_myisam;
fdpk
1
2
3

mysql> SELECT * FROM tab_innodB;
fdpk
3

MyISAM 테이블, InnoDB 테이블 모두 3 INSERT시 이미 있어 Duplicate 에러가 발생한다.

INNODB의 경우, INSERT INTO tab_innodb(fdpk) VALUES (1), (2), (3); 문장이 하나의 트랜잭션이므로 3 INSERT에서 Duplicate 에러 발생해 해당 SQL문을 실행하기 전 상태로 되돌린다. MyISAM 테이블의 경우, 3 INSERT시에 발생하지만 1과 2는 남아 있는걸 확인할 수 있다.

MyISAM 테이블에서 발생하는 이러한 현상을 부분 업데이트 (Partial Update)라고 표현하며, 이 현상은 테이블 데이터의 정합성을 맞추는데 상당히 어려운 문제를 만든다.

주의사항

트랜잭션은 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 이는 프로그램 코드에서 트랜잭션의 범위를 최소화하라는 의미이다.

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 스토리지 엔진, MySQL 엔진 레벨로 구분된다.

스토리지 엔진

• MySQL 엔진
• MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분
• MySQL 엔젠 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치게 되지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진간 상호 영향을 미치지 않는다.

글로벌 락(Global Lock)

MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 일단 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다. 대상 테이블이나 데이터베이스가 다르다 하더라도 동일하게 MySQL 서버 전체에 영향을 미친다. 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야할 때는 글로벌 락을 사용해야 한다.

mysql> FLUSH TABLES WITH READ LOCK

테이블 락(Table Lock)

개별 테이블 단위로 설정되는 잠금. 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

mysql> LOCK TABLES table_name [ READ | WRITE]

테이블 락은 MyISAM 뿐 아니라 InnoDB 스토리지 엔진을 사용하는 테이블도 동일하게 설정할 수 있다.

명시적으로 획득한 잠금은 "UNLOCK TABLES" 명령으로 잠금을 반납 할 수 잇다.

명시적인 테이블 락도 특별한상황이 아니면 애플리케이션에서 거의 사용할 필요가 없다.

묵시적 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다. MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다. 자동 획득/반납

InnoDB 테이블의 경우, 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되진 않는다. 정확하게는 InnoDB 테이블에도 락이 설정되지만 대부분의 데이터 변경 쿼리(DML)에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.

유저 락(User Lock)

GET_LOCK() 함수를 이용해 임의로 잠금 설정 가능

이 잠금의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것이다. 유저 락은 단순히 사용자가 지정한 문자열(String)에 대해 락을 획득하고 반납하는 잠금이다.

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

네임 락

데이터베이스 객체의 이름을 변경하는 경우 획득하는 잠금이다. 네임 락은 명시적으로 획득하거나 해제할 수 있는 것이 아니고 "RENAME TABLE tab_a to tab_b"와 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

MyISAM과 MEMORY 스토리지 엔진의 잠금

MyISAM이나 MEMORY 스토리지 엔진은 자체적인 잠금을 가지지 않고 MySQL 엔진에서 제공하는 테이블 락을 그대로 사용한다. 쿼리 단위로 필요한 잠금을 한꺼번에 모두 요청해 획득하므로 데드락이 발생할 수 없다.(??)

잠금 획득

읽기 잠금

• 테이블에 쓰기 잠금이 걸려 있지 않으면 바로 읽기 잠금을 획득하고 읽기 작업을 시작할 수 있다.

쓰기 잠금

• 테이블에 아무런 잠금이 걸려 있지 않아야만 쓰기 잠금을 획득할 수 있고, 그렇지 않다면 다른 잠금이 해제될 때까지 대기해야 한다.

 

잠금 튜닝

테이블 락에 대한 작업 상황 확인

mysql> SHOW STATUS LIKE 'Table%';

Variable_name                    |  Value
------------------------------------------
Table_locks_immediate             | 1151552
Table_locks_waited                | 15324

잠금 대기 쿼리 비율 = Table_locks_waited / (Table_locks_immediate + Table_locks_waited) * 100;

(15324/(1151552 + 15324)) * 100 = 1.31%

쿼리 100개 중 1개는 잠금 대기를 겪고 있다는 것을 알 수 있다. 만약 이 수치가 높고 테이블 잠그 때문에 경합(Lock contention)이 많이 발생하고 있으면 자연히 처리 성능이 영향을 받고 있음을 의미하므로 테이블을 분리한다거나 InnoDB 스토리지 엔진으로 변환하는 방법을 고려해 보는 것이 좋다. InnoDB 스토리지 엔진의 경우에는 레코드 단위의 잠금을 사용하기 때문에 집계에 포함되지 않는다.

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 MySQL 에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. 레코드 기반의 잠금 방식이므로 MyISAM 보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있다. 하지만 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다롭다. MySQL 5.1이후부터는 INFORMATION_SCHEMA라는 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해 조회하면 현재 어떤 트랜잭션이 어던 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있으며, 장시간 잠금을 가지고 있는 클라이언트를 종료시키는 것도 가능하다.

InnoDB 잠금 방식

비관적 잠금

• 현재 트랜잭션에서 변경하고자 하는 레코드에 대해 잠금을 획득하고 변경 작업을 처리하는 방식을 비관적 잠금이라 한다.
• "현재 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할 수 있다." 라는 비관적인 가정을 하기 때문에 먼저 잠금을 획득한 것이다.
• 일반적으로 높은 동시성 처리에는 비관적 잠금이 유리하다고 알려져 있으며 InnoDB는 비관적 잠금 방식을 채택하고 있다.

낙관적 잠금

• 각 트랜잭션이 같은 레코드를 변경할 가능성은 상당히 희박할 것이라고 가정한다.
• 그래서 우선 변경 작업을 수행하고 마지막에 잠금 충돌이 있었는지 확인해 문제가 있었다면 ROLLBACK 처리하는 방식을 의미한다.

InnoDB 잠금 종류

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로 또는 테이블 락으로 레벨업되는 경우는 없다. 일반 상용 DBMS와는 조금 다르게 InnoDB 스토리지 엔진에서는 레코드 락뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이라는 것이 존재한다.

레코드 락(Record lock, Record only lock)

레코드 자체만을 잠그는 것을 레코드 락이라고하며, 다른 DBMS의 레코드 락과 동일한 역할을 한다. 한 가지 중요 차이는 레코드 자체를 잠그는 것이 아닌 인덱스의 레코드를 잠근다는 점이다.

갭 락(Gap lock)

레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다. 갭 락은 개념일 뿐이고 자체적으로 사용되지 않는다. 넥스트 키 락의 일부로 사용된다.

넥스트 키 락(Next key lock)

레코드 락 + 갭 락 = 넥스트 키 락

STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다. 또한 innodb_locks_unsafe_for_binlog 파라미터가 비활성화되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.

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

넥스트 키 락과 갭락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생해 바이너리 로그 포멧을 ROW형태로 바꿔 넥스트 키 락이나 갭락을 줄이는 것이 좋다. 하지만 아직 ROW 포멧의 바이너리 로그는 그다지 널지 사용되지 않기 때문에 안정성을 확인하는 것이 어려운 상태이며, 또한 STATEMENT 포멧의 바이너리 로그에 비해 로그 파일의 크기가 상당히 커질 가능성이 많다.

자동 증가 락(Auto increment lock)

InnoDB 스토리지 엔진에서는 insert 되는 값에 대해 고유한 값을 가지기 위해 내부적으로 AUTO INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.

AUTO INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE나 DELETE 등의 쿼리에서는 걸리지 않는다. InnoDB의 다른 잠금과는 달리, AUTO_INCREMENT 의 다른 잠금과는 달리 AUTO_INCREMENT 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 값을 가져오는 순간만 락이 걸린 후 즉시 해제된다.

명시적으로 획득하고 해제하는 방법은 없다.

MySQL 5.1 이상부터는 innodb_autoinc_lock_mode 라는 파라미터를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.

Innodb_autoinc_lock_mode = 0 
    모든 INSERT 문장은 자동 증가 락을 사용한다.

Innodb_autoinc_lock_mode = 1 
    MySQL 서버가 INSERT 되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처        리한다. 개선된 래치는 자동 증가 락과 달리 아주 짧은 시간 동안만 잠금을 걸고 필요한 자동 증가 값을 가져오면 즉시 잠금이 해제된다. 
    하지만 INSERT...SELECT와 같이, MySQL서버가 건수를 예측할수 없을 때는 MySQL 5.0에서와 같이 자동 증가 락을 사용하게 된다. 

Innodb_autoinc_lock_mode = 2
    InnoDB 스토리지 엔진은 절대 자동증가 락을 걸지 않고 항상 경량화된 래치(뮤텍스)를 사용한다. 하지만 이 설정에서는 하나의 INSERT 문장으로 INSERT 되는 레코드라 하더라도 연속된 자동 증가 값을 보장하지는 안흔다. 그래서 이 설정 모드를 인터리빙 모드(Interleaved mode)라고도 한다. 통시 처리 성능이 높아지게 되지만 복제를 사용하는 경우네느 마스터와 슬레이브의 자동 증가 값이 달라질 가능성이 있어 주의해 사용해야한다.

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 즉, 변경해야 할 레코드르 찾기 위해 검색한 인덱스의 레코드를 모두 잠가야한다.

예를 들어 first_name이 Georgi이고 last_name이 klassen인 사원의 입사 일자를 오늘도 변경하고자 하며, 인덱스는 first_name에만 걸려있다고 가정한다.

위와 같은 상황에서 first_name이 Georgi인 레코드를 모두 잠근 상태에서 last_name이 klassen인 사람을 찾아 변경하고 락을 해제하게 된다. MySQL의 방식이며, MySQL의 InnoDB에서 인덱스 설계가 중요한 이유 또한 이 때문이다.

MySQL의 격리 수준

트랜잭션의 격리 수준(isolation level)이란 동시에 여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 혀용할지 말지를 결정하는 것이다.

데이터베이스 격리 수준을 이야기하면 항상 함께 언급되는 3가지 부정합 문제점이 있다. 이 3가지 부정합의 문제는 격리 수준의 레벨에 따라 발생할 수도 있고 발생하지 않을 수도 있다.

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	발생하지 않음	발생	발생
REPEATABLE READ	발생하지 않음	발생하지 않음	발생(InnoDB는 발생하지 않음)
SERIALIZABLE	발생하지 않음	발생하지 않음	발생하지 않음

READ UNCOMMITTED

어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상을 더티 리드라 하고, 이 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED이다. 더티 리드는 데이터가 나타났다가 사라졌다 하는 현상이 발생한다.

더티 리드를 유발하는 READ UNCOMMITTED는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다. MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장한다.

READ COMMITTED

오라클 DBMS에서 기본적으로 사용되고 있는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다. 더티 리드와 같은 현상은 발생하지 않는다. commit이 완료되기 전 다른 트랜잭션에서 조회시, 언두영역에 백업된 레코드에서 값을 가져온다.

READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ 라는 부정합 문제가 있다. 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때에는 항상 같은 값을 가져와야한다는 REPEATABLE READ 정합성에 어긋난다.

REPEATABLE READ

REPEATABLE READ 는 MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL의 장비에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경한다. 이러한 변경 방식을 MVCC라고 한다.

READ COMMITTED도 MVCC를 이용해 COMMIT되기 전의 데이터를 보여준다. READ COMMITTED와 REPEATABLE READ의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가는지에 있다.

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다. REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다.

SERIALIZABLE

가장 단순한 격리 수준이면서도 가장 엄격한 격리 수준이다. 또한 그만큼 동시 처리성능도 다른 트랜잭션 격리 수준보다 떨어진다. InnoDB 테이블에서 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행된다.

격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.