473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

deadlock with vacuum full on 7.4.5

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
9 2141
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Sean Shanny | last post by:
To all, The facts: PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI drives in hardware RAID 0 configuration. Database size with indexes is currently 122GB. DB size...
6
by: Alex | last post by:
Hi, just a few questions on the Vaccum I run a vacuum analyze on the database every night as part of a maintenance job. During the day I have a job that loads 30-70,000 records into two...
0
by: Jim Seymour | last post by:
Hi, Environment: PostgreSQL 7.4.2 Locally built with GCC 3.3.1 Solaris 8 (Sparc) I have a relatively simple database created with...
0
by: Rajesh Kumar Mallah | last post by:
Greeting, Will it be an useful feature to be able to vacumm / analyze all tables in a given schema. eg VACUUM schema.* ; at least for me it will be a good feature.
15
by: Ed L. | last post by:
If I see VACUUM ANALYZE VERBOSE output like this... INFO: --Relation public.foo-- INFO: Index idx_foo_bar: Pages 219213; Tuples 28007: Deleted 9434. CPU 17.05s/4.58u sec elapsed 3227.62 sec. ...
6
by: spied | last post by:
look please http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=253295 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
4
by: Ilia Chipitsine | last post by:
Dear Sirs I'm about to write plpgsql function which will "vacuum full" all existing databases. Below is an example how to get list of databases. What should I write instead of "raise notice" ?...
9
by: Aleksey Serba | last post by:
Hello! I have 24/7 production server under high load. I need to perform vacuum full on several tables to recover disk space / memory usage frequently ( the server must be online during vacuum...
0
by: cwho.work | last post by:
Hi! We are using apache ibatis with our MySQL 5.0 database (using innodb tables), in our web application running on Tomcat 5. Recently we started getting a number of errors relating to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.