実運用中のテーブルに対し、日次で reindex を実行したところ、時々、デッドロックが発生してしまいます。
どうやら、reindex と update の間でデッドロックが起きているようです。

エラーメッセージを見た感じでは、以下の状態でデッドロックになっているように読めます。
・update がテーブルのインデックスに対するロックを取得し、テーブルに対する RowExclusiveLock を取得しようとしている
・reindex がテーブルに対するロックを取得し、テーブルのインデックスに対する AccessExclusiveLock を取得しようとしている

※ 参考までに、update は select for update してから update しています。

Postgre のバグではないかと思うのですが、デッドロックを回避する方法はありますでしょうか?
(なるだけ、reindex を実行しているプロセスの方に手を入れることで対応できないかと思っています。例えば、reindexの前に事前にロックを取得する、とか・・・)

よろしくお願い致します。

このQ&Aに関連する最新のQ&A

A 回答 (1件)

REINDEXをテーブルごとに行っているならば、以下の手順でデッドロックは回避できます。



BEGIN;
LOCK tbl IN ACCESS EXCLUSIVE MODE;
REINDEX TABLE tbl;
COMMIT;

ただ、REINDEX中は参照も更新も待たされるため、基本的には、オンライン処理と並行しては実行できません。代わりに CREATE INDEX CONCURRENTLY + 古いインデックスを DROP INDEX というような運用を行うことはよくあります。

参考URL:http://www.postgresql.jp/document/current/html/s …
    • good
    • 0
この回答へのお礼

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

私も、検証環境で現象を再現した上で、同様の変更(私の場合は EXCLUSIVE MODE を使いました)を試みたところ、デッドロックは出なくなりました。

CREATE INDEX CONCURRENTLY については、別な問題があるようですので、採用していません。
対象のテーブルがバッチ制御用のテーブルで、一時的に参照がブロックされても直接オンライン処理に影響が出ないため、REINDEX を使っています。

なお、質問時に書き忘れたのですが、参考までに環境について記載します。

vl60_cmn=# SELECT version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)
(1 row)

OSは CentOS 5 を使っています。

お礼日時:2011/04/23 13:28

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qデッドロック(ORA-00060)とメモリの関係

デッドロック(ORA-00060)とメモリの関係

以前、
OS:WindowsServer2003 R2 x64 SP2
DB:Oracle 10.2.0.2.0
の環境でデッドロックが発生したのですが、その際にメモリ使用量も全体の85%以上
使用していた状態でした。(メモリサイズは約6.3GB)

(※デッドロックは、索引の処理を行うにあたり前処理が不足していたことが原因)

そのため、デッドロックと空きメモリの関係を確認したいのですが、
・空きメモリ不足によりデッドロックが発生する可能性があると考えられるでしょうか。
もしくは、
・デッドロックが発生したことにより、空きメモリが消費されてしまうと考えられる
でしょうか。

ご存知の方がおりましたらお教えいただけないでしょうか。
よろしくお願いいたします。

Aベストアンサー

> ・空きメモリ不足によりデッドロックが発生する可能性があると考えられるでしょうか。


  空きメモリ不足云々は、物理的な要因であり、デッドロック とはまったく
 関係ありません。




> ・デッドロックが発生したことにより、空きメモリが消費されてしまうと考えられる
でしょうか。


  ありません。

  ただし、デッドロックにより、データベース関連の処理がスムースに行われなくなり、
 システム全体としてのスループットが落ちます。
  そうすると、システムの中に滞留している状態の情報が増加するので、それらを保持
 すのために メモリが消費されることはあります。

QUPDATE文で既存テーブルへのデータ振り分け登録

テーブルが2つありまして、既存テーブルには
担当者コードが入っておらず(フィールドはあります)、その担当者コードフィールドへ既存テーブルの時刻からそれに合った担当者コードを担当者テーブルからを参照し登録する作業を行いたいのですが、1つのSQL文で可能なのでしょうか?

既存テーブル
ID,InDate(時刻),UserCode(担当者コード)
1,2006-09-01 10:20:30,''
2.2006-09-02 12:10:50,''

担当者テーブル
UserCode,UserName,StartTime,EndTime
111,ABC,2006-09-01 09:00:00,2006-09-01 13:30:00
222,DEF,2006-09-02 09:00:00,2006-09-01 13:30:00

の様なデータが合った場合
既存テーブルのID1のUserCodeへ111
既存テーブルのID2のUserCodeへ222

と入れるようなSQLを作成したいのです。

update 既存テーブル set usercode =
(select b.usercode
from 既存テーブル as a,担当者テーブル as b
where a.InDate > b.starttime
and a.InDate < b.endtime);

と作成はしてみたものの
ERROR: more than one row returned by a subquery used as an expression
とエラー表示されます。
複数のレコードがあるからエラー?ってことなのでしょうか。

ver:PostgreSQL 8.1

どのようにすると、可能になるか教えてほしいのですが、どうぞよろしくお願いします。(説明がうまく出来ず申し訳ありません…)

テーブルが2つありまして、既存テーブルには
担当者コードが入っておらず(フィールドはあります)、その担当者コードフィールドへ既存テーブルの時刻からそれに合った担当者コードを担当者テーブルからを参照し登録する作業を行いたいのですが、1つのSQL文で可能なのでしょうか?

既存テーブル
ID,InDate(時刻),UserCode(担当者コード)
1,2006-09-01 10:20:30,''
2.2006-09-02 12:10:50,''

担当者テーブル
UserCode,UserName,StartTime,EndTime
111,ABC,2006-09-01 09:00:00,2006-09-01 13:30:00
...続きを読む

Aベストアンサー

担当者テーブルのUserCode=222のEndTimeは、9月2日のデータの誤りですね?

update文にfrom句を使う指定方法があり、これを使うと簡単です。

update 既存テーブル
set UserCode=担当者テーブル.UserCode
from 担当者テーブル
where InDate between StartTime and EndTime

QSELECT文でのデッドロックに対しての対処方

先日よりデッドロックが発生するようになり、確認の為にトレースログを
抽出するように設定かけたのですが、UPDATE文とSELECT文がぶつかり
デッドロックが発生している事がわかりました。

しかし、UPDATE文にはトランザクションをかけていますが
SELECT文には特にロックかけていない為、どうして起こっているのか
わかっていません。

SELECT文を読んでいる時にUPDATE文によって
レコードに変更があった為、デッドロックが発生したのでしょうか?

SELECT文にはUNLOCKをつけた方がいいのでしょうか?



プログラムはDELPHI
DBはSQLSERVER2000になります。

お忙しい所申し訳ありません。
宜しくお願い致します。

Aベストアンサー

#6です。
気になったの書き込みます。

参照側で考えていましたが、更新側のロック範囲は適正でしょうか?
不必要に広い範囲に排他ロックを掛けていないでしょうか。

例えば更新が select xx from yy where zz>aa for browse のようになっていると広範囲にロックが確保され要注意です。
また、更新がバッチであれば、途中コミットして排他時間を短くするなりの対策が考えられます。
とにかく参照処理も更新処理も出来るだけ範囲を小さくされることをお勧めします。

QPHPからのpostgresDBのテーブルに対し『\copy』の実行はできますか?

$conn = pg_connect("host=xxxxxx user=postgres port=5432 dbname=xxxx");
$result = pg_Exec ($conn, $SQLSTR) ;

HTML上から上記のようにphpの関数 pg_Exec を使用して、
copy文を実行したいのですが、うまくできません。
phpからcopy文を実行することはできないのでしょうか?
コピー文:"\copy tablename with oid to /tmp/output"
ちなみに、$SQLSTRにselectやupdateなどのSQL文をいれると
うまくいきます。

使用している環境は、OSがlinuxで、APACHE+php3+postgres6.?? です。

Aベストアンサー

> バックアップ用に、OIDごとコピーしようと思っていたのですが、oidもコピーできるのでしょうか?

可能かと思います。
COPY tabename TO '/foo/foo.csv' WITH OIDS DELIMITERS ','
とかでいけるのではないでしょうか。
※度々ですが6.*系でできるのかどうかは判りかねます。

> テーブルに対してユーザapacheの権限を与えるには
どうしたらよいでしょうか?

具体的な命令文についてはURLなどのマニュアルをご覧下さい。
場合によってはテーブルに対する云々より、pg_hba.confなどをいじる必要があるかもしれません。

参考URL:http://www.postgresql.jp/document/pg653doc/j/user/sql-grant.htm

Qreindex と update のデッドロック

実運用中のテーブルに対し、日次で reindex を実行したところ、時々、デッドロックが発生してしまいます。
どうやら、reindex と update の間でデッドロックが起きているようです。

エラーメッセージを見た感じでは、以下の状態でデッドロックになっているように読めます。
・update がテーブルのインデックスに対するロックを取得し、テーブルに対する RowExclusiveLock を取得しようとしている
・reindex がテーブルに対するロックを取得し、テーブルのインデックスに対する AccessExclusiveLock を取得しようとしている

※ 参考までに、update は select for update してから update しています。

Postgre のバグではないかと思うのですが、デッドロックを回避する方法はありますでしょうか?
(なるだけ、reindex を実行しているプロセスの方に手を入れることで対応できないかと思っています。例えば、reindexの前に事前にロックを取得する、とか・・・)

よろしくお願い致します。

実運用中のテーブルに対し、日次で reindex を実行したところ、時々、デッドロックが発生してしまいます。
どうやら、reindex と update の間でデッドロックが起きているようです。

エラーメッセージを見た感じでは、以下の状態でデッドロックになっているように読めます。
・update がテーブルのインデックスに対するロックを取得し、テーブルに対する RowExclusiveLock を取得しようとしている
・reindex がテーブルに対するロックを取得し、テーブルのインデックスに対する AccessExclusiveLock を取得しよう...続きを読む

Aベストアンサー

REINDEXをテーブルごとに行っているならば、以下の手順でデッドロックは回避できます。

BEGIN;
LOCK tbl IN ACCESS EXCLUSIVE MODE;
REINDEX TABLE tbl;
COMMIT;

ただ、REINDEX中は参照も更新も待たされるため、基本的には、オンライン処理と並行しては実行できません。代わりに CREATE INDEX CONCURRENTLY + 古いインデックスを DROP INDEX というような運用を行うことはよくあります。

参考URL:http://www.postgresql.jp/document/current/html/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

Q外部キーが設定されているテーブルのupdateについて

PostgreSQL8.24を利用しています。

pgADMINIIIでテーブルを作成しました。
【TABLE_A】と【TABLE_B】があります。
【TABLE_A】の【ID】が【TABLE_B】の【ID2】が外部キーとして設定してあります。

CONSTRAINT TABLE_B_fkey FOREIGN KEY (ID2)
REFERENCES TABLE_A (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
上記のように記述されています。

SQL文のUPDATEを使い、IDが「01」を「05」に更新したいのですが、
以下のように記述するとエラーになります。
どのようなUPDATE文を記述すればよろしいのでしょうか。

UPDATE TABLE_A INNER JOIN TABLE_B on TABLE_A.ID=TABLE_B.ID2
SET TABLE_A.ID = '05',TABLE_B.ID2 = '05' WHERE TABLE_A.ID='01'"

よろしくお願いいたします。

PostgreSQL8.24を利用しています。

pgADMINIIIでテーブルを作成しました。
【TABLE_A】と【TABLE_B】があります。
【TABLE_A】の【ID】が【TABLE_B】の【ID2】が外部キーとして設定してあります。

CONSTRAINT TABLE_B_fkey FOREIGN KEY (ID2)
REFERENCES TABLE_A (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
上記のように記述されています。

SQL文のUPDATEを使い、IDが「01」を「05」に更新したいのですが、
以下のように記述するとエラーになります。
どのようなU...続きを読む

Aベストアンサー

失礼ながら、いろいろな勘違いをしているようです。

update文で、複数の表を更新することはできません。あくまでも、1表だけです。
他の表の列値で更新することは可能で、サブクエリを使う方法以外に、

update 表1
set 列2=表2.列2
from 表2
where 表1.列1=表2.列1

のように、ジョインで得られた結果で更新可能ですが、更新対象はこの例では表1です。
updateでこの例のように、複数表を指定する構文を持っているRDBMSがあり、RDBMSにより構文が違う場合もあります。

>UPDATE TABLE_A INNER JOIN TABLE_B on TABLE_A.ID=TABLE_B.ID2

複数のRDBMSを知っていますが、このような構文は、見たことがありません。なぜ、このような構文を思いついたのでしょうか?

外部キー(参照制約)についても、正しく理解されていないようです。
参照制約は、以下の制約で親子間の不整合を防ぎます。これは、階層DBやネットワーク型DBの特性を、RDBMSに取り入れたものです。
親子間の不整合の発生をRDBMS側で防止してくれる一方で、オーバヘッドがあったり、運用上の注意(親の表を削除する場合、子の表を先に削除など)が必要になります。
アプリケーション側で不整合を発生させないようにできるなら、わざわざ参照制約を使う必要はありません。

表定義でのディフォルトの指定(NO ACTION)での動作は、次のようになります。

(1)子のいる親の主キーは、更新できない。
→子の外部キーを一旦、null等に更新し、子のいない状態で親の主キーを更新。その後、子の外部キーを親の主キーと同じ値に更新する。

(2)子のいる親は、削除できない。
→子を削除してから、親を削除

表定義で、「ON UPDATE CASCADE」、「ON DELETE CASCADE」を指定した場合は、次のような動作になります。

(1)親の主キーを更新すると、子の外部キーも自動的に更新。
(2)親を削除すると、子も削除。

どちらの動作をさせたいでしょうか?
それにより、表定義を変えるか、SQLの発行順などが変わってきます。

=====例1(NO ACTION)=====
1.定義
create table tbl1
(pkey char(2) primary key,
data varchar(10));
create table tbl2
(pkey int primary key,
fkey char(2),
data varchar(10),
constraint tbl1_tbl2
foreign key(fkey) references tbl1(pkey)
match simple
on update no action
on delete no action
)
;
create index tbl2idx1 on tbl2(fkey);

2.操作
(1) 子のいない親の主キー更新
update tbl1
set pkey='10'
where pkey='04';

(2) 子のいる親の主キー更新
-- 子を更新して、親との関係を一時的になくす
update tbl2
set fkey=null
where fkey='03';
-- 親を更新
update tbl1
set pkey='05'
where pkey='03';
-- 子を親に対応付け
update tbl2
set fkey=tbl1.pkey
from tbl1
where fkey is null
and tbl1.pkey='05';
または
update tbl2
set fkey='05'
from tbl1
where fkey is null;

=====例2(CASCADE)=====
1.定義
create table tbl1
(pkey char(2) primary key,
data varchar(10));
create table tbl2
(pkey int primary key,
fkey char(2),
data varchar(10),
constraint tbl1_tbl2
foreign key(fkey) references tbl1(pkey)
match simple
on update CASCADE -- no action
on delete CASCADE -- no action
)
;
create index tbl2idx1 on tbl2(fkey);

2.操作
(1)親の更新の背景で、子も更新
update tbl1
set pkey='05'
where pkey='03';

※子の表に対する操作は、SQL上はないが、RDBMSにより更新されている。

失礼ながら、いろいろな勘違いをしているようです。

update文で、複数の表を更新することはできません。あくまでも、1表だけです。
他の表の列値で更新することは可能で、サブクエリを使う方法以外に、

update 表1
set 列2=表2.列2
from 表2
where 表1.列1=表2.列1

のように、ジョインで得られた結果で更新可能ですが、更新対象はこの例では表1です。
updateでこの例のように、複数表を指定する構文を持っているRDBMSがあり、RDBMSにより構文が違う場合もあります。

>UPDATE TABLE_A INNER JO...続きを読む

Qデッドロックが発生します。

sqlserverについて、アプリを実行すると「40001(1205)」というエラーが発生します。デッドロックが発生しているようです。原因が分かりません。どのような場合に発生するのでしょうか。?

Aベストアンサー

SQL Serverや、IBMのDB2などでは「ロック方式」というものが採用されておりまして、これは対象リソースに対してロックを取得し、同時実行に関する問題がないことを確認してから、実際のデータを読み書きしていく方式です。

よってただSELECTを発行しても、場合によってはトランザクション終了時までLOCKが発生します。

これを回避するにはSELECT文に WITH (NOLOCK)オプションを指定すると解決いたしますが、処理中に変更や削除が行われるテーブルに対してはオススメ致しません(RASISが損なわれる可能性があります)

サンプル
SELECT
DATA_A, DATA_B, DATA_C
FROM
TEST_TABLE WITH (NOLOCK)
WHERE
DATA_A = 'aaaa'

Qテーブルにはったインデックスは、ビューに対しても効力があるのか

環境:RedHat Linux AS3.0 / PostgresSQL 7.3.6

Publicスキーマにインデックス付きのテーブルを作成し、
複数のスキーマにビューを作成してそのテーブルをそのまま参照したいと思っています。
(スキーマの数が非常に多いので、実体をひとつにし、
 ディスク容量を抑えるのが目的です。)

環境のイメージは以下の通りです。

◆Publicスキーマにテーブル作成-------
create table TEST_TABLE (
id int,
data varchar
);

◆test_tableにインデックスを作成-------
create unique index TEST_KEY on TEST_TABLE (
id
);

◆test_schemaスキーマを作成-------
create schema TEST_SCHEMA;

◆test_schemaスキーマにビューを作成-------
create view TEST_SCHEMA.TEST_VIEW as
select
id,
data
from TEST_TABLE
;


このような環境にて「TEST_VIEW」にSELECTをかけた場合、
「TEST_KEY」は踏襲されるのでしょうか?
ビューに対してインデックスは作成できないようなので、
テーブルに対してはられたインデックスはビューでも生きている
のではないかと考えたのですが、
上記認識で合っているかどうか
ご存知の方がいらっしゃいましたらご教授頂けると助かります。
宜しくお願い申し上げます。

環境:RedHat Linux AS3.0 / PostgresSQL 7.3.6

Publicスキーマにインデックス付きのテーブルを作成し、
複数のスキーマにビューを作成してそのテーブルをそのまま参照したいと思っています。
(スキーマの数が非常に多いので、実体をひとつにし、
 ディスク容量を抑えるのが目的です。)

環境のイメージは以下の通りです。

◆Publicスキーマにテーブル作成-------
create table TEST_TABLE (
id int,
data varchar
);

◆test_tableにインデックスを作成-------
create unique ind...続きを読む

Aベストアンサー

ビューは仮想的なテーブルで、呼ばれたときに、実際に定義部分に書かれた、SELECT文が実行されます。

したがって、踏襲もなにも・・・
select
id,
data
from TEST_TABLE

が、実行されるんですから、インデックスは活きます。

Qデッドロックはどうやったら、かかる?

SQLServer2000
を使っています。

デッドロックって、どういう状況下
で起こるものなのでしょうか?

具体例をあげて教えてください。

Aベストアンサー

デットロックは以下のような処理を順に
行うと発生します。

(1)ユーザーXがあるレコードAを更新する。
 コミットしない。
(2)ユーザーYがあるレコードBを更新する。
 コミットしない。
(3)ユーザーXがレコードBを更新。
(4)ユーザーYがレコードAを更新。

このとき、(3)の処理は、(2)が同じレコードを
ロックしているために(2)が終わるまで
待たされてしまいます。
(2)のロックは、ユーザーYがコミットするまで
解除されませんが、ユーザーYは
(4)を処理しています。
この、(4)の処理は、(1)が同じレコードを
ロックしているので(1)が終わるまで、
待たされてしまいます。
しかし、その(1)のロックを解除するのに、
ユーザーXが(3)を処理中なので、
(3)が終わるまで、ロック解除できません。
ところが、その(3)の処理が終わるには......
最初に戻っていまいますね。
これではいつまでたっても
処理が終了しません。
デットロックとはこんな状態のことを
いいます。

QINSERT,UPDATEしなかったレコードだけをDELETEしたい

質問させてください、
CSV形式の10万行のデータをADO経由でINSERT,UPDATEしています
そのときUPDATEもINSERTもされなかったレコードをDELETEしているんですが、
今はINSERT,UPDATE処理が終わったあと、全レコードのrecordsetを1レコードずつ
csvファイルの1行と比較しています。

他に良い方法はないでしょうか?

csvファイルには
category, item, price
postgresのテーブル名はt_zaikoとして
id(INT[NOT_NULL, default_nextval, primary_key]), category(SMALL_INT), item(TEXT), price(SMALL_INT)
とします。

Aベストアンサー

#1の方のやり方でいいと思いますが、何かそれではまずいということなら

レコード項目にタイムスタンプ持たせといてインサート、アップデート時にその時刻も更新しておいて、処理開始時刻より前のレコードを一括削除すればよいのでは?


人気Q&Aランキング

おすすめ情報