on duplicate key update でロックがおこる条件
AWS RDS 上の MySQL にアクセスする
API(Apache + Fuel PHP)を追加して負荷試験を行っていたところ
Apache が接続を受け付けなくなって調べていくうちに
RDSのパフォーマンスインサイトのRDSのログの待機のところに
synch/cond/mysys/my_thread_var::suspend
というのがでてロックがおこってしまったようなのです
新規追加した MySQL のコードは以下です
INSERT INTO user_states
(user_id, x, y, state)
VALUES ('xxxxxxxx', 100, 200, 0)
ON DUPLICATE KEY UPDATE
x = VALUES(x), y = VALUES(y), state = VALUES(state)
という user_id のレコードがあれば x, y, state を更新なければ作成というコードで
これが重複を含む複数の user_id に対して非同期に大量にAPIに流れてきます
テーブルDDLは以下で PK とは別に user_id のみユニークインデックスがついています
CREATE TABLE `user_states` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(255) NOT NULL,
`x` decimal(9,6) NOT NULL,
`y` decimal(9,6) NOT NULL,
`state` int(11) NOT NULL DEFAULT '1',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `index_user_states_on_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=nnnnnn DEFAULT CHARSET=latin1
このクエリが原因かどうか実際はわからないんですが最近追加した機能の1つなので
RDS上でロックの原因を調べる方法はありますか?
またSQLやテーブルにどこか問題があったら教えていただきたいです
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.html
synch/cond/mysys/my_thread_var::suspend
この待機イベントの場合、スレッドは条件の待機中に停止されます。たとえば、スレッドがテーブルレベルのロックを待機しているときに、このイベントが発生します。ワークロードを調査し、データベースインスタンスのテーブルロックを取得する可能性があるスレッドを確認することをお勧めします。MySQL
のテーブルロックの詳細については、MySQL ドキュメントの「テーブルロックの問題」を参照してください。