[MySQL] XA 트랜잭션으로 인해 Kill 되지 않는 트랜잭션 이슈

Aurora MySQL 운영 중 특정 1 건의 Row 데이터를 업데이트했을 경우 Lock으로 인해 데이터가 업데이트되지 않는 문제가 발생했습니다. 어떤 방법으로 XA 트랜잭션을 식별 및 처리하였는지 공유해보겠습니다.

 

운영 중인 환경은 Aurora 3 버전입니다.

문제 해결을 위해 가장 먼저 실행 중인 프로세스 리스트를 확인해보지만 문제가 될만한 프로세스는 확인되지 않았습니다. 좀 더 세부적인 트랜잭션 확인을 위해 information_schema.INNODB_TRX 테이블을 체크합니다.

 

RUNNING 상태의 트랜잭션이 확인되었으며 1 Row에 대해 Lock이 걸려있는 것을 확인할 수 있었습니다. 다만 tx_mysql_thread_id 값은 0으로 해당 트랜잭션을 소유하고 있는 스레드가 존재하지 않았습니다.

 

여기서 추가로 performance_schema.data_locks 테이블을 체크해보니 PK를 조건으로 Record Lock을 점유하고 있는 것을 확인할 수 있었습니다.

 

performance_schema.data_locks 테이블 상에는 thread_id 가 표시되었으나 해당 ID는 Processlist에 나타나지 않았고 Kill 명령도 수행되지 않았습니다. 처음 발생한 상황에 애를 먹는 도중 두 가지 의문이 생기게 됩니다.

 

1. 이미 Lock을 보유했음에도 단일 행을 업데이트 하는데 왜 트랜잭션이 종료되지 않는지
2. 해당 트랜잭션을 실행하고 있는 trx_mysql_thread_id 값이 0인 이유

이 두 가지 의문으로 문제를 찾던 중 XA 트랜잭션으로 인한 상황임을 인지하게 됩니다. trx_mysql_thread_id는 기존에 존재하였으나 XA 트랜잭션 충돌로 인해 thread_id 가 0 값으로 갱신이 된 것으로 보입니다.

 

 

XA 트랜잭션이란

분산 트랜잭션의 개념으로써 2PC(2 phase commit)을 통한 분산 트랜잭션 처리를 위해 X-Open에서 명시한 표준으로 여러 리소스 간 데이터 처리에 대해 원자적으로 작동해야 할 경우 사용합니다.

https://dev.mysql.com/doc/refman/5.7/en/xa.html

 

XA 트랜잭션 식별 및 핸들링

아래의 쿼리로 현재 실행중인 XA 트랜잭션을 식별할 수 있습니다. (아래는 임의로 생성한 xid입니다)

mysql> XA RECOVER

-- MySQL 5.7.5부터는 16진수 xid 값을 확인할 수 있습니다.
mysql> XA RECOVER CONVERT XID

|formatID |gtrid_length  |bqual_length  |data                                                                |
|---------|--------------|--------------|--------------------------------------------------------------------|
|2        |30            |2             |0x31373632336663642D366138322D343061652D383337362D663366666231313036|

 

xid 값은 클라이언트에 의해 제공되거나 MySQL 서버에 의해 생성되며 1~3 개의 부분으로 구성됩니다.

xid : gtrid [, bqual [, formatID]]

 

 

XA 트랜잭션을 Rollback 또는 Commit 하기 위해 xid를 16 진수로 출력합니다.

XA RECOVER CONVERT XID

0x31373632336663642D366138322D343061652D383337362D663366666231313036

 

이때 xid에서 gtrid, bqual 부분을 추출해야 합니다. 아래의 형태로 xid를 전달할 경우 Unknown XID 오류가 발생하게 됩니다.

XA ROLLBACK 0x31373632336663642D366138322D343061652D383337362D663366666231313036

 

현재 변숫값은 아래와 같습니다.

• formatID : 2
• gtrid_length : 30
• bqual_length : 3

 

위의 xid는 16진수 이므로 처음 60자리가 gtrid 값이 되고 이후 6자리가 bqual 값이 됩니다. 데이터 필드를 추출한 후에는 아래와 같이 Rollback 또는 Commit을 진행합니다.

XA ROLLBACK [gtrid], [bqual], [formatID]

XA ROLLBACK X'31373632336663642D366138322D343061652D383337362D663366666231', X'313036', 2

 

이후 활성 트랜잭션이 종료되고 쿼리가 정상적으로 수행되는 것을 확인할 수 있습니다.

 

왜 이런 상황이 발생했을까..??

 

XA 트랜잭션 처리 중 발생한 충돌은 MySQL 내부에서 자동으로 복구가 되지만 극히 드문 경우에 충돌된 트랜잭션이 남아있는 경우가 발생(일종의 버그…) 한다고 합니다 재부팅 시에도 트랜잭션은 유지되고 별도의 조지가 없을 경우 해당 트랜잭션은 영원히 유지된 채 존재하기 때문에 반드시 Commit 또는 Rollback이 진행되어야 합니다.

 

여담으로 Percona mysql 개발자가 말하기를 8년 동안 2번의 사례가 있었다고 합니다. 빠른 장애 해결을 위해 XA 트랜잭션을 사용하는 애플리케이션의 경우 잔존하는 XA 트랜잭션에 대한 에러 핸들링 방법을 알아놓는 것도 좋다고 생각됩니다.

 

참고

https://www.percona.com/blog/2017/09/22/how-to-deal-with-xa-transactions-recovery/