트랜잭션 정리

트랜잭션이란 ?

데이터베이스의 상태를 변화시키기 위한 작업의 단위

트랜잭션의 성질(특성) ACID+S

1. 원자성 (Atomicity)

- 트랜잭션은 분리할 수 없는 하나의 단위로 모두 완료되거나 모두 취소되어야 하며, 완전히 끝나지 않았을 경우에는 처음과 동일하여야 한다.

2. 일관성 (Consistency)

- 사용되는 모든 데이터는 한 방향으로만 전달되어야 한다.

3. 격리(고립) 성 (Isolation)

 - 접근하고 있는 데이터는 다른 트랜잭션으로부터 격리되어야 한다.

즉, 트랜잭션 진행 전과 완료 후에 상태를 볼 수 있지만 트랜잭션이 진행되는 중간 데이터는 볼 수 없다.

4. 영속(지속) 성 (Durability)

- 트랜잭션이 정상 종료되면 그 결과는 영구적으로 데이터베이스에 적용되어야 한다.

* 순차성(Sequentiality)

- 데이터를 다시 로드하고 트랜잭션을 재생하여 원래 트랜잭션이 수행된 후의 상태로 데이터를 되돌리는 것을 말한다.

트랜잭션의 연산

Commit : 트랜잭션이 성공적으로 종료되어 요청한 트랜잭션이 데이터베이스에 정상적으로 반영되었음을 알려주기위해 사용되는 연산

Rollback : 트랜잭션의 처리가 비정상적으로 종료되어 원자성이 깨져 트랜잭션을 처음부터 다시 시작 상태로 돌리거나 종료처리됨을 알려주기위해 사용되는 연산

 트랜잭션의 격리 수준 ( SHOW VARIABLES like 'tx_isolation' )

• READ UNCOMMITTED

- 현재 작업중인 세션이 아닌 다른 세션에서 SELECT 트랜잭션 시 COMMIT 전 상태 ( Shard lock 미 적용 ) 를 확인 

- 오라클에서는 해당 레벨을 지원하지 않음

- Binary Log가 자동으로 Row Based로 기록됨 (Statement설정 불가, Mixed 설정 시 자동 변환)

• READ-COMMITTED

- 현재 작업중인 세션이 아닌 다른 세션에서 SELECT 트랜잭션 시 COMMIT 된 내역을 읽을 수 있는 상태 ( Shard lock 적용 ) 로, 다른 트랜잭션이 COMMIT 을 수행하면 해당 데이터를 Read할 수 있음

- Binary Log가 자동으로 Row Based로 기록됨 (Statement설정 불가, Mixed 설정 시 자동 변환)

• REPEATABLE READ

- MySQL InnoDB 스토리지 엔진의 Default Isolation Level

- SELECT 시 현재의 데이터 Snapshot을 만들고, 다른 세션에서 SELECT 시 그 Snapshot으로부터 데이터를 조회

- 동일 트랜잭션 내에서 데이터 일관성을 보장하고 데이터를 다시 읽기 위해서는 트랜잭션을 다시 시작해야 함

• SERIALIZABLE

- 가장 높은 Isolation Level로 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸림

- 다른 트랜잭션에서는 해당 영역에 관한 데이터 변경뿐만 아니라 입력도 불가

트랜잭션의 격리 수준별 문제점

• READ UNCOMMITTED

- Dirty read, Non-repeatable read, Phantom read

• READ-COMMITTED

- Non-repeatable read, Phantom read

• REPEATABLE READ

- Phantom read

1. Dirty Read

- SESSION1 에서 test.T_TEST 테이블의 '테스트' 값을 'TEST' 로 업데이트 할 때, SESSION2 에서 test.T_TEST 테이블 값을 조회하면 'TEST' 값을 반환한다. 하지만 이 때 SESSION1 이 COMMIT 을 하지 않으면 SESSION2 의 반환값은 잘못된 값이 된다.

2. Non-repeatable read

- SESSION1 에서 test.T_TEST 테이블을 조회하여 '테스트' 값을 반환 받았을때, SESSION2 에서 test.T_TEST 테이블의 '테스트' 값을 'TEST' 로 업데이트 하거나 삭제를 하고난 뒤 SESSION1 에서 test.T_TEST 테이블을 다시 조회하면 변경된 값 또는 삭제된 데이터를 반환한다.

3. Phantom read

- Non-repeatable read 와 같은 상태에서 SESSION2 이 ROLLBACK 을 하고 SESSION1 에서 테이블을 조회하면 데이터는 잘못된 값을 반환한다. 

- REPEATABLE-READ 동시성 장애발생 테스트 -

Session 1

mysql> insert into test.T_TEST_HY select * from test.T_TEST where regdate >= '20170401' and regdate < '20170501';

## 기준(T_TEST) 테이블의 데이터를 타겟(T_TEST_HY) 테이블로 INSERT INTO SELECT 삽입

Session 2

mysql> update test.T_TEST set reg_time = '000037' where id = 24504559 and regdate = '20170401';

## 기준(T_TEST) 테이블의 데이터를 업데이트

Session 3

mysql> show full processlist\G

*************************** 1. row ***************************

     Id: 1104426

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 2

  State: Updating        ## Session 2 의 쿼리가 완료되지 않고 대기상태가 된다

   Info: update test.T_TEST set reg_time = '000010' where id = 24657811

*************************** 2. row ***************************

     Id: 1104427

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 3

  State: Sending data

   Info: insert into test.T_TEST_HY select * from test.T_TEST where regdate >= '20170501' and regdate < '20170601'

** Session 2 의 쿼리는 Session 1 의 쿼리가 완료 된 후 적용되거나 Updating 시간이 길어지면 "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction : 잠금 대기시간을 초과하였습니다. 트랜잭션 재 시작을 시도하세요" 라는 에러가 발생하게 된다.

해결 방안

위 같은 상황에서는 Session 1 에서 트랜잭션 격리 수준을 일시적으로 변경하여 작업을 하고 난 뒤 원상복구를 시키면 다른 세션에서의 데이터 변경에도 아무런 문제가 생기지 않는다.

Session 1

mysql> set tx_isolation = 'READ-COMMITTED';

## Session 1 의 격리 수준을 

READ-COMMITTED ( Commit된 내역만 읽을 수 있는 상태 ) 으로 수정 후 작업을 진행한다.

변수 수정 후 위와같은 방식으로 진행을 하면 

Session 1

mysql> insert into test.T_TEST_HY select * from test.T_TEST where regdate >= '20170401' and regdate < '20170501';

Session 2

mysql> update test.T_TEST set reg_time = '000037' where id = 24504559 and regdate = '20170401';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

## 업데이트가 바로 완료 처리가 된다.

Session 3

mysql> show full processlist\G

*************************** 1. row ***************************

     Id: 1104427

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 3

  State: Sending data

   Info: insert into test.T_TEST_HY select * from test.T_TEST where regdate >= '20170501' and regdate < '20170601'

** 트랜잭션 격리 수준 ( isolation level ) 은 작업자의 상황에 맞게 또는 구조에 맞게 설정을 하는게 좋으며, 불가피한 삽입, 변경 또는 삭제를 진행해야 할 때 session 단위로 격리 수준을 조정하여 작업을 하는것이 가장 바람직하다.

* 참고 사이트

http://gywn.net/2012/05/mysql-transaction-isolation-level/