TIL#164 SQL insert subquery

사내 사이트에서 버그가 발견되어 수정하게 되면서 insert subquery를 알아보게 되었다. 식비 정산 관련 사이트인데 각 유저의 데이터 중 가장 최근에 생성된 데이터를 가져와서 이전 정산 금액과 마지막 식비, 배달비를 가져와서 계산한 후 새로운 정산 데이터를 create하는 로직을 수정하였다. 문제는 요청 하나당 2명 이상의 유저 데이터가 있는데 요청이 거의 같은 시간에 2번 이상 요청되는 경우 요청들이 섞여서 정확한 결과가 도출되지 않는다는 거였다.

예를 들어 첫번째 요청에서 a, b, c 유저가 있고 두번째 요청에 b, d 유저가 있는 경우 순차적으로 계산되어야 b 유저의 데이터 기록이 정확해진다. 하지만 첫번째 요청의 b의 계산이 끝나지 않았는데 두번째 요청의 b의 데이터 기록이 시작되는 바람에 두번째 요청에서 b의 가장 최근 데이터를 가져올때 첫번째 요청이 끝난 b의 데이터를 가져와야 하는데 첫번째 요청 이전의 b의 마지막 데이터를 가져와서 계산이 꼬여버리는 현상이 발생하였다. 돈계산이기 때문에 빠른 속도보다는 정확한 게 우선이기 때문에 비동기보다는 동기 방식으로 진행되어야 했다.

제일 먼저 생각한 것은 트랜잭션이었다. 작성해놓은 로직에는

@transaction.atomic
def ...

요청 하나의 트랜잭션만 적용되어 있는 상태였다.
모든 요청이 동기방식으로 순차적으로 처리되기 위해서는 non_atomic_requests() 함수가 필요했다.

@transaction.non_atomic_requests
@transaction.atomin
def ...

이렇게 적용하게 되면 요청이 순차적으로 처리된다.
단, 서버가 한대일 경우에만 효과적인 방법이었다.
분산서버일 경우 여러개의 요청이 여러 서버로 흩어지게 되면 결국은 결과적으로 무용지물이 되는 방법이라... 추가적으로 다른 방법을 더 고민하게 되었다.

원래 로직은 요청에 들어오는 데이터를 for문을 사용해 유저 한명당 데이터를 조회하여 최근 데이터를 select 후 그 데이터를 조합하여 계산 후 create하는 방식으로 진행하였다.
결국 select 와 insert를 하는 쿼리를 유저 숫자 만큼 반복하는 로직이었다. 이 모든 과정을 쿼리 하나로 처리하게 되면 분산서버라도 들어온 순서대로 처리하게 된다. 복잡한 쿼리는 orm 보다는 raw query로 처리하는게 낫다고 알고 있었기 때문에 raw query로 작성하기로 결정하였다.

일단 여러명의 데이터를 한번에 insert 해야 하기 때문에 bulk insert 방법을 알아보았고 두가지 방법을 찾았다.

# 1번째 방법
insert into table (a, b, c) 
  select d, e, f
  from table
  where {condition}...
  
# 2번째 방법
insert into table (a, b, c)
values (1, 2, 3), (4, 5, 6), (7, 8, 9)...

1번째 방법은 같은 조건의 다중 행을 한번에 생성하는 방법이고 2번째 방법은 각기 다른 다중 행을 생성하는 방법이다. 나에게 필요한 방법은 각 조건이 다른 단일행 여러 개를 bulk insert 하는 방법이었기에 1번째와 2번째 방법을 섞으면 어떨까 했다. 여러가지 이것저것 시도를 해보았고....

결론적으로 2번째 방법을 값을 직접 입력하는 방법은 정상적으로 동작한다. 그리고 행 안의 원소 하나에 대해서는 sub query가 동작한다. 하지만 전체 값에 대해서 sub query 는 동작하지 않았다.

# 2번째 방법

# 동작 O
insert into table (a, b, c) values (1, (select b from t_1) as b, 3)..

# 동작 X
insert into table (a, b, c) values (select d, e, f from t_1)....

그래서 생각해 낸 방법이
단일행 여러개를 가져오는 결과를 하나의 다중행을 가진 결과로 만들어서 1번째 방법을 사용하는 것이었다. 스스로도 대견☺️

insert into table (a, b, c)
  ((select 
      user, 
      cost, 
      pre, 
      cost + pre 
    from table 
    where user=user 
    order by created_at desc 
    limit 1) 
    union all 
    select 
      1, 
      'user', 
      5000, 
      0 
    where 
      not exists
      (select * from table where user=user)
    )
  union
  ((select....))

🙏🏻 다중행이 필요한 bulk insert 의 sub query 부분에 각기 다른 조건으로 단일행이 여러개 나오는 결과들을 union 을 이용해 하나의 다중행 결과물로 만들어서 실행!