MySQL 트랜잭션과 SELECT FOR UPDATE문

비동기로 수행하는 작업을 처리하는 기능을 개발하게 되었다.
테이블에서 조건에 맞는 데이터를 N개 씩 가져온 후 FOR UPDATE를 통해 상태 값을 처리중으로 변경하여 하나씩 처리하는 방식이다.

SELECT FOR UPDATE

SELECT FOR UPDATE문을 실행하면 LOCK을 획득하고, 해당 세션이 UPDATE 쿼리 후 COMMIT하기 전까지는 다른 세션들이 해당 ROW를 수정하지 못하도록 하는 기능이다.

쿼리는 다음과 같이 사용한다.

SELECT * FROM studeont WHERE id=1 FOR UPDATE;

SESSION#1>  SELECT * FROM studeont WHERE id=1 FOR UPDATE;
SESSION#2> SELECT * FROM studeont WHERE id=1 FOR UPDATE;

세션이 1이 면저 LOCK을 획득했기 때문에 session1이 UPDATE후 COMMIT을 실행하기 전까지 세션2는 LOCK WAIT TIME동안 대기한다.
session1이 완료하면 세션2는 이제서야 UPDATE 쿼리를 수행할 수 있게 된다.

유의사항

SELECT FOR UPDATE문을 사용하면 LOCK을 획득하기 때문에 다른 곳에서 UPDATE가 불가하다.
그렇기 때문에 다른 세션에서 접근이 가능하도록 최대한 짧게 트랜잭션을 물고 있는 것이 좋다.

비동기 프로세스를 처음 구현할 때 N개 씩 FOR UPDATE SELECT 후 N개 작업이 모두 끝나면 상태 값을 UPDATE했다. 이렇게 되면 작업이 완료될 때까지 LOCK이 걸려 버리기 때문에 SELECT한 후 바로 상태 값을 업데이트하여 다음 로직을 실행하도록 수정하였다.

여러 예제를 통해서 LOCK을 물고 있을 때 SELECT하면 어떤식으로 동작하는지 확인해보자.

mysql> start transaction;

mysql> select * from api_req where id = 1 for update;

+----+---------+-------+
| id | content | state |
+----+---------+-------+
|  1 | bye     |     0 |
+----+---------+-------+
1 row in set (0.00 sec)

SELECT FOR UPDATE 후 id = 1인 row를 조회하여 업데이트를 실행시켜보았다.
(LOCK_WAIT_TIMEOUT = 10)

let mysqlConn;

  mysqlConn = await pool.getConnection();

  await mysqlConn.beginTransaction();

  try {
    const query = `
      SELECT * FROM api_req WHERE id = 1;
    `;
   
    const [row] = await mysqlConn.query(query);

    console.log("end select query");
    
    const updateQuery = `
      UPDATE api_req SET content = 'bye bye' WHERE id = 1;
    `;

    await mysqlConn.query(updateQuery);

    console.log("end update query");

    await mysqlConn.commit();
  } catch (e) {
    console.log(e);
    console.log("transaction failed - rollback");
    await mysqlConn.rollback();
  } finally {
    mysqlConn.release();
  }

LOCK을 잡고 있는 세션이 COMMIT을 하지 않았기 때문에LOCK_WAIT_TIMEOUT 동안 기다리다가
에러를 발생시키고, rollback 시킨 것을 확인할 수 있다.

LOCK_WAIT_TIMEOUT 전에 COMMIT을 한다면?

mysql> select * from api_req where id = 1 for update;
+----+---------+-------+
| id | content | state |
+----+---------+-------+
|  1 | bye     |     0 |
+----+---------+-------+
1 row in set (0.00 sec)

mysql> update api_req set content = 'bye' where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

LOCK을 잡고 있는 세션이 타임아웃 전에 COMMIT했기 때문에 다른 세션은 기다렸다가 UPDATE 쿼리를 성공적으로 실행한다.
마지막으로 UPDATE문을 실행한 값으로 content 컬럼 값이 변경된 것을 확인할 수 있다.

mysql> select * from api_req where id = 1;
+----+---------+-------+
| id | content | state |
+----+---------+-------+
|  1 | bye bye |     0 |
+----+---------+-------+
1 row in set (0.00 sec)

그렇다면 SELECT FOR UPDATE를 실행하고, 다른 세션이 SELECT FOR UPDATE문으로 실행한다면?

SELECT문 조차도 실행되지 못하고 타임아웃이 발생한 것을 확인할 수 있다.

SELECT FOR UPDATE를 실행하면 LOCK을 걸어야 하는데 이미 다른 세션으로 인해 LOCK이 걸려있어서 SELECT문도 실행되지 못한 것이다.