| 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: | Whole Thread | Raw Message | 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 |