MySQL INSERT INTO ON DUPLICATE KEY UPDATE 로 인한 LOCK 이슈

MySQL의 편리한 기능 중 INSERT INTO ~ ON DUPLICATE KEY UPDATE 구문이 있습니다.
unique key, primary key 로 unique 제약조건이 걸려 있는 상황에서 중복된 데이터가 들어오면 DUPLICATE KEY UPDATE 구문으로 update 를 수행하는 구문인데요.
ORACLE 이나 기타 DB에서는 REPLCAE 에 해당하는 구문입니다.

참 편리한 기능이지만 DML이 많이 발생하는 상황에서 동일한 ROW 에 UPDATE 를 시도하는 세션이 많이 쌓이는 경우
LOCK 과 함께 CPU가 급증하는 현상을 만날 수 있는 주의해야할 쿼리이기도 합니다.

장애 상황

문제 쿼리

CREATE TABLE `log_table` (
  `ACCESS_DATE` date NOT NULL ,
  `LOG_TYPE` ,
  `AD_NO` int(11) NOT NULL ,
  `CLICK_CNT` int(11) NOT NULL DEFAULT '0' ,
  `ADVER_NO` int(11) DEFAULT NULL,
  `VIEW_CNT` int(11) DEFAULT '0',
  `DEVICE` enum('I','A') NOT NULL DEFAULT 'I',
  PRIMARY KEY (`ACCESS_DATE`,`LOG_TYPE`,`AD_NO`,`DEVICE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 


INSERT INTO log_table ( ACCESS_DATE, LOG_TYPE, AD_NO, VIEW_CNT, DEVICE ) values ( NOW(), 'T', 1671, 1, 'A' ) ON DUPLICATE KEY UPDATE VIEW_CNT = VIEW_CNT + 1

innodb status 로그

------------------
---TRANSACTION 14610287265, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24699280, OS thread handle 140026551707392, query id 15835524797   update
INSERT INTO log_table (
                        ACCESS_DATE, LOG_TYPE, AD_NO, VIEW_CNT, DEVICE
                    ) values (
                        NOW(), 'CALENDAR', 123, 1, 'A'
                    )
                    ON DUPLICATE KEY UPDATE
                        VIEW_CNT = VIEW_CNT + 1
------------------
---TRANSACTION 14610287264, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24699201, OS thread handle 140026585519872, query id 15835524793   update
INSERT INTO log_table (
                        ACCESS_DATE, LOG_TYPE, AD_NO, VIEW_CNT, DEVICE
                    ) values (
                        NOW(), 'CALENDAR', 123, 1, 'A'
                    )
                    ON DUPLICATE KEY UPDATE
                        VIEW_CNT = VIEW_CNT + 1
------------------
---TRANSACTION 14610287241, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24698954, OS thread handle 140026719504128, query id 15835524475  update
INSERT INTO log_table (
                        ACCESS_DATE, LOG_TYPE, AD_NO, VIEW_CNT, DEVICE
                    ) values (
                        NOW(), 'CALENDAR', 123, 1, 'A'
                    )
                    ON DUPLICATE KEY UPDATE
                        VIEW_CNT = VIEW_CNT + 1

장애 원인

위 테이블에서 visit_date 컬럼은 datetime type 이 아니라 date type고 결국 now() 는 ‘2020-11-01’ 로 데이터가 들어가게 됩니다.
그래서 결국 같은 ROW 에 update 가 많이 발생되게 되어 lock 경합이 심해지고 deadlock 이 발생하기 좋은 환경이 됩니다.
MySQL 에서는 “UPDATE 테이블 SET counter = counter + 1 WHERE key=‘a’” 를 처리할 때 아래와 같이 동작합니다.(insert on duplicate key update 동일)

  • 먼저 counter 필드 값을 읽고 이 때 다른 트랜잭션에서 값을 변경하는 것을 막기 위해 해당 레코드에 Shared Lock을 획득
  • 실제 업데이트를 수행할 때 다시 Exclusive Lock을 획득
BEGIN;
SELECT @var := counter FROM log_tale WHERE key=OO LOCK IN SHARE MODE;
UPDATE log_table SET counter = @var + 1 WHERE key=OO;
COMMIT;

이 때 여러 세션이 동일하게 들어오면 서로 같은 row 에 대해 SHARED LOCK을 획득한 후 서로 Exclusive Lock 을 획득하려 하기 때문에 deadlock 이 발생하게 됩니다.

해결방안

위에서 본 UPDATE 수행 과정이 해당 레코드에 대해 LOCK IN SHARE MODE 가 아니라 FOR UPDATE 로 적용되면 간단하게 해결되지만.. MySQL에서 수정되지 않는 이상 힘들것 같습니다.

BEGIN;
SELECT @var := counter FROM log_tale WHERE key='a' FOR UPDATE;
UPDATE log_table SET counter = @var + 1 WHERE key='a';
COMMIT;

따라서 아래 두가지 방법 중 하나로 우회하는 방법이 있겠습니다.

  • 트랜잭션 시작 전 SET SESSION transaction isolation level READ COMMITTED; 선언
  • temporary table 생성 후 temp 테이블에 insert 후 리얼 테이블의 PK 컬럼으로 group by 하여 count 를 sum 한 뒤 리얼 테이블에 update 치는 방식
  • EH cache 같은 간단한 local cache를 사용하여 count 값을 캐싱해 놓다가 DB 와 동기화하는 방식