Server error and deadlocks
Newbie here...
We have a web app with a MySQL (ISAM) db server and we're wanting to port
and run it on PostgreSQL. We've got data in PostgreSQL and code running
under Mercury Interactive "LoadRunner" but I'm seeing these messages on the
server:
------------------------------------------------------
WARNING: COMMIT: no transaction in progress
LOG: pq_recvbuf: unexpected EOF on client connection
ERROR: deadlock detected
ERROR: deadlock detected
ERROR: deadlock detected
. . .
------------------------------------------------------
Seems to me like commits are being made when there's nothing to commit.
There's gotta be some overhead here so I'm thinking unnecessary commits
should be removed from the code.
Also, on the EOF on client connection messages I'm thinking the cgi code
should explicitly disconnect. Does it matter?
Finally, there are LOTS of deadlocks and I'm thinking it's because they are
not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or they
are constantly updating the same rows. It seems our duhvelopers need to get
up to speed on transaction management with PostgreSQL. Any suggestions on
how to get them to do PostgreSQL transactions and wean them off MySQL ISAM?
TIA!!!!!!!!
db
On Mon, 13 Jan 2003, Orr, Steve wrote:
Finally, there are LOTS of deadlocks and I'm thinking it's because they are
not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or they
are constantly updating the same rows. It seems our duhvelopers need to get
Well, if you're using foreign keys, you might be running into a deficiency
in the foreign key implementation. If that is it, then currently a
partial workaround may be to make the constraints deferred which lessens
the length of the lock, but doesn't remove the base deadlock possibility.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
On Mon, 13 Jan 2003, Orr, Steve wrote:
Finally, there are LOTS of deadlocks and I'm thinking it's because they are
not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or they
are constantly updating the same rows. It seems our duhvelopers need to get
Well, if you're using foreign keys, you might be running into a deficiency
in the foreign key implementation.
If these folk are immigrants from MySQL, I bet they're not using foreign
keys. Taking table-level locks seems like a more likely route to
deadlock. But this is all guesswork --- we need to see some info about
the queries being issued before we can opine about the real cause.
regards, tom lane
Does anybody know if there is a plan to improve the foreign key support in
PostgreSQL?
While working with PostgreSQL 7.2.1 (Debian Linux/testing) we found out that
when a row is inserted in a table that has columns that are foreign keys,
Postgres normally locks the rows corresponding to the foreign keys (in their
original tables) both for reading and for writing. This is strange, because
it seems to me that it should allow reading from these rows (at least Oracle
8i does this) from other transactions. According to Postgres' logs, a SELECT
FOR UPDATE is executed on each of the foreign keys referenced in an INSERT,
UPDATE. Isn't this a little bit excessive?
This is a serious bottleneck in one application we've developed because we
have some basic tables from which foreign keys are referenced in a lot of
queries that cannot be executed in parallel because of this problem.
On Monday 13 January 2003 22:16, Stephan Szabo wrote:
On Mon, 13 Jan 2003, Orr, Steve wrote:
Finally, there are LOTS of deadlocks and I'm thinking it's because they
are not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or
they are constantly updating the same rows. It seems our duhvelopers need
to getWell, if you're using foreign keys, you might be running into a deficiency
in the foreign key implementation. If that is it, then currently a
partial workaround may be to make the constraints deferred which lessens
the length of the lock, but doesn't remove the base deadlock possibility.---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
Juan Jose Comellas
(juanjo@comellas.com.ar)
On Tue, 14 Jan 2003, Juan Jose Comellas wrote:
Does anybody know if there is a plan to improve the foreign key support in
PostgreSQL?
Umm, define plan. Seriously, I've been looking at it, but I've only got
a couple of hours a week in general, so it's not going terribly
well/quickly.
While working with PostgreSQL 7.2.1 (Debian Linux/testing) we found out that
when a row is inserted in a table that has columns that are foreign keys,
Postgres normally locks the rows corresponding to the foreign keys (in their
original tables) both for reading and for writing. This is strange, because
it seems to me that it should allow reading from these rows (at least Oracle
8i does this) from other transactions. According to Postgres' logs, a SELECT
FOR UPDATE is executed on each of the foreign keys referenced in an INSERT,
UPDATE. Isn't this a little bit excessive?
Yes, but there isn't a weaker lock that exists at the SQL statement level
currently that gives enough strength to actual guarantee the constraint.
Actually, getting the lock strength down is fairly easy with doing a
dirty read and some stuff with that so that you can say insert pointing
to the same row from concurrent transactions, it's dealing with the
created and existing deadlock conditions that's hard.
On Tuesday 14 January 2003 15:11, Stephan Szabo wrote:
On Tue, 14 Jan 2003, Juan Jose Comellas wrote:
Does anybody know if there is a plan to improve the foreign key support
in PostgreSQL?Umm, define plan. Seriously, I've been looking at it, but I've only got
a couple of hours a week in general, so it's not going terribly
well/quickly.
What I meant is: is the PostgreSQL development team aware of the problem? Is
there a proposed fix that's not implemented yet?
BTW, where in the Postgres sources can I find the code responsible for locking
foreign keys during and INSERT/UPDATE?
While working with PostgreSQL 7.2.1 (Debian Linux/testing) we found out
that when a row is inserted in a table that has columns that are foreign
keys, Postgres normally locks the rows corresponding to the foreign keys
(in their original tables) both for reading and for writing. This is
strange, because it seems to me that it should allow reading from these
rows (at least Oracle 8i does this) from other transactions. According to
Postgres' logs, a SELECT FOR UPDATE is executed on each of the foreign
keys referenced in an INSERT, UPDATE. Isn't this a little bit excessive?Yes, but there isn't a weaker lock that exists at the SQL statement level
currently that gives enough strength to actual guarantee the constraint.Actually, getting the lock strength down is fairly easy with doing a
dirty read and some stuff with that so that you can say insert pointing
to the same row from concurrent transactions, it's dealing with the
created and existing deadlock conditions that's hard.
Do you know of any way to decrease the lock strength without modifying
Postgres' sources?
Why should the weaker lock exist at the SQL statement level? Why can't you
have some kind of internal read-write lock so that you can lock a foreign key
for writing when inserting/updating a row that references it, but still allow
reading this foreign key from other transactions?
--
Juan Jose Comellas
(juanjo@comellas.com.ar)
On Tue, 14 Jan 2003, Juan Jose Comellas wrote:
On Tuesday 14 January 2003 15:11, Stephan Szabo wrote:
On Tue, 14 Jan 2003, Juan Jose Comellas wrote:
Does anybody know if there is a plan to improve the foreign key support
in PostgreSQL?Umm, define plan. Seriously, I've been looking at it, but I've only got
a couple of hours a week in general, so it's not going terribly
well/quickly.What I meant is: is the PostgreSQL development team aware of the problem? Is
there a proposed fix that's not implemented yet?
Yes and not entirely. There almost certainly is a complete
solution through the use of dirty reads and a bit of magic to prevent
deadlocks, but it's that bit of magic that I haven't managed to completely
wrap my brain around. If you look through the archives, you'll find an
incomplete patch for one of my tests for new code (doesn't entirely work).
I've started a couple more various attempts that haven't entirely worked
either, but as I said, I certainly haven't been spending alot of time on
it.
BTW, where in the Postgres sources can I find the code responsible for locking
foreign keys during and INSERT/UPDATE?
IIRC, backend/utils/adt/ri_triggers.c.
Yes, but there isn't a weaker lock that exists at the SQL statement level
currently that gives enough strength to actual guarantee the constraint.Actually, getting the lock strength down is fairly easy with doing a
dirty read and some stuff with that so that you can say insert pointing
to the same row from concurrent transactions, it's dealing with the
created and existing deadlock conditions that's hard.Do you know of any way to decrease the lock strength without modifying
Postgres' sources?Why should the weaker lock exist at the SQL statement level? Why can't you
have some kind of internal read-write lock so that you can lock a foreign key
for writing when inserting/updating a row that references it, but still allow
reading this foreign key from other transactions?
The triggers currently work by using SPI to run statements
(select/update/delete), so anything that the triggers want to do would
have to work within that framework unless the triggers were completely
rewritten.
Agreed but what I need rightnow is to find a "smoking gun" to beat the
duhvelopers on the head with in order to first get their attention. The
smoking gun would be an example of their poor SQL technique so I can ask
them, "What were you smoking when you wrote this junk?" In this context, is
there any way to create some sort of trace log of all the SQL submitted to
the server for a given time period, database, user, etc.? I can do this in
Oracle and I think it's a necessary feature.
Steve Orr
-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Tuesday, January 14, 2003 10:35 AM
To: Orr, Steve
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Server error and deadlocks
On Tue, 14 Jan 2003, Orr, Steve wrote:
You're right about the lack of foreign keys... it's a foreign concept to
the
DUHvelopers. I suspect table locks but are there any database level
tracing
tools to show the SQL? Will the trace_locks and debug_deadlocks parameters
help and how do I use them?
Actually, what you need is a code audit and some training for your
developers on how real transactions work and why they don't need to use
table level locks.
I'd have them study and learn from the section in the manual on MVCC and
then go back in and change their code to use transactions with select for
update instead of table locks.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Import Notes
Resolved by subject fallback
"Orr, Steve" <sorr@rightnow.com> writes:
Agreed but what I need rightnow is to find a "smoking gun" to beat the
duhvelopers on the head with in order to first get their attention. The
smoking gun would be an example of their poor SQL technique so I can ask
them, "What were you smoking when you wrote this junk?" In this context, is
there any way to create some sort of trace log of all the SQL submitted to
the server for a given time period, database, user, etc.?
See the logging options in postgresql.conf.
If you are interested, attached is the patch I just applied to CVS HEAD
to print out information about deadlocks. It should apply cleanly to
7.3.*. Sample output is
regression=# lock table tenk1;
NOTICE: Proc 27417 waits for AccessExclusiveLock on relation 18987 database 17054; blocked by 27415
NOTICE: Proc 27415 waits for ShareLock on transaction 6446; blocked by 27417
ERROR: deadlock detected
regression=#
regards, tom lane