Обсуждение: Deadlock

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.
SometimesI 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
aboutdebugging it. It happens only sometimes.
 

I do have unique key constrains on the table.

Any help will be appreciated.

-Nikhil
PS: how do i lookup all the constraints for a table in postgres?

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.
SometimesI 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
goabout 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?

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.

Yes. Also, I have about 65 tables and i never get a deadlock anywhere else, just here. However, this tables is accessed
foralmost 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?
: