By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,773 Members | 1,724 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,773 IT Pros & Developers. It's quick & easy.

deadlock with vacuum full on 7.4.5

P: n/a
I have a table that is usually really small (currently 316 rows) but
goes through spasams of updates in a small time window. Therefore I
have a vacuum full run every hour on this table.

Last night one of these vacuum fulls deadlocked with a query on this
table. Both were stuck doing nothing until I did a kill -INT on the
backends doing the vacuum.

So my questions:
1) What can I do to avoid this?
2) What do I do next time this happens to get more debugging info out of
the situation?

My postgres version:

PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Joseph Shraibman <jk*@selectacast.net> writes:
Last night one of these vacuum fulls deadlocked with a query on this
table. Both were stuck doing nothing until I did a kill -INT on the
backends doing the vacuum. So my questions:
1) What can I do to avoid this?
2) What do I do next time this happens to get more debugging info out of
the situation?


Look in pg_locks and pg_stat_activity.

I think it is highly unlikely that there was a deadlock inside the
database. Far more likely that both jobs were waiting on some
idle-in-transaction client whose transaction was holding a lock
on the table.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

P: n/a
Why then when I did a kill -INT on the vacuuming backends did everything
unfreeze?

Tom Lane wrote:
Joseph Shraibman <jk*@selectacast.net> writes:
Last night one of these vacuum fulls deadlocked with a query on this
table. Both were stuck doing nothing until I did a kill -INT on the
backends doing the vacuum.


So my questions:
1) What can I do to avoid this?
2) What do I do next time this happens to get more debugging info out of
the situation?

Look in pg_locks and pg_stat_activity.

I think it is highly unlikely that there was a deadlock inside the
database. Far more likely that both jobs were waiting on some
idle-in-transaction client whose transaction was holding a lock
on the table.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #3

P: n/a
Joseph Shraibman <jk*@selectacast.net> writes:
Why then when I did a kill -INT on the vacuuming backends did everything
unfreeze?


You could have had other stuff backed up behind the VACUUM FULL lock
requests.

It's not impossible that you had a deadlock *outside* the database,
that is some wait loop that is partially within and partially outside
the DB. But if you want me to believe there's a bug in our deadlock
detector, you're going to have to offer some actual evidence...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4

P: n/a
That is what I wanted to know, how to get the evidence for next time.

Tom Lane wrote:
Joseph Shraibman <jk*@selectacast.net> writes:
Why then when I did a kill -INT on the vacuuming backends did everything
unfreeze?

You could have had other stuff backed up behind the VACUUM FULL lock
requests.

It's not impossible that you had a deadlock *outside* the database,
that is some wait loop that is partially within and partially outside
the DB. But if you want me to believe there's a bug in our deadlock
detector, you're going to have to offer some actual evidence...

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #5

P: n/a
Joseph Shraibman wrote:
That is what I wanted to know, how to get the evidence for next time.


select * from pg_locks


Regards
Gaetano Mendola

Nov 23 '05 #6

P: n/a
jks
I have figured out the problem. When I do a BEGIN; and then a SELECT an
AccessShareLock is obtained on the table, and then not released until the
transaction is over. Then the vacuum comes in and tries to acquire an
exclusive lock, and in the process blocks any readers who are trying to
SELECT on the same table. My app reads from both db connections in the
same thread, thus the deadlock occurs.

So why isn't the AccessShareLock dropped as soon as the SELECT is over?

On Tue, 12 Oct 2004, Tom Lane wrote:
Joseph Shraibman <jk*@selectacast.net> writes:
Last night one of these vacuum fulls deadlocked with a query on this
table. Both were stuck doing nothing until I did a kill -INT on the
backends doing the vacuum.

So my questions:
1) What can I do to avoid this?
2) What do I do next time this happens to get more debugging info out of
the situation?


Look in pg_locks and pg_stat_activity.

I think it is highly unlikely that there was a deadlock inside the
database. Far more likely that both jobs were waiting on some
idle-in-transaction client whose transaction was holding a lock
on the table.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7

P: n/a
jks
I have figured out the problem. When I do a BEGIN; and then a SELECT an
AccessShareLock is obtained on the table, and then not released until the
transaction is over. Then the vacuum comes in and tries to acquire an
exclusive lock, and in the process blocks any readers who are trying to
SELECT on the same table. My app reads from both db connections in the
same thread, thus the deadlock occurs.

So why isn't the AccessShareLock dropped as soon as the SELECT is over?

On Tue, 12 Oct 2004, Tom Lane wrote:
Joseph Shraibman <jk*@selectacast.net> writes:
Last night one of these vacuum fulls deadlocked with a query on this
table. Both were stuck doing nothing until I did a kill -INT on the
backends doing the vacuum.

So my questions:
1) What can I do to avoid this?
2) What do I do next time this happens to get more debugging info out of
the situation?


Look in pg_locks and pg_stat_activity.

I think it is highly unlikely that there was a deadlock inside the
database. Far more likely that both jobs were waiting on some
idle-in-transaction client whose transaction was holding a lock
on the table.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #8

P: n/a
jk*@selectacast.net writes:
So why isn't the AccessShareLock dropped as soon as the SELECT is over?


In general, locks are held till transaction commit. See any basic
database text for the reasons why this is a good idea.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #9

P: n/a
jk*@selectacast.net writes:
So why isn't the AccessShareLock dropped as soon as the SELECT is over?


In general, locks are held till transaction commit. See any basic
database text for the reasons why this is a good idea.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.