MySQLで複数のテーブルを更新する際にトランザクションを使わずに、更新が失敗してもテーブル間の整合性を保てるテクニックがありましたら教えて下さい。


ちなみに、以下のような更新方法(長いのでコメントに記載)を考えており、この方法の問題点や他のやり方がある場合も是非。
よろしくお願いします。

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2010/04/09 14:33:24
  • 終了:2010/04/13 07:25:32

回答(3件)

id:taknt No.1

きゃづみぃ回答回数13539ベストアンサー獲得回数11982010/04/09 14:47:56

ポイント27pt

問題点ですが、

AテーブルのIDの取得をどうするのか?

同一タイトル名があった場合

同時に 複数の人が Aテーブルに更新を入れた場合(①の処理)

→Bテーブルの a_idに 同じIDが入る場合があるのでは?

あと flagが NULLのものは どうするのか?

などが 考えられます。

id:xxmasaxx

回答有り難うございます。ご指摘頂いた点ですが、

> AテーブルのIDの取得をどうするのか?

IDはAUTO_INCREMENTなので、①の時にIDは自動採番されますので、それを取得します。

> 同一タイトル名があった場合

> 同時に 複数の人が Aテーブルに更新を入れた場合(①の処理)

titleとflagに、ユニークインデックスを貼ってますが、

NULL値を持つカラムがあると、重複してても更新可能なので、①の時には問題ありません。

③のflag = 1になった時に、どちらかがエラーになると思います。

> Bテーブルの a_idに 同じIDが入る場合があるのでは?

上記の通り、AテーブルのIDは自動採番されますので、IDの重複はないと思います。

> あと flagが NULLのものは どうするのか?

不要になるので、あとでまとめて削除する、でしょうか。

以上、妄想したたけで、ちゃんと調べてないので、

もし間違っている or とぼけたことを言ってましたらお叱りください。

2010/04/09 15:25:42
id:hanako393 No.2

hanako393回答回数1142ベストアンサー獲得回数872010/04/11 17:30:50

ポイント27pt

トランザクションを使わずに整合性を保つ方法は本来ありません。

どうしてもやりたいのなら、

更新前のデータをファイル等に保存しておき、

更新失敗したら、データを戻すことです。

ただし、データが必ず戻せるかどうかは不明ですので、ここで不整合になります。

しかしながら、不整合になったと言うデータをファイルに保存しておいて

機会あるごとにデータを修復するとともに

不整合なデータを用いるときに注意をする必要があります。

id:xxmasaxx

回答どうも有り難うございます。

2010/04/12 18:52:28
id:koriki-kozou No.3

koriki-kozou回答回数480ベストアンサー獲得回数792010/04/13 02:19:32

ポイント26pt

トランザクション無しの更新は同時実行制御が煩雑で非現実的

追記限定であれば整合性を保つ事は容易

更新(メンテナンス)を他ユーザー完全シャットアウトで行えばトランザクション無しも可能


追記限定であればコメント欄の方法で対処可能

>Aテーブルのtitleとflagにタイトル名とNULLを入れて保存

>INSERT INTO a_table (title,flag) VALUES('タイトル',NULL);

>BテーブルにAテーブルのIDと書き込み内容を保存

>INSERT INTO b_table (a_id,body) VALUES('aテーブルのID','書き込み内容');

>途中でエラーがなければ、Aテーブルのflagを1に変更して、更新完了

>UPDATE a_table SET flage=1 WHERE id = 保存したbbsテーブルのID;


Nullよりはゼロなどの定数が良いでしょう

flag TINYINT DEFAULT 0,         # フラグ※更新中なら0、完了したら1


負荷もタイムラグも大きく

タイトな仕様にはトランザクション導入必須

id:xxmasaxx

回答ありがとうございます。参考にさせて頂きます。

2010/04/13 07:25:13
  • id:xxmasaxx
    考えてる方法を以下に書きます。

    ■ 条件 ■
    ・Aテーブルから、掲示板のタイトル一覧を読み出し、Bテーブルからその書き込み内容を表示。
    ・同じタイトル名の重複登録は禁止。

    ■ テーブル ■ ※テーブルは簡略化してます
    Aテーブル(タイトル一覧)
    create table a_table (
      id INT NOT NULL AUTO_INCREMENT, # タイトルID
      title VARCHAR(80) NOT NULL,     # タイトル名
      flag TINYINT NULL,         # フラグ※更新中ならNULL、完了したら1
      UNIQUE INDEX check (title, flag)   # titleとflagにユニークインデックスを貼る
    );

    Bテーブル(書き込み内容)
    create table b_table (
      a_id INT NOT NULL,     # aテーブルのID
      body VARCHAR(255) NOT NULL, # 書き込み内容
    );

    ■ 更新手順 ■
    ①Aテーブルのtitleとflagにタイトル名とNULLを入れて保存
    INSERT INTO a_table (title,flag) VALUES('タイトル',NULL);

    ②BテーブルにAテーブルのIDと書き込み内容を保存
    INSERT INTO b_table (a_id,body) VALUES('aテーブルのID','書き込み内容');

    ③途中でエラーがなければ、Aテーブルのflagを1に変更して、更新完了
    UPDATE a_table SET flage=1 WHERE id = 保存したbbsテーブルのID;

    この手順だと、②と③で更新が失敗しても、更新が完了したflagが1のタイトルだけselectして表示すればよい。

    また、他のユーザーと同じタイミングで同一タイトル名が登録されたとしても、ユニークインデックスを貼っているため、どちらかがエラーになり、タイトルの重複が起きない(更新に失敗したflagがNULLのものは、重複禁止の対象にならないため問題なし)
  • id:taknt
    一般的に DBでの処理は トランザクションを用いて 失敗したら ロールバックするのが普通だが、それが出来ない理由、もしくはしたくない理由があるんでしょうか?
  • id:xxmasaxx
    ③の記述に間違いがありました。

    誤: UPDATE a_table SET flage=1 WHERE id = 保存したbbsテーブルのID;
    正: UPDATE a_table SET flag = 1 WHERE id = 保存したa_tableのID;

    です。失礼しました。
  • id:xxmasaxx
    理由は2つあります。

    一つめは、検索中にebayはトランザクションレスだという記事を見つけて、単に好奇心がわいたこと。
    http://capsctrl.que.jp/kdmsnr/wiki/bliki/?Transactionless

    二つめは、複数のDBサーバー間をまたがってデータを更新した場合、ちゃんとコミットされてるか不安(2相コミット使えと言われそうですが、MySQLでの使用実績がないため使いたくない)というのが理由です。
  • id:taknt
    >①の時にIDは自動採番されますので、それを取得します。

    その取得のための SQL文を知りたかったのです。

    複数同時更新があったとき、NULLのレコードが 複数存在すると思いますが、それを
    どのように対処するのかな?と思いました。

    同一タイトル名ならば、順番(ID)は あまり関係ないかもしれませんけどね。

    複数の更新で、それぞれ みな同じIDを取得しちゃったら BのテーブルのaのIDは
    みな 同じものに なっちゃうのでは?と思います。
  • id:xxmasaxx
    IDの取得は、

    SELECT LAST_INSERT_ID();

    で解決すると思いますが、、、違いますかね。
  • id:xxmasaxx
    他の方からコメントを頂いた(今は何故か消えてる?)のをヒントに問題点をメモ。

    ■LAST_INSERT_ID()の問題点:

    LAST_INSERT_ID()は、コネクションごとに直近のinsertされたIDが保存されてるが、
    コネクションプールを行って一つのコネクションを使い回しすると、IDの整合性がとれない。

    ■解決策:

    その①
    どうしてもLAST_INSERT_ID()を使いたいなら該当処理の時だけ、個別にDBに接続し、コネクションプールを使わない

    その②
    今回のケースは、重複する値が全部同じものなので、takntさんが仰る通り、該当するNULLのものを適当にselectしてIDを取得し、それを使用する

    ということでしょうか。
    あー、なんかややこしいですね。
  • id:JULY
    消したコメントは私です(^^;

    LAST_INSERT_ID() が他のセッションで INSERT が有っても辻褄があう事に気づいて消しました。

    ただ、確かにコネクションプーリングをしていたら、という問題はありそうですね。

    本筋から外れますが、テーブル A を作成する SQL は通りませんね。たぶん、意図するものは下記のようなものだと思います。

    create table a_table (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(80) NOT NULL,
      flag TINYINT NULL,
      UNIQUE INDEX (title, flag)
    );

    ただ、NULL を許容するカラムをインデックスに使うと、重複したレコードを許容してしまうようです。

     http://bugs.mysql.com/bug.php?id=8173

    バグとして報告されたんだけど、SQL92 の規格としてはこれで正しい、という話らしく、status が「Not a bug」になってます。

    flag は NULL を許容するのではなく、DEFAULT 0 でも良いと思います。
  • id:xxmasaxx
    コメントどうも有り難うございます。
    軽い気持ちで質問したのですが、色々考えることでてきますね。。

    頂いたアドバイスで、試行錯誤してみたいと思います。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません