

AWS RDS 上の MySQL にアクセスする  
API(Apache + Fuel PHP)を追加して負荷試験を行っていたところ  
Apache が接続を受け付けなくなって調べていくうちに  
RDSのパフォーマンスインサイトのRDSのログの待機のところに

[code]

    synch/cond/mysys/my_thread_var::suspend
    
[/code]

というのがでてロックがおこってしまったようなのです

新規追加した MySQL のコードは以下です

[code]

    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)
    
[/code]

という user_id のレコードがあれば x, y, state を更新なければ作成というコードで  
これが重複を含む複数の user_id に対して非同期に大量にAPIに流れてきます

テーブルDDLは以下で PK とは別に user_id のみユニークインデックスがついています

[code]

    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
    
[/code]

このクエリが原因かどうか実際はわからないんですが最近追加した機能の１つなので  
RDS上でロックの原因を調べる方法はありますか？

またSQLやテーブルにどこか問題があったら教えていただきたいです

<https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.html>

> synch/cond/mysys/my_thread_var::suspend
>
>
> この待機イベントの場合、スレッドは条件の待機中に停止されます。たとえば、スレッドがテーブルレベルのロックを待機しているときに、このイベントが発生します。ワークロードを調査し、データベースインスタンスのテーブルロックを取得する可能性があるスレッドを確認することをお勧めします。MySQL  
>  のテーブルロックの詳細については、MySQL ドキュメントの「テーブルロックの問題」を参照してください。


CC BY-SA 4.0
2019-08-19T05:43:21.580
57446
2022-08-31T07:08:00.813
2019-08-20T03:27:15.573
question
mysql sql
on duplicate key update でロックがおこる条件


synch/cond/mysys/my_thread_var::suspend  
デッドロックが起こったというものではなく  
別のロックが発生しているため、該当のスレッドがロック解除待ちのため中断していることを表す待機イベントです。  
ロック解除されれば、自動で再開されて処理されるものです。

質問者さんの実行された並列テストでは  
「ユーザIDから同時にINSERTがくることもあります」とのことなので同じuser_idに対して並列でINSERT処理が走っているようなので、どうしても行ロックレベルですが、ロック待ちが発生してしまうと思います。  
これは排除できるものではなく、この作りでは受け入れなければならないものかなと思います。

要件としてINSERT時の行ロックの待ち時間ですら待てないということであれば、  
user_idのUnique制約をやめてしまってあればUPDATEするのではなく、常にINSERTするようにしてSELECT側で最新のcreated_atを参照するようにしてはどうでしょうか？  
そうするとデータ量とSELECT時の負荷が増えてしまいますが、INSERT時の行ロック待ち時間は発生しなくなります。


CC BY-SA 4.0
2019-08-20T03:26:19.447
57473
2019-08-20T03:26:19.447
22665
57446
answer