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
Table "public.users"
Column | Type |
Modifiers
------------------+--------------------------------+---------------------------------------------------------
user_id | integer | not null default
nextval('users_user_id_seq'::regclass)
user_last_name | character varying(64) |
user_first_name | character varying(64) |
user_middle_name | character varying(64) |
univ_id | integer |
usrtyp_id | integer |
user_disabled | boolean | default false
customer_id | integer |
sysuser_id | integer |
user_dob | date |
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
"fk_customer_id" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_users_2" FOREIGN KEY (univ_id) REFERENCES universities(univ_id)
"fk_users_3" FOREIGN KEY (usrtyp_id) REFERENCES user_type(usrtyp_id) ON
UPDATE RESTRICT
"system_user_sysuser_id_fkey" FOREIGN KEY (sysuser_id) REFERENCES
system_users(sysuser_id) ON UPDATE RESTRICT ON DELETE RESTRICT
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
