From: | Chris Campbell <chris(at)bignerdranch(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Deadlock with pg_dump? |
Date: | 2006-10-26 20:45:02 |
Message-ID: | 800AD8C1-A5B8-4735-806D-F253FAC3E8C4@bignerdranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
We're getting deadlock error messages in the production database logs
during times of inactivity, where the only other thing using the
database (we think) is the every-15-minutes pg_dump process. There
are still database connections up-and-running from unused Hibernate
Java processes, but they Shouldn't Be doing anything (and shouldn't
be holding locks, etc).
The deadlock error message looks like this:
ERROR: deadlock detected
DETAIL: Process 1120 waits for ShareLock on transaction 5847116;
blocked by process 1171.
Process 1171 waits for ExclusiveLock on tuple (6549,28) of relation
37637 of database 37574; blocked by process 1120.
Relation 37636 is the users table (schema attached).
Process 1120 was running an UPDATE query and changing a single row in
the users table. The users table does have foreign keys to 4 other
tables. Is it possible that those foreign key constraints acquire
locks in a different order than pg_dump (as it's SELECTing from the
tables), and it's hitting at *just* the right time to cause a deadlock?
I've tried to reproduce it on a test machine by running pgbench
(after adding foreign keys to the pgbench tables) and pg_dump in
tight loops in two concurrent shell scripts, but no deadlock.
Any ideas on how to track this down?
Under what conditions does a process acquire a ShareLock on another
transaction?
Thanks!
- Chris
Attachment | Content-Type | Size |
---|---|---|
schema.txt | text/plain | 1.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2006-10-26 20:58:21 | Re: plperl/plperlu interaction |
Previous Message | Andrej Ricnik-Bay | 2006-10-26 20:42:40 | Re: plperl/plperlu interaction |
From | Date | Subject | |
---|---|---|---|
Next Message | Jonah H. Harris | 2006-10-26 21:16:19 | Re: [HACKERS] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN |
Previous Message | Gregory Stark | 2006-10-26 20:40:27 | Re: [HACKERS] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN |