SELECT ... FOR UPDATE 문 관련 Dead Lock 문제

MySQL 기준..

 

1. "SELECT ... FOR UPDATE"란?

하나 또는 특정 범위의 row에 대해 여러 세션에서 접근하여 발생할 수 있는 동시성 문제를 해결하기 위해 이용할 수 있다. "SELECT ... FOR UPDATE" 쿼리를 실행하면(auto commit 모드를 비활성화 시킨 후에 "SELECT ... FOR UPDATE" 쿼리 실행해야 한다), Intention Lock(table-level) 걸린 후, Exclusive Lock(row-level) 걸리게 된다.

 

2. "SELECT ... FOR UPDATE" 이슈

=> 존재하지 않는 데이터에 대해서는 모든 세션에서 "SELECT FOR UPDATE"/"UPDATE"/"DELETE" 문을 실행할 수 있는데,

데이터 수가 1개 이상인 테이블에서는 존재하지 않는 데이터에 대해 여러 세션에서 "SELECT FOR UPDATE" 문을 실행하더라도 id가 동일한 데이터에 동시에 접근하지 않도록만 하면  Dead Lock이 발생하지 않지만

만약, 데이터 수가 0개인 테이블에 대해 여러 세션에서 "SELECT FOR UPDATE"를 실행시키면, 모든 세션에서 INSERT문을 아예 실행할 수 없다.

 

3. 테스트

(1) 데이터 수가 0개인 테이블에서 각 A, B 세션에서 "SELECT ... FOR UPDATE" 쿼리로 조회한 데이터가 아무것도 없는 경우

=> 두 세션 중 하나라도 트랜잭션을 종료(commit/rollback) 시키기 전까지는 두 세션 모두 INSERT 쿼리 자체를 실행할 수 없음. 해당 데이터가 아예 없기 때문에 두 세션에서 UPDATE/DELETE 쿼리는 실행 가능.

 

(2) id가 2인 데이터만 있는 상황에서

A세션이  "SELECT ... FOR UPDATE" 쿼리로 id가 1인 데이터를 조회하고

B세션이 "SELECT ... FOR UPDATE" 쿼리로 id가 3인 데이터를 조회한 경우

=> A세션에서는 id가 1인 데이터에 대해서 INSERT/UPDATE/DELETE 실행 가능하다.

B세션에서는 id가 3인 데이터에 대해서 INSERT/UPDATE/DELETE 실행 가능하다.

id가 2이거나 각 세션에서 "SELECT ... FOR UPDATE" 쿼리로 취급하지 않은 데이터에 대해서는 상황에 따라 다른 결과가 나와서 추가적으로 테스트가 필요하다.

 

 

따라서,

여러 세션에서 이미 commit되어 입력된 데이터를 대상으로 "SELECT ... FOR UPDATE" 쿼리를 실행하도록 해야 Dead Lock 문제를 예방할 수 있다.

 

[테스트 코드]

<세션1>

*******

SET autocommit = 1; 
INSERT INTO sss VALUES(2); 

SET autocommit = 0; 
SELECT * FROM sss WHERE id = 1 FOR UPDATE; 
INSERT INTO sss VALUES(4); 
UPDATE sss SET id = 3 WHERE id = 2; 
DELETE FROM sss WHERE id = 2; 
COMMIT; 

SELECT * FROM sss WHERE id = 2;
*******

 

-- 세션2
*******

SET autocommit = 1;
INSERT INTO sss VALUES(2);

SET autocommit = 0;
SELECT * FROM sss WHERE id = 2 FOR UPDATE;
INSERT INTO sss VALUES(4);
UPDATE sss SET id = 3 WHERE id = 1;
DELETE FROM sss WHERE id = 1;
COMMIT;

SELECT * FROM sss WHERE id = 1;
*******