Re: How do I interpret the data returned from a DeadLock-Exception?(Java)

=?ISO-8859-1?Q?=D8yvind_M=F8ller_Asbj=F8rnsen?= <oyvindma(at)tihlde(dot)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