Thread: FKs and deadlocks
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?
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.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Fri, 22 Oct 2004, Philippe Lang wrote:
>> 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.
Also look in pg_locks and pg_activity.
regards, tom lane
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.
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> I got a deadlock in my database this morning.
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:
> 11346 ?? R 236:43.23 postmaster: jlroubaty groupefpdb 172.17.10.14 UPDATE (postgres)
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
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 whole database, so where do they come from? I'm
accessingthe database through ODBC, is that maybe the reason?
Philippe
-----Message d'origine-----
De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
Envoyé : lundi, 25. octobre 2004 16:16
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] FKs and deadlocks
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> I got a deadlock in my database this morning.
There is no deadlock here. The ungranted rows in pg_locks all point to the transaction ID 6489299, which belongs to
PID11346, which is this
one:
> 11346 ?? R 236:43.23 postmaster: jlroubaty groupefpdb 172.17.10.14 UPDATE (postgres)
An UPDATE that churns for hours and hours may well represent a bug in your application (unconstrained join maybe?) but
it'snot a deadlock.
regards, tom lane
On Mon, Oct 25, 2004 at 05:56:04PM +0200, Philippe Lang wrote: > 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 > whole database, so where do they come from? I'm accessing the database > through ODBC, is that maybe the reason? 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í"
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> One more question: i'm surprised there are so many ExclusiveLocks when displaying pg_lock:
> 6514392 14385 ExclusiveLock t
> 6495858 11439 ExclusiveLock t
> ...etc...
Those are the transaction ID locks.
> I found in the documentation "EXCLUSIVE: This lock mode is not automatically acquired by any PostgreSQL command."
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