Обсуждение: How do I interpret the data returned from a DeadLock-Exception?(Java)

Hi folks.

I'm strugeling with a deadlock here, and was hoping that you could help
me out.

Software in use:
I'm using PostgreSQL 8.1.2 together with hibernate 3.1  and Java 1.5 in
windows XP.

The situation:
My java-application is producing dead locks quite frequently, and I
would like to resolve them. How do I interpret the data in the error
message? Are there any ways of finding out e.g what process 3224 is and
what it is doing? Which relation is nr 16409? what is tuple(0,25) and so
on. Any nice tools one could use?


-------ERROR MESSAGE-----------------------
2006-02-03 12:39:41,903 ERROR Thread-12
org.hibernate.util.JDBCExceptionReporter - ERROR: deadlock detected
Detail: Process 3224 waits for ExclusiveLock on tuple (0,25) of relation
30425 of database 16409; blocked by process 2632.
Process 2632 waits for ShareLock on transaction 96197; blocked by
process 2488.
Process 2488 waits for ExclusiveLock on tuple (0,25) of relation 30425
of database 16409; blocked by process 3224.
---------------------------------------------------

I'm looking forward to hearing from you.

Best Regards
Øyvind

=?ISO-8859-1?Q?=D8yvind_M=F8ller_Asbj=F8rnsen?= <oyvindma@tihlde.org> writes:
> My java-application is producing dead locks quite frequently, and I
> would like to resolve them. How do I interpret the data in the error
> message? Are there any ways of finding out e.g what process 3224 is and
> what it is doing? Which relation is nr 16409? what is tuple(0,25) and so
> on. Any nice tools one could use?

Well, you find out which database that is with
    select datname from pg_database where oid = 16409;
then you go into that database and find out the table involved with
    select relname from pg_class where oid = 30425;
and if you need to know the particular row then you go
    select * from that-table where ctid = '(0,25)';
However, what's probably going to be more interesting is to figure out
what SQL commmands are generating these errors.  If your application
doesn't give you any help with that then look in the postmaster log
(you may need to adjust the setting of log_min_error_statement).

> Detail: Process 3224 waits for ExclusiveLock on tuple (0,25) of relation
> 30425 of database 16409; blocked by process 2632.
> Process 2632 waits for ShareLock on transaction 96197; blocked by
> process 2488.
> Process 2488 waits for ExclusiveLock on tuple (0,25) of relation 30425
> of database 16409; blocked by process 3224.

This looks a little odd because as far as I can see, the first two lines
imply that process 2488 has already modified the row in question ... so
why does it need to take the row lock again?

When you've worked out which commands are producing the deadlock, if
it's not obvious to you that you did something wrong, please post the
details.

            regards, tom lane