PostgreSQL, MySQL 에서의 Lost update 대처 방안

Lost Update란?

동시성 제어 문제 중 하나로, 두 개 이상의 트랜잭션이 동일한 데이터를 동시에 읽고 업데이트할 때 발생합니다.

이 문제는 한 트랜잭션이 다른 트랜잭션이 한 업데이트를 덮어써서 그 업데이트가 '사라지는' 상황을 초래합니다. 이는 데이터 일관성에 심각한 영향을 미칠 수 있습니다.

 

관련 포스팅 - 트랜잭션 격리 수준 'read committed' 에서의 lost update 해결 (tistory.com)

 

앞선 포스팅에서의 Lost Update 대처 방안은 개발자가 직접 FOR UPDATE 등의 구문 없이 제어할 수 있으나, 특수한 경우에서만 사용 가능하고 역설적으로 개발자가 직접 트랜잭션 락을 관리하지 않기 때문에 예상치 못한 문제가 발생할 수 있습니다.

 

그래서 주로 격리 수준 Repeatable read를 사용해 Lost Update 문제를 해결합니다.

 

문제 발생 상황

Lost Update 문제는 주로 트랜잭션 격리 수준이 낮거나 동시 접근 제어가 제대로 이루어지지 않을 때 발생합니다. 이 문제는 데이터베이스의 일관성과 무결성을 해칠 수 있어, 이를 방지하기 위한 적절한 대책이 필요합니다.

 

해결 방안

PostgreSQL에서는 격리 수준을 Repeatable Read로 설정함으로써 Lost Update 문제를 효과적으로 방지할 수 있습니다. 

 

Repeatable Read 격리 수준에서는 트랜잭션이 시작된 이후 다른 트랜잭션이 동일한 데이터를 수정하려고 할 때, 먼저 트랜잭션이 완료될 때까지 대기하거나, 충돌이 발생할 경우 롤백이 수행됩니다. 

 

이를 통해 데이터의 일관성을 유지할 수 있습니다.

반면, MySQL에서는 Repeatable Read 격리 수준을 사용하더라도 Lost Update가 발생할 수 있습니다. 

 

MySQL에서 이 문제를 해결하기 위해서는 명시적인 잠금(SELECT FOR UPDATE)을 사용하는 것이 일반적입니다. 

 

이 방법을 통해 특정 레코드를 잠금으로써 다른 트랜잭션이 해당 레코드를 수정하지 못하도록 하여 데이터의 일관성을 유지할 수 있습니다.

 

예시 시나리오

PostgreSQL과 MySQL에서의 동작 차이를 통해 Lost Update 문제와 그 해결 방법을 구체적으로 이해할 수 있습니다.

 

PostgreSQL의 경우

1. 트랜잭션 A:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
SELECT * FROM accounts WHERE id = 1; 
-- 잔액 읽기 
UPDATE accounts SET balance = balance + 100 WHERE id = 1; 
-- 잔액 업데이트 
COMMIT;

 

 

2. 트랜잭션 B:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
SELECT * FROM accounts WHERE id = 1; 
-- 잔액 읽기 -- 여기서 트랜잭션 A가 업데이트하고 커밋함 
UPDATE accounts SET balance = balance + 200 WHERE id = 1; 
-- 잔액 업데이트 시도 
-- ERROR: could not serialize access due to concurrent update 오류 발생

 

해결 방법

이러한 오류를 해결하기 위해서는 트랜잭션 B를 롤백하고 다시 시도해야 합니다. 이는 애플리케이션 레벨에서 오류를 처리하고, 필요한 경우 트랜잭션을 재시도하는 로직을 구현하여 가능합니다.

 

MySQL의 경우

MySQL에서는 Repeatable Read 격리 수준이 Lost Update를 완전히 방지하지 못합니다. 

 

이 경우 명시적인 잠금(SELECT FOR UPDATE)을 통해 트랜잭션 간의 충돌을 방지할 수 있습니다. 

 

이를 통해 트랜잭션이 완료되기 전까지 다른 트랜잭션이 동일한 데이터를 수정하지 못하도록 하여 데이터 일관성을 유지합니다.

 

1. 트랜잭션 A:

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 잔액을 읽음 (현재 잔액: 100)
-- 트랜잭션 A는 잔액을 100에서 150으로 업데이트하려고 함

 

2. 트랜잭션 B:

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 잔액을 읽음 (현재 잔액: 100)
-- 트랜잭션 B는 잔액을 100에서 200으로 업데이트하려고 함

 

3. 트랜잭션 A:

UPDATE accounts SET balance = 150 WHERE id = 1;
COMMIT;  -- 잔액을 150으로 커밋

4. 트랜잭션 B:

UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;  -- 잔액을 200으로 커밋 (트랜잭션 A의 업데이트가 덮어써짐)

 

해결 방법

명시적 잠금 사용 (SELECT FOR UPDATE):

1. SELECT ... FOR UPDATE 문을 사용하여 명시적으로 레코드를 잠금으로써 다른 트랜잭션이 해당 레코드를 업데이트하지 못하도록 할 수 있습니다.
2. MySQL의 Locking read는 격리 수준과 상관없이 가장 최근에 commit된 데이터를 읽습니다.
   1. 아래의 SQL에서, 트랜잭션 A가 SELECT FOR UPDATE 구문을 통해 락을 획득하게 되면, 트랜잭션 B에서는 락을 획득할 때 까지 대기합니다.
   2. 트랜잭션 A가 끝난 이후, 트랜잭션 B에서는 SELECT FOR UPDATE 구문을 통해 계좌 잔액을 조회해옵니다.
   3. 이 때, 가장 최근에 commit된 데이터를 읽기 때문에 트랜잭션 B는 기존 잔액에 + 50 만큼 쌓인 금액을 조회해오게됩니다.

-- 트랜잭션 A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

-- 트랜잭션 B
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

 

 

 

결론

Lost Update 문제는 데이터베이스의 동시성 제어에서 중요한 이슈로, 이를 방지하기 위해 적절한 격리 수준 설정과 명시적인 잠금 사용이 필요합니다. 

 

PostgreSQL에서는 Repeatable Read 격리 수준을 통해 효과적으로 문제를 해결할 수 있으며, MySQL에서는 SELECT FOR UPDATE 구문을 통해 명시적인 잠금을 적용하는 것이 좋은 해결 방법입니다. 

 

이러한 방법들을 통해 데이터 일관성을 유지하고, 동시성 문제를 최소화할 수 있습니다.