쿼리 동시성 검증 (Mysql)

아래 쿼리가 원자적으로 동작하는지 검증해보자. InnoDB 엔진을 사용하는 MySQL이라고 가정한다.

UPDATE tmp SET cnt = cnt+1 WHERE id = 1;

쿼리가 원자적으로 동작한다는 것은 쿼리가 쪼개지지 않는지를 검증하는 것이다. 즉 위의 쿼리에서는 cnt가 1 증가하거나 전혀 증가하지 않는지를 검증한다.

 

1) 테스트 준비

아래 쿼리를 실행해 간단한 테이블과 데이터를 준비한다.

CREATE TABLE tmp (
  id INT PRIMARY KEY,
  cnt INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO tmp VALUES (1, 0), (2, 0);

 

2) 이론적 검증

먼저 InnoDB에서는 업데이트 되는 행에 exclusive lock(배타적 잠금)을 건다. 이를 통해 동시에 행을 업데이트 하는 것을 막는다. 이 락때문에 다른 트랜잭션이 대기하거나 데드락이 걸릴 수 있다. 이 내용은 3번에서 살펴보도록 하자.

잠금 종류

Exclusive Lock(X-Lock) : 나를 제외하고 쓰기/읽기 둘다 불가
Shared Lock(S-Lock) : 나를 제외하고 쓰기 불가(읽기는 가능)

InnoDB는 버퍼 풀이라는 메모리 공간이 있다. 해당 공간에 값을 캐싱해와서 사용한다. 그렇기에 업데이트가 발생하면 해당 row에 락을 걸고(락 미획득시 대기), 버퍼 풀에서 값을 변화시킨다. 또한 Crash를 대비해 redo log에 기록한다.

-- redo log 예시
Row id=1: cnt 0 -> 1

그리고 커밋 시점에 디스크에 변경내용을 반영한다.

그렇기에 이론적으로 위 쿼리는 원자적이다. InnoDB는 행 단위로 락을 걸고 업데이트를 하기 때문에 동시에 행을 수정하는 것을 못하게 막기 때문이다.

3) DB 엔진 레벨 테스트

이제 아래 3가지 상황에 대해 해당 쿼리가 기대한대로 동작하는지 확인해보도록 하자.

1. 단일 쿼리 실행 → Update문 반영
2. 두 세션에서 같은 행 업데이트 시도 → row-level lock이 걸려 늦게 수행한 쿼리 대기
3. 데드락 상황 → 데드락 감지 및 세션 킬&롤백 후 쿼리 수행

먼저 1번 상황에 대해 아래 쿼리를 실행한다.

UPDATE tmp SET cnt = cnt + 2 WHERE id = 1;

cnt가 2 증가했다면 기대한대로 동작한 것이다.

이제 2번 상황에 대해서는 아래와 같이 쿼리를 실행한다. 세션 1이 커밋될 때 까지 세션 2가 대기하면 된다.

-- 세션 1에서 수행
START TRANSACTION;
UPDATE tmp SET cnt = cnt + 2 WHERE id = 1;

-- 세션 2에서 수행
START TRANSACTION;
UPDATE tmp SET cnt = cnt + 1 WHERE id = 1; -- 대기

이때 show processlist; 를 통해 아래와 같이 업데이트가 실행되지 못하고 updating상태에 머물러 있는 것을 볼 수 있다. 

3번 상황에 대해서는 아래와 같이 쿼리를 실행한다. InnoDB는 데드락을 감지하면 내부 알고리즘에 따라 Kill할 세션을 선택하고(롤백 비용, 트랜잭션 ID 등 고려) 해당 세션의 트랜잭션을 롤백 및 락을 해제한다. 즉 데드락이 발생했을 때 이를 감지하고 세션을 Kill 및 롤백하여 데드락을 해소하고, 쿼리를 수행하면 성공이다.

-- 세션1
START TRANSACTION;
UPDATE tmp SET cnt = cnt + 2 WHERE id = 1;

-- 세션2
START TRANSACTION;
UPDATE tmp SET cnt = cnt + 1 WHERE id = 2;
UPDATE tmp SET cnt = cnt + 1 WHERE id = 1;

--세션1
-- ERROR 1213 (40001): Deadlock found when trying to get lock; 
-- try restarting transaction 
UPDATE tmp SET cnt = cnt + 2 WHERE id = 2; -- 데드락 발생

-- 세션 1
COMMIT;

-- 세션 2 
COMMIT;

이 상황을 그림과 표현하면 아래와 같다. 어떤 세션을 Kill할지는 내부 알고리즘에 의해 결정된다.

 

4) 동시성 테스트

이제 DB 레벨에서 동시성 테스트를 진행하도록 하자.

먼저 간단하게 mysqlslap을 이용해 쿼리를 동시에 실행해볼 수 있다.

mysqlslap --host=127.0.0.1 --port=3306 --user=root --password=<실제비밀번호> \\
--concurrency=100 --iterations=10 \\
--query="UPDATE tmp SET cnt = cnt + 1 WHERE id =1;" \\
--create-schema=backend_basic

1000이 실제로 증가했다면 성공이다. 

또한 SHOW ENGINE INNODB STATUS\G 명령어를 통해 데드락 여부나 Lock 대기 여부 등도 분석할 수 있다. 아래는 INNODB 내부 상태 로그를 발췌한 것이다.

=====================================
2025-10-28 14:27:30 281472164945664 INNODB MONITOR OUTPUT
=====================================
. . . 중략 . . . 
------------
TRANSACTIONS
------------
Trx id counter 11539
Purge done for trx's n:o < 11539 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 562947761801648, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 562947761800840, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 11536, ACTIVE 28 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 281472163827456, query id 115 192.168.65.1 root updating
/* ApplicationName=DataGrip 2022.3.3 */ UPDATE tmp SET cnt = cnt + 1 WHERE id = 1
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 4 n bits 72 index PRIMARY of table `backend_basic`.`tmp` trx id 11536 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002d0f; asc     - ;;
 2: len 7; hex 02000001522946; asc     R)F;;
 3: len 4; hex 800003ea; asc     ;;

------------------
---TRANSACTION 11535, ACTIVE 32 sec
2 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 281472164945664, query id 124 192.168.65.1 root starting
/* ApplicationName=DataGrip 2022.3.3 */ SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
. . . 이하 생략

여기서 트랜잭션 부분을 좀 더 보도록 하자.

먼저 트랜잭션 11536은 28초째 실행중이다. 또한 ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED: 부분을 보면 해당 트랜잭션이 락을 얻기 위해 5초째 대기중이라는 뜻이다.

---TRANSACTION 11536, ACTIVE 28 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 281472163827456, query id 115 192.168.65.1 root updating
/* ApplicationName=DataGrip 2022.3.3 */ UPDATE tmp SET cnt = cnt + 1 WHERE id = 1
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 4 n bits 72 index PRIMARY of table `backend_basic`.`tmp` trx id 11536 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002d0f; asc     - ;;
 2: len 7; hex 02000001522946; asc     R)F;;
 3: len 4; hex 800003ea; asc     ;;