conflicting lock request message

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

conflicting lock request message

Mark Steben-3
Good morning,

I am running a vacuum on a large table that has had over 1 billion rows deleted and am getting this message:

   postgres@10.53.4.110:prime_production INFO:  "customer_attributes": suspending truncate due to conflicting lock request
Mar  6 07:52:29 primeprodpsql01 postgres[8252]: [1156-1] postgres@10.53.4.110:prime_production INFO:  "customer_attributes": truncated 32541952 to 32173088 pages

I am running postgres 9.4.  This message has been repeatedly sent now for about 30 minutes after index scans have completed.

We have never had this problem before.  I'm sure it has something to do with the massive delete.

Any suggestions?  Thank you.

--
Mark Steben
 Database Administrator
@utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089

t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com





Reply | Threaded
Open this post in threaded view
|

Re: conflicting lock request message

Jeff Janes
On Fri, Mar 6, 2020 at 7:59 AM Mark Steben <[hidden email]> wrote:
Good morning,

I am running a vacuum on a large table that has had over 1 billion rows deleted and am getting this message:

   postgres@10.53.4.110:prime_production INFO:  "customer_attributes": suspending truncate due to conflicting lock request
Mar  6 07:52:29 primeprodpsql01 postgres[8252]: [1156-1] postgres@10.53.4.110:prime_production INFO:  "customer_attributes": truncated 32541952 to 32173088 pages

It needs an exclusive lock in order to do the truncation (of empty pages off the end of the table).  When it detects someone is waiting on that lock, it suspends the truncation and releases the lock, and then tries to truncate more pages later when the lock is available again.

Eventually, it will either finish, or give up.

I am running postgres 9.4.  This message has been repeatedly sent now for about 30 minutes after index scans have completed.

We have never had this problem before.  I'm sure it has something to do with the massive delete.


This is just an informational message.  It does not indicate that there is a problem.  This might cause unusual latency among sessions trying to access the table, but unless you do observe such an issue (and it bothers you) then there is nothing which needs to be done.

 Cheers,

Jeff
Reply | Threaded
Open this post in threaded view
|

Re: conflicting lock request message

Mark Steben-3

thank you Jeff - appreciate the prompt response.

On Fri, Mar 6, 2020 at 8:47 AM Jeff Janes <[hidden email]> wrote:
On Fri, Mar 6, 2020 at 7:59 AM Mark Steben <[hidden email]> wrote:
Good morning,

I am running a vacuum on a large table that has had over 1 billion rows deleted and am getting this message:

   postgres@10.53.4.110:prime_production INFO:  "customer_attributes": suspending truncate due to conflicting lock request
Mar  6 07:52:29 primeprodpsql01 postgres[8252]: [1156-1] postgres@10.53.4.110:prime_production INFO:  "customer_attributes": truncated 32541952 to 32173088 pages

It needs an exclusive lock in order to do the truncation (of empty pages off the end of the table).  When it detects someone is waiting on that lock, it suspends the truncation and releases the lock, and then tries to truncate more pages later when the lock is available again.

Eventually, it will either finish, or give up.

I am running postgres 9.4.  This message has been repeatedly sent now for about 30 minutes after index scans have completed.

We have never had this problem before.  I'm sure it has something to do with the massive delete.


This is just an informational message.  It does not indicate that there is a problem.  This might cause unusual latency among sessions trying to access the table, but unless you do observe such an issue (and it bothers you) then there is nothing which needs to be done.

 Cheers,

Jeff


--
Mark Steben
 Database Administrator
@utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089

t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com