目的
コネクションプーリングをしても採番テーブル(MySQL)が正しく利用できるかを確認したい。
目的（詳細）
まず、以下のように、採番テーブルからidを取得する仕組みがあるとする（採番テーブルについては こちらの記事 を参照）
UPDATE table_name SET id = LAST_INSERT_ID(id + 1);
SELECT LAST_INSERT_ID() AS id;
ここで、UPDATE文とSELECT文の間に排他制御を掛ける必要はない。
理由は、MySQLの「LAST_INSERT_ID」はグローバルな値ではなく、connection 単位で定義されるためである。
公式ドキュメントより
"The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions."
要するに、別の connection でLAST_INSERT_IDが更新されても影響されることはないようになっている。
一方、RDS Proxyを使ってDBのコネクションプーリングをする予定がある。ここでいう connection がDBのコネクションのことを指しているのであれば、コネクションを共有した別のクライアントにLAST_INSERT_IDを上書きされてしまうことはないかと考えた。
そこで、実際の挙動を確認してみる。
試験
シナリオ
- 複数のMySQLクライアントを起動し、RDS Proxy経由でMySQLのライターインスタンスに接続
- 各クライアントで順にLAST_INSERT_IDを更新していく
- 各クライアントで順にLAST_INSERT_IDの値を取得
期待する挙動
各クライアントのLAST_INSERT_IDの値が異なる（連番になっている）こと。すなわち、手順2でクライアント自身が設定したときの値となっており、他のクライアントの更新の影響を受けないこと
期待しない挙動
各クライアントのLAST_INSERT_IDの値が全て一致していること。手順2で最後のクライアントが設定したときの値となっており、他のクライアントの更新の影響を受けていること
試験結果
期待する挙動となっている（LAST_INSERT_IDはクライアント毎に異なる）。
▼6個のクライアントで順にLAST_INSERT_IDを更新していく様子
▼6個のクライアントでLAST_INSERT_IDの値を取得し、それぞれの値が異なっている様子
結論
RDS Proxy(コネクションプーリング)利用時にLAST_INSERT_IDの値がDBコネクション間で共有されるということはなく、クライアントに固有であることがわかった。おそらく採番テーブルとして利用して問題ないと考えられる。
補足: モニタリング情報
RDS Proxyのメトリクス
クライアント接続数＞DB接続数となっている
- ClientConenctions: 0→6に増加(+6)
- DatabaseConnections: 3→4に増加(+1)
RDS Proxyのログ
- クライアント〜Proxy〜MySQL接続確立時のRDS Proxyのログ
- コネクションプーリングをしている様子がわかる
- clientConnectionが6種類確立されているのに対して、dbConnectionはid=800703549, 3258076081の2種類が都度使い回されている
