Re: FKs and deadlocks

Hello,

I got a deadlock in my database this morning. This time it happened in an UPDATE, but sometimes it's in an INSERT, or during a transaction too. Here is what I could gather before killing the offending processes:

ps -afx:
--------
7075 ?? I 0:00.72 postmaster: jldousse groupefpdb 172.17.10.37 idle (postgres)
7448 ?? I 0:00.01 postmaster: ldupuis groupefpdb 172.18.10.248 idle (postgres)
8756 ?? I 0:00.48 postmaster: lbeselga groupefpdb 172.17.10.30 idle (postgres)
9034 ?? I 0:00.01 postmaster: ybastide groupefpdb 172.18.10.249 idle (postgres)
9141 ?? I 0:00.24 postmaster: jdcurrat groupefpdb 172.18.10.253 idle (postgres)
10407 ?? I 0:00.90 postmaster: cdunand groupefpdb 172.18.10.245 idle (postgres)
11346 ?? R 236:43.23 postmaster: jlroubaty groupefpdb 172.17.10.14 UPDATE (postgres)
11439 ?? S 0:00.27 postmaster: jlroubaty groupefpdb 172.17.10.14 UPDATE waiting (postgres)
12345 ?? I 0:00.03 postmaster: jlroubaty groupefpdb 172.17.10.14 UPDATE waiting (postgres)
12397 ?? I 0:00.17 postmaster: jlroubaty groupefpdb 172.17.10.14 UPDATE waiting (postgres)
13167 ?? I 0:00.24 postmaster: lbielmann groupefpdb 172.17.10.29 idle (postgres)
13440 ?? I 0:00.07 postmaster: jmjordil groupefpdb 172.18.10.243 idle (postgres)
13668 ?? I 0:00.09 postmaster: candrey groupefpdb 172.17.10.43 idle (postgres)
13973 ?? I 0:00.63 postmaster: paruozzi groupefpdb 172.17.10.46 idle (postgres)
14059 ?? I 0:00.07 postmaster: pgsql groupefpdb 10.1.1.4 idle (postgres)
14073 ?? I 0:00.04 postmaster: hbourguet groupefpdb 172.17.10.23 idle (postgres)
14311 ?? S 0:00.27 postmaster: jmrisse groupefpdb 172.17.10.12 idle (postgres)
14339 ?? I 0:00.01 postmaster: nschroeter groupefpdb 172.17.10.3 idle (postgres)
14381 ?? I 0:00.14 postmaster: pgsql groupefpdb 10.1.1.4 idle (postgres)
14385 ?? I 0:00.01 postmaster: pgsql groupefpdb 10.1.1.4 idle (postgres)
97763 ?? I 0:00.13 postmaster: nbussard groupefpdb 172.18.10.252 idle (postgres)

SELECT * FROM pg_locks;
-----------------------
relation database transaction pid mode granted
6489299 12345 ShareLock f
1261 0 11346 AccessShareLock t
6489299 11346 ExclusiveLock t --> jlroubaty
1260 0 11346 AccessShareLock t
33308 32920 11346 AccessShareLock t
16759 32920 14385 AccessShareLock t
33044 32920 12397 AccessShareLock t
33044 32920 12397 RowExclusiveLock t --> jlroubaty
33211 32920 11346 AccessShareLock t
32939 32920 11346 AccessShareLock t
33044 32920 11346 AccessShareLock t
33044 32920 11346 RowExclusiveLock t --> jlroubaty
33308 32920 12397 AccessShareLock t
33308 32920 11439 AccessShareLock t
6489299 11439 ShareLock f
33044 32920 12345 AccessShareLock t
33044 32920 12345 RowExclusiveLock t --> jlroubaty
6489299 12397 ShareLock f
32937 32920 11346 AccessShareLock t
33044 32920 11439 AccessShareLock t
33044 32920 11439 RowExclusiveLock t --> jlroubaty
6514392 14385 ExclusiveLock t --> pgsql
6495858 11439 ExclusiveLock t --> jlroubaty
33018 32920 11346 AccessShareLock t
6496304 12345 ExclusiveLock t --> jlroubaty
33308 32920 12345 AccessShareLock t
6500291 12397 ExclusiveLock t --> jlroubaty

Apparently, a user has locked himself in the database. All "ExclusiveLock" and "RowExclusiveLock" are linked to the user "jlroubaty", except one, which is "pgsql".

"pgsql" username is sometimes used for statistics from Excel. An Excel sheet is linked through ODBC to a view which has several joins.

I had a look at the the pg_class table, and found a relfilenode with OID 33044, the OID mentionned in the locks. This refers to a table that has 4 FKs and 5 triggers. It's one of the cental tables in the database.

Any idea how I could dig further?

Philippe Lang

-----Message d'origine-----
De : Stephan Szabo [mailto:sszabo(at)megazone(dot)bigpanda(dot)com]
Envoyé : vendredi, 22. octobre 2004 15:30
À : Philippe Lang
Cc : pgsql-general(at)postgresql(dot)org
Objet : Re: [GENERAL] FKs and deadlocks

On Fri, 22 Oct 2004, Philippe Lang wrote:

> I have tried to correct that by adding a "SET CONSTRAINTS ALL DEFERRED"
> in every trigger and function, hoping it would solve my problem. Maybe
> it helped, but it did not solve anything.

Note that set constraints all deferred does nothing unless you made the constraint deferrable which is not the default. If your constraints aren't then you won't see any effect, and you'll probably want to change them and see if that does help (and possibly making them initially deferred at the same time).

> I don't know if anyone has a better idea, but I would like to try
> taking away some FKs in my schema. My problem is that I really don't
> know which one to delete. There are over 40 tables. Are there rules to
> do that? Or maybe can I simply wait on the next deadlock, and try
> understanding who got locked by who? OK, but how can I do that?

I think you may be able to do this if you turn on statement locking and try to resurrect the state from the logs. If you want to send a possibly slightly sanitized typical sequence of events, we might be able to help with that part.