Read 시도 후 없으면 Insert , 있으면 Update 쿼리를 만들면 안 되는 이유 +Upsert 쿼리 알아보기

개요 목적

데이터 베이스를 사용할 때, Read 시도 후 없으면 Insert , 있으면 Update 쿼리를 만들면 안 되는 이유에 대해서 알아보자.

그리고 이 문제를 해결할 다양한 DB에서 직접 제공하는 Upsert 쿼리에 대해서 알아보자.

Read 시도 후 없으면 Insert , 있으면 Update 쿼리를 만들면 안 되는 이유

그 이유는 해당 글, DB Lock에 대한 이해와 MySQL Lock의 특징

MySQL 락의 특징에서 나온, Lock을 걸 때 행 단위 Record Lock을 걸기 때문이다.

• Record Lock이란, primary key 또는 unique index (multi-column unique index 포함)로 조회해서 하나의 인덱스 레코드(=row=행)에만 lock을 거는 것을 의미한다.

먼저 Read 후 있으면 Update하는 경우, 문제가 발생하지 않는다. 해당 레코드(행)에 락이 걸리고, Update를 진행하기 때문에, 다른 트랜잭션이 해당 레코드에 접근이 불가능하다. 해당 레코드에 대한 완전한 처리가 보장된다.

그러나, Read 후 없으면 Insert하는 경우 문제가 발생할 수 있다.

읽는 과정에서 해당 레코드가 없기 때문에, 락이 걸려있지 않은 상태이다. 그래서 다른 트랜잭션이 해당 조건에 마음대로 접근이 가능하다. 예를 들어, Insert를 하기 전에, 다른 트랜잭션이 다른 값을 먼저 넣어 값이 생겨버리는 문제가 발생한다(값이 없을 때 Insert하려고 했는데) 해당 레코드에 락이 걸려 있지 않기 때문에, 해당 쿼리에 대한 완전한 처리가 보장 받지 못한다.

해결 방법으로는

1. Mysql Lock Tables으로, 락을 레코드 행 단위가 아닌, 테이블 단위로 락을 거는 방법이다.
2. DB에서 제공하는 Upsert 쿼리를 사용하는 것이다.

DB가 제공하는 Upsert 쿼리

Upsert 쿼리란, 중복된 값이 있다면, Udate를 하고, 중복된 값이 없다면, Insert를 하는 쿼리이다. 자세히 말하면, Unique한 Key의 값이 중복된다면, Update를 하고, Unipue 컬럼의 값의 존재 하지 않으면, Insert를 한다.

MySql의 Upsert 쿼리

INSERT INTO upsert_test (id,name) values(1,'aa') ON DUPLICATE KEY UPDATE id=1, name='bb';

만약 중복된 값이 없으면, id =1, mame=’a’가 들어가고, id=1이라는 값이 이미 있으면, id=1에 name=’bb’값이 업데이트 된다.

또는 이렇게 작성해도 된다.

INSERT INTO upsert_test (id,name) values(1,'aa') ON DUPLICATE KEY UPDATE name='bb';

<쿼리 사용 예시>

MsSql의 Upsert 쿼리

MERGE 테이블명 AS A
USING (SELECT '설정값1', '설정값2') AS B (설정컬럼(NAME), 설정컬럼(EMAIL))
ON A.NAME = B.NAME     ---조건 지정하기.
WHEN MATCHED THEN
	UPDATE SET EMAIL = A.EMAIL + ';' + B.EMAIL     ---같은게 있다면 update
WHEN NOT MATCHED BY TARGET THEN
	INSERT (NAME, EMAIL) VALUES (B.NAME, B.EMAIL); ---없다면 insert

<예시>

MERGE #EMPLOYEES AS [Target]
USING (SELECT EmployeeID = 5, EmployeeName= 'Elon Musk') AS [Source] 
    ON [Target].EmployeeID = [Source].EmployeeID --- specifies the condition
WHEN MATCHED THEN
  UPDATE SET [Target].EmployeeName = [Source].EmployeeName ,[Target].Method='Update', [Target].UpdatedDateTime = GetDate() --UPDATE STATEMENT
WHEN NOT MATCHED THEN
  INSERT (EmployeeID, EmployeeName,Method,UpdatedDateTime) VALUES ([Source].EmployeeID,[Source].EmployeeName, 'Insert', GetDate()); --INSERT STATEMENT

Oracle의 Upsert 쿼리

MERGE INTO tablename USING dual ON ( val3 = in_val3 )
WHEN MATCHED THEN 
	UPDATE SET val1 = in_val1, val2 = in_val2
WHEN NOT MATCHED THEN 
	INSERT VALUES (in_val1, in_val2, in_val3)