구글에도 없고 gpt도 모르는 PostgreSQL deadlock

``` ERROR: deadlock detected Detail: Process 3034 waits for ShareLock on speculative token 7 of transaction 2964312410; blocked by process 2994. Process 2994 waits for ShareLock on transaction 2964320791; blocked by process 3034. Hint: See server log for query details. ``` 회사 센트리에 postgresql deadlock이 감지되었다는 메시지가 위와 같이 떴다. 요금제 개편을 하면서 새롭게 사용하는 쿼리에서 발생하고 있었다. 옆 자리 동료가 deadlock 해결 업무를 맡게 되었는데, 나도 마침 요금제 개편 업무가 모두 마무리된 상태여서 같이 보게 되었다. 처음에는 별 생각 없이 접근했는데 보면 볼수록 쿼리에서 deadlock이 발생할 여지가 안 보였다. ```sql -- example INSERT INTO email_stats (id, user_id, date, count) VALUES ('2024-01-01_1', 1, '2024-01-01', 1) ON CONFLICT (id) DO UPDATE SET count = email_stats.count + 1; ``` 위와 같은 쿼리만 실행하는 테이블에서 deadlock이 발생했는데, 한 트랜잭션 당 하나의 row만 삽입하고 있어서 교착상태가 발생할 여지가 없어보였다. 혹시 해당 테이블에 대해 쿼리하는 다른 곳이 있나 구석구석 살펴보았는데 전혀 없었고, datadog에서 쿼리를 보아도 마찬가지였다. 로컬에서 같은 쿼리를 반복해서 수행했더니 꽤 높은 확률로 똑같은 deadlock이 발생해서, 데이터베이스의 일시적인 장애라고 보기도 힘들었다. 그렇게 물음표를 안고 계속 살펴보던 중 에러 메시지에서 `speculative token`이라는 낯선 단어가 눈에 들어왔다. 보통 PostgreSQL의 deadlock 에러 메시지는 거의 `transaction`에 대해 걸리는데, 저 발음하기도 힘들어보이는 단어는 뭘까 생각하며 찾아보았다. 일단 speculative는 한국어로 위험한, 투기적인, 추론적인 정도로 번역이 가능했는데, `speculative token`은 한국어로 번역된 결과가 없었다. 그리고 구글에서 영어로 검색은 물론이고 chatgpt, bard한테도 열심히 물어봤지만 `INSERT INTO ON CONFLICT` 문에서 사용한다는 것만 나오고 구체적인 설명은 없었다. 심지어 `speculative token`으로 인한 deadlock에 대해서는 단 하나의 검색결과도 없었다. 그래도 `INSERT INTO ON CONFLICT`에 관련된 에러임은 알 수 있어서 해당 부분을 좀 더 살펴보았더니, deadlock이 발생한 테이블의 인덱스가 일반적이지 않은 방법으로 걸려있었다는 것을 알 수 있었다. 테이블의 pk는 두 컬럼의 값을 string으로 만든 값이고, 또 두 컬럼에는 unique index가 걸려있었다. PostgreSQL은 pk도 똑같이 unique index로 관리하기 때문에 같은 역할을 하는 두 가지 unique index가 만들어진 것이다. 명확한 근거는 없었지만 혹시 두 컬럼에 대한 unique index가 deadlock의 원인이 아닐까 싶어 unique constraint를 제거했더니 그 다음부터는 deadlock이 발생하지 않았다. 이슈를 해결하긴했지만 여전히 찝찝함이 남아있어서 PostgreSQL 소스코드를 살펴봤더니 군데군데 `speculative token`에 대한 주석이 있는 것을 볼 수 있었고, 열심히 읽었더니 어느 정도 정리가 가능했다. PostgreSQL에서는 `INSERT INTO ON CONFLICT` 문을 실행할 때 lock을 잡지 않기 위해 일단 삽입을 시도하고 추후 트랜잭션을 커밋할 때 충돌 여부를 검사하여 삽입을 확정하거나 ON CONFLICT 구문을 실행시키는데, 이것을 `speculative insertion`이라고 부르고 있었다. 동작 방식과 영단어의 의미를 고려해보면 `speculative insertion`은 한글로 `투기적 삽입` 정도로 번역할 수 있을 것 같다. 그리고 `speculative token`은 xid와 유사하게 전역적으로 괸리되는 integer 값이었다. `speculative insertion`을 시도할 때마다 1씩 증가하고, 해당 값은 삽입을 시도한 트랜잭션의 snapshot에 저장한 후 ExclusiveLock을 건다. 그리고 다른 트랜잭션에서 같은 index에 대해 `speculative insertion`을 시도하면 해당 `speculative token`에 대해 ShareLock 획득을 시도해서 ExclusiveLock이 풀리기를 대기한다. 즉 `speculative token`은 upsert 동작을 할 때 전체 테이블이나 b-tree에 락을 걸지 않고 CONFLICT를 체크하는 인덱스 값에 걸고자 함이었던 것 같다. https://www.postgresql.org/message-id/18279-9793f12b34aa8366%40postgresql.org 이렇게 `speculative token`에 대해 알아보았는데도 왜 deadlock이 발생하는지는 알 수가 없어서 PostgreSQL에 버그 리포트를 올렸다. 몇 시간 후에 여러 개의 인덱스가 있으면 제대로 동작 안 할 수도 있다고 답변이 왔는데, 납득이 잘 안되어서 다시 남긴 답장에는 묵묵부답이었다.