트랜잭션은 원자성을 보장해 준다. 이를 통해 작업의 부분 업데이트(Partial update - 작업의 일부만 적용되는 현상)가 발생하지 않게 한다.
트랜잭션, 락, 격리가 비슷한 개념 같지만 다음과 같은 차이가 존재한다.
- 트랜잭션: 데이터 정합성을 보장하기 위한 기능.
- 락: 동시성 제어를 위한 기능. 여러 커넥션이 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에 하나의 커넥션만 변경하게 한다.
- 격리: 하나 이상의 트랜잭션 간의 작업 내용을 공유하고 차단하는 방식을 결정하는 레벨.
트랜잭션
MySQL 서버에서 MyISAM과 MEMORY 스토리지 엔진은 트랜잭션을 지원하지 않는 반면 InnoDB는 트랜잭션을 지원한다. 이 차이로 인해 MyISAM, MEMORY 스토리지 엔진의 사용은 많은 고민을 야기한다. 다음과 같이 스토리지 엔진이 각각 MyISAM과 InnoDB인 두 개의 테이블을 만들고 데이터를 insert 해보자.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE TABLE tab_myisam (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO tab_myisam (id) VALUES (3);
CREATE TABLE tab_innodb (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO tab_innodb(id) VALUES (3);
# AUTO-COMMIT 활성화
SET autocommit=ON;
INSERT INTO tab_myisam(id) VALUES (1), (2), (3);
INSERT INTO tab_innodb(id) VALUES (1), (2), (3)
|
cs |
그러면 다음과 같이 PK 중복으로 인한 에러가 발생한다.
그럼에도 MyISAM을 사용하는 테이블은 트랜잭션을 지원하지 않기에 insert가 된 것을 볼 수 있다.
그에 반해 InnoDB를 사용한 테이블은 쿼리 실행 도중 에러 발생을 인해 롤백이 됐다. 이를 통해 정합성이 맞추어진다.
주의사항
프로그램의 코드가 DB 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화해야 한다. 일반적으로 DB 커넥션 개수는 제한적이어서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션 개수가 줄어든다. 이로 인해 각 단위 프로그램에서 커넥션을 가져가기 위해 기다리는 상황이 발생할 수 있다. 추가로 네트워크 작업이 있는 경우에도 트랜잭션에서 배제해야 한다. 이런 주의점을 지키지 않으면 DBMS 서버가 높은 부하 상태로 빠지거나 위험한 상태에 빠지는 경우가 빈번히 발생한다.
MySQL 엔진의 잠금
MySQL에서 사용되는 잠금은 스토리지 레벨과 MySQL 엔진 레벨로 나뉜다. MySQL 엔진 레벨의 잠금은 모든 스토리지에 영향을 미친다. 그에 반해 스토리지 레벨의 락은 엔진 간 상호 영향을 미치지 않는다.
글로벌 락(Global Lock)
글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며 범위가 MySQL 서버 전체로 MySQL에서 제공하는 락 중에 범위가 가장 크다. 따라서 한 세션에서 글로벌 락을 획득하면 다른 세선에서는 SELECT를 제외한 나머지 문장은 글로벌 락 해제 전까지 대기상태로 남는다. 여러 DB에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때 글로벌 락을 사용한다.
InnoDB는 트랜잭션을 지원하기에 글로벌 락처럼 일관된 데이터 상태를 위해 모든 작업을 멈출 필요가 없다. 따라서 좀 더 가벼운 락의 필요성이 생겼다. 그래서 MySQL 8.0부터 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.
|
1
2
3
|
LOCK INSTANCE FOR BACKUP;
# 백얼 실행
UNLOCK INSTANCE;
|
cs |
특정 세션이 백업 락을 획득했다면 모든 세션은 테이블 스키마나 사용자 인증 관련 정보를 변경할 수 없다.
- DB, table 등 모든 객체 생성 및 변경, 삭제
- REPAIR TABLE과 OPTIMIZE TABLE 명령
- 사용자 관리 및 비밀번호 변경
일반적인 MySQL 서버는 소스 서버와 레플리카 서버로 구성된다. 주로 백업이 진행되는 레플리카 서버에서 글로벌 락을 획득하면 복제가 백업 시간만큼 지연된다. XtraBackup이나 Enterprise Backup 툴이 실행되는 도중 스키마 변경이 실행되면 백업은 실패한다. 백업 락은 이런 목적으로 도입됐으며, 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 한다.
테이블 락(Table Lock)
개별 테이블 단위로 설정되는 잠금이다. 명시적으로는 "LOCK TABLES table_name [ READ | WRITE]" 명령으로 특정테이블의 락을 획득할 수 있다. UNLOCK TABLES 명령으로 잠금을 해제할 수 있다. 테이블 락은 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치므로 명시적으로 사용할 필요는 거의 없다.
묵시적 테이블 락은 MyISAM, MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다. MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태다. InnoDB의 경우 레코드 기반 잠금을 제공한다. 따라서 대부분의 데이터 변경(DML) 쿼리에서는 테이블락이 설정되지 않고 스키마를 변경하는 쿼리(DDL)의 경우에만 설정된다.
네임드 락(Named Lock)
GET_LOCK() 함수를 이용해 설정할 수 있다. 네임드 락은 DB 객체가 아닌 사용자가 지정한 문자열에 대해 락을 획득하고 반납한다. 네임드 락은 자주 사용되지 않지만 여러 클라이언트가 상호 동기화를 처리해야 하는 경우 네임드 락을 사용하면 편리하다. 예를 들어 DB 서버 1대에 5대의 웹 서버가 접속해 서비스할 때 웹 서버들이 어떤 정보를 동기화해야 하는 경우가 있다.
많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에서도 유용하다. 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 종종 데드락의 원인이 된다. 이 경우 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해 네임드 락을 걸로 쿼리를 실행하면 해결할 수 있다.
MySQL 8.0부터 네임드 락을 중첩해 상용하고 현제 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가됐다.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
# "mylock"이라는 문자열에 대해 네임드 락 설정
# 락을 사용중이면 2초간 대기, 대기 후 자동 잠금 해제
SELECT GET_LOCK('mylock', 2);
SELECT GET_LOCK('mylock1', 2);
# "mylock"이라는 문자열에 대해 잠금이 설정돼 있는지 확인
SELECT IS_FREE_LOCK('mylock');
# "mylock"이라는 문자열에 대해 획득했던 잠금을 해제
SELECT RELEASE_LOCK('mylock');
# 해당 세션의 모든 네임드 락을 해제
SELECT RELEASE_ALL_LOCKS();
|
cs |
메타데이터 락(Metadata Lock)
메타데이터 락은 DB 객체의 이름이나 구조를 변경하는 경우 획득하는 락이다. 묵시적으로만 획득할 수 있다.
InnoDB 스토리지 엔진 잠금
InnoDB는 MySQL에서 제공하는 락과는 별개로 자체적으로 레코드 기반 락을 제공한다. 이 때문에 MyISAM 보다 훨씬 뛰어난 동시성 처리를 제공한다. 하지만 이원화된 잠금 탓에 InnoDB 스토리지 엔진에서 사용되는 락에 대한 정보를 MySQL 명령을 통해 접근하기 어려웠다. lock_monitor(innodb_lock_monitor라는 이름의 InnoDB 테이블을 생성해 InnoDB의 잠금 정보를 덤프 하는 방법)와 SHOW ENGINE INNODB STATUS 명령이 존재하긴 했지만 거의 어셈블리 코드를 모는 것 같아서 이해하기 어려웠다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2018-04-12 15:14:08 0x7f971c063700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 15 srv_active, 0 srv_shutdown, 1122 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 24
OS WAIT ARRAY INFO: signal count 24
RW-shared spins 4, rounds 8, OS waits 4
RW-excl spins 2, rounds 60, OS waits 2
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 2.00 RW-shared, 30.00 RW-excl, 0.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-04-12 14:57:24 0x7f97a9c91700 Transaction:
TRANSACTION 7717, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 8, OS thread handle 140289365317376, query id 14 localhost root update
INSERT INTO child VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5), (NULL, 6)
Foreign key constraint fails for table `test`.`child`:
,
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE
Trying to add in child table, in index par_ind tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000003; asc ;;
But in parent table `test`.`parent`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 000000001e19; asc ;;
2: len 7; hex 81000001110137; asc 7;;
...
|
cs |
하지만 최근 버전에서 InnoDB의 트랜잭션과 잠금, 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입되었다. information_schema DB에 존재하는 INNODB_TRX, INNODB_LOKCS, INNODB_LOCK_WAITS라는 테이블을 조인해 조회하면 현재 잠금을 대기하고 있는 트랜잭션, 잠금을 가지고 있는 트랜잭션 조회와 장시간 잠금을 가지는 트랜잭션 종료를 할 수 있다. Performance Schema를 이용하면 InnoDB 스토리지 엔진 내부 잠금에 대한 모니터링을 할 수 있다.
InnoDB 스토리지 엔진의 잠금
InnoDB는 레코드 기반 락을 제고하고 락 정보가 상당히 작은 공간으로 관리된다. 일반 상용 DBMS와 다르게 InnoDB에는 레코드 사이를 잠그는 갭 락이 존재한다.
레코드 락(Record lock, Record only lock)
레코드 자체만 잠그는 것을 의미한다. InnoDB의 경우 다른 DBMS와 다르게 인덱스의 레코드를 잠근다. 인덱스가 없더라도 클러스터 인덱스를 이용해 락을 설정한다. InnoDB는 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용하고 PK 또는 유니크 인덱스에 의한 변경 작업은 레코드 락을 사용한다.
갭 락(Gap lock)
레코드와 바로 인접한 레코드 사이를 잠근다. 갭 락은 레코드와 레코드 사이의 간격에 레코드가 INSERT 되는 것을 제어한다.
넥스트 키 락(Next Key lock)
레코드 락과 갭 락을 합친 것을 의미한다. innodb_locks_unsage_for_binlog 시스템 변수를 비활성화하면 변경을 위해 검색하는 레코드에 넥스트 키 락을 건다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 같은 결과를 만드는 것을 보장하는 게 주목적이다. 넥스트 키 락과 갭 락으로 인한 데드락이나 다른 트랜잭션을 기다리게 하는 일이 생각보다 자주 발생한다. 따라서 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄여야 한다.
자동 증가 락(Auto increment lock)
MySQL은 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공한다. AUTO_INCREMENT를 사용하는 테이블에 여러 레코드가 동시에 INSERT 될 경, 저장되는 각 레코드는 순서대로 중복되지 않는 증가하는 번호를 가져야 한다. InnoDB는 이를 위해 내부적으로 AUTO_INCREMENT 락이라는 테이블 수준의 잠금을 사용한다.
AUTO_INCREMENT 락은 INSERT, REPLACE 같은 레코드를 저장하는 쿼리에서만 필요하다. 또 한 트랜잭션과 관련 없이 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다 해제된다. AUTO_INCREMENT 락은 테이블에 하나만 존재하기 때문에 두 개의 INSERT 쿼리가 동시에 실행될 경우 하나의 쿼리가 AUTO_INCREMENT 락을 걸면 나머지 쿼리는 AUTO_INCREMENT 락을 기다려야 한다.
자동 증가 락은 5.1부터 시스템 변수(innodb_autoinc_lock_mode)를 이용해 적용 방식을 선택할 수 있다. 위에서 설명한 적용 방식은 MySQL 5.0까지 적용된 방식이다. 자세한 설명은 링크를 보자.
인덱스와 잠금
아래와 같은 쿼리가 있고 first_name 칼럼을 인덱싱 했다 하자.
|
1
|
UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='klassen';
|
cs |
위 쿼리는 1 건의 업데이트를 하지만 first_name이 인덱스이기 때문에 그와 관련된 모든 레코드를 잠근다. 따라서 UPDATE 문장을 위한 적절한 인덱스가 준비돼 있지 않다면 각 클라이언트 간의 동시성이 떨어져서 산 세션에서 UPDATE 작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다리는 상황이 발생한다. 만약 인덱스가 안 걸려 있다면 풀테이블 스캔을 사용해 더 많은 레코드를 잠그게 된다.
레코드 수준의 잠금 확인 및 해제
MySQL 5.1부터 레코드 잠금과 잠금 대기에 대한 조회가 가능하다. 레코드 수준의 잠금을 확인하기 위해 다음과 같은 시나리오를 생각해 보자.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
# connection 1
BEGIN;
SET autocommit = false;
UPDATE employee SET birth_date=NOW() WHERE id=1;
# connection 2
BEGIN;
SET autocommit = false;
UPDATE employee SET hire_date=NOW() WHERE id=1;
# connection 3
BEGIN;
SET autocommit=false;
UPDATE employee SET birth_date=NOW(), hire_date=NOW() WHERE id=1;
# connection 4
SHOW PROCESSLIST;
|
cs |
위 시나리오에서 PROCESSLIST를 조회하면 UPDATE 명령이 실행된 프로세스들을 조회한다.
여기서 맨 처음 UPDATE 쿼리가 실행되고 COMMIT을 하지 않았으므로 이후에 실행된 UPDATE가 존재하는 6278, 6289번 스레드는 잠금 대기로 인해 UPDATE 명령을 실행 중이다.
이제 performance_schema의 data_locks 테이블과 data_lock_waits 테이블을 조인해 잠금 대기 순서를 살펴보자.
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_queyr,
innodb_trx.trx_id blocking_trx_id,
innodb_trx.trx_mysql_thread_id blocking_thread,
innodb_trx.trx_query blocking_query
FROM performance_schema.data_lock_waits data_lock_waits
INNER JOIN information_schema.INNODB_TRX innodb_trx
ON innodb_trx.trx_id = data_lock_waits.blocking_engine_transaction_id
INNER JOIN information_schema.INNODB_TRX r
ON r.trx_id = data_lock_waits.requesting_engine_transaction_id;
|
cs |
위에서 언급한 6278, 6279번 스레드가 대기 중 인 것을 알 수 있다. 또 한 blocking_thread column과 waiting_thread column을 통해 6278번 스레드는 6272번 스레드를 기다리고, 6279번 스레드는 6278번, 6272번 스레드를 기다리는 것을 볼 수 있다.
이제 performance_shcema의 data_locks 테이블을 조회해 6272번 스레드가 어떤 락을 가지는지 확인해 보자.
|
1
|
SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, ENGINE FROM performance_schema.data_locks;
|
cs |
위 결과를 통해 employee 테이블이 IX 잠금(Interntional Exclusive)을 가지고 있고, employee 테이블의 특정 레코드에 대해 쓰기 잠금을 가지는 것을 알 수 있다. REC_NOT_GAP 표시가 있으므로 레코드 잠금은 갭 락이 포함되지 않은 것을 알 수 있다.
만약 오랜 시간 잠금을 가지는 스레드가 있다면 다음과 같이 강제종료할 수 있다.
|
1
|
kill 6272;
|
cs |
MySQL의 격리 수준(Isolation Level)
트랜잭션 격리 수준은 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에 변경하거나 조회하는 데이터를 볼 수 있게 허용할지를 결정한다. 격리 수준은 다음과 같이 총 4개가 존재한다.
| DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
| READ UNCOMMITED | O | O | O |
| READ COMMITED | X | O | O |
| REPEATABLE READ | X | X | O(InnoDB는 발생하지 않음) |
| SERIALIZABLE | X | X | X |
이 4개의 격리 수준은 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리 정도가 높아지지만 동시에 동시 처리 성능도 떨어진다. READ UNCOMMITTED는 일반적인 DB에서 거의 사용하지 않고, SERIALIZABLE 역시 동시성이 중요한 경우 거의 사용하지 않는다. MySQL은 REPEATABLE READ를 주로 사용한다.
READ UNCOMMITED
각 트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK 여부와 관련 없이 다른 트랜잭션에서 보인다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
# transaction 1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
BEGIN;
CREATE TABLE member (
id BIGINT AUTO_INCREMENT,
name VARCHAR(255),
PRIMARY KEY (id)
);
SET autocommit=false;
INSERT INTO member VALUES (1, 'name');
# transaction 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
BEGIN;
SELECT * FROM member
|
cs |
위와 같이 임의의 트랜잭션에서 처리한 작업이 commit 되지 않았음에도 다른 트랜잭션에서 볼 수 있는 현상을 DIRTY READ라 한다. 더티 리드는 데이터가 나타났다 사라지는 현상을 초래하기에 개발자와 사용자 모두를 혼란스럽게 만든다.
READ COMMITED
오라클 DBMS에서 기본으로 사용되는 격리 수준이다. 온라인 서비스에서 가장 많이 선택되는 격리 수준이다. 이미의 트랜잭션에서 데이터를 변경했을 때 COMMIT 된 데이터만 다른 트랜잭션에서 조회할 수 있다. 이는 하나의 트랜잭션이 레코드 업데이트 쿼리를 날리고 커밋을 하지 않으면 다른 트랜잭션은 언두 영역에서 값을 조회하기 때문이다. 트랜잭션에서 업데이트 쿼리를 날리면 새로운 값은 테이블에 기록되고 이전 값은 언두 영역으로 백업된다.
하지만 READ COMMITED에서도 NON_REPEATABLE READ라는 부정합 문제가 발생한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
# transaction 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
BEGIN;
SELECT * FROM member WHERE name='updatedName';
# transaction 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
BEGIN;
UPDATE member SET name='updatedName' WHERE id=1;
COMMIT;
# transaction 1
SELECT * FROM member WHERE name='updatedName';
|
cs |
위 케이스에선 transaction1에서 한 번의 조회가 발생한 뒤 해당 트랜잭션이 끝나지 않은 상태에서 trasaction2에서 레코드를 업데이트하고 커밋을 했다. 이 때문에 transaction1에서 또 한 번의 조회가 발생하면 이전엔 없던 레코드를 조회하게 된다. 이는 하나의 트랜잭션에서 여러 번의 조회가 발생할 때 항상 같은 결과를 지녀야 한다는 REPEATABLE READ 정합성에 어긋난다.
트랜잭션 내에서 실행되는 SELECT 문과 트랜잭션 없이 실행되는 SELECT 문은 READ COMMITTED 격리 수준에선 별 차이가 없다. 하지만 REPEATABLE READ 격리 수준에선 기본적으로 SELECT 쿼리도 트랜잭션 범위 내에서만 작동한다. 따라서 트랜잭션을 시작하면 끝날 때까지 하나의 쿼리는 항상 같은 결과만 반환한다.
REPEATABLE READ
InnoDB가 디폴트로 사용하는 격리 수준이다. 바이너리 로그를 가진 MySQL 서버에서는 최도 REPEATABLE READ를 사용해야 한다. InnoDB 스토리지 엔진은 트랜잭션 롤백에 대비해 변경 전 레코드를 언두(undo) 공간에 백업하고 실제 레코드를 변경한다. 이 방식이 MVCC다. READ COMMITTED, REPEATABLE READ 모두 언두 영역에 백업된 데이터를 이용해 COMMIT 되기 이전 데이터를 보여준다. 하지만 언두 영역에 백업된 레코드 버전 중 어느 버전까지 사용하느냐의 차이가 존재한다.
모든 InnoDB 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가)를 가진다. 언두 영역에 백업된 모든 레코드에는 이 트랜잭션 번호가 포함돼 있다. InnoDB는 이 데이터가 불필요하다고 판단될 때 주기적으로 삭제한다. REPEATABLE READ에선 MVCC 보장을 위해 가장 오래된 트랜잭션 번호보다 앞선 언두 영역 데이터를 삭제하지 않는다. 하지만 이를 가장 오래된 트랜잭션에 이전에 변경된 모든 언두 데이터가 필요하다는 의미로 받아들여선 안된다. 정확히는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다.
언두 영역엔 하나의 레코드에 대해 백업된 데이터가 얼마든지 한 개 이상 존재할 수 있다. 또 한 트랜잭션이 장시간 유지되면 언두 영역에 백업된 데이터가 커진다. 따라서 MySQL 서버 처리 성능 저하가 발생할 수 있다.
REPEATABLE READ는 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 PHANTOM READ라는 문제가 발행한다.
|
1
2
3
4
5
6
7
8
9
10
11
|
# transaction 1
BEGIN;
SELECT * FROM member WHERE id = 1 FOR UPDATE;
# transaction 2
BEGIN;
INSERT INTO member VALUES (2, 'name2');
COMMIT;
# transaction 1
SELECT * FROM member WHERE id = 2 FOR UPDATE;
|
cs |
SELECT... FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하지만 언두 레코드는 락을 걸 수 없다. 이 때문에 언두 영역에서 데이터를 가져오는 대신 실제 레코드에서 값을 가져온다. SELECT ... LOCK IN SHARE MODE도 마찬가지다.
SERIALIZABLE
가장 단순하면서 동시에 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능이 떨어진다. SERIALIZABLE은 다른 격리 수준과 달리 읽기에서 조차 락을 건다. 그 때문에 PHANTOM READ가 발생하지 않는다. InnoDB의 경우 넥스트 키 락과 갭 락 덕분에 REPEATABLE READ에서도 PHANTOM READ가 발생하지 않는다(위 REPEATABLE READ에서 서술한 특수 경우 제외). 따라서 굳이 SERIALIZABLE을 사용할 필요 없다.
출처 - Real MySQL