MySQL Deadlock 과 Lock 에 대한 고찰

이광훈·2024년 9월 30일
Deadlocklockmysql
@Query(value = "INSERT INTO coupon (code) " +
            "SELECT :code " +
            "FROM DUAL " +
            "WHERE NOT EXISTS (" +
            "    SELECT 1 FROM coupon WHERE code = :code" +
            ") " +
            "AND (SELECT COUNT(*) FROM coupon) < 100",
            nativeQuery = true)
    void saveByConditions(String code);

다음과 같은 쿼리를 작성하고 부하테스트를 진행하는 도중 아래의 에러가 발생했다.이때 한번에 300개의 요청을 보내는 Jmeter 구성으로 테스트를 하고 있었다.

  • lock 을 얻는 도중에 deadlock 이 발생하여 해당 트랜잭션이 롤백되었다. 어디가 문제인지 파악하기 위해 실험을 해보았다.

먼저 요청을 딱 하나만 보내는 경우에는 잘 돌아갔다. 하지만 Jmeter 를 이용해 요청을 동시에 2개를 보내는 경우, 바로 위와같은 에러가 다시 나타났다. 그러면 lock 이 쿼리 내에서 발생한것이 아니라 여러개의 쿼리가 DB 로 날아오면서 발생한 것이라 추측할 수 있었다.

그래서 해당 쿼리를 서버단이 아닌 직접 데이터베이스 레벨에서 TX 를 시작하고 분석을 해보았다. 아래는 실험을 해보면서 사용한 쿼리들이다.


start trasaction; // 트랜잭션 시작

INSERT INTO coupon (code)
SELECT '1acd'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM coupon WHERE code = '1acd'
)
AND (SELECT COUNT(*) FROM coupon) < 100;

// 현재 데이터이스에 걸려있는 Lock 의 종류를 확인할 수 있다.
SELECT * FROM performance_schema.data_locks;

// 이 쿼리를 통해 현재 데드락 상태에 대한 자세한 정보를 얻을 수 있다.
SHOW ENGINE INNODB STATUS;

트랜잭션을 시작하고 위의 INSERT .. 쿼리를 날린 이후 lock 종류를 확인하는 쿼리를 사용하여 해당 쿼리가 어떤 lock 을 발생시키는지 확인해보았다. 아래는 이 쿼리가 발생시키는 lock 의 종류들이다.

Lock data 는 lock 의 종류마다 다르지만 s 락인 경우 pk 나 인덱스 값을 나타낸다. 현재 coupon 테이블에는 PK 가 1,4,6,13 인 데이터들이 존재한다.

  • 먼저 1,4,6,13 번 데이터들에 대해 공유 락 (S 락) 이 걸리는 것을 확인 할 수 있다.

  • Supremum pseudo-record 는 데이터가 아닌 인덱스에 대한 (현재 PK) 락으로 해당 인덱스에서 존재할 수 있는 최댓값에 대한 lock 이다. 이는 새로운 레코드가 삽입될 때, 위치를 찾기위해 존재한다. 여기에도 S 락이 걸려있다.

  • coupon 테이블에 X 락이 접근하는 것을 막기위한 IS 락,

  • INSERT 를 위한 X 락 등 또한 나타나고 있다. 

    	- S Lock : (공유 락) 읽기 락 이라고도 불리며 다른 TX 가 S lock 을 거는것은 허용하지만 X 락은 허용하지 않는다
	- X Lock : (배타 락) 쓰기 락 이라고도 불리며, 다른 TX 의 S , X 락 모두를 허용하지 않는다.
	- IS Lock : S 락을 걸기위한 의도를 나타내는 lock 으로, table 단위로 걸린다. 이 IS락은 row 단위로 S 락을 획득할 시 , 다른 TX가 테이블단위로 X 락을 획득하는 것을 막는 용도이다.

그리고 이후 아래 쿼리를 통해 어떻게 데드락 상황이 발생했는지를 알 수 있다.그리고 그 아래에는 해당 쿼리를 실행하면 나오는 설명이다.

SHOW ENGINE INNODB STATUS; 
2024-09-28 16:11:10 0x16fb07000
*** (1) TRANSACTION:
TRANSACTION 195989, ACTIVE 0 sec inserting
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 3179, OS thread handle 6197456896, query id 1364716 localhost 127.0.0.1 root executing
INSERT INTO coupon (code) SELECT 'bc39ecc3' FROM DUAL WHERE NOT EXISTS (    SELECT 1 FROM coupon WHERE code = 'bc39ecc3') AND (SELECT COUNT(*) FROM coupon) < 100

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 306 page no 4 n bits 72 index PRIMARY of table concurrency.coupon trx id 195989 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 306 page no 4 n bits 72 index PRIMARY of table concurrency.coupon trx id 195989 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION 195990, ACTIVE 0 sec inserting
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 3180, OS thread handle 6188544000, query id 1364717 localhost 127.0.0.1 root executing
INSERT INTO coupon (code) SELECT '3304ce7d' FROM DUAL WHERE NOT EXISTS (    SELECT 1 FROM coupon WHERE code = '3304ce7d') AND (SELECT COUNT(*) FROM coupon) < 100

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 306 page no 4 n bits 72 index PRIMARY of table concurrency.coupon trx id 195990 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 306 page no 4 n bits 72 index PRIMARY of table concurrency.coupon trx id 195990 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
  • 이 설명을 정리해보면 다음과 같다.

###Transaction 1###

Hold 하고 있는 lock

  • RECORD LOCK , 즉 데이터 INSERT 전에 다른 TX 가 index 를 변경하지 않도록 PK 에 대한 S Lock 을 들고 있다.

필요한 lock

  • RECORD LOCK, 즉 데이터를 INSERT 하기 위해 index 에 대한 X lock 을 획득하려고 한다.

###Transaction 2###

Hold 하고 있는 lock

  • RECORD LOCK , 즉 데이터 INSERT 전에 다른 TX 가 index 를 변경하지 않도록 PK 에 대한 S Lock 을 들고 있다.

필요한 lock

  • RECORD LOCK, 즉 데이터를 INSERT 하기 위해 index 에 대한 X lock 을 획득하려고 한다.

두 TX 모두 SELECT COUNT(*) 에 대한 결과로 테이블의 데이터들에 대한 S lock 을 들고있다. 하지만 Insert 를 하기 위해 이 테이블에 INSERT 작업을 위해 IX Lock 을 걸어야 하는데 두 TX 모두 S lock 을 들고 놓지 않고 있는 상태이기 때문에 어느 TX 도 IX lock 을 획득할 수 없는 상태인 것이고 이 때문에 Deadlock 이 발생했다

그러면 왜 (어느 부분 때문에) 발생한 것 일까?

INSERT INTO coupon (code)
SELECT '1acd'
FROM DUAL WHERE
(SELECT COUNT(*) FROM coupon) < 100;


INSERT INTO coupon (code)
SELECT '1acd'
FROM DUAL WHERE NOT EXISTS (
    SELECT 1 FROM coupon WHERE code = '1acd'
);

INSERT INTO coupon (code)
SELECT '1acd'
FROM DUAL;


SELECT '1acd'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM coupon WHERE code = '1acd'
)
AND (SELECT COUNT(*) FROM coupon) < 100;

  1. 첫번째 쿼리의 결과

  2. 두번째 쿼리의 결과

  3. 세번째 쿼리의 결과

  4. 네번째 쿼리의 결과

  • 위의 결과를 보면, FROM DUAL 뒤에 WHERE 조건을 붙이면서 테이블 내의 데이터 들 뿐만 아니라 supremum 레코드들에 대해서 S Lock 이 걸리는 것을 볼 수 있다. 그리고 네 번째 쿼리는 Insert 없이 그냥 select 만 진행하는 쿼리인데, 이 경우 아무런 lock 도 걸리지 않는것을 확인할 수 있다.

  • 결론적으로, DUAL 테이블에 WHERE 조건을 걸어 내부에 coupon 테이블에 대한 select 과정을 거친 후, 이 결과를 INSERT 하는 과정에서 , S lock 과 IX 락의 충돌로 인해 Deadlock 이 발생하였다.

  • 개인적인 추측으로는, WHERE 절에서 조건을 정확히 판별하려면 해당 테이블에 다른 TX 에서의 쓰기 작업이 없어야 하기 때문에, 그리고 이 이후 INSERT 작업까지 영향을 주는것을 막기 위해 S lock 을 획득하도록 수행을 하지 않나 싶다. 만약 S 락을 걸지 않은 상태에서 조건문을 판별하고 이후 작업이 다른 TX 로 넘어가 해당 row 들에 대한 수정 작업이 이뤄지는 경우, 이후의 현재 TX 에 대한 쓰기 작업에서 조건이 달라져 데이터의 무결성을 해칠 수 있기 때문이다.

어떻게 해결해야 할까

1) Application 단에서의 분리

  • 현재 조건문을 만족하면 INSERT 를 수행하겠다라는 쿼리를 작성하기 위해 INSERT 와 SELECT 를 함께 사용하였다. 이를 분리하여, 먼저 application 단에서 100개 미만 조건과 중복 없음 조건을 검사하고 이후에 INSERT 를 수행하면 해결이 가능하다.

2) select 쿼리 수행시 X Lock 을 획득하도록 만든다

  • 현재 deadlock 상황은 2개의 TX 에서 모두 S lock 을 획득하였는데, 두 TX 모두 X lock 을 대기하고 있기 때문에 발생한다. 그러면 처음부터 X lock 을 획득하게 하면 어떨까? 테이블의 레코드들에 대해서 TX 1 이 먼저 X lock 을 획득하면, 이후 TX 2 는 TX 1 이 X lock 을 놓을때 까지 lock 을 획득하지 못한다. 그러면 동시에 자원을 들고 있지 않기 때문에 Deadlock 문제를 피할 수 있을것이다.

  • X lock 을 획득하게 하는 방법은 select 를 이용하는 것이 아니라 select... for update 를 사용하면 된다. 최종적인 쿼리는 다음과 같아진다.

INSERT INTO coupon (code)
SELECT '1acd'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM coupon WHERE code = '1acd' FOR UPDATE
)
AND (SELECT COUNT(*) FROM coupon FOR UPDATE) < 100;

하지만 이 과정에서 1가지 문제가 생겼다. 다른 경우에는 괜찮은데 테이블에 데이터가 들어있지 않는 경우에만 계속 Deadlock 문제가 생겼다;; 이것때문에 정말 힘들었다. 그래서 이번에도 어느 상황에 데드락이 생겼는지 원인을 찾아보았다.

처음에는 이 데드락 로그를 보고 황당했다. 어떻게 동일한 리소스에 서로 다른 TX 에서 X 락을 걸 수 있는거지? 그래서 정말 많은 생각을 해봤다.

  • 동일한 페이지에 서로 다른 row 에 대해서 락을 걸었는데 여기에 IX 락이 걸려서 데드락이 발생하나? 그런데 그러면 왜 테이블에 데이터가 없을때만 데드락이 발생하는거지,,,말이 안되는데,,, 그래서 이 아이디어는 폐기했다.
  • 혹시 내가 모르는 경우에 X 락이 동일한 리소스에 걸릴 수 있나? 라는 생각도 해봤는데 이건 아닌것 같았다.

그래서 오픈 카톡에도 물어보고, 비슷한 경우를 찾아보는 등 계속 서칭을 한 끝에 답을 찾을 수 있었다.

결론은 SELECT .... FOR UPDATE 쿼리를 수행할 때, 해당하는 혹은 만족하는 데이터가 없을 경우, GAP LOCK 이 걸린다, 그런데 이 GAP LOCK 의 경우, S 락 , X 락의 종류에 관계없이 동시에 걸릴 수 있다. 아래는 이에 관한 MySQL 의 공식 문서의 일부이다.

  • 문서를 보면, 첫번째 index record 이전에도 gap lock 이 발생하는것을 확인할 수 있다.

https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html

  • 그리고 S 락 , X 락 에 관련없이 같은 범위에 걸릴 수 있다.

그리고 이에 관해서 다시 실험을 해 보았다. 테이블에 데이터가 있는 경우와 없는 경우를 나눠서 쿼리를 수행했다. 쿼리의 수행을 아래 쿼리와 같이 해보았다.

START TRANSACTION;

SELECT count(*) FROM coupon for update;

INSERT INTO coupon (code) VALUES ("12345");
	
COMMIT;

1 ) 테이블에 데이터가 없는 경우

  • TX 1 에서 먼저 select ... for upate 를 수행한다. 이때, 테이블에 데이터가 없으므로 gap lock 을 획득한다.

  • 이후 TX 2 에서 select ... for upate 를 수행한다. 이때 gap lock 을 획득하는데 위에 작성했듯이 gap lock 은 동일한 범위에 대해 동시에 걸릴 수 있다.

  • 그리고 TX 1 에서 INSERT 를 수행하려한다. 이때, TX 1 은 IX 락을 획득해야 하는데, TX 2 에서 IX 락은 X 락 과는 다르다. 이에 따라서 TX 1 은 TX 2 가 gap lock 을 놓을때까지 대기한다.

  • 그런데 여기서 TX 2 가 INSERT 를 수행하면, Deadlock 에러가 뜨면서 한쪽 트랜잭션이 rollback 되고 다른 트랜잭션만 commit 된다.

2) 테이블에 데이터가 있는 경우

  • TX 1 에서 먼저 select... for update 를 수행한다. 이때는 위와 달리 데이터가 존재하므로 gap lock 이 아닌 X lock 이 걸린다.
  • 그리고 TX 2 에서 select .... for update 를 수행하면, 이때는 TX 1 이 X 락을 풀기까지 대기한다. 따라서 이 상황에서는 deadlock 이 걸리지 않는다.

이러한 점 때문에 데이터가 없는 맨 처음 insert 상황에서만 deadlock 이 발생하고 그 이후에는 발생하지 않았다. 힘들어따;;; 그래도 해결!

3) Isolation level 을 Read-committed 로 변경한다.

  • 위의 사진은 isolation-level 을 read-committed 로 변경하고 각각의 TX 에서 select count(*) ... for update 를 수행한 후 lock 의 상태를 나타낸 것 이다. 레코드 수준의 Lock 은 없고 둘 모두 INSERT 를 위한 IX Lock 만을 획득하려 하는 것을 볼 수 있다. 그리고 이후 INSERT 쿼리는 두 TX 에서 모두 문제없이 수행된다.

  • Read Committed 에서는 Gap lock 이 발생하지 않기 때문에 위의 문제에 대한 해결이 가능하다. 하지만 read-committed 에서의 문제인 phantom-read 문제에 대해 조심할 필요가 있다. 아래는 그 예시

두개의 select count(*) 사이에 다른 TX 에서 Insert 를 수행해서 동일 쿼리에 대해 결과가 달라진 모습

번외

그러면 Gap Lock 은 언제 걸리는걸까?

  • Gap lock 은 Repeatable-read 와 serializable isolation level 에서 동작하는, phantom read 를 방지하기 위한 lock 이다.

  • 이 Gap lock 은 레코드 간의 빈 공간 에 대해 설정되는 잠금이다.

  • 위의 예시와 같이 만약 id 10 과 20 사이를 select for update 할 경우, 중간 범위의 간격은 gap lock 이 걸려 이 사이에 데이터를 넣을 수 없다. 아래와 같은 상황에 gap lock 이 발생할 수 있다
SELECT ... FOR UPDATE

SELECT ... LOCK IN SHARE MODE

UPDATE  , DELETE 쿼리

<= >= < > 등의 범위

레코드가 없는 경우
profile
이광훈
허허,,,
이전 포스트

HikariCP Deadlock

0개의 댓글