오늘은 postgreSQL을 사용하면서 데이터가 많지 않았음에도 deadlock이 발생했던 원인과 그와 관련된 내용들을 정리하기 위해 포스팅하려고 한다.
deadlock 발생 상황
예시)
sp_bus_stop_select_update라는 프로시저는tb_bus_stop테이블에 대한 SELECT, UPDATE 쿼리를 순차적으로 수행하는 프로시저이다.sp_bus_stop_select_update프로시저는 동시에 여러번 호출 될 수 있다.
🔹postgreSQL 로그
postgreSQL 로그 확인시, 다음과 같은 로그들이 확인되었다.
DETAIL: Process 44902 waits for ShareLock on transaction 1561836802; blocked by process 220584.
Process 220584 waits for ShareLock on transaction 1561836859; blocked by process 33137.
Process 33137 waits for ExclusiveLock on tuple (658,13) of relation 18202 of database 16401;로그를 분석해보면 아래와 같은 상황이 발생한걸로 시나리오가 그려진다.
44902 process:shareLock대기중220584 process:shareLock대기중33137 process:ExclusiveLock대기중ExclusiveLock을 보유한33137 process가shareLock을 대기하면서 deadLock 발생
shareLock과 ExclusiveLock을 발생시킨 쿼리를 공개할 순 없지만,
비슷하게.. 예시를 들어보자면 아래와 같은 모양이다.
update tb_bus_stop
set city_code = declare_city_code
where bus_stop_id = ANY(SELECT UNNEST(declare_city_code_target))🔹 문제
SELECT UNNEST(declare_city_code_target)실행시,tb_bus_stop에 해당bus_stop_id를 찾기 위해 인덱스 검색(Index Scan)이 수행될 수 있음.- PostgreSQL은
SELECT중에 데이터 무결성을 보장하기 위해shareLock을 걸 수 있음. - 하지만
UPDATE문이 같은tb_bus_stop테이블의 같은node_id를 Update하기 때문에RowExclusiveLock이 발생할 수 있다.
그런데, 위와 같은 논리라면 모든 update 쿼리에서 where절로 인덱스 스캔을 하는 경우 deadlock이 걸려야할것으로 보인다.
그런데?? 왜 deadlock이 발생하지 않는걸까?
그 이유는 서로 같은 트랜잭션이기 때문이다.
🔹 결론
-
PostgreSQL에서shareLock의 역할
shareLock은 읽기 작업에서 사용하는 잠금으로, 다른 트랜잭션이 해당 데이터를 수정하지 못하도록 한다.
하지만,UPDATE는 수정 작업이므로shareLock이 걸린 데이터에 대해UPDATE를 하려고 하면 잠금 충돌이 발생 할 수 있다. -
UPDATE가 가능한 경우
UPDATE가 실행될때PostgreSQL은 기존 데이터를 수정하지 않고 새로운 버전의 행을 만들기 때문에 기존 행의shareLock이 걸려있더라도 새로운 행에 대해서는 수정작업이 가능하다.
따라서,UPDATE쿼리가 기존행을 직접 수정하지 않기 때문에shareLock이 걸려있는 상태에서도 실행할 수 있다. -
UPDATE가 불가능한 경우
UPDATE가 불가능한 경우는 다른 트랜잭션이 같은 데이터를 수정하려고 할때shareLock이 걸려 있는 행을 다른 트랜잭션에서RowExclusiveLock을 획득하려고 충돌이 발생하는 경우이다.
🔹해결 방법
-
UPDATE ~ SELECT구문에서 범위를 제한하자.
where bus_stop_id ANY~로 여러 bus_stop_id로 범위를 확장하고 있는데 범위를 제한하면 deadlock이 발생하지 않는다. -
SELECT FOR UPDATE사용
SELECT ... FOR UPDATE구문을 사용하면 트랜잭션이 해당 행을 먼저 잠그고 이후에UPDATE하는 방식으로 순서 문제를 방지하도록 도와준다.
만약, 위 구문을 사용하지 않는다면UPDATE할때Lock순서가 보장되지 않아 데드락이 발생할 가능성이 높아진다. -
UPDATE순서 문제를 조심하자(순환대기,deadlock condition)
예를들어, 여러 프로시저가 실행되면서SELECT이후UPDATE를 실행하는데, 서로 다른 트랜잭션이 교차로 같은 행을 갱신하려고 하면 데드락이 발생할 수 있다.
예시)
- 트랜잭션A가
bus_stop_id=1를 잠그고bus_stop_id=2업데이트 시도 -->bus_stop_id=1shareLock,bus_stop_id=2Row ExclusiveLock이 걸린 상황 - 트랜잭션B가
bus_stop_id=2를 잠그고bus_stop_id=1업데이트 시도 -->bus_stop_id=2shareLock 시도,bus_stop_id=1Row ExclusiveLock 시도
- 트랜잭션 내부에서 같은 순서로 데이터를 처리하면 교차 업데이트 방지
예를 들어 항상 작은bus_stop_id부터UPDATE를 수행하도록 한다.
BEGIN;
SELECT bus_stop_id from b_bus_stop_id WHERE condition ORDER BY bus_stop_id FOR UPDATE;
UPDATE tb_bus_stop SET column = new_value WHERE condition;
COMMIT;