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
[EMAIL PROTECTED] 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
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
Joseph Shraibman [EMAIL PROTECTED] 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
Why then when I did a kill -INT on the vacuuming backends did everything
unfreeze?
Tom Lane wrote:
Joseph Shraibman [EMAIL PROTECTED] 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
Joseph Shraibman [EMAIL PROTECTED] 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
That is what I wanted to know, how to get the evidence for next time.
Tom Lane wrote:
Joseph Shraibman [EMAIL PROTECTED] 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
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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
