On Mon, 17 Feb 2003 09:51:54 +0800, "Christopher Kings-Lynne" <[EMAIL PROTECTED]> wrote: >Strategy three: > >begin; >lock table in exclusive mode; >update row; >if (no rows affected) insert row; >commit; > >Problem - Works, but this table needs high concurrency.
Chris, distributing congestion might improve that. Instead of locking the whole table just lock one row in a dummy table: CREATE TABLE dummylock(id INT PRIMARY KEY); INSERT INTO dummylock VALUES (0); INSERT INTO dummylock VALUES (1); ... INSERT INTO dummylock VALUES (999); Create an immutable function lockhash(<type of PK>) returning a value between 0 and 999. BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE t SET c = 'newval' WHERE pk = 'pk'; if (no rows affected) THEN SELECT * FROM dummylock WHERE id = lockhash('pk') FOR UPDATE; -- try again UPDATE t SET c = 'newval' WHERE pk = 'pk'; if (no rows affected) THEN INSERT INTO t ...; END IF; END IF; COMMIT; This is just an idea. Completely untested ... Servus Manfred ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html