MySQL 트랜잭션 이해하기

 

from 데이터베이스 개론

 

트랜잭션은 데이베이스에 대한 하나의 조작 단위라고 정의할 수 있습니다. 데이터베이스에 대한 조작을 트랜잭션 단위로 관리해서, 여러 사용자가 같은 데이터에 대해서 동시에 복수의 읽기나 변경작업을 관리할 수 있게 됩니다.

 

하지만, 트랜잭션으로 관리해도 문제가 남아있습니다. 

 

 

Dirty read

Read중에 Update하는 경우입니다. 데이터 일부가 잘못되는 Dirty read가 일어날 수 있습니다. 가령, Update 중에 rollback이 되는 데이터를 Read한 경우가 이에 해당합니다.

트랜잭션 1	트랜잭션 2	비고
start transaction;	start transaction;
Update x, old value x = 1, new value x = 2
	Read x, x = 2 (dirty read)
Rollback		트랜잭션 1이 rollback해서 x는 다시 1로 설정됩니다.

 

Non repeatable read

Non repeatable read는 한 트랜잭션이 다른 트랜잭션의 commited 데이터를 읽어 select 전후에 일관성 없는 데이터가 발생하는 것을 말합니다. 예를 들어, 트랜잭션 1는 일부 데이터를 수정하고 커밋하지만 트랜잭션 2는 이를 읽고 트랜잭션 2는 Non repeatable read가 발생하게 됩니다.

 

트랜잭션 1	트랜잭션 2	비고
Start transaction	Start transaction
Read x = 1	Read x = 1
Update x = 2
Commit
	Read x = 2	트랜잭션 2는 1번째와 2번째 Read시에 각긱 다른 값을 조회합니다.

 

Phantom read

Phantom read는 동일한 트랜잭션이 동일한 select 문을 두 번 실행하여 다른 결과를 얻거나, 선택 과정에서 레코드가 없지만 insert시에 레코드가 이미 존재하기 때문에 삽입할 수 없는 증상이 발생하게 된다.

 

트랜잭션 1	트랜잭션 2	비고
Start transaction
	Start transaction
회원 SELECT	회원 SELECT	회원이 존재 하지 않음
	회원 INSERT	회원 추가 처리
	COMMIT
INSERT		회원 추가 처리(실패!)

 

Update 내용을 손실하는 경우-1 (Dirty writing)

두 트랜잭션이 동시에 하나의 데이터를 업데이트하는 것입니다. 한 트랜잭션이 업데이트되고 커밋된 후 다른 트랜잭션이 롤백되어 커밋된 업데이트가 손실될 수 있습니다. 이렇게 update시에 데이터 손실이 나는 것을 Dirty writing이라는 용어를 사용하기도 합니다.

 

트랜잭션 1	트랜잭션 2	비고
Start transaction	Start transaction
Read x = 1	Read x = 1
Modify x = 2	Modify x = 3
	COMMIT
ROLLBACK		트랜잭션 2 입장에서는 3이 되어야하지만,X가 1로 되어버림.

 

 

Update 내용을 손실하는 경우-2 (Dirty writing)

두 트랜잭션이 동시에 하나의 데이터를 업데이트하는 것입니다. 첫 번째 업데이트 트랜잭션에서 제출한 데이터는 나중에 업데이트된 트랜잭션에서 제출한 데이터로 처리됩니다. 즉, 첫 번째 업데이트 트랜잭션에서 제출한 데이터는 손실됩니다.

 

트랜잭션 1	트랜잭션 2	비고
Start transaction	Start transaction
Read x = 1	Read x = 1
Update x = 2
Commit		X는 2로 변경됨.
	Update x = 3
	Commit	트랜잭션1이 X를 2로 변경했지만, 트랜잭션 2가 업데이트 하면서 X는 3이됨.

 

 

 

 

 

---

ACID

위의 문제들을 해결하기 위해서 데이터베이스에서 트랜잭션을 구현시 충족시켜야하는 요건이 있는데, 이것을 흔히 ACID라고 합니다.

 

ACID는 다음 4가지 용어의 줄임말입니다.

• Atomicity(원자성)
  원자성은 트랜잭션이 완전히 실행되거나 전혀 실행되지 않음을 의미합니다.
  
  
• Consistency(일관성)
  트랜잭션이 완료되면 데이터는 일관된 상태여야 합니다. 트랜잭션 실행 중 오류가 발생하면 트랜잭션이 실행되기 전의 상태로 롤백되어 데이터가 일관된 상태가 됩니다. 오류 후 트랜잭션이 롤백되지 않고 수정된 내용의 일부가 데이터베이스에 기록되면 데이터가 일치하지 않는 상태가 됩니다.
  
  
• Isolation(격리)
  여러 트랜잭션을 동시에 처리할 때 한 트랜잭션의 실행이 다른 트랜잭션의 간섭을 받지 않고 트랜잭션의 내부 작업이 다른 동시 트랜잭션과 격리됨
  
  
• Durability(영구)
  트랜잭션이 커밋된 후 데이터 변경 사항은 영구적입니다.

 

 

---

Isolation Level 4가지

위의 ACID중에서 Isolation에 대해서는 Ansi SQL-92 에서 4가지로 정의되어있습니다. 이 4가지 모드는 거의 모든 DB에서 그대로 사용됩니다.

 

Read uncommited

격리수준에 가장 낮다. 트랜잭션 처리중에 커밋되지 않은 데이터를 다른 트랜잭션이 읽을 수 있습니다. 보통 이 isolation level은 읽기 전용으로 사용할 때 사용됩니다.

트랜잭션 1	트랜잭션 2	비고
SET @@session.AUTOCOMMIT = 0; SET @@session.tx_isolation='read-uncommitted'; START TRANSACTION;
	SET @@session.AUTOCOMMIT = 0; SET @@session.tx_isolation='read-uncommitted'; START TRANSACTION;
SELECT * FROM users;
	SELECT * FROM users;	blocking 되지 않음.
	INSERT INTO users (username, created_at) VALUES ('Kasandra_Homenick', '2016-12-12 06:50:07.996');	insert 성공
SELECT * FROM users;		추가된 레코드 확인O

테스트 결과처럼 트랜잭션 1에서 트랜잭션 2에서 커밋하기 전의 데이터를 확인할 수 있다. 만약에 트랜잭션 2가 rollback된다면, Dirty read 문제가 발생한다.

 

 

Read commited

S lock을 걸지 않으며, 조회시마다 새로운 스냅샷을 매번 생성한다.

 

다른 트랜잭션이 commit한 변경내용에 대해서만 확인되기 때문에, Dirty Read 문제를 방지할 수 있습니다.

 

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking. 

 

MS SQL Server가 Default로 사용하는 Isolation Level.

 

트랜잭션 1	트랜잭션 2	비고
SET @@session.AUTOCOMMIT = 0; SET @@session.tx_isolation='read-committed'; START TRANSACTION;
	SET @@session.AUTOCOMMIT = 0; SET @@session.tx_isolation='read-committed'; START TRANSACTION;
SELECT * FROM users;
	SELECT * FROM users;	blocking 되지 않음.
	INSERT INTO users (username, created_at) VALUES ('Kasandra_Homenick', '2016-12-12 06:50:07.996');	insert 성공
SELECT * FROM users;		추가된 레코드 확인X
	commit;
SELECT * FROM users;		추가된 레코드 확인O
	START TRANSACTION; UPDATE users set created_at=NOW() WHERE id=2; commit;	update 성공
SELECT * FROM users;		변경된 레코드 확인O

트랜잭션 1은 트랜잭션 2의 INSERT 한 레코드를 commit되고 나서야 확인할 수 있었다. Non Repeataable read 문제가 발생한다.

 

 

Repeatable read

트랜잭션이 범위 내에서 조회한 데이터의 내용이 트랜잭션 레벨에서 항상 동일함(Consistent reads)을 보장한다.

Consistent read를 보장하기 위해서 InnoDB는 MVCC라는 스냅샷 처리 기능을 이용한다. 

 

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows

검색이나 index scan시에 phantom row 문제점을 막기 위해서 next-key lock을 사용합니다.

 

트랜잭션 1	트랜잭션 2	비고
SET @@session.AUTOCOMMIT = 0; SET @@session.tx_isolation='repeatable-read'; START TRANSACTION;
	SET @@session.AUTOCOMMIT = 0; SET @@session.tx_isolation='repeatable-read'; START TRANSACTION;
SELECT * FROM users;
	SELECT * FROM users;	blocking 되지 않음.
	INSERT INTO users (username, created_at) VALUES ('Kasandra_Homenick', '2016-12-12 06:50:07.996');	INSERT 성공
SELECT * FROM users;		추가된 레코드 확인X
	commit;
SELECT * FROM users;		추가된 레코드 확인X
	START TRANSACTION; DELETE FROM users WHERE id=1;	DELETE 성공
SELECT * FROM users;		삭제된 레코드 확인X
	commit;
SELECT * FROM users;		삭제된 레코드 확인X
	START TRANSACTION; UPDATE users set created_at=NOW() WHERE id=2;	UPATE 성공
SELECT * FROM users;		변경된 레코드 확인X

 

트랜잭션 1은 트랜잭션 2의 변경사항을 트랜잭션처리중에 확인되지 않는다. 하지만, 다른 트랜잭션에서 추가한 추가/삭제한 데이터는 확인할 수 없다. 따라서, 트랜잭션 2에서 추가된 데이터를 트랜잭션 1에서 추가하다가 테이블 제약속성(UNIQUE) 으로 인해 에러가 발생할 수 있다. 이것이 위에서 언급한 Phantom Read 문제점이다.

 

 

Serializable

REPEATABLE READ와 비슷하지만 자동 커밋이 비활성화된 경우 InnoDB는 모든 일반 SELECT 문을 SELECT ... LOCK IN SHARE MODE로 암시적으로 변환하기 때문에, SELECT 시에 S lock이 걸리게 됩니다.

 

따라서 트랜잭션이 작업이 완료할 때까지, 다른 트랜잭션은 해당 데이터에 대한 수정및 입력이 불가능합니다.

Repeatable read의 경우에는 INSERT/DELETE/UPDATE는 가능하였지만, Serializable 에서는 blocking되기 때문에, 

phantom read 문제점도 발생하지 않게 됩니다.

트랜잭션 1	트랜잭션 2	비고
SET @@session.AUTOCOMMIT = 0; SET @@session.tx_isolation='serializable'; START TRANSACTION;
	SET @@session.AUTOCOMMIT = 0; SET @@session.tx_isolation='serializable'; START TRANSACTION;
SELECT * FROM users;
	SELECT * FROM users;	blocking 되지 않음.
	INSERT INTO users (username, created_at) VALUES ('Kasandra_Homenick', '2016-12-12 06:50:07.996');	insert blocking됨. 타임아웃에러 발생.
	commit;
	START TRANSACTION;
INSERT INTO users (username, created_at) VALUES ('Gus93', '2016-06-24 19:36:30.978');
	SELECT * FROM users;	blocking 됨.
commit;
	SELECT * FROM users;	추가된 레코드 확인O

위의 테스트로 Repeatable-read 에서 발생할 수 있었던 Phantom Read 문제가 발생할 수 있는 여지조차 없다는 걸 알 수 있습니다.

 

 

 

다시한번 transaction Isolation level 별로 위에서 언급한 문제점을 아래 표에 정리하였습니다.

Isolation Level	Dirty read	Non-repeatable read	Phantom Read
Read Uncommited	O	O	O
Read Commited	X	O	O
Repeatable Read	X	X	O
Serializable	X	X	X

 

Serializable이 가장 안전한 isolation level인 것을 알 수 있습니다. 하지만, 위에서 본 것 처럼 lock을 거는 시간이 많이 때문에, 속도는 느리겠죠. Serializable 같은 경우는 성능상의 제약사항이 너무 커서 거의 사용되는 경우가 없는 것으로 알고 있습니다. 보통은 Repeatable-read를 이용하는 것으로 알고 있습니다.

 

하지만, 만약에 OLAP시스템과 같이 MySQL에 대량의 데이터를 쌓기만하고, 여러 트랜잭션이 같은 데이터를 조회하는 경우가 매우 적다면, Read Uncommited가 적절한 선택이 될 것입니다.

 

 

---

Consistent read

그리고 transaction isolation level을 이해할 때 꼭 짚고 넘어가야 되는 것이 Consistent read 개념입니다. 이 개념은 MySQL문서에 아래처럼 나와있다. 기본적으로 Read Committed와 Repeatable Read에만 적용됩니다.

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

The type of read varies for selects in clauses like INSERT INTO … SELECT, UPDATE … (SELECT), and CREATE TABLE … SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE:
By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.

Consistent read는 Read Committed와 Repeatable Read의 경우에 기본으로 동작하고, table에 락을 걸지 않기 때문에, 다른 트랜잭션이 마음대로 변경할 수 있습니다. 그런데 예외적으로 INSERT INTO ... SELECT, UPDATE ... SELECT, CREATE TABLE ... SELECT같은 SELECT절의 경우에는 SELECT절에 더 강한 lock을 걸고, READ COMMITTED의 동작과 같이 매번 새로운 스냅샷으로 조회한다고 합니다.