Deadlock

On Thu, 3 Oct 2002, Nikhil G. Daddikar wrote:

While updating database (7.1.3) thro' JDBC I get a deadlock error :

Exception Message: ERROR: Deadlock detected.
See the lock(l) manual page for a possible cause.

Are you using any foreign keys to or from this table?

On Thu, 3 Oct 2002, Nikhil G. Daddikar wrote:

While updating database (7.1.3) thro' JDBC I get a deadlock error :

Exception Message: ERROR: Deadlock detected.
See the lock(l) manual page for a possible cause.

As far as i can see I am not doing anything which can cause such a thing, everything is pretty straight forward. Sometimes I get:

Cannot insert a duplicate key into unique index db_security_desc_object_id_key

I've looked at the code and these things shouldn't be happening, I am sure the mistake is mine but don't know how to go about debugging it. It happens only sometimes.

I do have unique key constrains on the table.

Any help will be appreciated.

Have you reset a sequence counter lately maybe? Or is the JDBC trying to
set the sequence number by itself instead of letting postgresql do it?

Yes. Also, I have about 65 tables and i never get a deadlock anywhere else, just here. However, this tables is accessed for almost all operation (read-mode) since all pages require security checks.

create table db_security_desc (
id integer primary key,
vdb_id integer,
ts timestamp not null,
object_id integer not null,
object_type integer not null,
strid varchar(100) not null,
company_id integer not null references db_company(id),
acl_id integer not null UNIQUE,
is_n_on_no_match char(1) not null,
is_n_on_conflict char(1) not null,
unique(object_id, object_type, company_id),
unique(strid, company_id)
);

----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Nikhil G. Daddikar" <ngd@celoxis.com>
Cc: "pggeneral" <pgsql-general@postgresql.org>
Sent: Friday, October 04, 2002 9:10 PM
Subject: Re: [GENERAL] Deadlock

: On Thu, 3 Oct 2002, Nikhil G. Daddikar wrote:
:
: > While updating database (7.1.3) thro' JDBC I get a deadlock error :
: >
: > Exception Message: ERROR: Deadlock detected.
: > See the lock(l) manual page for a possible cause.
:
: Are you using any foreign keys to or from this table?
:

On Sat, 5 Oct 2002, Nikhil G. Daddikar wrote:

Yes. Also, I have about 65 tables and i never get a deadlock anywhere
else, just here. However, this tables is accessed for almost all
operation (read-mode) since all pages require security checks.

Hmm, you might want to turn on query logging if you haven't and see
what else is going on the system. My first guess would be that the
overly strong fk locking is causing you trouble, perhaps there's
something that's waiting on a previous update you've done in that sessions
that's holding a lock the check wants to get.