Skip site navigation (1) Skip section navigation (2)

Re: Pgsql jdbc driver 8.3 Build 603: Commit deadlock

From: "Shawn Chasse" <shawnchasse(at)gmail(dot)com>
To: "Oliver Jowett" <oliver(at)opencloud(dot)com>
Cc: "Kris Jurka" <books(at)ejurka(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Pgsql jdbc driver 8.3 Build 603: Commit deadlock
Date: 2008-11-11 14:05:23
Message-ID: 1b81b11d0811110605r105da8e1ic1bfb4f43a9dd46d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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(at)opencloud(dot)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
>

In response to

Responses

pgsql-jdbc by date

Next:From: Ingmar LötzschDate: 2008-11-11 15:48:48
Subject: Re: Using SELECT IN with prepared statement
Previous:From: Alexander PanzhinDate: 2008-11-11 10:19:40
Subject: Re: Using SELECT IN with prepared statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group