(데이터베이스) Lost update와 serializable

트랜잭션에는 4가지 격리수준이 있습니다.

 

• read uncomitted
• read comitted
• repeatable read
• serializable

 

그리고 각각의 트랜잭션은 다음과 같은 문제를 발생할 수 있습니다.

• dirty read
• non-repeatable read
• phantom read

(phantom read는 MVCC덕에 발생하지 않습니다.)

 

하지만 이와 별개로 serialiable을 제외한 트랜잭션 격리 레벨에서 발생할 수 있는 lost update 문제가 있습니다.

 

Lost update

Lost Update는 동시성 제어 문제 중 하나로, 두 개 이상의 트랜잭션이 동시에 같은 데이터를 수정하려고 할 때 발생할 수 있습니다. 이는 하나의 트랜잭션이 수행한 업데이트가 다른 트랜잭션에 의해 덮어쓰여지는 상황을 말합니다. 즉, 최종적으로 한 트랜잭션의 변경 내용이 손실됩니다.

 

 

예를 들어 봅시다.

격리 수준 repeatable read에서 두 개의 트랜잭션이 수행됩니다.

 

 

• account 테이블에 balance 컬럼이 있는 하나의 레코드가 존재한다고 가정합니다.
• 초기 balance 값은 100입니다.

 

• T1: balance 값을 100 증가시키려고 합니다.
• T2: balance 값을 50 감소시키려고 합니다.

 

1. 먼저 트랜잭션 T1이 balance 값을 읽습니다.

-- T1
BEGIN;
SELECT balance FROM account WHERE id = 1;  -- 결과: 100

 

 

2. 트랜잭션 T2가 balacne 값을 읽습니다.

-- T2
BEGIN;
SELECT balance FROM account WHERE id = 1;  -- 결과: 100

 

 

3. 트랜잭션 T1이 값을 증가시킵니다.

-- T1
UPDATE account SET balance = balance + 100 WHERE id = 1;
-- balance = 200

 

 

4. 트랜잭션 T1이 커밋됩니다.

-- T1
COMMIT;

 

5. 트랜잭션 T2가 값을 감소시킵니다.

-- T2
UPDATE account SET balance = balance - 50 WHERE id = 1;
-- balance = 50 (T1의 업데이트가 덮어써짐)

 

 

6. 트랜잭션 T2가 커밋됩니다.

-- T2
COMMIT;

 

 

이렇게 되었을 때, 최종 결과는 아래가 된다.

balance = 50

 

 

하지만 PSQL에서는 다릅니다.

 

PSQL에서의 Lost update

PSQL이든MySQL이든  모두 MVCC를 사용합니다.

(MySQL의 MVCC는 repeatable read부터이고, PSQL의 MVCC는 read comitted부터 적용됩니다.)

 

MVCC는 Multi Version Concurrency Control의 약자로 동시성 제어 메커니즘입니다.

스냅샷을 격리시키기 때문에 여러 개의 트랜잭션의 간섭을 최소화해줍니다.

 

그럼 MVCC를 가지고 위의 예시를 살펴봅시다.

 

 

1. 먼저 트랜잭션 T1이 balance 값을 읽습니다.

-- T1
BEGIN;
SELECT balance FROM account WHERE id = 1;  -- 결과: 100

 

 

2. 트랜잭션 T2가 balacne 값을 읽습니다.

-- T2
BEGIN;
SELECT balance FROM account WHERE id = 1;  -- 결과: 100

 

 

3. 트랜잭션 T1이 값을 증가시킵니다.

-- T1
UPDATE account SET balance = balance + 100 WHERE id = 1;
-- balance = 200

 

4. 트랜잭션 T1이 커밋됩니다.

-- T1
COMMIT;

 

그리고 5번에서 트랜잭션 T2가 UPDATE를 시도합니다.

 

하지만 UPDATE 쿼리가 동작하지 않고 충돌로 롤백되게 됩니다.

 

그 이유는 MVCC때문인데요,

 

update 쿼리는 내부적으로 실행하기 전에 내부적으로 select가 발생합니다.

select를 하면 balance는 T1에 의해서 이미 변경된 200이 됩니다.

 

이때 MVCC가 관여하는데, 가장 처음에 스냅샷에서 가져온 값과 조회한 값이 다르기 때문에

트랜잭션이 실패하게 됩니다.

 

 

해결

이를 해결하기 위해서는 3가지 방법이 있습니다.

 

1. select for udpate로 lock을 사용한다.
2. 롤백 되어 실패할 경우 재시도 과정을 넣는다.
3. 격리 수준을 serializable로 올린다.

 

lock을 걸면 잠금 경합이 발생할 수 있고, 이로 인해 데드락이 발생할 수 있습니다.

serializable로 격리수준을 올리면 동시성 문제가 발생할 가능성은 없어지지만, 성능 저하가 발생합니다.

 

 

MySQL에서는?

MySQL에서는 repeatable read이상에서는 데이터를 조회하면 갭락과 로우락을 걸고 데이터를 읽어옵니다.

 

그렇기 때문에 lost update가 발생하지 않습니다.