MySQL 트랜잭션과 잠금

잠금 Lock

동시성을 제어하기 위한 기능

 

트랜잭션 Transaction

데이터의 정합성 Consistency을 보장하기 위한 기능

 

MySQL 트랜잭션

 

트랜잭션이란 논리적인 작업 셋 자체가 100% 적용되거나(Commit) 아무것도 적용되지 않아야(Rollback)함을 보장해 주는 것

 

데이터의 부분 업데이트 문제

 

CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MyISAM;

INSERT INTO tabl_myisam ( fdpk ) VALUES (3);

 

CREATE TABLE tabl_innodb ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=INNODB;

INSERT INTO tab_innodb (fdpk) VALUES (3);

 

MyISAM과 INNODB 엔진을 사용하는 두 테이블을 만들고 레코드 하나를 저장한다.

 

SET autocommit=ON;

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

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

 

AUTO-COMMIT 모드에서

두 개의 스토리지에 위 INSRET 쿼리를 사용하면 모두 3의 값을 가진 레코드를 삽입할 때 에러가 발생하지만 (중복된 키값) 다음과 같은 두 차이를 지닌다.

 

MyISAM : (1),(2) 값에 대하여 레코드 삽입 결과가 성공하고 (3)이 실패했더라도 (1),(2)값이 남아 있게 된다.

InnoDB : 쿼리 중 일부라도 오류가 발생하며 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 문장을 실행하기 전 상태로 그대로 복구한다. 따라서 (1)과 (2)의 삽입 결과는 되돌려진다.

 

트랜잭션 범위의 최소화

트랜잭션은 InnoDB 엔진에서 언두 로그를 예상보다 오랫동안 길게 남아 있게 할 수 있으며 적용해야하는 최적의 구간에만 트랜잭션을 적용해야한다.

 

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔젠 레벨로 나눌 수 있다. MySQL 엔젠 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지지는 않는다.

 

글로벌 락

글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득 가능하며 MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 잠금은 서버 전체에 적용되며 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT을 제외한 DDL 혹은 DML문장을 실행하는 경우 글로벌락이 해제될 때까지 해당 문장이 대기 상태로 남는다.

 

테이블 락

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

명시적 : LOCK TABLES table_name [READ | WRITE] 

묵시적 : MyISAM이나 Memory 테이블이 데이터를 변경하는 쿼리를 실행하면 발생한다. InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다.

 

네임드 락

네임드 락(Named Lock)은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.

• SELECT GET_LOCK('mylock', 2);
• SELECT IS_FREE_LOCK('mylock');
• SELECT RELEASE_LOCK('mylock');

 

메타데이터 락

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

RENAME TABLE tab_a TO tab_B 같은 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

 

InnoDB 스토리지 엔진 잠금

 

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. 레코드 기반의 잠금 방식 덕분에 InnoDB 엔진은 높은 동시성 처리를 제공할 수 있다.

 

InnoDB 엔진에서 잠금의 종류

레코드 락 : 단일 레코드에 대한 락

레코드 자체가 아니라 인덱스의 레코드를 잠근다. 인덱스가 없다면 자동 생성된 클러스트 인덱스를 사용해 잠금을 설정한다.

 

갭 락 : 레코드 사이의 범위를 잠그는 락

레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것

 

넥스트 키락 : 레코드 락과 갭 락을 합친 범위로 높은 수준의 격리 수준에서 자주 잠기는 락이다.

 

자동 증가 락

AUTO_INCREMENT 락이라고 도하며 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하다. (UPDATE나 DELETE에서는 필요하지 않다.)

 

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.

 

employees 테이블에 다음과 같은 DML 쿼리를 사용한다 하자

UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';

 

KEY ix_first_name (first_name) -> employees 테이블에 first_name 컬럼에 인덱스가 만들어져 있다.

 

UPDATE 쿼리는 인덱스가 있는 레코드를 모두 잠그게 된다. 만약 'Georgi'라는 이름이 해당 테이블에 253건이 있다하면 (last_name에는 인덱스가 없으므로) first_name='Georgi'를 갖는 레코드는 해당 레코드가 Update될 때까지 모두 잠긴다.

 

인덱스가 하나도 없다면 테이블을 풀 스캔하면서 UPDATE를 진행하는데 이 과정에서 테이블에 있는 모든 레코드를 잠그게된다.

 

MySQL 격리 수준

 

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

 

격리 수준은 "READ UNCOMITTED", "READ COMMITTED", "REPEATABLE READ", "SERIALIZABLE"이 있다.

 

격리 수준에 따른 데이터 부정합 발생 여부

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(InnoDB 제외)
SERIALIZABLE	없음	없음	없음

 

READ UNCOMMITTED

 

READ UNCOMMITED

READ UNCOMMITED 격리 수준에서는 트랜잭션의 변경 내용이 COMMIT 혹은 ROLLBACK 여부에 상관없이 중간 결과가 모두 다른 세션에서 보이게 된다.

 

유저 A가 트랜잭션 도중 emp_no=500000인 레코드를 삽입하였다. 유저 B는 유저 A가 COMMIT을 하든 ROLLBACK을 하든 상관없이 중간 SELECT 조회로 해당 중간 결과를 볼 수 있으며 이는 유저 A가 롤백을 할 경우 유저 B는 해당 테이블에서 제거된 비정상적인 레코드를 보게 된다.

 

어떤 트랜잭션에서 작업이 완료되지 않았음에도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty Read)라고 한다.

더티 리드는 실제 어플리케이션에서 상당한 혼란을 유발하므로 최소한 격리 수준을 READ COMMITTED 이상을 권고한다.

 

READ COMMITTED

READ COMMITTED

더티 리드는 발생하지 않는 격리 수준이다. COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.

이제 어떤 트랜재션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.

유저 A가 emp_no=500000인 레코드의 first_name을 Update 했더하더라도 유저 B는 그 중간에 해당 레코드를 조회를 했을 때 이전 버전인 Lara인 레코드를 얻게된다. 이는 언두 로그를 사용하여 변경 내역을 기록하고 그 언두 로그를 참조하여 조회 결과를 반환하기 때문이다.

 

READ COMMITTED 수준에서는 "NON-REPEATABLE READ" 부정합 문제가 있다.

NON-REPEATABLE READ

유저 B가 트랜잭션을 시작하고 first_name='Toto'인 레코드를 조회하지만 해당 테이블에 조건에 맞는 레코드는 존재하지 않는 상태이다. 유저 B가 트랜잭션을 유지한 채로 유저 A가 트랜잭션을 시작하여 first_name='Toto'인 레코드를 만들었다 하자. 유저 B는 다시 SELECT을 하게 되고 이 때 first_name='Toto'인 레코드를 얻게된다. NON-REPEATABLE READ는 하나의 트랜잭션에서 여러 읽기 결과가 다르게 나오는 부정합을 의미한다.

 

REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본을 사용되는 격리 수준이다. 이 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 NON-REPEATABLE READ 부정합 문제가 발생하지 않는다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경한다. 이러한 방식을 MVCC(Multi Version Concurreny Control)이라고 한다.

 

REPEATABLE READ는 MVCC 기술을 이용하여 단일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다. READ COMMITTED도 MVCC를 사용해 COMMIT 되기 이전의 상태를 보여주나 REPEATABLE READ와의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야에 있다.

 

InnoDB의 트랜잭션은 고유한 트랜잭션 번호를 가지고 언두 영역에는 백업된 모든 레코드에 대한 변경 트랜잭션 기록에 대한 번호가 포함돼있다. 언두 영역의 백업된 데이터는 스토리지 엔진이 필요없다고 판단되는 시점에 주기적으로 삭제한다. REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 중 특정 구간의 트랜잭션을 보존한다.

 

유저 B가 트랜잭션 도중 유저 A가 트랜잭션 번호가 12번인 쿼리(UPDATE SET first_name='Toto')를 실행하고 COMMIT했더라도 유저 B가 두번째 조회를 할 때 언두 로그 중 자신의 트랜잭션 번호 10번 안에서 실행된 결과 중 최신의 것을 가져오게 된다. 따라서 두 번의 읽기는 일치된 결과를 가져온다.

 

REPEATABLE READ 격리 수준에서도 PHANTOM READ라는 부정합 문제가 발생할 수 있다. SELECT ... FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데 언두 레코드에는 잠금을 걸 수 없다. 따라서 해당 조회는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드 값을 가져오게 되고 불일치가 발생한다.

 

SERIALIZABLE

가장 단순한 격리 수준이며 엄격한 격리 수준이다. InnoDB 테이블에서 순수한 SELECT 작업은 잠금이 필요없는 일관된 읽기를 제공하는데 격리 수준이 SERIALIZABLE인 경우 공유 잠금을 반드시 획득해야하며 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.

반드시 한 트랜잭션만 한 레코드를 읽거나 수정할 수 있게 되며 동시성이 가장 떨어지는 격리 수준이다.