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/ ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html