Thread: postgresql locks the whole table!
Help!
I have a table that multiple processes must be able to
write to concurrently. However, it for some reason
gets locked in exclusive mode. I narrowed it down to
one SQL statement + some weirdness with foreign keys.
To debug this, I opened two psql sessions and typed in
the sql statements manually. Here is the situation:
CREATE TABLE take2
(
id serial not null,
timestamp timestamp NOT NULL DEFAULT now(),
description text,
iteration smallint,
asset_id integer,
-- FOREIGN KEY (asset_id) REFERENCES public.asset
(id), -- ON UPDATE CASCADE ON DELETE CASCADE,
primary key(id)
);
(notice that the foreign key statement is commented
out). Just to make sure I am not causing excessive
locking unintentionally, I did "set transaction
isolation level read committed" in both psql shells
(default was serializable).
Now I type the following commands:
shell 1:
1. BEGIN
2. insert into take2 values(default, 'now()', 't1', 1,
1);
shell 2:
1. BEGIN
2. insert into take2 values(default, 'now()', 't2', 1,
1);
this works.
However, if I uncomment the foreign key statement and
recreate the table, then the second shell blocks on
the insert statement. As soon as the first transaction
is either committed or rolled back, the insert
statement goes through.
My question is why??? The two insert operations do not
conflict with each other (at least not in the
real-world situation). Also, why does the foreign key
make a difference?
looking at pg_locks, I see the following:
relation | database | transaction | pid |
mode | granted
----------+----------+-------------+-------+------------------+---------
39356 | 34862 | NULL | 18671 |
AccessShareLock | t
39356 | 34862 | NULL | 18671 |
RowExclusiveLock | t
NULL | NULL | 9914 | 18671 |
ExclusiveLock | t
39354 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
RowShareLock | t
16759 | 34862 | NULL | 18671 |
AccessShareLock | t
(7 rows)
Where does the ExclusiveLock come from? What is being
locked?
It is critical for us to run multiple transactions
concurrently -- in fact that was one of the reasons
for choosing PostgreSQL over MySQL. There are a lot of
file system operations and other processing that need
to happen along side the DB transaction. Those things
take a long time, so there is typically up to a
5-minute span between BEGIN and COMMIT. We cannot
block the production floor for 5 minutes when a user
tries to run a transaction, so as a temporary fix, we
got rid of the begin/commit. But obviously we would
rather not lose the atomicity.
So, in summary:
why does PostgreSQL lock the entire table?
what can we do about it?
This was tested on PostgreSQL 7.4.0 and 7.3.2.
thanks in advance,
Eugene
__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 - -- Dr NoName <spamacct11@yahoo.com> wrote: > Help! you may look in the archives; one day ago there was the same question. Ciao Alvar - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.org/ ** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html ** ODEM.org-Tour: http://tour.odem.org/ ** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQE/z51FOndlH63J86wRAooRAKCDV1tex7pn1XBDaphLP2ub5UBMEACfUPcg ewedvwMydauojzsrwQXddfo= =mLXm -----END PGP SIGNATURE-----
On Thu, 4 Dec 2003, Alvar Freude wrote:
> > Help!
>
> you may look in the archives; one day ago there was the same question.
>
Actually, this _is_ the question from a few days ago. A quick look at the
message headers shows that the message was held up for a couple of days by
one of the mailing list's servers.
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
by noon.pghoster.com with esmtp (Exim 4.24)
id 1AS0DB-0005f7-Td
for jpion@valhalla.homelinux.org; Thu, 04 Dec 2003 14:36:34 -0600
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
by svr1.postgresql.org (Postfix) with ESMTP id 21F63D1B4D6
for <pgsql-general-postgresql.org@localhost.postgresql.org>; Tue,
2 Dec 2003 23:02:52 +0000 (GMT)
^^^^^^^^^^^^^^^^^^^^^^^^^
I've noticed a fair bit of mail coming in where the answer shows up even
before the question does. Are there problems with the mail servers?
Jason
On Fri, 5 Dec 2003, Jason C. Pion wrote: > On Thu, 4 Dec 2003, Alvar Freude wrote: > > > > Help! > > > > you may look in the archives; one day ago there was the same question. > > > > Actually, this _is_ the question from a few days ago. A quick look at the > message headers shows that the message was held up for a couple of days by > one of the mailing list's servers. > > Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org) > by noon.pghoster.com with esmtp (Exim 4.24) > id 1AS0DB-0005f7-Td > for jpion@valhalla.homelinux.org; Thu, 04 Dec 2003 14:36:34 -0600 > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org > Received: from localhost (neptune.hub.org [200.46.204.2]) > by svr1.postgresql.org (Postfix) with ESMTP id 21F63D1B4D6 > for <pgsql-general-postgresql.org@localhost.postgresql.org>; Tue, > 2 Dec 2003 23:02:52 +0000 (GMT) > ^^^^^^^^^^^^^^^^^^^^^^^^^ > > I've noticed a fair bit of mail coming in where the answer shows up even > before the question does. Are there problems with the mail servers? It was probably originally sent from an unsubscribed account which meant it got put into the moderator queue. The user probably subscribed upon receipt of the message that it was being held and resent the message.