Thread: Query regarding deadlock

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.

Regards,
Yogesh

On 11/24/2016 5:44 PM, Yogesh Sharma wrote:
> I cannot change calling of REINDEX and insert query sequence because it is execute automatically through some cron
script.

any cron scripts are your own doing, so this statement makes no sense at
all.


--
john r pierce, recycling bits in santa cruz

Dear John,

Thanks for your support.
I mean to say, the REINDEX calls hourly and insert query executes every minute to update.
So, it might be race condition that these queries can call at same time.

If there is any solution like we can add some check before REINDEX operation performed.
If it is possible?

Regards,
Yogesh

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Friday, November 25, 2016 10:55 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Query regarding deadlock

On 11/24/2016 5:44 PM, Yogesh Sharma wrote:
> I cannot change calling of REINDEX and insert query sequence because it is execute automatically through some cron
script.

any cron scripts are your own doing, so this statement makes no sense at all.


-- 
john r pierce, recycling bits in santa cruz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2016-11-24 9:06 PM, Yogesh Sharma wrote:

> Dear John,
>
> Thanks for your support.
> I mean to say, the REINDEX calls hourly and insert query executes every minute to update.
> So, it might be race condition that these queries can call at same time.

Why do you need to run REINDEX every hour? That sounds like a stopgap
solution for another problem.
> If there is any solution like we can add some check before REINDEX operation performed.
> If it is possible?

Try to find out why the cron script is there in the first place. Then go
from there - eliminate it, or do it only in down periods.

>
> Regards,
> Yogesh
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
> Sent: Friday, November 25, 2016 10:55 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Query regarding deadlock
>
> On 11/24/2016 5:44 PM, Yogesh Sharma wrote:
>> I cannot change calling of REINDEX and insert query sequence because it is execute automatically through some cron
script.
> any cron scripts are your own doing, so this statement makes no sense at all.
>
>

On Fri, Nov 25, 2016 at 11:14 AM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:
> Does this mean that you reindex quite often based on a schedule. Personally I don't prefer that. To me it is like you
aretrying to fix something that is not broken. 
>
> Ideally reindex only what needs to be reindexed. I would not want to reindex a table in OLTP env.

Like VACUUM FULL, don't forget that REINDEX needs an exclusive lock
when working. As that's very intrusive, usually you take measures on
your database to be sure that you *never* require it, particularly if
this is a production instance.
--
Michael

Yogesh Sharma <Yogesh1.Sharma@nectechnologies.in> writes:
> 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

Offhand I would not expect those two commands to deadlock in isolation.
Are they parts of larger transactions that take additional locks?

            regards, tom lane