バッチ削除のロック待ちを解消した事例

mysql_hosting

運用していると古いデータを削除したくなるケースがあるかと思います。

今回は、バッチ削除実行時のロックにより更新系クエリが待ち状態になってしまっていたため、その時解消した方法を書きたいと思います。

やりたいこと

以下のようなテーブルから、指定日以下のNULL以外のデータを削除し、同時にINSERTも実行したい感じです。
※2017-03-04 00:00:00以下を削除対象にします

> CREATE TABLE `test_trx` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(10) unsigned NOT NULL,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

> INSERT INTO `test_trx` (`id`, `c1`, `date`)
VALUES
(1, 1, '2017-03-01 00:00:00'), -- 消す
(2, 1, '2017-03-02 00:00:00'), -- 消す
(3, 1, NULL),                  -- 残す
(4, 1, '2017-03-04 00:00:00'), -- 消す
(5, 1, NULL),                  -- 残す
(6, 1, '2017-03-06 00:00:00'); -- 残す

> DELETE FROM test_trx WHERE date <= "2017-03-04 00:00:00";
> INSERT INTO `test_trx` (`c1`,`date`) VALUES(1,'2017-03-06 00:00:00');

ロックが発生するか確認してみる

session1 : バッチが実行
session2 : 特定のユーザが実行
の想定です。

session1

> START TRANSACTION;
> DELETE FROM `test_trx` WHERE date <= "2017-03-04 00:00:00";
Query OK, 3 rows affected (0.00 sec)
> -- ROLLBACK or COMMIT;

session2

> INSERT INTO `test_trx` (`c1`,`date`) VALUES(1,'2017-03-06 00:00:00');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session2がロック待ちでタイムアウトしました。
トランザクション情報を見ると、INSERTがロック待ちになっていました。

> use information_schema;
> SELECT * FROM innodb_trx\G;
*************************** 1. row ***************************
trx_id: 26011177
trx_state: LOCK WAIT
trx_started: 2017-03-02 19:16:28
trx_requested_lock_id: 26011177:14169:3:1
trx_wait_started: 2017-03-02 19:16:28
trx_weight: 2
trx_mysql_thread_id: 4391947
trx_query: INSERT INTO `test_trx` (`c1`,`date`) VALUES(1,'2017-03-06 00:00:00')
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 26011161
trx_state: RUNNING
trx_started: 2017-03-02 19:16:25
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 5
trx_mysql_thread_id: 4391809
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 7
trx_rows_modified: 3
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
> SELECT * FROM innodb_lock_waits;
*************************** 1. row ***************************
requesting_trx_id: 26011177
requested_lock_id: 26011177:14169:3:1
blocking_trx_id: 26011161
blocking_lock_id: 26011161:14169:3:1
1 row in set (0.00 sec)

ロックについて調べる

InnoDB のレコード、ギャップ、およびネクストキーロック
https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html

レコードロック
レコードロックでは、テーブルにインデックスが定義されていなくても必ず、インデックスレコードがロックされます。このような場合は、InnoDB によって非表示のクラスタ化されたインデックスが作成され、このインデックスを使用してレコードロックが行われます。

「非表示のクラスタ化されたインデックス」ってなんだろう。
テーブルロックに似たような挙動になるとのことだったので、dateフィールドにindexを貼ってみます。

> ALTER TABLE `test_trx` ADD INDEX idx(`date`);
> EXPLAIN SELECT * FROM test_trx WHERE date <= "2017-03-04 00:00:00";
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
| 1    | SIMPLE      | test_trx | range | idx           | idx  | 9       | NULL | 3    | Using index condition |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

session1

> START TRANSACTION;
> DELETE FROM `test_trx` WHERE date <= "2017-03-04 00:00:00";
Query OK, 3 rows affected (0.00 sec)
> -- ROLLBACK or COMMIT;

session2

> INSERT INTO `test_trx` (`c1`,`date`) VALUES(1,'2017-03-06 00:00:00');
Query OK, 1 row affected (0.00 sec)

通りました。
が、以下のようなINSERTはロック待ちになります。

> INSERT INTO `test_trx` (`c1`,`date`) VALUES(1,'2017-03-05 23:59:59');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
> INSERT INTO `test_trx` (`c1`,`date`) VALUES(1,NULL);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

この辺りは、ネクストキーロック・ギャップロックの影響なんだと思います。
※NULLについてはよくわかりませんでした

過去の日付が入る想定はなかったのですが、NULLは通したかったので、
事前に削除対象のPRIMARYKEYの範囲を抽出してから削除する方法を取りました。

session1

> START TRANSACTION;
> SELECT MIN(id) AS min, MAX(id) AS max FROM `test_trx` WHERE date <= "2017-03-04 00:00:00";
+------+------+
| min  | max  |
+------+------+
| 1    | 4    |
+------+------+
1 row in set (0.00 sec)
> DELETE FROM `test_trx` WHERE `id` BETWEEN :min AND :max AND `date` <= "2017-03-04 00:00:00"; -- min: 1, max: 4
Query OK, 3 rows affected (0.00 sec)
> -- ROLLBACK or COMMIT;

session2

> INSERT INTO `test_trx` (`c1`,`date`) VALUES(1,'2017-03-06 00:00:00');
Query OK, 1 row affected (0.00 sec)

> INSERT INTO `test_trx` (`c1`,`date`) VALUES(1,'2017-03-05 23:59:59');
Query OK, 1 row affected (0.00 sec)

> INSERT INTO `test_trx` (`c1`,`date`) VALUES(1,NULL);
Query OK, 1 row affected (0.01 sec)

NULLも通るようになりました。