[pgpool-general-jp: 205] Re: 大量のINSERTを行うとデッドロックする
maeda ＠ motion.ne.jp
maeda ＠ motion.ne.jp
2007年 7月 13日 (金) 11:24:57 JST
浅羽 様
いつもお世話になっております。前田です。
五十嵐が長期休暇の為、代わってご報告させていただきます。
修正頂いたpgpoolで再度insertを実行いたしましたが、
残念ながら縮退が発生してしまいました。
また、前回までは縮退の場合、セカンダリがdownしていたのですが、
今回はマスターがdownしております。
テストは複数のセッションで各々１万件のinsert文を実行しようとしております。
エラー状況を以下にご報告させていただきます。
よろしくお願い致します。
＜Session1＞psql -f 3.sql DBNAME で実行
psql:3.sql:5563: ERROR: kind mismatch between backends
HINT: check data consistency between master and secondary
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:3.sql:5563: connection to server was lost
＜Session2＞psql -f 4.sql DBNAME で実行
psql:4.sql:5299: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:4.sql:5299: connection to server was lost
＜MasterDB postgres.log＞
postgres[21933]: [6623-1] LOG: statement: insert into lock_test values(31324,'D');
postgres[21666]: [46963-1] LOG: statement: lock lock_test in row exclusive mode;
postgres[21933]: [6624-1] LOG: statement: commit;
postgres[21933]: [6625-1] LOG: statement: begin;
postgres[21933]: [6626-1] LOG: statement: lock lock_test in row exclusive mode;
postgres[21666]: [46964-1] LOG: statement: LOCK TABLE lock_test IN SHARE ROW EXCLUSIVE MODE
postgres[21933]: [6627-1] LOG: statement: LOCK TABLE lock_test IN SHARE ROW EXCLUSIVE MODE
postgres[21933]: [6628-1] ERROR: deadlock detected
postgres[21933]: [6628-2] DETAIL: Process 21933 waits for ShareRowExclusiveLock on relation 170406893 of database 170405435; blocked by process 21666.
postgres[21933]: [6628-3] Process 21666 waits for ShareRowExclusiveLock on relation 170406893 of database 170405435; blocked by process 21933.
postgres[21666]: [46965-1] LOG: statement: insert into lock_test values(21391,'C');
postgres[21933]: [6629-1] LOG: statement: insert into lock_test values(31325,'D');
postgres[21934]: [1-1] LOG: connection received: host=192.168.2.51 port=44453
postgres[21666]: [46966-1] LOG: unexpected EOF on client connection
postgres[21933]: [6630-1] ERROR: current transaction is aborted, commands ignored until end of transaction block
postgres[21933]: [6631-1] LOG: could not send data to client: Broken pipe
postgres[21933]: [6632-1] LOG: unexpected EOF on client connection
postgres[28964]: [8-1] LOG: unexpected EOF on client connection
postgres[28963]: [9-1] LOG: unexpected EOF on client connection
postgres[28957]: [16-1] LOG: unexpected EOF on client connection
postgres[28962]: [21-1] LOG: unexpected EOF on client connection
postgres[28961]: [13-1] LOG: unexpected EOF on client connection
postgres[28958]: [27-1] LOG: unexpected EOF on client connection
postgres[28955]: [40-1] LOG: unexpected EOF on client connection
postgres[28921]: [63-1] LOG: unexpected EOF on client connection
postgres[28953]: [29-1] LOG: unexpected EOF on client connection
postgres[28954]: [19-1] LOG: unexpected EOF on client connection
postgres[21802]: [40005-1] LOG: unexpected EOF on client connection
postgres[28920]: [24-1] LOG: unexpected EOF on client connection
postgres[28938]: [5-1] LOG: unexpected EOF on client connection
postgres[28952]: [17-1] LOG: unexpected EOF on client connection
postgres[21934]: [2-1] LOG: could not receive data from client: Connection reset by peer
postgres[21934]: [3-1] LOG: unexpected EOF within message length word
＜SecondaryDB postgres.log＞
postgres[29343]: [6623-1] LOG: statement: insert into lock_test values(31324,'D');
postgres[29191]: [46962-1] LOG: statement: lock lock_test in row exclusive mode;
postgres[29343]: [6624-1] LOG: statement: commit;
postgres[29343]: [6625-1] LOG: statement: begin;
postgres[29343]: [6626-1] LOG: statement: lock lock_test in row exclusive mode;
postgres[29343]: [6627-1] LOG: statement: LOCK TABLE lock_test IN SHARE ROW EXCLUSIVE MODE
postgres[29191]: [46963-1] LOG: statement: LOCK TABLE lock_test IN SHARE ROW EXCLUSIVE MODE
postgres[29191]: [46964-1] ERROR: deadlock detected
postgres[29191]: [46964-2] DETAIL: Process 29191 waits for ShareRowExclusiveLock on relation 170406893 of database 170405435; blocked by process 29343.
postgres[29191]: [46964-3] Process 29343 waits for ShareRowExclusiveLock on relation 170406893 of database 170405435; blocked by process 29191.
postgres[29191]: [46965-1] LOG: statement: insert into lock_test values(21391,'C');
postgres[29191]: [46966-1] ERROR: current transaction is aborted, commands ignored until end of transaction block
postgres[29348]: [1-1] LOG: connection received: host=192.168.2.51 port=44454
postgres[29348]: [2-1] LOG: could not receive data from client: Connection reset by peer
postgres[29348]: [3-1] LOG: unexpected EOF within message length word
postgres[29191]: [46967-1] LOG: unexpected EOF on client connection
postgres[29343]: [6628-1] LOG: unexpected EOF on client connection
postgres[18895]: [7-1] LOG: unexpected EOF on client connection
postgres[18896]: [7-1] LOG: unexpected EOF on client connection
postgres[18894]: [22-1] LOG: unexpected EOF on client connection
postgres[18890]: [16-1] LOG: unexpected EOF on client connection
postgres[18891]: [30-1] LOG: unexpected EOF on client connection
postgres[18888]: [37-1] LOG: unexpected EOF on client connection
postgres[18893]: [11-1] LOG: unexpected EOF on client connection
postgres[29294]: [40005-1] LOG: unexpected EOF on client connection
postgres[18887]: [21-1] LOG: unexpected EOF on client connection
postgres[18868]: [56-1] LOG: unexpected EOF on client connection
postgres[18886]: [29-1] LOG: unexpected EOF on client connection
postgres[18867]: [24-1] LOG: unexpected EOF on client connection
postgres[18880]: [5-1] LOG: unexpected EOF on client connection
postgres[18885]: [17-1] LOG: unexpected EOF on client connection
On Wed, 11 Jul 2007 11:22:43 +0900 (JST)
Yoshiyuki Asaba <y-asaba ＠ sraoss.co.jp> wrote:
> 浅羽です。
>
> From: Yoshiyuki Asaba <y-asaba ＠ sraoss.co.jp>
> Subject: [pgpool-general-jp: 196] Re: 大量のINSERTを行うとデッドロックする
> Date: Tue, 10 Jul 2007 14:18:00 +0900 (JST)
>
> > pgpool では今のところデッドロックエラーについて何も考慮していないため、
> >
> > * node-1 ではデッドロックによってトランザクションがアボート
> > * node-2 ではロックが獲得できた
> >
> > という不整合が発生しているようです。
> >
> > そこで、マスタからデッドロックエラー(エラーコード: 40P01)が返ってきた
> > 場合には、他のノードにはトランザクションがエラーになるようなクエリを送
> > るように修正しようかと考えています。
>
> すべてのブランチに修正を入れました。手元の環境ではフェイルオーバが発生
> しないようになりました。以下のように pgbench を使って確認しました。
> もし可能であれば、五十嵐様や他の方の環境でも発生しないかご確認いただけ
> ると助かります。
>
> % cat a.sql
> begin;
> lock table t in row exclusive mode;
> lock table t in share row exclusive mode;
> insert into t values (1);
> end;
>
> % pgbench -n -C -c 2 -t 10 -f a.sql -p 9999 x
> Client 1 aborted in state 2: ERROR: deadlock detected
> DETAIL: Process 23834 waits for ShareRowExclusiveLock on relation 149612 of database 149611; blocked by process 23840.
> Process 23840 waits for ShareRowExclusiveLock on relation 149612 of database 149611; blocked by process 23834.
> transaction type: Custom query
> ...
>
> pgpool をお使いであれば、
>
> % cvs -d :pserver:anonymous ＠ cvs.pgfoundry.org:/cvsroot/pgpool login
> % cvs -d :pserver:anonymous ＠ cvs.pgfoundry.org:/cvsroot/pgpool checkout pgpool
>
> で取得することができます。
>
> なお、[pgpool-general-jp: 187] の 1. を実装したのちに、今月中にでも
> pgpool 3.3.1 か 3.4 でリリースを検討しています。
> --
> Yoshiyuki Asaba
> y-asaba ＠ sraoss.co.jp
> _______________________________________________
> pgpool-general-jp mailing list
> pgpool-general-jp ＠ sraoss.jp
> http://www.sraoss.jp/mailman/listinfo/pgpool-general-jp
pgpool-general-jp メーリングリストの案内