본문 바로가기

MySQL

MySQL Transaction Isolation level: REPEATABLE_READ Mode에서의 Lock 이해

Transaction Isolation level에는 READ_UNCOMMITTED, READ_COMMITED, REPEATABLE_READ, SERIALIZABLE 네 가지 종류가 있다. 왼쪽에서 오른쪽으로 갈 수록 강력한 isolation 효과를 볼 수 있지만, 그만큼 동시성이 떨어지게 된다.

그림1: Isolation mode에 따른 Consistency와 Concurrency

 

MySQL에서, 현재의 isolation level은 아래와 같은 SQL로 확인할 수 있다 .

mysql> SELECT @@global.tx_isolation;  -Global Level
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT @@tx_isolation;  -Session Level
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

본 글에서는 MySQL InnoDB engine의 default isolation level인 REPEATABLE_READ 모드에서의 동작에 대해 알아보고자 한다. 

 

SELECT

REPEATABLE_READ mode에서는 SELECT 시 해당 operation을 수행한 시간을 기록하여, 현재 시점의 snapshot을 만들고, 그 이후에는 모든 read operation을 해당 snapshot을 기준으로 consistent read를 하여 조회하게 된다. 그 말은 즉, Transaction A 가 record X를 조회하고, 이후 Transaction B가 record X를 update시키고 commit한 후 Transaction A가 commit 전 동일 레코드를 다시 SELECT한 경우, 업데이트되기 전의 데이터를 받아볼 수 있다는 이야기이다. 이는 MySQL document에 아래와 같이 명시되어있다.

 

consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. 

 ...

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

 

 

확인을 위해 테스트 테이블을 만들고 실험을 해보았다. 

CREATE TABLE `item_order`
(
    `item_id`       bigint NOT NULL,
    `count`         bigint,
    `created_at`    timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at`    timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`item_id`)
);


CREATE TABLE `customer_order`
(
    `customer_id`   bigint NOT NULL,
    `count`         bigint,
    `created_at`    timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at`    timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`customer_id`)
);

 

주문 시스템에서, 특정 아이템의 주문 수량을 트래킹하기 위한 테이블이라고 가정하자. 이해를 위해 테이블은 최대한 간략화하였다. 

아래와 같이 item_order에는 두 개의 record, customer_order에는 하나의 record가 들어있는 상태이다. 

mysql> SELECT * FROM item_order;
+---------+-------+-------------------------+-------------------------+
| item_id | count | created_at              | updated_at              |
+---------+-------+-------------------------+-------------------------+
|       1 |     0 | 2021-01-28 23:03:30.863 | 2021-01-28 23:03:30.863 |
|       2 |     0 | 2021-01-28 23:03:43.834 | 2021-01-28 23:03:43.834 |
+---------+-------+-------------------------+-------------------------+
2 rows in set (0.00 sec)


mysql> SELECT * FROM customer_order;
+-------------+-------+-------------------------+-------------------------+
| customer_id | count | created_at              | updated_at              |
+-------------+-------+-------------------------+-------------------------+
|           1 |     0 | 2021-01-28 23:06:17.509 | 2021-01-28 23:06:17.509 |
+-------------+-------+-------------------------+-------------------------+
1 row in set (0.00 sec)

 

그림 1. REPEATABLE_READ mode에서의 두 개의 트랜잭션

이 때 두 개의 트랜잭션 (이후 각각 TxA, TxB라 통칭)에서 item_order에 아래의 순서대로 접근해보았다. 

좌측을 TxA, 우측을 TxB라고 했을 때, 제일 먼저 TxB에서 (1) start transaction을 수행하고, (2) SELECT를 한다. 이후에 TxA에서 (3) start transaction을 수행하고 (4) 해당 row를 업데이트하고, (5) SELECT로 조회해본다. TxA에서는 count값이 정상적으로 증가되어서 표시되는 것을 볼 수 있다. 이후에 TxB에서 (6) 다시 같은 row를 조회해보면, 업데이트 되기 이전의 count=0의 값이 표시된다. TxA에서 커밋이 되지 않아 예전 값이 보이는 것이 당연하지만, (7) TxA를 commit하고 나서 다시 (8) TxB에서 조회를 해보아도, 변경된 값은 반영이 되지 않는다. (9) TxB를 commit하고 나서, 해당 트랜잭션을 벗어나 다시 (10) 조회를 해보았을 때 비로소 변경된 값이 조회된다. 

이처럼, REPEATABLE READ mode에서는 transaction 안에서 select를 했을 때, 그 시점의 snapshot을 가지고 해당 transaction이 commit될 때까지 해당 snapshot을 사용한다. 

 

 

UPDATE

REPEATABLE_READ mode에서 트랜잭션 안에서 Update문을 실행할 시에는 row lock이 걸리게 된다. 즉, TxA가 특정 row에 대해 업데이트를 수행한 상태에서 같은 row에 대하여 TxB가 업데이트를 시도했을 때, TxB는 TxA가 commit되기 전까지 lock을 대기하는 상태가 된다.

그림2: 두 개의 tx에서 동일한 row에 대해 Update를 했을 때 한 쪽 Tx에서 lock을 대기하고 있는 모습

위의 예시에서 (1) TxA, (2) TxB에서 각각 start transaction을 하고, (3) TxA에서 특정 row를 업데이트 한뒤 commit하지 않은 상태에서 (4) TxB에서 동일한 row에 대한 업데이트를 시도하였다. 캡쳐에서 보이는 것처럼, TxB에서는 해당 UPDATE문이 수행되지 못하고 대기상태인 것을 볼 수 있다. 이 때, 아래와 같이 TxA에서 commit을 commit하면 대기하고 있던 (4)번 UPDATE가 비로소 수행된다. 

 

 

그림3: TxA에서 트랜잭션을 commit하고나서야 대기 중이던 TxB가 UPDATE를 수행한 모습

(5)에서 TxA가 transaction을 커밋하였고, 이 때 (3)에서 업데이트 된 item_id=2인 row에 대한 lock이 release된다. 대기 중이던 (4)번 트랜잭션은 이후에야 비로소 이 lock을 acquire하여 UPDATE를 수행한다. 각각 SELECT를 해보면, (6) TxA에서는 1이 증가된 현재 상태를 보여주게 되고, (7) TxB에서는 A에서 UPDATE를 한 상태 + TxB에서 UPDATE하여 아직 commit되지 않은 상태가 보여진다. 앞서 SELECT에서는 snapshot을 가지고 같은 transaction 안에서는 해당 snapshot을 사용한다고 했는데, 업데이트 하려는 row가 다른 트랜잭션에 의해 업데이트된 상태임에도 이전 snapshot을 사용하게 된다면, 정합성이 깨지게 된다. 이에 따라 UPDATE가 된 경우 해당 row에 대한 consistent read는 refresh되고, latest state에 해당 UPDATE를 반영한 결과가 보여지게 된다. 이는 MySQL 공식문서에 아래와 같이 명시되어있다.

 

 

consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

 

 

직역해보자면 REPEATABLE_READ mode에서 특정 transaction 안에서 row가 UPDATE가 된 경우, 다른 row들은 snapshot을 찍은 버전으로 조회를 하게 되고, update된 row는 transaction안에서 변경된 상태로 조회를 하게 되므로, 여러 session이 동일한 테이블을 한꺼번에 업데이트하는 경우 실제 table에 존재한 적이 없는 table의 snapshot을 가져오게 될 수도 있다는 것이다. 

 

 

또 한 가지 주의해야 할 점은, 한 transaction 안에서 여러 개의 UPDATE문을 수행하는 경우이다. 이것은 비단 REPEATABLE_READ뿐만 아니라 lock이 걸리는 모든 경우에 해당되는데, 아래의 예시를 보자.

 

그림4: UPDATE 중 lock wait timeout으로 인해 transaction이 abort되는 경우

 

TxA는 고객의 주문을 처리하는 중이고, TxB는 고객의 환불을 처리하는 중이다. TxA는 주문된 item의 누적값과 customer의 누적 주문 횟수를 증가시켜야 하고, TxB는 주문된 item의 누적값과 customer의 누적 주문 횟소를 감소시켜야 한다. 

그런데 위의 예시에서 TxA는 item -> customer 순으로 업데이트를 수행하고, TxB는 customer -> item순으로 업데이트를 수행한다. (3)에서 TxA는 item_order의 item_id=1인 row에 대한 lock을 잡고, (4)에서 TxB에서는 customer_order의 customer_id=1인 row에 대한 lock을 잡고있다. 이 때 (5) TxA는 customer_order에 대한 update를 수행하기 위해 TxB가 가지고 있는 customer_id=1인 row의 lock이 release되기를 기다리지만, TxB가 commit되지 않기 때문에 lock wait timeout이 지나가 transaction이 abort된 것을 볼 수 있다. 

TxB가 (5) 이후에 item_order를 증가하려고 시도하기까지 하면, 아래와 같은 에러 메세지를 볼 수 있다. 

 

그림5: UPDATE 중 deadlock으로 인해 transaction이 abort되는 경우

 

(5) 에서 TxA가 customer에 대한 lock이 풀리기를 기다리고 있는 도중, (6)에서 item_order에 대한 update를 시도한다. TxA는 item에 대한 lock을 가지고 customer의 lock이 풀리기를 기다리고 있으며, TxB는 customer에 대한 lock을 가지고 item에 대한 lock이 풀리기를 기다리고 있으므로 이는 순환 대기에 의한 deadlock상황이다. MySQL에서는 이를 감지하고 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 라는 에러메세지와 함께 TxB를 abort시켰고, 이 때 customer에 대한 lock이 relelase되면서 TxA가 정상 수행된 상황이다. (TxA의 (5) Update가 수행될 때까지 5.84초를 기다린 것을 볼 수 있다.)

이와 같은 상황을 방지하기 위해서는, 한 transaction안에서 여러 개의 UPDATE가 수행되고, 이러한 transaction이 여러 종류가 있을 경우, 순환대기가 발생하지 않도록 table혹은 row에 접근하는 순서를 동일하게 맞춰주어야 한다. 위와 같은 상황에서는 주문 / 환불 두 개의 트랜잭션 모두 item -> customer, 혹은 customer -> item의 순서로 접근하도록 통일을 해주면 deadlock이 생기는 것을 막을 수가 있다 .

 

References

 

Repeatable Read isolation level SELECT vs UPDATE...WHERE

maybe you can shed light on something for me here: DB = MySQL 5.7 Storage engine: InnoDB Isolation level: Repeatable Read Following table: --------------- | MyTable | --------------- | PK |

stackoverflow.com

 

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.3 Consistent Nonlocking Reads

15.7.2.3 Consistent Nonlocking Reads A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, a

dev.mysql.com