1.트랜잭션 락
- 다중 문 트랜잭션을 사용 할 때 커밋을 하지 않을 때 발생하는 락을 말한다.
2. 장애 상황
- session 1, session 2 2개의 세션 중에 session 1에서 트랜잭션을 시작(BEGIN) 명령어를 실행하고 update문을 수행하였다. 같은 시간 session 2에서는 동일한 테이블의 같은 행을 update 수행했고 lock이 발생 했다.
- session 2에 발생한 락은 무엇이며, 어떤 세션의 쿼리의 의해서 lock이 발생했는지지 원인을 파악하고 lock을 해결 할 수 있는 가장 좋은 방법은 무엇인가?
3. 테스트
#테스트 환경
- CentOS 7
- MariaDB 10.3.8
3.1 트랜잭션 락 발생
1. 테스트 테이블 및 데이터 생성
MariaDB [test]> create table hanglock( no int(10) unsigned not null auto_increment, primary key(no));
Query OK, 0 rows affected (0.608 sec)
MariaDB [test]> insert into hanglock values();
Query OK, 1 row affected (0.002 sec)
MariaDB [test]> insert into hanglock select null from hanglock;
Query OK, 1 row affected (0.001 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into hanglock select null from hanglock;
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into hanglock select null from hanglock;
Query OK, 4 rows affected (0.001 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into hanglock select null from hanglock;
Query OK, 8 rows affected (0.001 sec)
Records: 8 Duplicates: 0 Warnings: 0
2.트랜잭션 발생
#session 1
- 트랜잭션을 시작(BEGIN)하고 업데이트문을 실행 한 후 트랜잭션 종료를 하지 않는다.
MariaDB [test]> BEGIN;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> UPDATE hanglock set no = 33 where no = 27;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#session 2
- session 1과 동일한 로우 업데이트를 실행 한다.
MariaDB [test]> BEGIN;
Query OK, 0 rows affected (0.000 sec)
- session 1에서 트랜잭션을 종료하지 않았기 때문에 대기 한다.
MariaDB [test]> update hanglock set no = 36 where no = 27;
MariaDB [test]> ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- 대기 시간은 innodb_lock_wait_timeout에 설정 된 초만큼 대기하다가 종료 된다.
MariaDB [test]> show variables like '%innodb_lock_wait_timeout%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 180 |
+--------------------------+-------+
1 row in set (0.001 sec)
#session 3
- session 3에서 현재 락에 걸려 있는 상황을 모니터링 한다.
- 2개의 로우에 lock이 발생 했다는 사실을 알 수 있으나, 어떤 세션에서 락을 걸었는지 또는 기다리는지 알 수 없다.
MariaDB [information_schema]> select * from innodb_locks\G;
*************************** 1. row ***************************
lock_id: 123423579:63806:3:16
lock_trx_id: 123423579
lock_mode: X
lock_type: RECORD
lock_table: "test"."hanglock"
lock_index: PRIMARY
lock_space: 63806
lock_page: 3
lock_rec: 16
lock_data: 19
*************************** 2. row ***************************
lock_id: 123423493:63806:3:16
lock_trx_id: 123423493
lock_mode: X
lock_type: RECORD
lock_table: "test"."hanglock"
lock_index: PRIMARY
lock_space: 63806
lock_page: 3
lock_rec: 16
lock_data: 19
2 rows in set (0.000 sec)
ERROR: No query specified
- lock을 요청한 세션과, lock에 의해 대기중인 세션을 확인 할 수 있다.
MariaDB [information_schema]> select * from innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 123423579
requested_lock_id: 123423579:63806:3:16
blocking_trx_id: 123423493
blocking_lock_id: 123423493:63806:3:16
1 row in set (0.000 sec)
- innodb_trx 테이블을 조회를 통해사 상세한 정보를 확인 할 수 있다. trx_state 컬럼을 통해서 lock 상태를 확인 할 수 있으며, trx_mysql_thread_id로 lock을 발생 시킨 세션을 종료 할 수 있다.
MariaDB [information_schema]> select * from innodb_trx\G;
*************************** 1. row ***************************
trx_id: 123423493
trx_state: RUNNING
trx_started: 2020-05-19 08:33:08
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 714185
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 123423579
trx_state: LOCK WAIT
trx_started: 2020-05-19 08:33:22
trx_requested_lock_id: 123423579:63806:3:16
trx_wait_started: 2020-05-19 08:37:21
trx_weight: 2
trx_mysql_thread_id: 713497
trx_query: update hanglock set no = 200 where no = 19
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.000 sec)
ERROR: No query specified
- lock을 잡고 있는 세션(714185)을 kill 한다.
[hiwdbusr@hiwsvr01 ~]$ mysqladmin -uroot -pNetand1410 kill 714185
#session 2
- lock wait 중이던 session 2의 update 작업이 완료 되었다.
MariaDB [test]> update hanglock set no = 200 where no = 19;
Query OK, 1 row affected (1 min 4.808 sec)
Rows matched: 1 Changed: 1 Warnings: 0
4. 해결방법 및 결론
- 트랜잭션을 시작(BEGIN)하고 쿼리를 수행 후 종료(COMMMIT/ROLLBACK)을 하지 않으면 트랜잭션이 계속 락을 유지 하기 때문에 반드시 COMMIT 또는 ROLLBACK을 통해 종료 한다.
- 트랜잭션 LOCK이 발생 했을 경우, 위의 테이블을 통해 LOCK을 발생시킨 대상을 찾아서 적절히 조치를 취해야 한다.