質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

4回答

9975閲覧

二つのテーブルの同期を完全に行いたい

Auxo

総合スコア34

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

1グッド

0クリップ

投稿2016/11/30 08:29

2つの別の場所に存在するテーブルのレコードをプログラムで同期したいと考えています。
それぞれのテーブルは同じ情報を管理していますが、別なフィールドを更新しており、いずれかで更新されたものをもう一方のテーブルに定期的にコピーするという仕組みです。

※レプリケーションなどの方法ではなく、プログラムにより同期を取りたいと考えております。

A、Bそれぞれのテーブルではテーブルの中身は同じですが双方で更新するフィールドはなく、いずれかのフィールドは必ずどちらかのテーブルで更新され、その部分をもう一方のテーブルにコピーするという仕組みです。

主キーはAのテーブルでのみ新規に生成されるAutoIncrementの属性を持つフィールドで管理し、新規のレコードは必ずAのテーブルでのみ生成されます。

最終更新日時のみ同期を取らず、Aの最終更新日時フィールドはAの最終更新日時、Bの最終更新日時フィールドはBの最終更新日時を保持しています。

現在の方法はそれぞれのテーブルで同期処理時点の最終更新日時(DateTime)を保持し、前回の更新以降の日時を持つレコードを最終更新日時フィールドで検索し、該当したレコードの情報をAからBへ、BからAへコピーしています。

SQL

1SELECT * FROM A WHERE update_at > (前回実行時の最終更新日時) AND update_at <= (現在日時);

しかしこの方法ですと、例えば同期処理を行うバッチプログラムが実行された際、1秒以内の同時更新(バッチ処理が16時12分13秒に実行された場合、同じ16時12分13秒に情報が更新されたもの)が漏れてしまうことになると考えています。

仮に更新日時をマイクロ秒まで管理できるようにする(DATETIME(6)とする)場合も、完全に同時処理を制御する事はできないと考えています。

こういった処理を実装する場合、最終更新日時で管理するのは不適切なのでしょうか。
例えば、排他制御(ロック)が行われる事を前提として、更新フラグのようなものを作り、そのフィールドを更新時にはTrueとし、バッチ処理で更新処理後にクリアー(falseを設定)するような方がよいのでしょうか。

KiyoshiMotoki👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答4

0

ベストアンサー

Aテーブルにトリガーを貼り、追加、更新、削除する毎にログテーブルに書込みを行います、ログテーブルも追加の際に作成時刻を保存する様にしています。
これを複数のクライアントが最後に更新した時刻より取得しながら、ログテーブルよりクライアントDBのBテーブルに取込(追加、変更、削除)を行う処理をする仕組みで行っているケースは有ります。

投稿2016/11/30 09:33

A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Auxo

2016/11/30 09:48

ご回答ありがとうございます。 なるほど・・・確かにログテーブルを利用する事で上手くいくような気もします。 ログテーブル上に通常のキーとは別のAutoIncrementのキーを準備し、前回同期処理完了時点のキーを保持し、次回の更新時はそのキー以降のデータを対象にする事で漏れは確実になくなる気がします。 実装についても既存の処理は触らずに対処も可能な雰囲気ですので、そういった意味でも有効な方法であると考えます。 もう少し頭の中で整理して実施の可否を判断したいと思います。
A.Ichi

2016/11/30 10:05

複数のトランザクションがコミットするタイミングで、連番も時刻でも逆転する可能性が有る場合。更新のシリアライズが必要な場合も有ります。
Auxo

2016/11/30 10:16

追加のコメントありがとうございます。 履歴の取得はMySQLのトリガーを用いて処理したいと考えているのですが、私の知識ではどのように配慮が必要なのかがちょっとわかりません。 私の認識ではMySQLのAutoIncrementでは連番であることの保証はない(処理により欠番が生じる事もあるとの認識)ものの、採番される番号の順序(常に大きい番号が採番される。但し循環時を除く)は保証されると認識しています。 確かにデータベースに書き込みを行うクライアントは複数ありますが、同時書き込み(レコードロック)ロックは適切に行われているようです。 この状況下で特別な配慮が必要でしょうか。
A.Ichi

2016/11/30 12:50

ロックがされて更新順序の逆転が無いのであれば、シリアル番号にての管理は可能です。言われる様に、課題はシリアル番号の巡回時の考慮が必要ですね。 タイムスタンプが '1970-01-01 00:00:01.000000'でユニークになれば使えると思います。
A.Ichi

2016/11/30 13:02

タイムスタンプはキーには不向きなので、循環期間より短い長さの数字を先頭に付けるのはどうでしょうか?YYYYMMとか?
Auxo

2016/12/01 00:57

コメントありがとうございます。 循環時の対策については現状運用回避を行う方向で議論中です。 といいますのも、更新件数は少なくはありませんが、該当キーが"unsigned bigint"である為、計算上は10年程度の更新量には耐えうるとの見込みです。 実際の履歴テーブル自体は複合キーとして、履歴レコード毎に採番されるAutoIncrement方のIDフィールドとDB側での記録時の日時(マイクロ秒まで記録)もので一意となるようにし、万が一循環した場合も重複しない仕組みで対応したいと思います。 これであれば元アプリケーションへの影響は最小限に同期処理を更新し切り替える事もできそうです。 色々とご助言ありがとうございます。
guest

0

同期処理ということでしたら、前回更新済みのレコードが再度対象となっても問題にように思います。

「update_at >= (前回実行時の最終更新日時)」とか「update_at >= (前回実行時の最終更新日時- 1秒)」ではだめでしょうか?

投稿2016/11/30 09:03

attercop

総合スコア246

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Auxo

2016/11/30 09:12

ご回答ありがとうございます。 時刻をキーにする場合、閾値(この場合1秒)を超えるタイムラグが生じた場合に、その部分は同期漏れが発生するのではないかと考えております。 そもそも1秒のタイムラグが生じる更新というのはあり得るのか?という話ではあるのですが、将来的に性能低下により遅延が発生するなどして同期漏れが生じるリスクは避けたいと考えています。
attercop

2016/11/30 09:34

同期処理の実行頻度によると思いますが、タイムラグを考慮した十分な時間を前回実行時の最終更新日時から引いてあげてはどうでしょうか? 前回実行時の最終更新日時が、処理の開始時刻でしたら性能低下による遅延は、あまり影響がないように思います。
Auxo

2016/11/30 09:54

追加のコメントありがとう御座います。 確かにタイムラグ(処理遅延など)を考慮して十分余裕を見れば実際には実用に耐えうるとは思います。 また、現実的にDB操作の処理遅延が数秒単位で起きると事は通常想定しないところではあるのですが、悲しいかな最終更新日時が更新処理の開始時刻を最初で確定しており、その時刻を全ての更新対象レコードに設定しているようでした。 結果として、1レコード毎の処理時間ではなく、更新処理全体の遅延が閾値との比較となる為、その点で『やばい・・・』と感じ、時間を用いた方法以外で確実に同期できる方法はないものか、と考えておりました。 更新日時を処理時点の日時に変更する事も考えたのですが、アプリケーションで他にも参照している箇所もありあまり既存の処理に影響を及ぼしたくないと考え、その点は触らない方向で対応できればと考えております。 制限(負債)が多く、なんとも言えないのがつらいところです。
attercop

2016/11/30 11:21

「最終更新日時が更新処理の開始時刻を最初で確定しており」は同期処理ではなく、Aテーブル、Bテーブルの更新処理のことでしょうか? 最終更新日時があてにならないなら、ご質問にあるようにフラグ管理するか、A.Ichiさんのご回答の通りトリガーでの対応をご検討された方がよいと思います。
Auxo

2016/12/01 00:52

コメントありがとうございます。 >「最終更新日時が更新処理の開始時刻を最初で確定しており」は同期処理ではなく、Aテーブル、Bテーブルの更新処理のことでしょうか? 仰るとおり、この部分はAテーブルまたはBテーブルの更新を行うアプリケ—ション側の仕様です。 >最終更新日時があてにならないなら、ご質問にあるようにフラグ管理するか、A.Ichiさんのご回答の通りトリガーでの対応をご検討された方がよいと思います。 そうですね。 私も色々と考えたのですが、現状はA.Ichiさんからお教えいただいた方法が一番影響、安全性共に優れているように感じました。 色々とご助言ありがとう御座います。
guest

0

実行時の同期を次の処理で送ればよいのでは?


毎分バッチで同期するとします。(毎分0秒に)
16時55分の処理の場合
SELECT * FROM A WHERE update_at >= YYYY/MM/DD 16:54:00 AND update_at < YYYY/MM/DD 16:55:00

※16:55の更新は16:56に同期される

投稿2016/11/30 09:02

koutan1976

総合スコア142

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Auxo

2016/11/30 09:19

ご回答ありがとうございます。 時刻をキーにする場合、閾値(この場合1秒)を超えるタイムラグが生じた場合に、その部分は同期漏れが発生するのではないかと考えております。 ご助言いただいたケースでは、指定時刻が分単位でも時間単位でも対象のレコードを管理する時間の精度(この場合、秒単位)以下の精度での同期は保証できないのではないかと考えております。 仮に「16:55:00」を『未満』という条件で指定したとしても、現実性はともかくとして2秒以上の更新時間がかかったデータが存在した瞬間にそのデータは同期漏れという事になると考えています。 更新日時を他でも処理に使っているようで迂闊に仕様を変えられないという事もあるのですが、時間をキーにする方法ではどうしても個人的な意見ですが完全に同期漏れを防ぐ事は無理なのではないかと考えています。 その為、質問に記載した例えばフラグを持つような方法しかないのだろうか・・・と考えています。
guest

0

「片方がマスター(書き込みはここに行う)で、もう片方がコピー」という状況であればMySQL標準にあるレプリケーション機能を使えるのですが…

標記のような運用を行う必要性がどのあたりにあるのか教えていただければ幸いです。

投稿2016/11/30 08:39

maisumakun

総合スコア145121

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Auxo

2016/11/30 08:52

ご回答ありがとうございます。 一つはそれぞれ別のアプリケーションで管理されている情報を同期する為です。 完全に一致したテーブルであれば、セキュリティ(要件)が満たされるのであればレプリケーションによる同期でもよいのですが、別なアプリケーションで管理されているテーブルの共通属性の同期を行いたい為、このような方法を検討しています。 二つのテーブルは完全に一致しているわけではなく、動作しているアプリケーションに大きな影響を及ぼしたくない為、シンプルな方法で且つ安全に同期できないものかとかんがえています。 その為、単純に更新日時は別々なものですのでそれで同期すれば良いのでは・・・と考えたのですが、同時処理については完全には防ぎ切れませんでした。 日時次バッチのようなレベルで良ければそれほど気にしないのですが、定期的(毎分など)に実行される為、同時処理についても検討が必要となっています。 上記のような事情でレプリケーションではなく、プログラムでの同期処理を検討しています。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問