답변

SQL은 테이블에 데이터를 업데이트하거나 삽입하는 다양한 방법을 제공합니다:

UPDATE — 조건에 맞는 기존 레코드를 변경합니다.
MERGE (또는 UPSERT) — 삽입 및 업데이트 로직을 하나의 명령으로 통합합니다: 행이 발견되면 업데이트하고, 그렇지 않으면 추가합니다.
INSERT ... ON DUPLICATE KEY UPDATE (MySQL) 또는 INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) — 새로운 행을 삽입하고, 키 충돌 시 기존 행을 업데이트합니다.

언제 무엇을 사용해야 하는지:

레코드가 없다는 것이 확실하다면 INSERT를 사용합니다.
기존 레코드를 업데이트하려면 조건과 함께 UPDATE를 사용합니다.
레코드 존재 여부를 모르는 경우, 효율적으로 MERGE 또는 UPSERT를 사용하는 것이 좋습니다 — 이는 복잡성을 줄이고 SQL 요청 수를 줄입니다.
UPSERT 작업은 많은 레코드에 대해 일반적으로 더 빠르며 경쟁 상태를 최소화합니다.

예시 (PostgreSQL):

INSERT INTO employees(id, name, salary)
VALUES (1, 'Ivan', 100000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

성능 및 무결성에 미치는 영향:

여러 개의 개별 UPDATE/INSERT는 경쟁 및 차단을 초래할 수 있습니다.
MERGE/UPSERT는 대량 마이그레이션 시 보다 효율적이며 원자성을 지원합니다.

함정 질문

유니크 필드에서 UPSERT를 시도하고 동일한 트랜잭션에서 동일한 행을 삽입 및 업데이트하면 어떤 일이 발생합니까?

올바른 답변: 트랜잭션이 동일한 행을 두 번 변경하려고 하기 때문에 잠금/충돌 오류가 발생할 수 있습니다. 작업이 완료되지 않거나 DBMS에 따라 deadlock 오류로 종료됩니다.

예시:

BEGIN;
INSERT INTO users(id, name) VALUES (1, 'Oleg') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
UPDATE users SET name = 'Petr' WHERE id = 1;
COMMIT;

잘못된 순서와 격리 수준에서 변경 충돌이 발생할 수 있습니다.

이야기 #1

데이터 마이그레이션 로직에서는 SELECT 후 INSERT 또는 UPDATE 조합을 사용했는데, 다른 트랜잭션이 데이터를 먼저 설정하면 "duplicate key" 오류가 발생했습니다. 이로 인해 야간 데이터 내보내기에서 잦은 실패가 발생했으며, UPSERT 로직으로 교체해야 했습니다.

이야기 #2

MySQL 프로젝트에서 ON DUPLICATE KEY UPDATE를 잘못 적용하여 복합 유니크 키를 올바르게 처리하지 않았습니다. 그 결과 일부 데이터가 업데이트되지 않고 중복이 발생했습니다. 문제는 실제 환경에서만 발견되었습니다.

이야기 #3

프로젝트에서 사용자 데이터를 동기화하기 위해 UPSERT 대신 UPDATE를 사용했습니다. 연결 오류가 발생할 경우 새로운 사용자에 대한 업데이트가 건너뛰어 일부 데이터가 손실되었습니다. 분석 후 MERGE/UPSERT를 완전히 구현했습니다.

SQL의 UPDATE, MERGE 및 INSERT ... ON DUPLICATE KEY UPDATE 명령의 차이점을 설명하십시오. 어떤 경우에 어떤 접근 방식이 더 바람직하며 이러한 도구가 성능 및 데이터 무결성에 미치는 영향은 무엇입니까?

답변

함정 질문