mysql_affected_rows가 "0"를 반환할때...

작년 즈음에 개발해 두고 상용으로 거의 2년만에 돌리고자 하는 시스템이 있다.

4대의 시스템에서 도는 것을 한대에서 돌도록 통합 개발했던 것이다.

이유 없이 시스템에서 DB 처리가 안되어 일정 기간동안 데이터가 날아갔다.

오늘 복구 작업을 진행하려고 이것저것 처리했는데 로그상에 이상한 것이 보이는 것이다.

insert 작업 처리를 무려 100만개 넘게 못하고 들고 있는 것이었다.

이유가 뭔가 하고 찾다가... 결국 찾기는 찾았다.

프로그램은 다음과 같이 처리하도록 되어 있다.

1. 데이터 수신 

2. 기록테이블에 데이터 삽입

3. 최종 상태 테이블에 데이터 업데이트

4. 업데이트 후 mysql_affected_rows()를 통해 적용 row수 체크

5. row가 0인 경우 최종 상태에 업데이트 할 row가 없는 것으로 인식 ---- 문제!!!

6. 업데이트 할 row가 없으면 최종 상태에 새로운 row insert --- 문제!!!

위의 두가지 문제라는 것들은 예상하지 못했던 부분들이다.

둘 중 하나만 예상대로 되었다면 문제는 발생하지 않았을 것이다.

두가지 모두 예상 밖 혹은 문제였기에 최종적으로 문제를 발생 시켰다.

~~~~ #1 ~~~~~~~~~~

최종 상태 테이블에 ID당 하나씩만 존재했어야 한다.

하지만 해당 테이블에 key를 unique가 아닌 일반 key로 해뒀다.

이것이 가장 큰 문제이긴 했다.

어쨋든 여러개의 ID가 들어갈 수 있는 상태가 되었다.

그래서 수만개 이상의 row가 들어가는 상황이 만들어 졌다.

~~~~~~~~~~~~~~~~~

~~~~ #2 ~~~~~~~~~~

mysql_affected_rows()가 "0"을 반환한다고 where 절에 해당하는 row가 0인 것은 아니다!

https://mariadb.com/kb/en/mariadb/mysql_affected_rows/ 의 내용을 보자.

"When using UPDATE, MariaDB will not update columns where the new value is the same as the old value."

단말기가 이상하여 아주 짧은 시간에 많은 데이터를 올리다 보니 여러 데이터가 완벽히 같은 경우가 존재했다.

실질적으로는 시간만 하더라도 달랐어야 하는데 말이다.

그래서 결국 최종 데이터가 있음에도 mysql_affected_rows()는 update 쿼리 실행후 실제로 업데이트한 row가 존재하지 않았다.

~~~~~~~~~~~~~~~~~

~~~~ #3 ~~~~~~~~~~

저런 문제를 드러나게 한 것은 

우리 단말이 잘못된 설정과 문제로 인해 매우 짧은 시간에 같은 데이터를 반복적으로 보냈기 때문이다.

~~~~~~~~~~~~~~~~~

저런 문제들이 중복되어...

동일 ID에 대한 수만개 이상의 row가 최종 테이블에 존재했으며...

해당 ID의 단말은 1초에도 10개씩 update를 시도 했다.

그러다 보니 매 업데이트 때마다 수만개 이상의 row를 업데이트 했다.

거기에 마지막 업데이트는 직전의 업데이트와 동일했고 수많은 row가 꾸준히 추가되었다.

결국 계속 update 대상 row가 증가한 것이다.

...........................

문제의 해결은 unique key를 달아 놓는 것으로 끝냈다.

소스상에서 update 후 mysql_affected_rows()가 0인 경우 insert를 시도한다.

그 경우 unique key에 의해 insert가 실패한다.

소스상에서 저 부분을 가드 하려면 두가지 방법이 있다.

1. 직전의 데이터를 가지고 비교하여 같으면 업데이트 쿼리와 삽입 쿼리 모두를 작동 시키지 않는 것이다.

2. mysql_affected_rows()가 아닌 매번 select를 하여 row가 존재하면 update를 진행하는 것이다.

unique key가 존재하면 두가지 모두 꼭 필요하지는 않아 작업을 진행하지 않았다.

...

참고: https://mariadb.com/kb/en/mariadb/mysql_affected_rows/