Re: FKs and deadlocks - Mailing list pgsql-general

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
duringa 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
severaljoins. 

I had a look at the the pg_class table, and found a relfilenode with OID 33044, the OID mentionned in the locks. This
refersto 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@megazone.bigpanda.com]
Envoyé : vendredi, 22. octobre 2004 15:30
À : Philippe Lang
Cc : pgsql-general@postgresql.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
youwant to send a possibly slightly sanitized typical sequence of events, we might be able to help with that part.