[MySQL] DML과 LOCK 동작 원리 및 충돌 해결 가이드 🔒

MySQL에서 DML(INSERT, UPDATE, DELETE)을 사용할 때 잠금(LOCK) 개념은 단순한 동시성 제어 그 이상이다. 실무에서는 자주 발생하는 데드락(Deadlock)이나 락 경합 문제를 예방하고 해결하는 것이 성능 유지와 데이터 무결성 보장에 핵심이다.

🔧 MySQL의 락 종류

1. 테이블 락 (Table Lock)

• MyISAM 등에서 주로 사용
• 테이블 전체를 잠금
• 동시에 하나의 세션만 DML 가능

LOCK TABLES users WRITE;
-- users 테이블에 쓰기 락이 걸림

2. 행 레벨 락(Row-Level Lock) - InnoDB

• 레코드 단위로 락 적용
• 동시에 여러 세션에서 병렬 DML 가능
• InnoDB 기본 동작 방식

UPDATE users SET name = 'Mike' WHERE id = 1;
-- id=1인 레코드만 잠금

반응형

⚠️ DML 수행 시 자동으로 발생하는 락

DML 명령어	기본 락 동작 (InnoDB 기준)
SELECT ... FOR UPDATE	공유/배타적 락
INSERT	삽입되는 레코드에 락
UPDATE	해당 행에 배타적(X) 락
DELETE	삭제되는 행에 배타적(X) 락

참고: InnoDB는 MVCC 기반으로 SELECT 자체는 락을 걸지 않는다.

🧱 락 충돌(Lock Conflict) 상황

예시 1: 동일 행 UPDATE 충돌

-- 세션 1
START TRANSACTION;
UPDATE users SET age = age + 1 WHERE id = 100;

-- 세션 2
START TRANSACTION;
UPDATE users SET name = 'Lee' WHERE id = 100;

→ 세션 2는 세션 1의 락이 해제될 때까지 대기하거나, 락 대기 타임아웃이 발생한다.

반응형

⛔ 데드락 발생과 해결

데드락(Deadlock)은 서로의 자원을 기다리는 교착 상태를 말한다.

예시 2: 교차 UPDATE 충돌

-- 세션 A
START TRANSACTION;
UPDATE users SET age = 30 WHERE id = 1;

-- 세션 B
START TRANSACTION;
UPDATE users SET age = 25 WHERE id = 2;

-- 세션 A
UPDATE users SET age = 30 WHERE id = 2; -- 세션 B가 잠금 중

-- 세션 B
UPDATE users SET age = 25 WHERE id = 1; -- 세션 A가 잠금 중

→ 이 경우 MySQL은 둘 중 하나의 트랜잭션을 강제 종료(Rollback) 처리한다.

해결 방법

• 한 방향으로만 쿼리 실행 순서를 통일 (id 순 정렬 등)
• 가능한 한 짧은 트랜잭션 단위로 처리
• 꼭 필요한 경우에만 명시적 락 사용

🔍 트랜잭션 격리 수준과 락 관계

격리 수준	설명	SELECT 락 사용 여부
READ UNCOMMITTED	변경 중인 데이터도 읽음	X
READ COMMITTED	커밋된 데이터만 읽음	X
REPEATABLE READ (기본값)	트랜잭션 내 같은 결과 보장	MVCC로 처리
SERIALIZABLE	완전한 직렬화 보장	SELECT 시에도 락 발생

명시적으로 락 걸기

SELECT * FROM users WHERE id = 1 FOR UPDATE;

→ 트랜잭션 내에서 해당 행을 다른 세션이 수정하지 못하게 보호

반응형

🧰 실무에서 사용하는 쿼리 패턴

1. 동시성 제어가 필요한 재고 처리

START TRANSACTION;
SELECT stock FROM products WHERE id = 10 FOR UPDATE;

-- 재고 확인 후 처리
UPDATE products SET stock = stock - 1 WHERE id = 10;
COMMIT;

2. Deadlock 회피를 위한 정렬된 업데이트

-- 항상 id 순서대로 처리
UPDATE users SET point = point + 10 WHERE id IN (101, 102) ORDER BY id;

🧩 LOCK 관련 설정 및 모니터링

• 락 대기 시간 확인: SHOW ENGINE INNODB STATUS;
• 락 대기 시간 조정:

SET innodb_lock_wait_timeout = 5;

• 현재 락 확인:

SELECT * FROM performance_schema.data_locks;

📘 공식문서 참고

MySQL InnoDB Locking 공식 가이드