Обсуждение: 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.