Hello,

As the amount of simultaneous users of my database grows (25 users
sometimes, PGSQL 7.4.5), deadlocks are unfortunately more and more
frequent. I guess this is due to the FKs problem with Postgresql.

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.

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?

Thanks for your help!

Philippe

Note: I have read about a patch for FK's, is that something that can
really be used in production?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Fri, 22 Oct 2004, Philippe Lang wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com> writes:
Also look in pg_locks and pg_activity.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

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 FKsand 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.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:

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 think you may be able to do this if you turn on statement locking and tryto 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.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

"Philippe Lang" <philippe.lang (AT) attiksystem (DOT) ch> writes:
There is no deadlock here. The ungranted rows in pg_locks all point to
the transaction ID 6489299, which belongs to PID 11346, which is this
one:

An UPDATE that churns for hours and hours may well represent a bug in
your application (unconstrained join maybe?) but it's not a deadlock.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Thanks a lot Tom.

One more question: i'm surprised there are so many ExclusiveLocks when displaying pg_lock:

33044 32920 11439 RowExclusiveLock t
6514392 14385 ExclusiveLock t
6495858 11439 ExclusiveLock t
....etc...

I found in the documentation "EXCLUSIVE: This lock mode is not automatically acquired by any PostgreSQL command."

I'm not using any TABLE LOCK or SET TRANSACTION ISOLATION call in the wholedatabase, so where do they come from? I'm accessing the database through ODBC, is that maybe the reason?

Philippe


-----Message d'origine-----
De : Tom Lane [mailto:tgl (AT) sss (DOT) pgh.pa.us]
Envoyé : lundi, 25. octobre 2004 16:16
À : Philippe Lang
Cc : pgsql-general (AT) postgresql (DOT) org
Objet : Re: [GENERAL] FKs and deadlocks

"Philippe Lang" <philippe.lang (AT) attiksystem (DOT) ch> writes:
There is no deadlock here. The ungranted rows in pg_locks all point to thetransaction ID 6489299, which belongs to PID 11346, which is this
one:

An UPDATE that churns for hours and hours may well represent a bug in your application (unconstrained join maybe?) but it's not a deadlock.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Mon, Oct 25, 2004 at 05:56:04PM +0200, Philippe Lang wrote:

The bottom two are transaction locks. Those are held while the
transaction is running, and they are a fundamental part of MVCC. They
will only conflict if you try to update a tuple which has already been
updated by that transaction (and I think they are also used in UNIQUE
constraints and FK constraints.)

The RowExclusiveLock I'm not sure about, I think it may be from an ALTER
TABLE or something (assuming it's not on a system catalog).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La verdad no siempre es bonita, pero el hambre de ella sí"


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Philippe Lang" <philippe.lang (AT) attiksystem (DOT) ch> writes:
Those are the transaction ID locks.

With respect to *tables* this is a true statement. For *transactions*,
the owning backend takes ExclusiveLock on its transaction ID, and any
other backend that has to wait for that transaction tries to take
ShareLock on the ID, which will block it until the ExclusiveLock is
released at transaction end. We don't use any other lock modes with
transaction IDs. (This is documented in the pg_locks view's documentation.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)