• ベストアンサー
  • 困ってます

mysqlのalter table中のロックについてです。

mysqlのalter table中のロックについてです。 下記のように、alterでテーブルを再構築中に同じテーブルにinsertが実行された場合、 接続Bのinsertはブロックされるかと思うのですが、テーブルが大きくalterに時間がかかる場合、 タイムアウトなどは発生するのでしょうか。 またもし発生する場合、タイムアウト値の設定などの確認方法はあるのでしょうか。 1.接続A  alter table table1 add columnB int(11) ; 2.接続B(接続Aのalter実行中)  insert into table1(columnA) values('aaa');

共感・応援の気持ちを伝えよう!

  • MySQL
  • 回答数1
  • 閲覧数1392
  • ありがとう数4

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.1
  • moousi
  • ベストアンサー率70% (21/30)

ロック待ち関連のパラメータは、 innodb_lock_wait_timeout table_lock_wait_timeout だと思うんですが、DDLでロックされているテーブルのロック待ちに関しては、上記パラメータが関係ないようでした。 私の設定値は、デフォルトの50(両方とも) ※確認方法 show variables like '%timeout%'; 2パターン、やってみました。 セッション1 alter table table1 add primary key(c1); /* 20分間 */ セッション2 start transaction; insert into a values (100000001); /* セッション1終了後、ロック解除 */ rollback; セッション1 alter table table1 drop primary key; /* 10分経過 */ セッション2 lock tables table1 write; /* おそらく、セッション1終了後ロック解除するのでしょう */

共感・感謝の気持ちを伝えよう!

質問者からのお礼

お礼が遅れ申し訳ありません。 こちらでもinnodb_lock_wait_timeoutやtable_lock_wait_timeoutをいじって、なぜ適用されないのだろうと悩んでいました。 ブログシステムを運用しているのですが、記事数が予想外に増えて膨れあがったテーブルへの、 ALTERでのテーブル変更を予定しているのですが、大変助かりました。 ありがとうございました。

関連するQ&A

  • SQL Server(MSDE2000) : ALTER TABLE

    SQL Server(MSDE2000) : ALTER TABLE した項目に対し、直後に UPDATE で値をセットできない 既存のテーブルを仕様変更するため、列を追加し、初期値で埋める SQL 文を書こうとしていますが、うまく動かず、エラーとなってしまい、原因が判らず困っています。 サンプル SQL 文: ------ CREATE TABLE TEST_TABLE ( TEST_FIELD1 SMALLINT, TEST_FIELD2 SMALLINT) INSERT INTO TEST_TABLE VALUES( 1, 2 ) ALTER TABLE TEST_TABLE ADD TEST_FIELD3 SMALLINT UPDATE TEST_TABLE SET TEST_FIELD3 = 3 ------ ※テスト毎に、必ず DROP TABLE TEST_TABLE されている事が前提です。 上記 SQL 文のうち、1行目~3行目までを抜粋して実行すると、ちゃんと CREATE TABLE され、INSERT され、ALTER TABLE される事を確認しました。 ところが、4行目までを一気に実行しようとすると、 ------ SQL実行中に以下のエラーが発生しました。 エラーコード:207 [Microsoft][ODBC SQL Server Driver][SQL Server]列名 'TEST_FIELD3' は無効です。 SQLステータス:S0022 ------ となってしまい、UPDATE で初期値を埋める事ができません。 しかも、UPDATE に失敗するどころか、2行目の INSERT から以降が結果に反映されなくなるという状況に陥ってしまいます。 また、既存のテーブルの仕様変更が目的なので、その状況に近づけるために、まず、 ------ CREATE TABLE TEST_TABLE ( TEST_FIELD1 SMALLINT, TEST_FIELD2 SMALLINT) INSERT INTO TEST_TABLE VALUES( 1, 2 ) ------ を実行し、既存のテーブル(とレコード内容)が存在する状態を作り出された事を、ツール等で確認してから、 ------ ALTER TABLE TEST_TABLE ADD TEST_FIELD3 SMALLINT UPDATE TEST_TABLE SET TEST_FIELD3 = 3 ------ の2行を実行してみると、やはり UPDATE は失敗し、前述と同じエラーが発生します。 またこの場合、ALTER TABLE の実行結果も反映されていません。(つまり、TEST_FIELD3 が列追加されていない) もちろん、ALTER TABLE だけを実行した場合には、ちゃんと列は追加されます。 その後に、UPDATE を実行すれば、ちゃんと追加列に初期値がセットされます。 どうやら、「一回の SQL 文の実行の中で、ALTER TABLE によって新設した列に対しては、UPDATE などでのアクセスはすぐにはできない」のではないか?という状況のようなのです。一回の SQL 文の実行の中において、何らかのトランザクション動作っぽい挙動を感じます。 つまり、ALTER TABLE で追加された列は、その時点ではまだ完全にシステムに認知されていないため、直後の UPDATE 文で認識できずに失敗するのではないか?と。そして、そこでのエラー発生が、ロールバック的に実行した処理をキャンセルしてしまうため、結果として、ALTER TABLE が実行されなかった事になったり、INSERT が実行されなかった事になったりしているのではないか?と思う次第です。 考えられる回避策としては、SQL 文を別々に作成し、個別に実行すれば良いだけの事なのですが、できれば、SQL 文一つにまとめたいと考えています。 どなたか、こういった現象に対する原因・理由の説明、或いは回避策など、何か情報をお持ちの方はいらっしゃいませんでしょうか? 宜しくお願いします。

  • トランザクションとテーブルロック

    初心者の質問で申し訳ありません。 トランザクションで複数のテーブルを更新する時、テーブルロックはかかっているのでしょうか。 具体的に言うと、 BEGIN TRAN a  INSERT INTO A VALUES('data1')  INSERT INTO B VALUES('data1','data2')  INSERT INTO C VALUES('data1','data2','data3') COMMIT TRAN a とするとき、テーブルA,B,Cはあらかじめロックしておく必要があるのでしょうか。 アドバイスお願いいたします。

  • insert文等で「`」の有無で失敗するテーブルとしないテーブルがある。

    XAMPP(winXP、PHP4)を使っています。 Aのテーブルでは $query= "insert into table (a,b,c,d) values('1','2','3','4')"; $result = mysql_query($query); でデータが追加されるのに、Bのテーブルでは失敗しました。 今日ずっと何が間違っているのかわからなかったのdですが、 $query= "insert into table (`a`,`b`,`c`,`d`) values('1','2','3','4')"; $result = mysql_query($query); とすると成功しました。 なぜAのテーブルでは「`」が無くても成功したのに、Bのテーブルでは必要なのでしょうか。 宜しくお願いします。

    • ベストアンサー
    • MySQL
  • Access2003_ALTER TABLE構文

    Accessのテーブルにフィールドを追加したくSQLで命令文を書いています。 1.[T_テーブル1]というテーブルに[フィールドA]というフィールドを データ型:数値型 フィールドサイズ:単精度浮動小数点型 小数点以下表示桁数:2位まで表示 で追加したいです。 →ALTER TABLE T_テーブル1 ADD COLUMN フィールドA FLOAT4 これに少数点以下桁数を設定するにはどう書けばよろしいでしょうか? 2.[T_テーブル1]というテーブルに[フィールドB]というフィールドを データ型:数値型 フィールドサイズ:長整数型 で追加しました。 →ALTER TABLE T_テーブル1 ADD COLUMN フィールドA INT 「説明」の箇所に説明書きも入れたいのですがここでの設定は可能でしょうか? 以上2点につきましてご指導頂きたくよろしくお願い致します。

  • 複数テーブルで ID の一意性を保つ

    Debian GNU/Linux 3.1 で psql 8.1.2 を使用しています。 下記の要領でテーブルを作成し、2つのテーブルでIDが重ならないようにしようと意図しました。 CREATE TABLE t1 ( t1_id int2 UNIQUE NOT NULL, t1_name text NOT NULL ); NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_t1_id_key" for table "t1" CREATE TABLE CREATE TABLE t2 ( t2_id int2 UNIQUE NOT NULL, t2_name text NOT NULL ); NOTICE: CREATE TABLE / UNIQUE will create implicit index "t2_t2_id_key" for table "t2" CREATE TABLE CREATE FUNCTION unique_t12_id(int2) RETURNS BOOLEAN AS 'SELECT NOT ( EXISTS(SELECT * FROM t1 WHERE t1_id = $1) AND EXISTS(SELECT * FROM t2 WHERE t2_id = $1) );' LANGUAGE SQL; CREATE FUNCTION ALTER TABLE t1 ADD CONSTRAINT con_unique_t12 CHECK (unique_t12_id(t1_id)); ALTER TABLE ALTER TABLE t2 ADD CONSTRAINT con_unique_t12 CHECK (unique_t12_id(t2_id)); ALTER TABLE ところが実際は上手くいきませんでした。 INSERT INTO t1 VALUES(1, 'test1'); INSERT 0 1 INSERT INTO t2 VALUES(2, 'test2'); INSERT 0 1 INSERT INTO t2 VALUES(2, 'test3'); ERROR: duplicate key violates unique constraint "t2_t2_id_key" # ここまでは期待通りの挙動です INSERT INTO t2 VALUES(1, 'test4'); INSERT 0 1 ここで制約が働いて欲しかったのですが、素通りでINSERTされています。 下記のように、操作後の関数の戻り値は「偽」なのでこの操作は制約に引っかかると思うのですが 何故意図したように動かないのでしょうか。 SELECT *,unique_t12_id(t2_id) FROM t2; t2_id | t2_name | unique_t12_id -------+---------+--------------- 2 | test2 | t 1 | test4 | f (2 rows) スペースが詰まって読みづらい箇所もありますが、よろしくお願いします。

  • マルチテーブル・インサート

    ご教授よろしくお願い致します。 現在、table_aにInsertされたカラム(no,name)のデータを同時にtable_bにも反映するSQL文を考えております。 【table_a】              no | name | price               ==============         005  apple   500         001  banana  250         【table_b】 no | name | color ================= 005  apple   red 001  banana  yellow Insertをそれぞれのテーブルで実行すれば、もちろん反映はできるのですが一度のInsert文でできるのならそれにこしたことはありません。 いろいろ調べた結果、下記のサイトでできるようなことが記載してあったため、実行したのですが、『ALL』がシンタックスエラーとなってしまいます。 そのため、postgresのマニュアルを確認してみたところ、Insert文にALL句の指定は特別記載されていなかったため、できないとも思っているのですが… サイト↓ http://www.atmarkit.co.jp/fdb/rensai/sqlclinic12/sqlclinic12_1.html サイトを参考にしたSQL文 『INSERT ALL INTO table_a(no,name) VALUES(005,apple) INTO table_b(no,name) VALUES(005,apple); 』 何か良い方法をご存知の方がいましたら、ご教授よろしくお願い致します。同じことをUpadteでもやらなければいけないのですが、それはもう少し自分で考えてみます。 只、できないことが明白な場合は、ご指摘を頂けると有難いです。 環境としましては、 OS 【Windows xp】 DB 【PostgreSQL 8.2】 です。

  • ALTER TABLE mytable CHANGE

    mytableというテーブルのaカラムの型と名前を変更するのは ALTER TABLE mytable CHANGE a new_a INT; でできましたが、今度はnew_aをbにすると動時にTINYINT(4)にしたいのですが、SQLの文法が違うようだったので正しいものを教えてください。 ALTER TABLE mytable CHANGE new_a b TINYINT(4); でなく、何なのですか?

    • ベストアンサー
    • MySQL
  • IDENTITY列の複数テーブルへのINSERT

    テーブルAへINSERTをした場合のIDENTITY列の値を 同じトランザクション内で テーブルBの別のフィールドへINSERTしたいのですが、 どのようにSQLを書けばよいのか、困っています。 どうか、ご教示お願い致します。 INSERT INTOTABLE_A (NAME_A) VALUES ('あいうえお') ↓ INSERT INTOTABLE_A (A_ID,NAME_B) VALUES (取得したA_ID, 'さしす') INSERT INTOTABLE_A (NAME_A) VALUES ('かきくけこ') ↓ INSERT INTOTABLE_A (A_ID,NAME_B) VALUES (取得したA_ID, 'たちつ') INSERT INTOTABLE_A (NAME_A) VALUES ('さしすせそ') ↓ INSERT INTOTABLE_A (A_ID,NAME_B) VALUES (取得したA_ID, 'なにぬ') TABLE_A ID NAME_A 5  'あいうえお' 6  'かきくけこ' 7  'さしすせそ' TABLE_B ID A_ID NAME_B 1  5  'さしす' 2  6  'たちつ' 3  7  'なにぬ'

  • MySql5でのネクストロックについて

    mysql5(repeatable read)のロック機構について勉強しています。 [sample1(主キー:id)] +----+---+ | id | point | +--+-----+ | 10 | 10 | | 15 | 15 | | 20 | 20 | | 25 | 25 | | 30 | 30 | +--+-----+ 上記のようなテーブルに対して、以下の2つのスレッド(A,B)にて SQLを発行した際、☆1部分は即時に実行されます。 (A) start transaction; (A) select * from sample1 where id >= 15 and id <= 25 for update;  (B) start transaction;  (B) insert into sample1 values(14, 14); #☆1  (B) rollback; (A) rollback; しかし、以下の2つのスレッド(A,B)にて SQLを発行した際、☆2部分は即時に実行されません。(スレッドAのロック解除待ちになります) (A) start transaction; (A) select * from sample1 where id >= 15 and id <= 25 for update;  (B) start transaction;  (B) insert into sample1 values(26, 26); #☆2 (A) rollback;  (B) rollback; ネクストロックという機構があることは存じているのですが、その具体的な仕組みがよくわかりません。 どうして、☆1が即時に実行され、☆2はロックがかかってしまうのでしょう?

    • ベストアンサー
    • MySQL
  • mysql alter table 終わらない

    ALTER TABLEの処理が全然終わらなくなってしまったので、質問させて下さい。 下記のような状態です。 ・テーブルの行数は962362 ・topコマンドで見たとき、普段mysqldのCPU使用率が50~100%なのですが、ALTER TABLE後に確認すると2%前後になる ・試した処理はdrop indexとadd column ・mysqlを再起動しようにもstopするのに数分かかる ・nginxを一度stopし、負荷の少ない状態でも同じことを試しましたが結果はかわりませんでした ・検索してみると、key_buffer_sizeが少なすぎることが原因という記事をみかけたので、試しにkey_buffer=512Mとして、設定を読み込み直してから試してみましたが結果はかわりませんでした このような場合の原因と対策を教えて頂ければ幸いです。 足りていない情報は随時補足させていただきます。 是非回答宜しくお願い致します。