MySQLでロックを特定、強制終了してみた
はじめに
こんにちは、SHIFT の開発部門に所属しているmurasawaです。今期より中途で入社、バックエンド関連の開発を担当しています。
現在、研修でデータベースやRestAPIについて基本的な事から学んでいます。学んだことをアウトプットし理解を深めていくとともに技術の共有として役に立てば幸いです。
今回はロック競合しているかの確認、およびプログラムの不備により、処理が止まってしまった時の対応用に mysql5.7でロックを確認したり、強制終了する際のコマンドをまとめてみました。
ロックの状態を確認
検証用テーブルの用意
今回の検証用に以下テーブルとレコードを用意しました。
テーブル
mysql> show create table app.test_score \G;
*************************** 1. row ***************************
Table: test_score
Create Table: CREATE TABLE `test_score` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`score` int(10) unsigned NOT NULL,
`grade` int(10) unsigned NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.06 sec)
mysql>
レコード
mysql> select * from test_score;
+----+----------+-------+-------+---------------------+---------------------+
| id | name | score | grade | updated_at | created_at |
+----+----------+-------+-------+---------------------+---------------------+
| 1 | kato | 98 | 5 | 2021-10-27 18:03:05 | 2021-10-27 18:03:05 |
| 2 | sato | 89 | 5 | 2021-10-27 18:03:18 | 2021-10-27 18:03:18 |
| 3 | endo | 69 | 4 | 2021-10-27 18:04:05 | 2021-10-27 18:04:05 |
| 4 | yamamoto | 59 | 3 | 2021-10-27 18:04:55 | 2021-10-27 18:04:55 |
+----+----------+-------+-------+---------------------+---------------------+
4 rows in set (0.00 sec)
mysql>
二つのトランザクションでUPDATEを実行
一つ目のトランザクションでUPDATEを実行しロックをかけてみます。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test_score SET score = 99 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ここで一度、information_schema.INNODB_TRXというビューを参照してロック状態を確認してみます。
mysql> SELECT trx_id, trx_rows_locked, trx_mysql_thread_id FROM information_schema.INNODB_TRX;
+--------+-----------------+---------------------+
| trx_id | trx_rows_locked | trx_mysql_thread_id |
+--------+-----------------+---------------------+
| 7949 | 1 | 2 |
+--------+-----------------+---------------------+
1 row in set (0.00 sec)
mysql>
この表から実行したUPDATE文通り、スレッドId２のプロセスで一行ロックが一件あることがわかります。
別コンソールを開き二つ目のトランザクションでUPDATEを実行。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test_score SET score = 100 where id = 1;
ロック獲得待ちになりました。
もう一度information_schema.INNODB_TRXというビューを参照してロック状態を確認してみます。
mysql> SELECT trx_id, trx_rows_locked, trx_mysql_thread_id FROM information_schema.INNODB_TRX;
+--------+-----------------+---------------------+
| trx_id | trx_rows_locked | trx_mysql_thread_id |
+--------+-----------------+---------------------+
| 7950 | 1 | 3 |
| 7949 | 1 | 2 |
+--------+-----------------+---------------------+
2 rows in set (0.00 sec)
mysql>
二個目のトランザクション分のロックが増えました。
プロセスを強制終了
プログラムの不備などにより、ロックが競合して処理が進まなくなってしまった場合、 trx_mysql_thread_id をつかって、KILLすることで強制終了させることができます。
KILLする際にはほかの正常なプロセスを削除したり、ほかに影響が出ないよう十分に気を付けてください。
mysql> KILL 2;
Query OK, 0 rows affected (0.02 sec)
mysql> KILL 3;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT trx_id, trx_rows_locked, trx_mysql_thread_id FROM information_schema.INNODB_TRX;
Empty set (0.00 sec)
mysql>
これでロックで止まっていたプロセスを終了させることができました。
ロック獲得待ちの確認
ロック獲得待ちをしているロック状態の確認方法も調べてみました。
まずは上記の手順と同様、二つのトランザクションでUPDATEを実行します。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test_score SET score = 99 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ここで今度は、sys.innodb_lock_waitsというビューを参照してロック獲得待ち状態を確認してみます。
mysql> SELECT * FROM sys.innodb_lock_waits\G
Empty set, 3 warnings (0.00 sec)
mysql>
ロック獲得待ちはないので、何も表示されませんでした、
3 WARNINGsはこのビューから参照しているinformation_schema.INNODB_LOCKS・information_schema.INNODB_LOCK_WAITSの2つのテーブルの次バージョン（8.0）での廃止を伝えるものです。
別コンソールを開き二つ目のトランザクションでUPDATEを実行。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test_score SET score = 100 where id = 1;
ロック獲得待ちになりました。
ここでもう一度、sys.innodb_lock_waitsに加えついでに上記WARNINGがでた、
SELECT * FROM information_schema.INNODB_LOCKS\G、
SELECT * FROM information_schema.INNODB_LOCK_WAITS\G
というビューを参照してロック獲得待ち状態を確認してみます。
mysql> SELECT * FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2021-10-28 14:15:05
wait_age: 00:00:04
wait_age_secs: 4
locked_table: `app`.`test_score`
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 7953
waiting_trx_started: 2021-10-28 14:15:05
waiting_trx_age: 00:00:04
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 3
waiting_query: UPDATE test_score SET score = 100 where id = 1
waiting_lock_id: 7953:34:3:2
waiting_lock_mode: X
blocking_trx_id: 7952
blocking_pid: 5
blocking_query: NULL
blocking_lock_id: 7952:34:3:2
blocking_lock_mode: X
blocking_trx_started: 2021-10-28 14:12:37
blocking_trx_age: 00:02:32
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 5
sql_kill_blocking_connection: KILL 5
1 row in set, 3 warnings (0.00 sec)
mysql>
mysql> SELECT * FROM information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 7953:34:3:2
lock_trx_id: 7953
lock_mode: X
lock_type: RECORD
lock_table: `app`.`test_score`
lock_index: PRIMARY
lock_space: 34
lock_page: 3
lock_rec: 2
lock_data: 1
*************************** 2. row ***************************
lock_id: 7952:34:3:2
lock_trx_id: 7952
lock_mode: X
lock_type: RECORD
lock_table: `app`.`test_score`
lock_index: PRIMARY
lock_space: 34
lock_page: 3
lock_rec: 2
lock_data: 1
2 rows in set, 1 warning (0.00 sec)
mysql>
mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 7953
requested_lock_id: 7953:34:3:2
blocking_trx_id: 7952
blocking_lock_id: 7952:34:3:2
1 row in set, 1 warning (0.00 sec)
mysql>
現在待っているのは「waiting_query: UPDATE test_score SET score = 100 where id = 1」の実行、待たせている側待っている側の情報である「requesting_trx_id、blocking_trx_id」などの情報が獲得できます。
なお、これらの情報はロック獲得待ちの間のみ参照でき、 タイムアウトなどで待ち状態が終わると参照できなくなります。
終わりに
今回はmysqlのロックプロセスを強制終了する方法を調べました。
これで有事の際にも対応できます。
間違い等、ほかに良い方法等あればコメントいただければ幸いです。
■この公式ライターの他の記事はこちら
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
執筆者プロフィール：Satoshi Murasawa
前社ではRPAツールの技術サポート、開発を1年半経験。 SHIFTでは、バックエンドエンジニアとして入社し、node.jsやmysqlに触れはじめた。
DBに漠然とした興味があり、勉強して部の中でDB関連で役割を持つことができたらよいなと思っています。
★３分でわかるSHIFTについて
★SHIFTの導入事例はコチラ
★SHIFTの最新イベント情報はコチラ
★SHIFTの最新コラムはコチラ