Обсуждение: Pgsql jdbc driver 8.3 Build 603: Commit deadlock

Поиск
Список
Период
Сортировка

Pgsql jdbc driver 8.3 Build 603: Commit deadlock

От
"Shawn Chasse"
Дата:
Hi,
I'm running the 8.3 build 603 jdbc4 driver on a windows server machine running postgres 8.3.3, hibernate 3.2, and java 1.6.

Recently I have run into a problem where a commit call in my java code will propogate down to the driver and eventually deadlock waiting for a response. The specific call stack is as follows: 

"Analyzer" daemon prio=6 tid=0x67de4400 nid=0x1414 runnable [0x6890f000..0x6890fc18]
       java.lang.Thread.State: RUNNABLE
            at java.net.SocketInputStream.socketRead0(Native Method)
            at java.net.SocketInputStream.read(SocketInputStream.java:129)
            at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
            at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
            at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
            at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259)
            at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1166)
            at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:351)
                  - locked <0x0a1db010> (a org.postgresql.core.v3.QueryExecutorImpl)
            at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2674)
            at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
            at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
            at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
            at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
            at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
            at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:222)
            at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2229)
            at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2665)
            at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:60)
            at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
            at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
            at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
            at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
            at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
            at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
            at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
            at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
            at com.exagrid.triage.recv.JarBreaker.processCurrentReportSection(JarBreaker.java:390)
            at com.exagrid.triage.recv.JarBreaker.processReportSegments(JarBreaker.java:353)
            at com.exagrid.triage.recv.JarBreaker.saveData(JarBreaker.java:79)
            at com.exagrid.triage.recv.ReportAnalyzer.analyzeReport(ReportAnalyzer.java:70)
            at com.exagrid.triage.threads.AnalyzerInstance.processWorkItem(AnalyzerInstance.java:121)
            at com.exagrid.triage.threads.AnalyzerInstance.run(AnalyzerInstance.java:51)
            at java.lang.Thread.run(Thread.java:619)

I am not sure if the problem is specifically related to the driver, or if it is a postgres problem. I did upgrade from postgres 8.1 to 8.3 and I still have this problem occurring. One option for me to get around this issue is to do a vacuum on the database. following the vacuum I do not see this problem. 

I saw this problem start up unexpectedly, it did not occur alongside an update of my source code so therefore it is hard to attribute it to something that I may have done. There is a bug that seems to follow the same path listed in the postgresql mailing lists:

http://archives.postgresql.org/pgsql-hackers/2006-10/msg00289.php

Shawn Chasse

Re: Pgsql jdbc driver 8.3 Build 603: Commit deadlock

От
Oliver Jowett
Дата:
Shawn Chasse wrote:
> Hi,
> I'm running the 8.3 build 603 jdbc4 driver on a windows server machine
> running postgres 8.3.3, hibernate 3.2, and java 1.6.
>
> Recently I have run into a problem where a commit call in my java code
> will propogate down to the driver and eventually deadlock waiting for a
> response. The specific call stack is as follows:
>
> "Analyzer" daemon prio=6 tid=0x67de4400 nid=0x1414 runnable
> [0x6890f000..0x6890fc18]
>        java.lang.Thread.State: RUNNABLE
>             at java.net.SocketInputStream.socketRead0(Native Method)
>             at java.net.SocketInputStream.read(SocketInputStream.java:129)
>             at
> org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
>             at
> org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
>             at
> org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
>             at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259)
>             at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1166)
>             at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:351)
>                   - locked <0x0a1db010> (a
> org.postgresql.core.v3.QueryExecutorImpl)
>             at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2674)

While your top-level code may be calling commit() on a Hibernate API,
the actual JDBC work that is going on doesn't involve a commit - it
looks like a batch insert to me.

The driver is waiting for results from the server, which presumably
never comes back. So you need to look at what the server is doing. (I
have no idea how to do that on a Windows box)

-O

Re: Pgsql jdbc driver 8.3 Build 603: Commit deadlock

От
Kris Jurka
Дата:

On Tue, 11 Nov 2008, Oliver Jowett wrote:

> Shawn Chasse wrote:
>>
>> Recently I have run into a problem where a commit call in my java code will
>> propogate down to the driver and eventually deadlock waiting for a
>> response.
>
> While your top-level code may be calling commit() on a Hibernate API, the
> actual JDBC work that is going on doesn't involve a commit - it looks like a
> batch insert to me.

It could be another example of this deadlock:

http://archives.postgresql.org/pgsql-jdbc/2008-10/msg00045.php

Shawn, are you doing batch executions that involve many parameters?

Kris Jurka

Re: Pgsql jdbc driver 8.3 Build 603: Commit deadlock

От
Oliver Jowett
Дата:
Kris Jurka wrote:
>
>
> On Tue, 11 Nov 2008, Oliver Jowett wrote:
>
>> Shawn Chasse wrote:
>>>
>>> Recently I have run into a problem where a commit call in my java
>>> code will propogate down to the driver and eventually deadlock
>>> waiting for a response.
>>
>> While your top-level code may be calling commit() on a Hibernate API,
>> the actual JDBC work that is going on doesn't involve a commit - it
>> looks like a batch insert to me.
>
> It could be another example of this deadlock:
>
> http://archives.postgresql.org/pgsql-jdbc/2008-10/msg00045.php

Hmm, but shouldn't that manifest as the driver blocking on a socket write?

In Shawn's case we're blocking on read.

-O

Re: Pgsql jdbc driver 8.3 Build 603: Commit deadlock

От
"Shawn Chasse"
Дата:
Thanks for the replies so far.

Re Kris large parameter count: How many would be considered large? The table that always seems to be the one getting the inserts when this occurs is defined as follows:
This definition has recently grown, but this was several months ago, and it only grew by the few columns at the end starting with ftpserver.

-- Table: subgrids

-- DROP TABLE subgrids;

CREATE TABLE subgrids
(
  site_id bigint NOT NULL,
  uuid character varying(36) NOT NULL,
  "name" character varying(255),
  label character varying(255),
  description character varying(255),
  "local" boolean,
  master_ip character varying(20),
  admin_status character varying(20),
  oper_status character varying(20),
  allow_one_site_hsm boolean,
  allow_nas_shares boolean,
  allow_d2d_shares boolean,
  report_id bigint NOT NULL,
  gridname character varying(255),
  targetbackuprate bigint,
  targetprotectionrate bigint,
  dnsserver character varying(255),
  supportsmtpaddress character varying(255),
  supportsmtpmailtype character varying(255),
  supportsmtpinfo boolean,
  supportsmtpwarning boolean,
  supportsmtperror boolean,
  supportsmtpaudit boolean,
  supportsmtpsendstatus boolean,
  mailsourceaddress character varying(255),
  mailsourcedelay character varying(255),
  mailsourcehost character varying(255),
  mailsourceport integer,
  ftpserver character varying(255),
  ftpuser character varying(255),
  ftppassword character varying(255),
  "hour" integer,
  "minute" integer,
  emailsupport boolean,
  ftpsupport boolean,
  CONSTRAINT subgrids_pkey PRIMARY KEY (site_id),
  CONSTRAINT fkb321f7ad839667ff FOREIGN KEY (report_id)
      REFERENCES reports (report_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE subgrids OWNER TO healthserver;
GRANT ALL ON TABLE subgrids TO healthserver;
GRANT SELECT, REFERENCES, TRIGGER ON TABLE subgrids TO triage;

-- Index: subgrid_report_id

-- DROP INDEX subgrid_report_id;

CREATE INDEX subgrid_report_id
  ON subgrids
  USING btree
  (report_id);

-- Index: subgrids_uuid

-- DROP INDEX subgrids_uuid;

CREATE INDEX subgrids_uuid
  ON subgrids
  USING btree
  (uuid);

The thing that baffles me the most is that a vacuum clears up this problem for a short length of time (several days, where i get a flood of new data on a nightly basis).

Shawn

On Mon, Nov 10, 2008 at 8:46 PM, Oliver Jowett <oliver@opencloud.com> wrote:
Kris Jurka wrote:


On Tue, 11 Nov 2008, Oliver Jowett wrote:

Shawn Chasse wrote:

Recently I have run into a problem where a commit call in my java code will propogate down to the driver and eventually deadlock waiting for a response.

While your top-level code may be calling commit() on a Hibernate API, the actual JDBC work that is going on doesn't involve a commit - it looks like a batch insert to me.

It could be another example of this deadlock:

http://archives.postgresql.org/pgsql-jdbc/2008-10/msg00045.php

Hmm, but shouldn't that manifest as the driver blocking on a socket write?

In Shawn's case we're blocking on read.

-O

Re: Pgsql jdbc driver 8.3 Build 603: Commit deadlock

От
Kris Jurka
Дата:

On Tue, 11 Nov 2008, Shawn Chasse wrote:

> Re Kris large parameter count: How many would be considered large? The table
> that always seems to be the one getting the inserts when this occurs is
> defined as follows:

Oliver is correct, the deadlock I was referring to would show different
symptoms, so this isn't it.  In this case you are likely looking at some
kind of a server problem.  Are you sure you're truly deadlocked, not just
waiting on something (check pg_locks)?

Kris Jurka

Re: Pgsql jdbc driver 8.3 Build 603: Commit deadlock

От
"Shawn Chasse"
Дата:
I believe so, This is a simple operation that should be fairly straightforward and typically happens hundreds of times per day. When this occurs, i have waited hours without it ever returning.

checking the system this is running on, cpu usage etc are all nil from my process that is working on this.

On Tue, Nov 11, 2008 at 2:26 PM, Kris Jurka <books@ejurka.com> wrote:


On Tue, 11 Nov 2008, Shawn Chasse wrote:

Re Kris large parameter count: How many would be considered large? The table
that always seems to be the one getting the inserts when this occurs is
defined as follows:

Oliver is correct, the deadlock I was referring to would show different symptoms, so this isn't it.  In this case you are likely looking at some kind of a server problem.  Are you sure you're truly deadlocked, not just waiting on something (check pg_locks)?

Kris Jurka