Re: Query regarding deadlock

On Thu, Nov 24, 2016 at 5:44 PM, Yogesh Sharma <
Yogesh1(dot)Sharma(at)nectechnologies(dot)in> wrote:

> Dear All,
>
> Thanks in advance.
> I found below deadlock in postgresql logs.
> I cannot change calling of REINDEX and insert query sequence because it is
> execute automatically through some cron script.
>
> ERROR: deadlock detected
> DETAIL: Process 2234 waits for AccessShareLock on relation 16459 of
> database 16385; blocked by process 4111.
> Process 4111 waits for ShareLock on relation 16502 of database 16385;
> blocked by process 2234.
> Process 2234: INSERT INTO table1 ( id , unique_id )VALUES( '1', '4')
> Process 4111: REINDEX TABLE table1
>
> Could you please provide any solution to resolve this deadlock.
>

What are tables 16459 and 16502? Are they related to each other through
triggers or FK constraints?

Are you reindexing multiple tables in the same transaction? If not, I
don't see why these should deadlock. One should win, and the other should
block.

If you are reindexing multiple tables in the same transaction, why are you
doing that? I can't think of a situation where you couldn't use separate
transactions per table.

Cheers,

Jeff