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

2023/10/19 11:39

このQ&Aのポイント

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

ベストアンサー

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

2010/09/13 18:10

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');

maenoina
お礼率31% (26/82)

MySQL
回答数1
ありがとう数4

みんなの回答 （1）
専門家の回答

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

ベストアンサー

moousi
ベストアンサー率70% (21/30)

2010/09/14 00:32 回答No.1

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

質問者

お礼 2010/09/16 19:26

お礼が遅れ申し訳ありません。こちらでも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 文のうち、１行目～３行目までを抜粋して実行すると、ちゃんと CREATE TABLE され、INSERT され、ALTER TABLE される事を確認しました。ところが、４行目までを一気に実行しようとすると、 ------ SQL実行中に以下のエラーが発生しました。エラーコード：207 [Microsoft][ODBC SQL Server Driver][SQL Server]列名 'TEST_FIELD3' は無効です。 SQLステータス：S0022 ------ となってしまい、UPDATE で初期値を埋める事ができません。しかも、UPDATE に失敗するどころか、２行目の 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 ------ の２行を実行してみると、やはり UPDATE は失敗し、前述と同じエラーが発生します。またこの場合、ALTER TABLE の実行結果も反映されていません。（つまり、TEST_FIELD3 が列追加されていない）もちろん、ALTER TABLE だけを実行した場合には、ちゃんと列は追加されます。その後に、UPDATE を実行すれば、ちゃんと追加列に初期値がセットされます。どうやら、「一回の SQL 文の実行の中で、ALTER TABLE によって新設した列に対しては、UPDATE などでのアクセスはすぐにはできない」のではないか？という状況のようなのです。一回の SQL 文の実行の中において、何らかのトランザクション動作っぽい挙動を感じます。つまり、ALTER TABLE で追加された列は、その時点ではまだ完全にシステムに認知されていないため、直後の UPDATE 文で認識できずに失敗するのではないか？と。そして、そこでのエラー発生が、ロールバック的に実行した処理をキャンセルしてしまうため、結果として、ALTER TABLE が実行されなかった事になったり、INSERT が実行されなかった事になったりしているのではないか？と思う次第です。考えられる回避策としては、SQL 文を別々に作成し、個別に実行すれば良いだけの事なのですが、できれば、SQL 文一つにまとめたいと考えています。どなたか、こういった現象に対する原因・理由の説明、或いは回避策など、何か情報をお持ちの方はいらっしゃいませんでしょうか？宜しくお願いします。
- ベストアンサー
- SQL Server
トランザクションとテーブルロック
初心者の質問で申し訳ありません。トランザクションで複数のテーブルを更新する時、テーブルロックはかかっているのでしょうか。具体的に言うと、 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はあらかじめロックしておく必要があるのでしょうか。アドバイスお願いいたします。
- ベストアンサー
- SQL Server
insert文等で「`」の有無で失敗するテーブルとしないテーブルがある。
XAMPP(winXP、PHP4)を使っています。 Aのテーブルでは $query= "insert into table (a,b,c,d) values('1','2','3','4')"; $result = mysql_query($query); でデータが追加されるのに、Bのテーブルでは失敗しました。今日ずっと何が間違っているのかわからなかったのｄですが、 $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で命令文を書いています。１．[T_テーブル１]というテーブルに[フィールドA]というフィールドをデータ型：数値型フィールドサイズ：単精度浮動小数点型小数点以下表示桁数：２位まで表示で追加したいです。 →ALTER TABLE T_テーブル１ ADD COLUMN フィールドA FLOAT4 これに少数点以下桁数を設定するにはどう書けばよろしいでしょうか？２．[T_テーブル１]というテーブルに[フィールドB]というフィールドをデータ型：数値型フィールドサイズ：長整数型で追加しました。 →ALTER TABLE T_テーブル１ ADD COLUMN フィールドA INT 「説明」の箇所に説明書きも入れたいのですがここでの設定は可能でしょうか？以上２点につきましてご指導頂きたくよろしくお願い致します。
- 締切済み
- その他(データベース)
複数テーブルで ID の一意性を保つ
Debian GNU/Linux 3.1 で psql 8.1.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) スペースが詰まって読みづらい箇所もありますが、よろしくお願いします。
- 締切済み
- PostgreSQL
マルチテーブル・インサート
ご教授よろしくお願い致します。現在、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】です。
- 締切済み
- PostgreSQL
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　　'なにぬ'
- ベストアンサー
- SQL Server
MySql5でのネクストロックについて
mysql5(repeatable read)のロック機構について勉強しています。 [sample1(主キー:id)] +----+---+ | id | point | +--+-----+ | 10 | 10 | | 15 | 15 | | 20 | 20 | | 25 | 25 | | 30 | 30 | +--+-----+ 上記のようなテーブルに対して、以下の２つのスレッド(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; しかし、以下の２つのスレッド(A,B)にて SQLを発行した際、☆2部分は即時に実行されません。(スレッドＡのロック解除待ちになります) (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として、設定を読み込み直してから試してみましたが結果はかわりませんでしたこのような場合の原因と対策を教えて頂ければ幸いです。足りていない情報は随時補足させていただきます。是非回答宜しくお願い致します。
- 締切済み
- MySQL